REM drv: <create type="pkgbodys"/>
Rem
Rem $Header: ts_pkgbody.sql 13-mar-2006.21:58:39 vsagar Exp $
Rem
Rem ts_pkgbody.sql
Rem
Rem Copyright (c) 2005, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ts_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    vsagar      03/11/06 - vsagar_dev060307
Rem    vsagar      01/03/06 - 
Rem    tasingh     12/16/05 - Use target specific property value for version 
Rem                           comparison instead of generic 'Version' string. 
Rem    tasingh     12/08/05 - Created
Rem

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

CREATE OR REPLACE PACKAGE BODY MGMT_ASPROV_TS_PKG
IS

  --
  -- Get an array of targets matching the search criteria.
  --
  FUNCTION get_targets(
    p_target_type in VARCHAR2,
    p_target_name in VARCHAR2,
    p_on_host     in VARCHAR2,
    p_version     in VARCHAR2,
    p_platform_id in VARCHAR2,
    p_vendor      in VARCHAR2,
    p_base_version in VARCHAR2
  ) RETURN OUTPUT_REF_CURSOR

  IS

  l_target_list  ASPROV_TS_REC_ARRAY := ASPROV_TS_REC_ARRAY( );
  l_result_cur   OUTPUT_REF_CURSOR;

  BEGIN

    FOR ts_rec IN (
      SELECT
        tgt.target_guid,
        tgt.target_type,
        tgt.type_display_name,
        tgt.target_name,
        tgt.display_name,
        tgt.host_name,
        tgt.emd_url,
        tgt.timezone_region,
        NVL( avail.current_status, MGMT_GLOBAL.G_STATUS_UNKNOWN) cur_status,
        tgt_prop.property_value tgt_version,
	aru_plat.platform_name tgt_platform,
	aru_plat.platform_id tgt_platform_id,
        agent_tgt_prop.property_value agent_version
      FROM
        MGMT_TARGETS tgt,
        MGMT_TARGET_PROPERTIES tgt_prop,
        MGMT_CURRENT_AVAILABILITY avail,
        MGMT_TARGETS host_tgt,
        MGMT_ECM_SNAPSHOT snap,
        MGMT_HC_OS_SUMMARY os_summ,
        MGMT_ARU_PLATFORMS aru_plat,
        MGMT_TARGET_PROPERTIES host_tgt_prop,
	MGMT_TARGETS agent_tgt,
	MGMT_TARGET_PROPERTIES agent_tgt_prop
      WHERE
        tgt.target_type = p_target_type
        AND upper( tgt.target_name) LIKE upper( p_target_name)
        AND upper( NVL( tgt.host_name, '%')) LIKE upper( p_on_host)
        AND tgt.target_guid = avail.target_guid (+)
        AND tgt.target_guid = tgt_prop.target_guid
        AND tgt_prop.property_name = decode( p_target_type, 'cluster', 'CRSVersion', 'oracle_database', 'DBVersion', 'osm_instance', 'DBVersion', 'Version')
        AND tgt_prop.property_value LIKE p_version
        AND (tgt.host_name=host_tgt.target_name)
        AND host_tgt.target_type = 'host'
        AND host_tgt.target_name = snap.target_name
        AND snap.snapshot_type = 'host_configuration'
        AND snap.target_type = 'host'
        AND snap.is_current = 'Y'
        AND snap.snapshot_guid = os_summ.snapshot_guid
        AND host_tgt_prop.target_guid = host_tgt.target_guid
        AND host_tgt_prop.property_name = 'OS'
        AND aru_plat.em_os_name = host_tgt_prop.property_value
        AND os_summ.address_length_in_bits LIKE aru_plat.em_os_bitlength || '%'
        AND aru_plat.platform_id LIKE p_platform_id
	AND agent_tgt.target_type = 'oracle_emd'
        AND agent_tgt.host_name = tgt.host_name
	AND agent_tgt.target_guid = agent_tgt_prop.target_guid 
	AND agent_tgt_prop.property_name = 'Version'
	AND os_summ.vendor_name LIKE p_vendor
	AND os_summ.base_version LIKE p_base_version
    )

    LOOP
      l_target_list.extend;

      l_target_list(l_target_list.last) := ASPROV_TS_REC_TYPE(
        ts_rec.target_guid,
        ts_rec.target_type,
        ts_rec.type_display_name,
        ts_rec.target_name,
        ts_rec.display_name,
        ts_rec.host_name,
        ts_rec.emd_url,
        ts_rec.timezone_region,
        ts_rec.cur_status,
        ts_rec.tgt_version,
	ts_rec.tgt_platform,
	ts_rec.tgt_platform_id,
	ts_rec.agent_version
      );
    END LOOP;

    OPEN l_result_cur FOR
      SELECT * FROM TABLE( CAST( l_target_list AS ASPROV_TS_REC_ARRAY));

    RETURN l_result_cur;
    
  END get_targets;

END MGMT_ASPROV_TS_PKG;
/

show errors