REM drv: <create type="pkgbodys" condition="EM_REPOS_MODE=SYSAUX"/>
Rem
Rem $Header: ts_pkgbody.sql 20-oct-2006.17:24:33 gmanglik 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    gmanglik    10/20/06 - 
Rem    shgangul    09/16/06 - 
Rem    amroy       08/23/06 - Creation
Rem    amroy       08/23/06 - Creation for DBControl
Rem    tasingh     12/28/05 - Support Advanced Search
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_DBTS_PKG
IS

  FUNCTION get_property_name(
   p_target_type in VARCHAR2,
   p_version in VARCHAR2
  ) RETURN VARCHAR2
  IS
   l_property_name VARCHAR2(40);
  BEGIN
   IF (p_target_type = 'cluster') THEN
      l_property_name := 'CRSVersion';
   ELSIF (p_target_type = 'oracle_database') THEN
      l_property_name := 'DBVersion';
   ELSIF (p_target_type = 'osm_instance') THEN
      l_property_name := 'DBVersion';
   ELSIF ((p_target_type = 'rac_database') AND (p_version LIKE '10.1.0'||'%')) THEN
      l_property_name := 'DBVersion';
   ELSE
      l_property_name := 'Version';
   END IF;
   RETURN l_property_name;
  END get_property_name;

  --
  -- Get an array of targets matching the simple 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
  ) RETURN OUTPUT_REF_CURSOR

  IS

  l_target_list  DBTS_REC_ARRAY := DBTS_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
      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
      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', 'rac_database','DBVersion','Version')
        AND tgt_prop.property_name = get_property_name(p_target_type, p_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
    )

    LOOP
      l_target_list.extend;

      l_target_list(l_target_list.last) := DBTS_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
      );
    END LOOP;

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

    RETURN l_result_cur;
  END get_targets;


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

  IS

  l_target_list  DBTS_REC_ARRAY := DBTS_REC_ARRAY( );
  l_result_cur   OUTPUT_REF_CURSOR;

  BEGIN

    FOR ts_rec IN (
 SELECT distinct
        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
      FROM
        MGMT_TARGETS tgt,
        MGMT_TARGET_PROPERTIES tgt_prop,
        MGMT_TARGETS host_tgt,
        MGMT_TARGET_PROPERTIES host_tgt_prop,
        MGMT_ARU_PLATFORMS aru_plat,
         MGMT_CURRENT_AVAILABILITY avail

      WHERE
        tgt.target_type = p_target_type
        AND upper( tgt.target_name) LIKE p_target_name
        AND tgt.target_guid = tgt_prop.target_guid
        AND tgt_prop.property_value LIKE p_version
        AND (tgt.host_name=host_tgt.target_name)
        AND host_tgt_prop.target_guid = host_tgt.target_guid
        AND aru_plat.em_os_name = host_tgt_prop.property_value
        AND aru_plat.platform_id LIKE p_platform_id
         AND tgt.target_guid = avail.target_guid (+)
        )
 LOOP
      l_target_list.extend;

      l_target_list(l_target_list.last) := DBTS_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
      );
    END LOOP;

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

    RETURN l_result_cur;
  END get_dbtargets;







-- End of new search for db targets


  --
  -- Get an array of targets matching the advanced search criteria.
  --
  FUNCTION get_targets_for_adv_search(
    p_member_target_type in VARCHAR2,
    p_member_target_name in VARCHAR2,
    p_on_host     in VARCHAR2,
    p_version     in VARCHAR2,
    p_platform_id in VARCHAR2,
    p_comp_target_type in VARCHAR2,
    p_comp_target_name in VARCHAR2
  ) RETURN OUTPUT_REF_CURSOR

  IS

  l_target_list  DBTS_REC_ARRAY := DBTS_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
      FROM
        MGMT$TARGET_COMPOSITE comp_tgt,
        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
      WHERE
        upper( comp_tgt.composite_name) like upper( p_comp_target_name)
        AND comp_tgt.composite_type = p_comp_target_type
        AND upper( comp_tgt.member_target_name) like upper(p_member_target_name)
        AND comp_tgt.member_target_type = p_member_target_type
        AND comp_tgt.member_target_type = tgt.target_type
        AND upper( comp_tgt.member_target_name) = upper( tgt.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_member_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
    )

    LOOP
      l_target_list.extend;

      l_target_list(l_target_list.last) := DBTS_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
      );
    END LOOP;

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

    RETURN l_result_cur;

  END get_targets_for_adv_search;

END MGMT_DBTS_PKG;
/

show errors