create or replace PACKAGE BODY ecm_drill as


PROCEDURE sw_targets_drill  (inventory_cur_out OUT cursorType,
                              external_name_in IN varchar2,
                              version_in IN varchar2,
                              target_name_in IN varchar2,
                              target_type_in IN varchar2,
			      targetList_in IN MGMT_INV_TARGET_LIST_OBJ_ARRAY) IS

BEGIN
  IF((target_name_in IS NULL) and (target_type_in IS NULL))
  THEN
    OPEN inventory_cur_out FOR   
      SELECT 
          inv.target_name, 
          inv.display_name, 
          inv.host_name,
          inv.display_target_name, 
          inv.container_name,
          inv.container_location, 
          inv.target_type,
          DECODE(advisory.home_location, NULL, 0, 1) as has_advisory
      FROM
          (SELECT UNIQUE
              t.target_name, 
              t.display_name, 
              t.host_name,
              s.display_target_name, 
              h.container_name,
              h.container_location, 
              t.target_type
          FROM 
              mgmt_inv_component c, 
	      mgmt_inv_versioned_patch ivp,
              mgmt_ecm_snapshot s,
              mgmt_inv_container h, 
              mgmt_targets t, 
              mgmt_targets host,
              mgmt_target_properties p,
              mgmt_target_type_component_map m,
	      TABLE(CAST(targetList_in AS MGMT_INV_TARGET_LIST_OBJ_ARRAY)) ttlist
          WHERE c.external_name  = external_name_in
            and decode(ivp.version, NULL, c.version, ivp.version)  = version_in
	    and c.component_guid = ivp.component_guid(+)
            and c.container_guid = h.container_guid 
            and h.snapshot_guid  = s.snapshot_guid
            and s.is_current = 'Y'
            and s.snapshot_type  = 'host_configuration'
            and t.host_name = host.target_name
            and host.target_name = s.target_name
            and host.target_type = s.target_type
            and t.target_guid = p.target_guid
            and p.property_name = 'OracleHome'
            and p.property_value = h.container_location
            and t.target_type = m.target_type
            and m.component_name = c.name
	    and m.target_type = ttlist.target_type
	    and t.target_type = ttlist.target_type
	    and ((m.property_name IS NULL and m.property_value IS NULL and ttlist.property_name IS NULL 
	            and ttlist.property_value IS NULL) or
		    exists (select * from mgmt_target_properties prop where       
                        prop.target_guid = t.target_guid
                        and m.property_name = ttlist.property_name 
			and m.property_value = ttlist.property_value
                        and ttlist.property_name = prop.property_name 
		        and ttlist.property_value = prop.property_value ))
          ) inv,
          (SELECT UNIQUE
                hrv.column_str_value as home_location,
                orv.column_str_value as host_name
           FROM
                mgmt_policies p,
                mgmt_current_violation v,
                mgmt_violation_context hrv,
                mgmt_violation_context orv
           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.exempt_code = 0 or
                 (v.exempt_code = 2 and v.exempt_until <= SYSDATE))     
             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 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'
          ) advisory         
      WHERE inv.target_name = advisory.host_name(+)
        and inv.container_location = advisory.home_location(+);          
  ELSIF(NOT ((target_name_in IS  NULL) or (target_type_in IS NULL))) 
  THEN
    OPEN inventory_cur_out FOR
      SELECT 
          inv.target_name, 
          inv.display_name, 
          inv.host_name,
          inv.display_target_name, 
          inv.container_name,
          inv.container_location, 
          inv.target_type,
          DECODE(advisory.home_location, NULL, 0, 1) as has_advisory
      FROM
          (SELECT UNIQUE 
              t.target_name as target_name, 
              t.display_name as display_name, 
              t.host_name,
              s.display_target_name, 
              h.container_name,
              h.container_location, 
              t.target_type as target_type
          FROM 
              mgmt_inv_component c, 
	      mgmt_inv_versioned_patch ivp,
              mgmt_ecm_snapshot s,
              mgmt_inv_container h, 
              mgmt_flat_target_assoc tm,
	      mgmt_targets source_target,
              mgmt_targets t,
	      mgmt_targets host,
              mgmt_target_properties p,
              mgmt_target_type_component_map m,
	      mgmt_target_properties prop,
	      TABLE(CAST(targetList_in AS MGMT_INV_TARGET_LIST_OBJ_ARRAY)) ttlist	      
          WHERE tm.is_membership = 1 
	    and source_target.target_name = target_name_in 
            and source_target.target_type = target_type_in
            and tm.source_target_guid = source_target.target_guid
            and tm.assoc_target_guid = t.target_guid
            and c.external_name  = external_name_in
            and decode(ivp.version, NULL, c.version, ivp.version) = version_in
	    and c.component_guid = ivp.component_guid(+)
            and c.container_guid = h.container_guid 
            and h.snapshot_guid = s.snapshot_guid
            and s.is_current = 'Y'
            and s.snapshot_type = 'host_configuration'
            and t.host_name = s.target_name
	    and s.target_name = host.target_name
	    and s.target_type = host.target_type
            and tm.assoc_target_guid = p.target_guid
            and p.property_name  = 'OracleHome'
            and p.property_value = h.container_location
            and t.target_type = m.target_type
            and m.component_name = c.name
	    and m.target_type = ttlist.target_type
	    and t.target_type = ttlist.target_type
	    and ((m.property_name IS NULL and m.property_value IS NULL and ttlist.property_name IS NULL 
	            and ttlist.property_value IS NULL) or
		    exists (select * from mgmt_target_properties prop where       
                        prop.target_guid = t.target_guid
                        and m.property_name = ttlist.property_name 
			and m.property_value = ttlist.property_value
                        and ttlist.property_name = prop.property_name 
		        and ttlist.property_value = prop.property_value ))
        ) inv,
          (SELECT UNIQUE
                hrv.column_str_value as home_location,
                orv.column_str_value as host_name
           FROM
                mgmt_policies p,
                mgmt_current_violation v,
                mgmt_violation_context hrv,
                mgmt_violation_context orv
           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.exempt_code = 0 or
                 (v.exempt_code = 2 and v.exempt_until <= SYSDATE))     
             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 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'
          ) advisory         
      WHERE inv.target_name = advisory.host_name(+)
        and inv.container_location = advisory.home_location(+);           
  END IF;
end sw_targets_drill;

