CREATE OR REPLACE PACKAGE body ecm_inv AS

--
-- PROCEDURE: get_inv_hw_info
--
-- PURPOSE:
--   This procedure returns HW inventory info for a given context
--
-- IN Parameters:
--   target_name_in: the name of the target that defines the context in which we are searching
--   target_type_in: the type of the target that defines the context in which we are searching
--   these two must either both be null or both be non-null
--
-- OUT Parameters:
--    inventory_cur_out: the cursor containing the inventory information
--    number_of_hosts_out: The total number of host targets found
--    refreshed_hosts_out: the number of updated hosts 
PROCEDURE get_inv_hw_info( inventory_cur_out OUT cursorType,
                           target_name_in IN VARCHAR2,
                           target_type_in IN VARCHAR2,
                           number_of_hosts_out OUT NUMBER,
			   refreshed_hosts_out OUT NUMBER,
			   collection_errors_out OUT NUMBER) IS

-- Package level Type Definition 


BEGIN
  IF((target_name_in IS NULL) and (target_type_in IS NULL))
  THEN
    --We want the table for the whole enterprise - so just use the old SQL to return it 
    OPEN inventory_cur_out FOR
      SELECT system_config as sc, machine_architecture as ma, num_hosts as co
        FROM EM$ECM_HARDWARE_COUNT;
    
     -- Get total number of hosts discovered by emd
     SELECT count(*) into number_of_hosts_out
       FROM  mgmt_targets
       WHERE target_type = 'host';
     

     -- Get the number of hosts that actually have current snapshots
     SELECT count(distinct t.target_name)  into refreshed_hosts_out
       FROM 
         mgmt_ecm_snapshot s, 
         mgmt_ecm_snap_component_info c, 
         mgmt_targets t 
       WHERE s.is_current = 'Y'
             and s.target_type = 'host'
             and s.snapshot_type = 'host_configuration'
             and s.target_name = t.target_name 
             and s.target_type = t.target_type 
             and s.snapshot_guid = c.snapshot_guid 
             and c.component_name = 'oracle.hardware'
             and c.collection_status = 'COLLECTED';

  -- Get collection errors which includes warnings also	            
  SELECT count (*) into collection_errors_out
      FROM( 
        SELECT t.target_name
        FROM 
          mgmt_targets t, 
          mgmt_ecm_snapshot s, mgmt_ecm_snap_component_info i 
        WHERE 
          s.snapshot_guid     = i.snapshot_guid 
          and 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 (i.collection_status = 'COLLECTION_FAILED' 
                or i.collection_message IS NOT NULL) 
	  and i.component_name = 'oracle.hardware' 
        UNION 
        SELECT t.target_name
        FROM 
          mgmt_targets t
        WHERE 
          t.target_type        = 'host' 
          and NOT EXISTS 
               (SELECT * 
                FROM 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'
               ) 
  	);

  ELSIF(not ((target_name_in IS NULL) or (target_type_in IS NULL)))
  THEN
    OPEN inventory_cur_out FOR
      select
        sc,
        ma,
        count(distinct host_name) as co
      FROM
        (  
          SELECT
            hw.system_config as sc,
            hw.machine_architecture as ma,
            lt.hname             as host_name
          FROM
            mgmt_hc_hardware_master hw,
            mgmt_ecm_snapshot ps,
            mgmt_ecm_snap_component_info c, 
            (
              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 source_target.target_name = target_name_in 
                      and source_target.target_type = target_type_in
                      and m.source_target_guid = source_target.target_guid
                      and m.is_membership = 1
                  UNION
                    SELECT UNIQUE 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
                ps.snapshot_guid = c.snapshot_guid 
                and c.component_name = 'oracle.hardware'
                and c.collection_status = 'COLLECTED'		
                and lt.hname = ps.target_name
        )
      group by sc, ma;  



      -- Get total number of hosts discovered by emd for this particular composite target
      SELECT count(distinct target_space.host_name) into number_of_hosts_out
      FROM
        ( 
            SELECT UNIQUE t.host_name as host_name     
            FROM
              mgmt_flat_target_assoc m,
              mgmt_targets t,
	      mgmt_targets source_target
            WHERE m.is_membership = 1
	      AND t.target_guid = m.assoc_target_guid
              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.host_name
            FROM  mgmt_targets t
            WHERE t.target_name = target_name_in 
              and t.target_type = target_type_in*/
        ) target_space
      WHERE TRIM(target_space.host_name) IS NOT NULL;
      
      --get number of hosts in this composite target that have a refreshed configuration
      SELECT count(distinct target_space.host_name)  into refreshed_hosts_out
        FROM 
          mgmt_ecm_snapshot s, 
          mgmt_ecm_snap_component_info c, 
--          mgmt_targets host,
	  ( 
            SELECT UNIQUE t.host_name       
            FROM
              mgmt_flat_target_assoc m,
	      mgmt_targets source_target,
              mgmt_targets t
            WHERE m.is_membership = 1 
	      AND t.target_guid = m.assoc_target_guid
              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.host_name
            FROM  mgmt_targets t
            WHERE t.target_name = target_name_in 
              and t.target_type = target_type_in
        ) target_space 
        WHERE
	  target_space.host_name = s.target_name 
          --and s.target_name = host.target_name
	  --and s.target_type = host.target_type
	  and s.target_type = 'host'
          and s.is_current = 'Y'
          and s.snapshot_type = 'host_configuration'
          and s.snapshot_guid = c.snapshot_guid 
          and c.component_name = 'oracle.hardware'
          and c.collection_status = 'COLLECTED';      

	SELECT count(*) into collection_errors_out
	    FROM(
		SELECT
		  t.target_name 
		FROM
		  mgmt_targets t,
		  mgmt_ecm_snapshot s,
		  mgmt_ecm_snap_component_info i,
		  (
		    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
		      ) lt1
		    WHERE
		      lt1.mguid = t.target_guid
		  )lt
	WHERE
	  lt.hname = t.target_name
	  and s.snapshot_guid     = i.snapshot_guid
	  and 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 (i.collection_status = 'COLLECTION_FAILED'
		or i.collection_message IS NOT NULL)
	  and i.component_name = 'oracle.hardware' 
        UNION
        SELECT
          t.target_name 
        FROM
          mgmt_targets t,
          (
            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
              ) lt1
            WHERE
              lt1.mguid = t.target_guid
          )lt
        WHERE
          lt.hname = t.target_name
          and t.target_type        = 'host'
          and NOT EXISTS
            (SELECT *
             FROM 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'
            )
	);	          
            
  END IF;