PROCEDURE sw_installs_drill (inventory_cur_out OUT cursorType,
                             product_version_in IN varchar2,
                             product_name_in IN varchar2,
                             target_name_in IN varchar2,
                             target_type_in IN varchar2,
			     targetList_in IN MGMT_INV_TARGET_LIST_OBJ_ARRAY) IS

BEGIN

  IF((target_name_in IS NULL) and (target_type_in IS NULL))
  THEN
    OPEN inventory_cur_out FOR
      SELECT 
          inv.display_target_name as display_target_name,
          inv.target_name as target_name,
          inv.container_name as container_name,
          inv.container_location as container_location,
          DECODE(advisory.home_location, NULL, 0, 1) as has_advisory
      FROM
          (SELECT UNIQUE
                s.display_target_name,
                s.target_name, 
                ct.container_name,
                ct.container_location
            FROM
                mgmt_inv_component cp,
		mgmt_inv_versioned_patch ivp,
                mgmt_ecm_snapshot s,
                mgmt_inv_container ct,
                mgmt_targets host,
		mgmt_target_type_component_map map,
		TABLE(CAST(targetList_in AS MGMT_INV_TARGET_LIST_OBJ_ARRAY)) ttlist
            WHERE host.target_type = 'host'
              and host.target_name = s.target_name
              and host.target_type = s.target_type
              and cp.container_guid = ct.container_guid
              and ct.snapshot_guid  = s.snapshot_guid
              and s.is_current = 'Y'
              and s.snapshot_type = 'host_configuration'
              and cp.external_name = product_name_in
              and decode(ivp.version, NULL, cp.version, ivp.version) = product_version_in
	      and cp.component_guid = ivp.component_guid(+)
	      and cp.name = map.component_name
	      and map.target_type = ttlist.target_type
	      and ((map.property_name IS NULL and map.property_value IS NULL and ttlist.property_name IS NULL 
	            and ttlist.property_value IS NULL) or
		   (map.property_name = ttlist.property_name and map.property_value = ttlist.property_value))
           ) inv,
          (SELECT UNIQUE
                hrv.column_str_value as home_location,
                orv.column_str_value as host_name
           FROM
                mgmt_policies p,
                mgmt_current_violation v,
                mgmt_violation_context hrv,
                mgmt_violation_context orv
           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.exempt_code = 0 or
                 (v.exempt_code = 2 and v.exempt_until <= SYSDATE))     
             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 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'
          ) advisory         
      WHERE inv.target_name = advisory.host_name(+)
        and inv.container_location = advisory.home_location(+);
  ELSIF(NOT ((target_name_in IS NULL) or (target_type_in IS NULL)))
  THEN
    OPEN inventory_cur_out FOR
      SELECT 
          inv.display_target_name as display_target_name,
          inv.target_name as target_name,
          inv.container_name as container_name,
          inv.container_location as container_location,
          DECODE(advisory.home_location, NULL, 0, 1) as has_advisory
      FROM
          (SELECT UNIQUE
            display_target_name,
            target_name,
            container_name,
            container_location
          FROM
          (
            SELECT UNIQUE
                v.display_host_name as display_target_name, 
                v.host_name as target_name, 
                v.container_name as container_name,
                v.container_location as container_location
            FROM 
	      em$ecm_installs_groups v,
	      TABLE(CAST(targetList_in AS MGMT_INV_TARGET_LIST_OBJ_ARRAY)) ttlist
            WHERE v.composite_target_name = target_name_in 
              and v.composite_target_type = target_type_in
              and v.external_name = product_name_in
              and v.version = product_version_in
	      and v.software_target_type = ttlist.target_type
	      and ((v.property_name IS NULL and v.property_value IS NULL and ttlist.property_name IS NULL 
	            and ttlist.property_value IS NULL) or
		    exists (select * from mgmt_target_properties prop where       
                        prop.target_guid = v.software_target_guid
                        and v.property_name = ttlist.property_name 
			and v.property_value = ttlist.property_value
                        and ttlist.property_name = prop.property_name 
		        and ttlist.property_value = prop.property_value ))
          )
          UNION
          (
            SELECT UNIQUE    
                v.display_host_name as display_target_name, 
                v.host_name as target_name, 
                v.container_name as container_name,
                v.container_location as container_location
            FROM
	      em$ecm_host_installs_groups v,
	      TABLE(CAST(targetList_in AS MGMT_INV_TARGET_LIST_OBJ_ARRAY)) ttlist
            WHERE v.composite_target_name = target_name_in 
              and v.composite_target_type = target_type_in 
              and v.external_name = product_name_in 
              and v.version = product_version_in
	      and v.target_type = ttlist.target_type
	      and ((v.property_name IS NULL and v.property_value IS NULL and ttlist.property_name IS NULL 
	            and ttlist.property_value IS NULL) or
		   (v.property_name = ttlist.property_name and v.property_value = ttlist.property_value))
	  )
        ) inv,
          (SELECT UNIQUE
                hrv.column_str_value as home_location,
                orv.column_str_value as host_name
           FROM
                mgmt_policies p,
                mgmt_current_violation v,
                mgmt_violation_context hrv,
                mgmt_violation_context orv
           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.exempt_code = 0 or
                 (v.exempt_code = 2 and v.exempt_until <= SYSDATE))     
             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 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'
          ) advisory         
      WHERE inv.target_name = advisory.host_name(+)
        and inv.container_location = advisory.home_location(+);              
  END IF;


end sw_installs_drill;

PROCEDURE sw_patches_drill(inventory_cur_out OUT cursorType,
         total_homes_out OUT number,
                           product_version_in IN varchar2,
                           product_name_in IN varchar2,
                           target_name_in IN varchar2,
                           target_type_in IN varchar2,
			   targetList_in IN MGMT_INV_TARGET_LIST_OBJ_ARRAY) IS

BEGIN
  IF((target_name_in IS NULL) and (target_type_in IS NULL))
  THEN
    OPEN inventory_cur_out FOR
      SELECT 
        patches.patchid as id, 
        count(*) as count 
      FROM
      ( 
        SELECT DISTINCT 
          cont.container_guid,
          patch.id as patchid
        FROM
          mgmt_inv_container cont,
          mgmt_inv_patch patch,
          mgmt_inv_component  component,
	  mgmt_inv_versioned_patch ivp,
          mgmt_target_type_component_map map,
          mgmt_ecm_snapshot s,
          mgmt_targets t,
	  TABLE(CAST(targetList_in AS MGMT_INV_TARGET_LIST_OBJ_ARRAY)) ttlist
        WHERE
          s.is_current = 'Y' and
          s.snapshot_type = 'host_configuration' and
          s.target_name = t.target_name and
          s.target_type = t.target_type and
          s.snapshot_guid = cont.snapshot_guid and
          cont.container_guid = patch.container_guid and
          cont.container_guid = component.container_guid and
          component.name = map.component_name and
	  map.target_type = ttlist.target_type and
	  ((map.property_name IS NULL and map.property_value IS NULL and ttlist.property_name IS NULL 
	            and ttlist.property_value IS NULL) or
		   (map.property_name = ttlist.property_name and map.property_value = ttlist.property_value)) and
          component.external_name = product_name_in and 
          decode(ivp.version, NULL, component.version, ivp.version) = product_version_in and
	  component.component_guid = ivp.component_guid(+)
      ) patches
      GROUP BY patches.patchid;

    SELECT count(distinct cont2.container_guid) into total_homes_out
    FROM
      mgmt_inv_container cont2,
      mgmt_inv_component comp2,
      mgmt_inv_versioned_patch ivp,
      mgmt_ecm_snapshot s2,
      mgmt_targets t2,
      TABLE(CAST(targetList_in AS MGMT_INV_TARGET_LIST_OBJ_ARRAY)) ttlist,
      mgmt_target_type_component_map map
    WHERE
      s2.is_current = 'Y' and
      s2.snapshot_type = 'host_configuration' and
      s2.target_name = t2.target_name and
      s2.target_type = t2.target_type and
      cont2.snapshot_guid = s2.snapshot_guid and
      cont2.container_guid = comp2.container_guid and
      comp2.external_name = product_name_in and
      decode(ivp.version, NULL, comp2.version, ivp.version) = product_version_in and
      comp2.component_guid = ivp.component_guid(+) and
      map.component_name = comp2.name and
      map.target_type = ttlist.target_type and
      ((map.property_name IS NULL and map.property_value IS NULL and ttlist.property_name IS NULL 
	            and ttlist.property_value IS NULL) or
		   (map.property_name = ttlist.property_name and map.property_value = ttlist.property_value));

  ELSIF(NOT ((target_name_in IS NULL) or (target_type_in IS NULL)))
  THEN
    OPEN inventory_cur_out FOR
      SELECT
        id as id,
	count (distinct container_guid) as count
      FROM
        (
	  (
	    SELECT
	      patch.id as id,
	      v.container_guid as container_guid
	    FROM
	      em$ecm_installs_groups v,
	      mgmt_inv_patch patch,
	      TABLE(CAST(targetList_in AS MGMT_INV_TARGET_LIST_OBJ_ARRAY)) ttlist
	    WHERE v.composite_target_name = target_name_in
	      and v.composite_target_type = target_type_in
	      and v.container_guid = patch.container_guid
	      and v.external_name= product_name_in
	      and v.version= product_version_in
	      and v.software_target_type = ttlist.target_type
	      and ((v.property_name IS NULL and v.property_value IS NULL and ttlist.property_name IS NULL 
	            and ttlist.property_value IS NULL) or
		    exists (select * from mgmt_target_properties prop where       
                        prop.target_guid = v.software_target_guid
                        and v.property_name = ttlist.property_name 
			and v.property_value = ttlist.property_value
                        and ttlist.property_name = prop.property_name 
		        and ttlist.property_value = prop.property_value ))
	  )
	  UNION
	  (
	    SELECT
	      patch.id as id,
	      v.container_guid as container_guid
	    FROM
	      em$ecm_host_installs_groups v,
	      mgmt_inv_patch patch,
	      TABLE(CAST(targetList_in AS MGMT_INV_TARGET_LIST_OBJ_ARRAY)) ttlist
	    WHERE
	      v.composite_target_name = target_name_in
	      and v.composite_target_type = target_type_in
	      and v.container_guid = patch.container_guid
	      and v.external_name= product_name_in
	      and v.version= product_version_in
	      and v.target_type = ttlist.target_type
	      and ((v.property_name IS NULL and v.property_value IS NULL and ttlist.property_name IS NULL 
	            and ttlist.property_value IS NULL) or
		   (v.property_name = ttlist.property_name and v.property_value = ttlist.property_value))
	  )
	)
      GROUP BY id;
      
  
    SELECT count(distinct container_guid)  into total_homes_out
    FROM
      (
        (
          SELECT v.container_guid as container_guid
	  FROM
	    em$ecm_installs_groups v,
	    TABLE(CAST(targetList_in AS MGMT_INV_TARGET_LIST_OBJ_ARRAY)) ttlist
	  WHERE v.composite_target_name = target_name_in
	    and v.composite_target_type = target_type_in
	    and v.external_name= product_name_in
	    and v.version= product_version_in
	    and v.software_target_type = ttlist.target_type
	    and ((v.property_name IS NULL and v.property_value IS NULL and ttlist.property_name IS NULL 
	            and ttlist.property_value IS NULL) or
		    exists (select * from mgmt_target_properties prop where       
                        prop.target_guid = v.software_target_guid
                        and v.property_name = ttlist.property_name 
			and v.property_value = ttlist.property_value
                        and ttlist.property_name = prop.property_name 
		        and ttlist.property_value = prop.property_value ))
	)
	UNION
	(
          SELECT v.container_guid as container_guid
	  FROM
	    em$ecm_host_installs_groups v,
	    TABLE(CAST(targetList_in AS MGMT_INV_TARGET_LIST_OBJ_ARRAY)) ttlist
	  WHERE
	    v.composite_target_name = target_name_in
	    and v.composite_target_type = target_type_in
	    and v.external_name= product_name_in
	    and v.version= product_version_in
	    and v.target_type = ttlist.target_type
	    and ((v.property_name IS NULL and v.property_value IS NULL and ttlist.property_name IS NULL 
	            and ttlist.property_value IS NULL) or
		   (v.property_name = ttlist.property_name and v.property_value = ttlist.property_value)) 
	)
      );

  END IF; 
end sw_patches_drill;

PROCEDURE hw_hosts_drill(inventory_cur_out OUT cursorType,
                         system_config_in IN varchar2,
                         machine_arch_in IN varchar2, 
                         target_name_in IN varchar2,
                         target_type_in IN varchar2) IS