END get_inv_hw_info;

-- PROCEDURE: get_inv_os_info
--
-- PURPOSE:
--   This procedure returns os inventory info for a given context
--
-- IN Parameters:
--   target_name_in: the name of the target that defines the context in which we are searching
--   target_type_in: the type of the target that defines the context in which we are searching
--   these two must either both be null or both be non-null
--
-- OUT Parameters:
--    inventory_cur_out: the cursor containing the inventory information
--    number_of_hosts_out: The total number of host targets found
--    refreshed_hosts_out: the number of updated hosts 
PROCEDURE get_inv_os_info(inventory_cur_out OUT cursorType,
                    target_name_in IN VARCHAR2,
                    target_type_in IN VARCHAR2,
                    number_of_hosts_out OUT NUMBER,
                    refreshed_hosts_out OUT NUMBER,
                    collection_errors_out OUT NUMBER) IS
BEGIN
  IF((target_name_in IS NULL) and (target_type_in IS NULL))  
  THEN
    OPEN inventory_cur_out FOR
       SELECT name, base_version, update_level, num_hosts, patched
         FROM EM$ECM_OS_COUNT;

    -- Get total number of hosts discovered by emd
    SELECT count(*) into number_of_hosts_out
    FROM  mgmt_targets
    WHERE target_type = 'host';

    -- Get the number of hosts which have oracle software collected
    --successfully by ECM
    SELECT count(*)  into refreshed_hosts_out
    FROM 
      mgmt_ecm_snapshot s, 
      mgmt_ecm_snap_component_info c, 
      mgmt_targets t 
    WHERE 
      s.is_current = 'Y'
      and s.target_type = 'host'
      and s.snapshot_type = 'host_configuration'
      and s.target_name = t.target_name 
      and s.target_type = t.target_type 
      and s.snapshot_guid = c.snapshot_guid 
      and c.component_name = 'oracle.os_software'
      and c.collection_status = 'COLLECTED';
      
  SELECT count (*) into collection_errors_out
      FROM( 
        SELECT t.target_name
        FROM 
          mgmt_targets t, 
          mgmt_ecm_snapshot s, mgmt_ecm_snap_component_info i 
        WHERE 
          s.snapshot_guid     = i.snapshot_guid 
          and 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 (i.collection_status = 'COLLECTION_FAILED' 
                or i.collection_message IS NOT NULL) 
	  and i.component_name = 'oracle.os_software' 
        UNION 
        SELECT t.target_name
        FROM 
          mgmt_targets t
        WHERE 
          t.target_type        = 'host' 
          and NOT EXISTS 
               (SELECT * 
                FROM 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'
               ) 
  	);	      

  ELSIF(not ((target_name_in IS NULL) or (target_type_in IS NULL)))
  THEN
    OPEN inventory_cur_out FOR
      SELECT
        o.name,
        o.base_version,
        o.update_level,
        count(*) num_hosts,
        decode(max(o.patches), 0, 'No', 'Yes') as patched
      FROM
        mgmt_hc_os_summary o
      WHERE
        o.snapshot_guid in
          (
            SELECT /*+ ORDERED */ c.snapshot_guid
            FROM
              (
                SELECT DISTINCT
                  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 ALL
                    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,
              mgmt_ecm_snapshot s,
              mgmt_ecm_snap_component_info c
            WHERE
              s.snapshot_guid   = c.snapshot_guid and
              s.is_current      = 'Y' and 
              s.snapshot_type   = 'host_configuration' and 
              c.component_name = 'oracle.os_software' and
	      c.collection_status = 'COLLECTED' and
              lt.hname            = s.target_name
          )
      GROUP BY o.name, o.base_version, o.update_level;


      SELECT count(distinct target_space.host_name) into number_of_hosts_out
      FROM
        ( 
            SELECT UNIQUE t.host_name as host_name     
            FROM
              mgmt_flat_target_assoc m,
	      mgmt_targets source_target,
              mgmt_targets t
            WHERE m.is_membership = 1 
	      AND t.target_guid = m.assoc_target_guid
              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.host_name
            FROM  mgmt_targets t
            WHERE t.target_name = target_name_in 
              and t.target_type = target_type_in
        ) target_space
      WHERE TRIM(target_space.host_name) IS NOT NULL;


      --get number of hosts in this composite target that have a refreshed configuration
      SELECT count(distinct target_space.host_name)  into refreshed_hosts_out
        FROM 
          mgmt_ecm_snapshot s, 
          mgmt_ecm_snap_component_info c, 
--          mgmt_targets host,
	  ( 
            SELECT UNIQUE t.host_name       
            FROM
              mgmt_flat_target_assoc m,
	      mgmt_targets source_target,
              mgmt_targets t
            WHERE m.is_membership = 1 
	      AND t.target_guid = m.assoc_target_guid
              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.host_name
            FROM  mgmt_targets t
            WHERE t.target_name = target_name_in 
              and t.target_type = target_type_in*/
        ) target_space 
        WHERE
	  target_space.host_name = s.target_name 
          --and s.target_name = host.target_name
	  --and s.target_type = host.target_type
	  and s.target_type = 'host'
          and s.is_current = 'Y'
          and s.snapshot_type = 'host_configuration'
          and s.snapshot_guid = c.snapshot_guid 
          and c.component_name = 'oracle.os_software'
          and c.collection_status = 'COLLECTED';     

      SELECT count(*) into collection_errors_out
      FROM(
        SELECT
          t.target_name 
        FROM
          mgmt_targets t,
          mgmt_ecm_snapshot s,
          mgmt_ecm_snap_component_info i,
          (
            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
              ) lt1
            WHERE
              lt1.mguid = t.target_guid
          )lt
        WHERE
          lt.hname = t.target_name
          and s.snapshot_guid     = i.snapshot_guid
          and 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 (i.collection_status = 'COLLECTION_FAILED'
                or i.collection_message IS NOT NULL)
          and i.component_name = 'oracle.os_software' 
        UNION
        SELECT
          t.target_name 
        FROM
          mgmt_targets t,
          (
            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
              ) lt1
            WHERE
              lt1.mguid = t.target_guid
          )lt
        WHERE
          lt.hname = t.target_name
          and t.target_type        = 'host'
          and NOT EXISTS
            (SELECT *
             FROM 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'
            )
	)t;          


  END IF;