BEGIN
  IF((target_name_in IS NULL) and (target_type_in IS NULL))
  THEN
    OPEN inventory_cur_out FOR
      SELECT UNIQUE
          hostname,      
          osname,
          hwname
      FROM
        ( 
          SELECT
            t.target_name as hostname,
            (o.name || ' ' || o.base_version || ' ' || o.update_level) as osname,
            (hw.system_config || ' ' || hw.machine_architecture) as hwname
          FROM
            mgmt_hc_hardware_master hw,
            mgmt_hc_os_summary o,
            mgmt_targets t,
            mgmt_ecm_snapshot s
          WHERE
            s.snapshot_guid = hw.snapshot_guid and
            s.snapshot_guid = o.snapshot_guid and
            s.target_name = t.target_name and
            s.target_type = t.target_type and
            s.is_current = 'Y' and
            s.snapshot_type = 'host_configuration' and
            s.target_type = 'host' and
            NVL(hw.system_config, ' ') = system_config_in and
            NVL(hw.machine_architecture, ' ') = machine_arch_in
        );
  ELSIF(NOT ((target_name_in IS NULL) or (target_type_in IS NULL)))
  THEN
  --we have to modify this code somewhat - the hosts on which the OCS targets reside are not
  --"in" the composite target, so they don't turn up in that recursive query we do. what we have
 --to do is take the master list and filter by whether or not any of the targets we get from
--the target space query has a host that is from the master list.
    OPEN inventory_cur_out FOR
      SELECT UNIQUE
        hw_list.host_name as hostname,
        hw_list.osname || ' ' || hw_list.osversion || ' ' || hw_list.oslevel as osname,
        hw_list.sc || ' ' || hw_list.ma as hwname
      FROM
       (
         SELECT UNIQUE  
           hw.system_config as sc,
           hw.machine_architecture as ma,
           lt.hname             as host_name,
           o.name as osname,
           o.base_version as osversion,
           o.update_level as oslevel
         FROM
           mgmt_hc_hardware_master hw,
           mgmt_hc_os_summary o,
           mgmt_ecm_snapshot ps,
           (
             select unique
               t.host_name as hname
             FROM
               mgmt_targets t,
               (  SELECT m.assoc_target_guid as mguid
                  FROM mgmt_flat_target_assoc m,
		    mgmt_targets source_target
                  WHERE m.is_membership = 1
                    and m.source_target_guid = source_target.target_guid 
		    and source_target.target_name = target_name_in
                    and source_target.target_type = target_type_in
              UNION
                 SELECT UNIQUE t.target_guid as mguid
                 FROM  mgmt_targets t
                 WHERE target_name = target_name_in
                    and target_type = target_type_in
               ) lt1
             WHERE
               lt1.mguid = t.target_guid
           )lt
         WHERE
               ps.snapshot_guid   = hw.snapshot_guid and
               ps.is_current      = 'Y' and
               ps.snapshot_type   = 'host_configuration' and
               lt.hname            = ps.target_name and
               o.snapshot_guid = hw.snapshot_guid
       ) hw_list
    WHERE
      NVL(hw_list.sc, ' ') = system_config_in and
      NVL(hw_list.ma, ' ') = machine_arch_in;

  END IF;

end hw_hosts_drill;


PROCEDURE os_hosts_drill(inventory_cur_out OUT cursorType,
                         osName_in IN varchar2,
                         osVersion_in IN varchar2,
                         patchLevel_in IN varchar2,
                         target_name_in IN varchar2,
                         target_type_in IN varchar2) IS
BEGIN
  IF((target_name_in IS NULL) and (target_type_in IS NULL))
  THEN
    OPEN inventory_cur_out FOR
       SELECT 
         hostname, 
         hwname, 
         osname 
       FROM
        ( 
          SELECT 
            t.target_name as hostname, 
            hw.system_config  || ' '  || Machine_architecture as hwname, 
             o.name || ' ' || o.base_version || ' ' ||  o.update_level as osname 
          FROM 
            mgmt_hc_hardware_master hw, 
            mgmt_hc_os_summary o, 
            mgmt_targets t, 
            mgmt_ecm_snapshot s 
          WHERE 
              s.snapshot_guid = hw.snapshot_guid and 
              s.snapshot_guid = o.snapshot_guid and 
              s.target_name = t.target_name and 
              s.target_type = t.target_type and 
              s.is_current = 'Y' and 
              s.snapshot_type = 'host_configuration' and 
              s.target_type = 'host' and 
              NVL(o.name, ' ') = osName_in and 
              NVL(o.base_version, ' ') = osVersion_in and 
              NVL(o.update_level, ' ') = patchLevel_in 
        );
  ELSIF(NOT ((target_name_in IS NULL) or (target_type_in IS NULL)))
  THEN
  --we'll have to do the same thing here that we did for the HW case; just get all the hosts
  --linked to this composite target, and then filter by whether or not they have the os we want

    OPEN inventory_cur_out FOR
       SELECT UNIQUE
        hw_list.host_name as hostname,
        hw_list.osname || ' ' || hw_list.osversion || ' ' || hw_list.oslevel as osname,
        hw_list.sc || ' ' || hw_list.ma as hwname
      FROM
        (
          SELECT UNIQUE  
            hw.system_config as sc,
            hw.machine_architecture as ma,
            lt.hname             as host_name,
            o.name as osname,
            o.base_version as osversion,
            o.update_level as oslevel
          FROM
            mgmt_hc_hardware_master hw,
            mgmt_hc_os_summary o,
            mgmt_ecm_snapshot ps,
            (
              select
                 unique t.host_name as hname
              FROM
                mgmt_targets t,
                  ( 
                    SELECT UNIQUE m.assoc_target_guid as mguid
                     FROM
                      mgmt_flat_target_assoc m,
		      mgmt_targets source_target
                     WHERE m.is_membership = 1 
                       and m.source_target_guid = source_target.target_guid
		       and source_target.target_name = target_name_in 
		       and source_target.target_type = target_type_in
                   UNION
                      SELECT t.target_guid as mguid
                     FROM  mgmt_targets t
                    WHERE target_name = target_name_in and target_type = target_type_in
                ) lt1
              WHERE
                lt1.mguid = t.target_guid
            )lt
          WHERE
            ps.snapshot_guid   = hw.snapshot_guid and
             ps.is_current      = 'Y' and
             ps.snapshot_type   = 'host_configuration' and
             lt.hname            = ps.target_name and
             o.snapshot_guid = hw.snapshot_guid
        ) hw_list
      WHERE
        NVL(hw_list.osname, ' ') = osName_in and 
        NVL(hw_list.osversion, ' ') = osVersion_in and 
        NVL(hw_list.oslevel, ' ') = patchLevel_in;      
  END IF;