END get_inv_os_info;

--
-- PROCEDURE: get_inv_sw_info
--
-- PURPOSE:
-- This procedure returns information about various kinds of software, depending on the 
-- types listed in the targetList_in argument
PROCEDURE get_inv_sw_info(
                           inventory_cur_out OUT cursorType,
                           target_name_in IN VARCHAR2,
                           target_type_in IN VARCHAR2,
                           targetList_in IN MGMT_INV_TARGET_LIST_OBJ_ARRAY,
			   number_of_targets_out OUT NUMBER,
			   collection_errors_out OUT NUMBER) IS


BEGIN
  /*We don't really need to compute the total number of host and the number of refreshed hosts
    What we display for the software table is collection errors, so let's just count that number
    directly*/

  IF((target_name_in IS NULL) and (target_type_in is NULL))
    THEN
    
    SELECT count (*) into collection_errors_out
      FROM( 
        SELECT t.target_name
        FROM 
          mgmt_targets t, 
          mgmt_ecm_snapshot s, mgmt_ecm_snap_component_info i 
        WHERE 
          s.snapshot_guid     = i.snapshot_guid 
          and 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 (i.collection_status = 'COLLECTION_FAILED' 
                or i.collection_message IS NOT NULL) 
	  and i.component_name = 'oracle.oracle_inventory' 
        UNION 
        SELECT t.target_name
        FROM 
          mgmt_targets t 
        WHERE 
          t.target_type        = 'host' 
          and NOT EXISTS 
               (SELECT * 
                FROM 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'
               ) 
  	);
    
    SELECT count(distinct target.target_guid) into number_of_targets_out
      from 
        mgmt_targets target, 
        TABLE(CAST(targetList_in AS MGMT_INV_TARGET_LIST_OBJ_ARRAY)) ttlist
      where 
        ttlist.target_type = target.target_type and
        ((ttlist.property_name is null and ttlist.property_value is null) or
          exists (select * from mgmt_target_properties sub_type
                  where
                    target.target_guid = sub_type.target_guid and
                    ttlist.property_name = sub_type.property_name and
                    ttlist.property_value = sub_type.property_value) );

      OPEN inventory_cur_out FOR
        SELECT 
          external_name as product_name, 
          version as product_version, 
          num_installs, 
          num_instances, 
          patched
        FROM
          (
            SELECT 
              itarget_type, 
              external_name, 
              version,
              count(distinct it.container_guid) as num_installs,
              count(distinct it.target_guid) as num_instances,
              case max(it.is_patched) when 0 then 'No' else 'Yes' end as patched
            FROM
              (
                SELECT /*+ USE_HASH(targs inst) */
                  external_name as external_name,
                  version as version,
                  container_guid as container_guid,
                  target_guid,
                  is_patched,
                  inst.target_type as itarget_type
                FROM
                  (
                    SELECT
                      summary.map_target_type as target_type,
                      summary.map_property_name as property_name,
                      summary.map_property_value as property_value,
                      summary.comp_external_name as external_name,
                      summary.comp_version as version,
                      host.target_name as host_name,
                      summary.container_location,
                      summary.is_patched,
                      summary.container_guid
                    FROM
                      mgmt_targets host,
                      MGMT_INV_SUMMARY summary,
                      mgmt_ecm_snapshot snapshot,
                      TABLE(CAST(targetList_in AS MGMT_INV_TARGET_LIST_OBJ_ARRAY)) ttlist
                    WHERE host.target_type = 'host'
                      and host.target_name = snapshot.target_name
                      and host.target_type = snapshot.target_type
                      and snapshot.snapshot_guid = summary.snapshot_guid
                      and snapshot.snapshot_type = 'host_configuration'
                      and snapshot.is_current = 'Y'
                      and summary.map_target_type = ttlist.target_type
                      and ((ttlist.property_name IS NULL and 
ttlist.property_value IS NULL and summary.map_property_name IS NULL
                           and summary.map_property_value IS NULL) or
                           (ttlist.property_name = summary.map_property_name and 
ttlist.property_value = summary.map_property_value))
                  ) inst,
                  (
                    SELECT
                      target.target_type,
                      target.host_name as host_target_name,
                      target.target_name as target_name,
                      target.target_guid,
                      location_property.property_value as home_location,
                      ttlist.property_name,
                      ttlist.property_value
                    FROM
                      mgmt_targets target,
                      mgmt_target_properties location_property,
                      TABLE(CAST(targetList_in AS MGMT_INV_TARGET_LIST_OBJ_ARRAY)) ttlist
                    WHERE
                      ttlist.target_type = target.target_type
                      and location_property.target_guid = target.target_guid
                      and location_property.property_name = 'OracleHome'
                      and ((ttlist.property_name is null and ttlist.property_value is null) or
                            exists 
                            (
                              select * 
                              from mgmt_target_properties sub_type
                              where
                                target.target_guid = sub_type.target_guid and
                                ttlist.property_name =sub_type.property_name and
                                ttlist.property_value = sub_type.property_value
                            ) 
                          )
                  ) targs
                WHERE inst.host_name = targs.host_target_name(+)
                  and inst.container_location = targs.home_location(+)
                  and inst.target_type = targs.target_type(+)
                  and ((inst.property_name IS NULL and inst.property_value IS NULL) or
                       (inst.property_name = targs.property_name and inst.property_value = targs.property_value))
              ) it
            group by itarget_type, external_name, version
          );

  ELSIF(NOT ((target_name_in IS NULL) OR (target_type_in IS NULL)))
    THEN	      
      SELECT count(*) into collection_errors_out
      FROM(
        SELECT
          t.target_name 
        FROM
          mgmt_targets t,
          mgmt_ecm_snapshot s,
          mgmt_ecm_snap_component_info i,
          (
            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
              ) lt1
            WHERE
              lt1.mguid = t.target_guid
          )lt
        WHERE
          lt.hname = t.target_name
          and s.snapshot_guid     = i.snapshot_guid
          and 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 (i.collection_status = 'COLLECTION_FAILED'
                or i.collection_message IS NOT NULL)
          and i.component_name = 'oracle.oracle_inventory' 
        UNION
        SELECT
          t.target_name 
        FROM
          mgmt_targets t,
          (
            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
              ) lt1
            WHERE
              lt1.mguid = t.target_guid
          )lt
        WHERE
          lt.hname = t.target_name
          and t.target_type        = 'host'
          and NOT EXISTS
            (SELECT *
             FROM 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'
            )
	)t;
      
      --get software targets in this group
      SELECT count(distinct target.target_guid) into number_of_targets_out
      FROM
        (  
          SELECT 
            assoc_target.target_guid as target_guid,
            assoc_target.target_type as target_type
          FROM 
            mgmt_flat_target_assoc m,
	    mgmt_targets source_target,
            mgmt_targets assoc_target
          WHERE m.is_membership = 1 
            and assoc_target.target_guid = m.assoc_target_guid
            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
        ) target,
        TABLE(CAST(targetList_in AS MGMT_INV_TARGET_LIST_OBJ_ARRAY)) ttlist
      WHERE 
        ttlist.target_type = target.target_type
	and ((ttlist.property_name is null and ttlist.property_value is null) or
	      exists (select * from mgmt_target_properties sub_type
		      where
			target.target_guid = sub_type.target_guid and
			ttlist.property_name = sub_type.property_name and
			ttlist.property_value = sub_type.property_value) );

      OPEN inventory_cur_out FOR
        SELECT
	  external_name as product_name,
	  version as product_version,
	  count(distinct inst_container_guid) as num_installs,
	  count(distinct target_guid) as num_instances,
	  case max(is_patched) when 0 then 'No' else 'Yes' end as patched
	FROM
	(
	  (
	    SELECT
	      summary.comp_external_name as external_name,
	      summary.comp_version as version, 
	      summary.container_guid as inst_container_guid,
              summary.is_patched,
	      t.target_guid as target_guid
	    FROM
	      (  
	        SELECT 
	          m.assoc_target_guid as target_guid
	        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 
		  target_guid as target_guid 
		FROM 
		  mgmt_targets 
		WHERE target_name = target_name_in
		  and target_type = target_type_in
	      ) target_space,
	      mgmt_targets t,
	      mgmt_targets host_t,
	      TABLE(CAST(targetList_in AS MGMT_INV_TARGET_LIST_OBJ_ARRAY)) ttlist,
	      mgmt_ecm_snapshot s, 
              mgmt_inv_summary summary,
	      mgmt_target_properties t_home  
	    WHERE
	      t.target_guid = target_space.target_guid and
	      host_t.target_name = t.host_name and --this gets us all the hosts involved with this group
	      s.target_name = host_t.target_name and
	      s.target_type = host_t.target_type and
	      s.is_current = 'Y' and
	      s.snapshot_type = 'host_configuration' and
	      s.snapshot_guid = summary.snapshot_guid and
	      summary.map_target_type = ttlist.target_type and 
	      ((ttlist.property_name IS NULL and ttlist.property_value IS NULL and summary.map_property_name IS NULL and summary.map_property_value IS NULL) or
	       (summary.map_property_name = ttlist.property_name and summary.map_property_value = ttlist.property_value)) and
	      t.target_guid = t_home.target_guid and
	      t_home.property_name = 'OracleHome' and
	      ttlist.target_type = t.target_type and
	      ((ttlist.property_name is null and ttlist.property_value is null) or
	        exists (select * from mgmt_target_properties sub_type
		        where
			  t.target_guid = sub_type.target_guid and
			  ttlist.property_name = sub_type.property_name and
			  ttlist.property_value = sub_type.property_value) ) and
	      summary.container_location = t_home.property_value(+)
	  )
          UNION
          (
            SELECT
	      summary.comp_external_name as external_name,
	      summary.comp_version as version, 
	      summary.container_guid as inst_container_guid,
              summary.is_patched,
	      NULL as target_guid
	    FROM
	      (
	        SELECT 
		  m.assoc_target_guid as target_guid
	        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 
		  target_guid as target_guid 
		FROM 
		  mgmt_targets 
		WHERE target_name = target_name_in 
		  and target_type = target_type_in
              ) target_space,
	      mgmt_targets t,
	      mgmt_targets host_t,
	      TABLE(CAST(targetList_in AS MGMT_INV_TARGET_LIST_OBJ_ARRAY)) ttlist,
	      mgmt_ecm_snapshot s, 
              mgmt_inv_summary summary
	    WHERE
	      t.target_guid = target_space.target_guid and
	      t.target_type = 'host' and
	      host_t.target_name = t.host_name and --this gets us all the hosts involved with this group
	      s.target_name = host_t.target_name and
	      s.target_type = host_t.target_type and
	      s.is_current = 'Y' and
	      s.snapshot_type = 'host_configuration' and
	      s.snapshot_guid = summary.snapshot_guid and
	      summary.map_target_type = ttlist.target_type and
	      ((ttlist.property_name IS NULL and ttlist.property_value IS NULL and summary.map_property_name IS NULL and summary.map_property_value IS NULL)
	       or (summary.map_property_name = ttlist.property_name and summary.map_property_value = ttlist.property_value))
          )
        )                    
        GROUP BY external_name, version;
    END IF;

END get_inv_sw_info;

end ecm_inv;
/

show errors;