end os_hosts_drill;

PROCEDURE os_patches_drill(inventory_cur_out OUT cursorType,
                           total_count_out OUT NUMBER,
                           osName_in IN varchar2,
                           osVersion_in IN varchar2,
                           patchLevel_in IN varchar2,
                           target_name_in IN varchar2,
                           target_type_in IN varchar) IS

BEGIN


  IF((target_name_in IS NULL) and (target_type_in IS NULL))
  THEN
    OPEN inventory_cur_out FOR
       SELECT 
         c.name, 
         count(*) as instcount 
       FROM ( 
         SELECT
           s.snapshot_guid as guid 
          FROM 
           mgmt_ecm_snapshot s, 
           mgmt_targets t, 
           mgmt_hc_os_summary o 
         WHERE 
           s.is_current = 'Y' and 
           s.snapshot_type = 'host_configuration' and 
           s.target_name = t.target_name and 
           s.target_type = t.target_type and 
           s.snapshot_guid = o.snapshot_guid and 
           NVL(o.name, ' ') = osName_in and 
           NVL(o.base_version, ' ') = osVersion_in and 
           NVL(o.update_level, ' ') = patchLevel_in 
         ) snapshot, 
         mgmt_hc_os_components c 
       where 
         snapshot.guid = c.snapshot_guid and 
         c.type = 'Patch' 
       group by c.name;

    SELECT count(*) into total_count_out
    FROM 
     mgmt_ecm_snapshot s, 
     mgmt_targets t, 
     mgmt_hc_os_summary o 
   WHERE 
     s.is_current = 'Y' and 
     s.snapshot_type = 'host_configuration' and 
     s.target_name = t.target_name and 
     s.target_type = t.target_type and 
     s.snapshot_guid = o.snapshot_guid and 
     NVL(o.name, ' ') = osName_in and 
     NVL(o.base_version, ' ') = osVersion_in and 
     NVL(o.update_level, ' ') = patchLevel_in; 

  ELSIF(NOT ((target_name_in IS NULL) or (target_type_in IS NULL)))
  THEN
    OPEN inventory_cur_out FOR
       SELECT 
         c.name, 
         count(*) as instcount
       FROM 
         ( 
           SELECT 
             ps.snapshot_guid as guid 
           FROM
             mgmt_hc_os_summary  o,
             mgmt_ecm_snapshot ps,
             (
               SELECT UNIQUE 
                 t.host_name as hname
               FROM
                 mgmt_targets t,
                 (
                   SELECT
                     m.assoc_target_guid as mguid
                   FROM 
                     mgmt_flat_target_assoc m,
		     mgmt_targets source_target
                   WHERE m.is_membership = 1
                     and m.source_target_guid = source_target.target_guid 
		     and source_target.target_name = target_name_in 
                     and source_target.target_type = target_type_in
                  UNION
                    SELECT t.target_guid as mguid
                    FROM  
                      mgmt_targets t
                    WHERE t.target_name = target_name_in
                      and t.target_type = target_type_in
                  ) lt1
               WHERE 
                 lt1.mguid = t.target_guid
             )lt
           WHERE 
             ps.snapshot_guid   = o.snapshot_guid and
             ps.is_current      = 'Y' and 
             ps.snapshot_type   = 'host_configuration' and 
             lt.hname            = ps.target_name and
             NVL(o.name, ' ') = osName_in and
             NVL(o.base_version, ' ') = osVersion_in and
             NVL(o.update_level, ' ') = patchLevel_in
         ) snapshot, 
         mgmt_hc_os_components c 
       WHERE 
         snapshot.guid = c.snapshot_guid and 
         c.type = 'Patch' 
       GROUP BY c.name;

       SELECT count(*) into total_count_out 
       FROM
        mgmt_hc_os_summary  o,
        mgmt_ecm_snapshot ps,
        (
          SELECT UNIQUE 
                 t.host_name as hname
               FROM
                 mgmt_targets t,
                 (
                   SELECT
                     m.assoc_target_guid as mguid
                   FROM 
                     mgmt_flat_target_assoc m,
		     mgmt_targets source_target
                   WHERE m.is_membership = 1 
               	     and m.source_target_guid = source_target.target_guid
                     and source_target.target_name = target_name_in 
                     and source_target.target_type = target_type_in
                  UNION
                    SELECT t.target_guid as mguid
                    FROM  
                      mgmt_targets t
                    WHERE t.target_name = target_name_in
                      and t.target_type = target_type_in
                  ) lt1
               WHERE 
                 lt1.mguid = t.target_guid
        )lt
      WHERE 
        ps.snapshot_guid   = o.snapshot_guid and
        ps.is_current      = 'Y' and 
        ps.snapshot_type   = 'host_configuration' and 
        lt.hname            = ps.target_name and
        NVL(o.name, ' ') = osName_in and
        NVL(o.base_version, ' ') = osVersion_in and
        NVL(o.update_level, ' ') = patchLevel_in;
    
  END IF;

end os_patches_drill;




PROCEDURE os_with_patch_drill(inventory_cur_out OUT cursorType,
            osName_in IN varchar2,
                              osVersion_in IN varchar2,
                              patchLevel_in IN varchar2,
                              patchName_in IN varchar2,
                              target_name_in IN varchar2,
                              target_type_in IN varchar) IS

BEGIN
  IF ((target_name_in IS NULL) and (target_type_in IS NULL))
  THEN
    OPEN inventory_cur_out FOR
      SELECT UNIQUE
        s.target_name as hostname, 
        (h.system_config || ' ' ||  h.machine_architecture) as hwname, 
        (o.name ||  ' ' || o.base_version || ' ' || o.update_level) as osname
      FROM
        mgmt_ecm_snapshot s,
        mgmt_targets t,
        mgmt_hc_os_summary  o,
        mgmt_hc_os_components  p,
        mgmt_hc_hardware_master h
      WHERE
        s.target_name = t.target_name and
        s.target_type = t.target_type and
        s.is_current = 'Y' and
        s.snapshot_type = 'host_configuration' and 
        NVL(o.name, ' ') = osName_in and 
        NVL(o.base_version, ' ') = osVersion_in and 
        NVL(o.update_level, ' ') = patchLevel_in and 
        s.snapshot_guid = o.snapshot_guid and 
        s.snapshot_guid = h.snapshot_guid(+) and 
        s.snapshot_guid = p.snapshot_guid and 
        p.type = 'Patch' and 
        exists 
          (   
            SELECT * 
            FROM 
              mgmt_hc_os_components  p2 
            WHERE 
              p2.type = 'Patch' and 
              p2.name = patchName_in and 
              s.snapshot_guid = p2.snapshot_guid
          ) 
        GROUP BY s.target_name, h.system_config || ' ' ||  h.machine_architecture, o.name ||  ' ' || o.base_version || ' ' || o.update_level;
    
    ELSIF (NOT ((target_name_in IS NULL) or (target_type_in IS NULL))) 
    THEN
      OPEN inventory_cur_out FOR 
        SELECT UNIQUE
          hw_list.host_name as hostname,
          hw_list.osname || ' ' || hw_list.osversion || ' ' || hw_list.oslevel as osname,
          hw_list.sc || ' ' || hw_list.ma as hwname
        FROM
          (
            SELECT UNIQUE  
              hw.system_config as sc,
              hw.machine_architecture as ma,
              lt.hname             as host_name,
              NVL(o.name, ' ') as osname,
              NVL(o.base_version, ' ') as osversion,
              NVL(o.update_level, ' ') as oslevel,
              ps.snapshot_guid
            FROM
              mgmt_hc_hardware_master hw,
              mgmt_hc_os_summary o,
              mgmt_ecm_snapshot ps,
              (
SELECT UNIQUE 
                 t.host_name as hname
               FROM
                 mgmt_targets t,
                 (
                   SELECT
                     m.assoc_target_guid as mguid
                   FROM 
                     mgmt_flat_target_assoc m,
		     mgmt_targets source_target
                   WHERE m.is_membership = 1 
                     and m.source_target_guid = source_target.target_guid
		     and source_target.target_name = target_name_in 
                     and source_target.target_type = target_type_in
                  UNION
                    SELECT t.target_guid as mguid
                    FROM  
                      mgmt_targets t
                    WHERE t.target_name = target_name_in
                      and t.target_type = target_type_in
                  ) lt1
               WHERE 
                 lt1.mguid = t.target_guid
              )lt
            WHERE
               ps.snapshot_guid   = hw.snapshot_guid and
               ps.is_current      = 'Y' and
               ps.snapshot_type   = 'host_configuration' and
               lt.hname            = ps.target_name and
               o.snapshot_guid = hw.snapshot_guid
          ) hw_list,
          mgmt_hc_os_components p
        WHERE
          NVL(hw_list.osname, ' ') = osName_in and 
          NVL(hw_list.osversion, ' ') = osVersion_in and 
          NVL(hw_list.oslevel, ' ') = patchLevel_in and
          hw_list.snapshot_guid = p.snapshot_guid and
          p.type = 'Patch' and
          exists 
          (   
            SELECT * 
            FROM 
              mgmt_hc_os_components  p2 
            WHERE 
              p2.type = 'Patch' and 
              p2.name = patchName_in and 
              hw_list.snapshot_guid = p2.snapshot_guid
          );

  END IF;

end os_with_patch_drill;

PROCEDURE os_no_patch_drill(inventory_cur_out OUT cursorType,
            osName_in IN varchar2,
                              osVersion_in IN varchar2,
                              patchLevel_in IN varchar2,
                              patchName_in IN varchar2,
                              target_name_in IN varchar2,
                              target_type_in IN varchar) IS

BEGIN
  IF ((target_name_in IS NULL) and (target_type_in IS NULL))
  THEN
    OPEN inventory_cur_out FOR
      SELECT UNIQUE
        s.target_name as hostname, 
        (h.system_config || ' ' ||  h.machine_architecture) as hwname, 
        (o.name ||  ' ' || o.base_version || ' ' || o.update_level) as osname
      FROM
        mgmt_ecm_snapshot s,
        mgmt_targets t,
        mgmt_hc_os_summary  o,
        mgmt_hc_os_components  p,
        mgmt_hc_hardware_master h
      WHERE
        s.target_name = t.target_name and
        s.target_type = t.target_type and
        s.is_current = 'Y' and
        s.snapshot_type = 'host_configuration' and 
        NVL(o.name, ' ') = osName_in and 
        NVL(o.base_version, ' ') = osVersion_in and 
        NVL(o.update_level, ' ') = patchLevel_in and 
        s.snapshot_guid = o.snapshot_guid and 
        s.snapshot_guid = h.snapshot_guid(+) and 
        s.snapshot_guid = p.snapshot_guid and 
        p.type = 'Patch' and 
        not exists 
          (   
            SELECT * 
            FROM 
              mgmt_hc_os_components  p2 
            WHERE 
              p2.type = 'Patch' and 
              p2.name = patchName_in and 
              s.snapshot_guid = p2.snapshot_guid
          ) 
        GROUP BY s.target_name, h.system_config || ' ' ||  h.machine_architecture, o.name ||  ' ' || o.base_version || ' ' || o.update_level;
    ELSIF (NOT ((target_name_in IS NULL) or (target_type_in IS NULL)))
    THEN
      OPEN inventory_cur_out FOR 
        SELECT UNIQUE
          hw_list.host_name as hostname,
          hw_list.osname || ' ' || hw_list.osversion || ' ' || hw_list.oslevel as osname,
          hw_list.sc || ' ' || hw_list.ma as hwname
        FROM
          (
            SELECT UNIQUE  
              hw.system_config as sc,
              hw.machine_architecture as ma,
              lt.hname             as host_name,
              NVL(o.name, ' ') as osname,
              NVL(o.base_version, ' ') as osversion,
              NVL(o.update_level, ' ') as oslevel,
              ps.snapshot_guid
            FROM
              mgmt_hc_hardware_master hw,
              mgmt_hc_os_summary o,
              mgmt_ecm_snapshot ps,
              (
                select 
                    unique t.host_name as hname
                FROM
                  mgmt_targets t,
                  (
                      SELECT UNIQUE m.assoc_target_guid as mguid
                      FROM
                        mgmt_flat_target_assoc m,
			mgmt_targets source_target
                      WHERE m.is_membership = 1
                        and m.source_target_guid = source_target.target_guid
		        and source_target.target_name = target_name_in 
                        and source_target.target_type = target_type_in
                    UNION
                       SELECT UNIQUE t.target_guid as mguid
                      FROM  mgmt_targets t
                      WHERE target_name = target_name_in
                        and target_type = target_type_in
                  ) lt1
                WHERE
                  lt1.mguid = t.target_guid
              )lt
            WHERE
               ps.snapshot_guid   = hw.snapshot_guid and
               ps.is_current      = 'Y' and
               ps.snapshot_type   = 'host_configuration' and
               lt.hname            = ps.target_name and
               o.snapshot_guid = hw.snapshot_guid
          ) hw_list,
          mgmt_hc_os_components p
        WHERE
          NVL(hw_list.osname, ' ') = osName_in and 
          NVL(hw_list.osversion, ' ') = osVersion_in and 
          NVL(hw_list.oslevel, ' ') = patchLevel_in and
          hw_list.snapshot_guid = p.snapshot_guid and
          p.type = 'Patch' and
          not exists 
          (   
            SELECT * 
            FROM 
              mgmt_hc_os_components  p2 
            WHERE 
              p2.type = 'Patch' and 
              p2.name = patchName_in and 
              hw_list.snapshot_guid = p2.snapshot_guid
          );

  END IF;

end os_no_patch_drill;


PROCEDURE sw_with_patch_drill (inventory_cur_out OUT cursorType,
                             product_version_in IN varchar2,
                             product_name_in IN varchar2,
			     patch_name_in IN varchar2,
                             target_name_in IN varchar2,
                             target_type_in IN varchar2,
			     targetList_in IN MGMT_INV_TARGET_LIST_OBJ_ARRAY) IS

BEGIN
  IF ((target_name_in IS NULL) and (target_type_in IS NULL))
  THEN
    OPEN inventory_cur_out FOR
      SELECT  
          inv.container_location, 
          inv.container_name, 
          inv.target_name, 
          inv.display_target_name,
          DECODE(advisory.home_location, NULL, 0, 1) as has_advisory    
      FROM
        (SELECT DISTINCT 
            cont.container_location, 
            cont.container_name, 
            s.target_name, 
            s.display_target_name
        FROM 
            mgmt_inv_container cont, 
            mgmt_inv_component  comp, 
	    mgmt_inv_versioned_patch ivp,
            mgmt_target_type_component_map map, 
            mgmt_targets t, 
            mgmt_ecm_snapshot s,
	    TABLE(CAST(targetList_in AS MGMT_INV_TARGET_LIST_OBJ_ARRAY)) ttlist 
        WHERE 
          s.target_name = t.target_name and 
          s.target_type = t.target_type and 
          s.is_current = 'Y' and 
          s.snapshot_type = 'host_configuration' and 
          cont.snapshot_guid=s.snapshot_guid and 
          cont.container_guid=comp.container_guid and 
          comp.external_name = product_name_in and 
          decode(ivp.version, NULL, comp.version, ivp.version) = product_version_in and 
	  comp.component_guid = ivp.component_guid(+) and
          comp.name = map.component_name and 
	  map.target_type = ttlist.target_type and
	  ((map.property_name IS NULL and map.property_value IS NULL and ttlist.property_name IS NULL 
	            and ttlist.property_value IS NULL) or
		   (map.property_name = ttlist.property_name and map.property_value = ttlist.property_value)) and
          exists 
            ( 
              SELECT * 
              FROM 
                mgmt_inv_patch patch 
              WHERE 
                patch.id = patch_name_in and 
                cont.container_guid = patch.container_guid )
          ) inv,
          (SELECT UNIQUE
                hrv.column_str_value as home_location,
                orv.column_str_value as host_name
           FROM
                mgmt_policies p,
                mgmt_current_violation v,
                mgmt_violation_context hrv,
                mgmt_violation_context orv
           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.exempt_code = 0 or
                 (v.exempt_code = 2 and v.exempt_until <= SYSDATE))     
             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 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'
          ) advisory         
      WHERE inv.target_name = advisory.host_name(+)
        and inv.container_location = advisory.home_location(+);
    ELSIF (NOT ((target_name_in IS NULL) OR (target_type_in IS NULL)))
    THEN
      OPEN inventory_cur_out FOR
      /* We can copy the same query we used above. However, we need to filter the 
        software targets by whether or not they are linked to the composite target in question */
      SELECT  
          inv.container_location, 
          inv.container_name, 
          inv.target_name, 
          inv.display_target_name,
          DECODE(advisory.home_location, NULL, 0, 1) as has_advisory    
      FROM 
        (
	  (-- these are the homes that correspond to software targets in the group
	    SELECT DISTINCT 
              v.container_location, 
              v.container_name, 
              v.host_name as target_name, 
              v.display_host_name as display_target_name
	    FROM 
	      em$ecm_installs_groups v,
	      TABLE(CAST(targetList_in AS MGMT_INV_TARGET_LIST_OBJ_ARRAY)) ttlist
            WHERE v.composite_target_name = target_name_in and
              v.composite_target_type = target_type_in and
	      v.external_name = product_name_in and 
	      v.version = product_version_in and 
	      v.software_target_type = ttlist.target_type and
	      ((v.property_name IS NULL and v.property_value IS NULL and ttlist.property_name IS NULL 
	            and ttlist.property_value IS NULL) or
		    exists (select * from mgmt_target_properties prop where       
                        prop.target_guid = v.software_target_guid
                        and v.property_name = ttlist.property_name 
			and v.property_value = ttlist.property_value
                        and ttlist.property_name = prop.property_name 
		        and ttlist.property_value = prop.property_value )) and
	      exists 
                ( 
                  SELECT * 
		  FROM 
                    mgmt_inv_patch patch 
                  WHERE 
                    patch.id = patch_name_in and 
		    v.container_guid = patch.container_guid 
		)
	  ) 
	  UNION
	  (
	    SELECT DISTINCT
	      v.container_location, 
              v.container_name, 
              v.host_name as target_name, 
              v.display_host_name
	    FROM
	      em$ecm_host_installs_groups v,
	      TABLE(CAST(targetList_in AS MGMT_INV_TARGET_LIST_OBJ_ARRAY)) ttlist 
	    WHERE v.composite_target_name = target_name_in and
              v.composite_target_type = target_type_in and
	      v.target_type = ttlist.target_type and
	      ((v.property_name IS NULL and v.property_value IS NULL and ttlist.property_name IS NULL 
	            and ttlist.property_value IS NULL) or
		   (v.property_name = ttlist.property_name and v.property_value = ttlist.property_value)) and 
	      v.external_name = product_name_in and 
	      v.version = product_version_in and 
	      exists 
                ( 
                  SELECT * 
		  FROM 
                    mgmt_inv_patch patch 
                  WHERE 
                    patch.id = patch_name_in and 
		    v.container_guid = patch.container_guid 
		)
	  )
        ) inv,
          (SELECT UNIQUE
                hrv.column_str_value as home_location,
                orv.column_str_value as host_name
           FROM
                mgmt_policies p,
                mgmt_current_violation v,
                mgmt_violation_context hrv,
                mgmt_violation_context orv
           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.exempt_code = 0 or
                 (v.exempt_code = 2 and v.exempt_until <= SYSDATE))     
             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 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'
          ) advisory         
      WHERE inv.target_name = advisory.host_name(+)
        and inv.container_location = advisory.home_location(+);
  END IF;
end sw_with_patch_drill;

PROCEDURE sw_no_patch_drill (inventory_cur_out OUT cursorType,
                             product_version_in IN varchar2,
                             product_name_in IN varchar2,
                             patch_name_in IN varchar2,
                             target_name_in IN varchar2,
                             target_type_in IN varchar2,
			     targetList_in IN MGMT_INV_TARGET_LIST_OBJ_ARRAY) IS

BEGIN
  IF ((target_name_in IS NULL) and (target_type_in IS NULL))
  THEN
    OPEN inventory_cur_out FOR
      SELECT DISTINCT 
          inv.container_location, 
          inv.container_name, 
          inv.target_name, 
          inv.display_target_name,
          DECODE(advisory.home_location, NULL, 0, 1) as has_advisory 
      FROM
        (SELECT DISTINCT 
            cont.container_location, 
            cont.container_name, 
            s.target_name, 
            s.display_target_name
        FROM 
            mgmt_inv_container cont, 
            em$inv_component  comp, 
            mgmt_target_type_component_map map, 
            mgmt_targets t, 
            mgmt_ecm_snapshot s 
        WHERE 
          s.target_name = t.target_name and 
          s.target_type = t.target_type and 
          s.is_current = 'Y' and 
          s.snapshot_type = 'host_configuration' and 
          cont.snapshot_guid = s.snapshot_guid and 
          cont.container_guid = comp.container_guid and 
          comp.name = map.component_name and 
          comp.external_name = product_name_in and
          comp.version = product_version_in and 
          not exists 
            ( 
              SELECT * 
              FROM 
                mgmt_inv_patch patch 
              WHERE 
                patch.id = patch_name_in and 
                cont.container_guid = patch.container_guid ) 
          ) inv,
          (SELECT UNIQUE
                hrv.column_str_value as home_location,
                orv.column_str_value as host_name
           FROM
                mgmt_policies p,
                mgmt_current_violation v,
                mgmt_violation_context hrv,
                mgmt_violation_context orv
           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.exempt_code = 0 or
                 (v.exempt_code = 2 and v.exempt_until <= SYSDATE))     
             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 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'
          ) advisory         
      WHERE inv.target_name = advisory.host_name(+)
        and inv.container_location = advisory.home_location(+);
        
    ELSIF (NOT ((target_name_in IS NULL) OR (target_type_in IS NULL)))
    THEN
      OPEN inventory_cur_out FOR
      /* We can copy the same query we used above. However, we need to filter the 
        software targets by whether or not they are linked to the composite target in question */
      SELECT  
          inv.container_location, 
          inv.container_name, 
          inv.target_name, 
          inv.display_target_name,
          DECODE(advisory.home_location, NULL, 0, 1) as has_advisory    
      FROM 
        (
	  (
	    SELECT DISTINCT 
              v.container_location, 
              v.container_name, 
              v.host_name as target_name, 
              v.display_host_name as display_target_name
	    FROM 
	      em$ecm_installs_groups v,
	      TABLE(CAST(targetList_in AS MGMT_INV_TARGET_LIST_OBJ_ARRAY)) ttlist
            WHERE v.composite_target_name = target_name_in and
              v.composite_target_type = target_type_in and
	      v.external_name = product_name_in and 
	      v.version = product_version_in and 
	      v.software_target_type = ttlist.target_type and
	      ((v.property_name IS NULL and v.property_value IS NULL and ttlist.property_name IS NULL 
	            and ttlist.property_value IS NULL) or
		    exists (select * from mgmt_target_properties prop where       
                        prop.target_guid = v.software_target_guid
                        and v.property_name = ttlist.property_name 
			and v.property_value = ttlist.property_value
                        and ttlist.property_name = prop.property_name 
		        and ttlist.property_value = prop.property_value )) and
	      not exists 
                ( 
                  SELECT * 
		  FROM 
                    mgmt_inv_patch patch 
                  WHERE 
                    patch.id = patch_name_in and 
		    v.container_guid = patch.container_guid 
		)
	  ) -- these are the homes that correspond to software targets in the group
	  UNION
	  (
	    SELECT DISTINCT
	      v.container_location, 
              v.container_name, 
              v.host_name as target_name, 
              v.display_host_name
	    FROM
	      em$ecm_host_installs_groups v,
	      TABLE(CAST(targetList_in AS MGMT_INV_TARGET_LIST_OBJ_ARRAY)) ttlist 
	    WHERE v.composite_target_name = target_name_in and
              v.composite_target_type = target_type_in and
	      v.target_type = ttlist.target_type and
	      ((v.property_name IS NULL and v.property_value IS NULL and ttlist.property_name IS NULL 
	            and ttlist.property_value IS NULL) or
		   (v.property_name = ttlist.property_name and v.property_value = ttlist.property_value)) and 
	      v.external_name = product_name_in and 
	      v.version = product_version_in and 
	      not exists 
                ( 
                  SELECT * 
		  FROM 
                    mgmt_inv_patch patch 
                  WHERE 
                    patch.id = patch_name_in and 
		    v.container_guid = patch.container_guid 
		)
	  )
        ) inv,
          (SELECT UNIQUE
                hrv.column_str_value as home_location,
                orv.column_str_value as host_name
           FROM
                mgmt_policies p,
                mgmt_current_violation v,
                mgmt_violation_context hrv,
                mgmt_violation_context orv
           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.exempt_code = 0 or
                 (v.exempt_code = 2 and v.exempt_until <= SYSDATE))     
             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 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'
          ) advisory         
      WHERE inv.target_name = advisory.host_name(+)
        and inv.container_location = advisory.home_location(+);

  END IF;
end sw_no_patch_drill;



end ecm_drill;

/

show errors;