Rem drv: <create type="pkgbodys" pos="target/target_pkgbody.sql+"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/target/target_ui_pkgbody.sql /st_emcore_10.2.0.4.3db11.2.0.2/1 2010/04/26 20:30:59 bram Exp $
Rem
Rem target_ui_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2010, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem    target_ui_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       bram     10/03/09 - Perf Fixes
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    bram        05/10/05 - Bug 4350323 EM_TARGET_UI HAS 1 SQL CODING VIOLATION
Rem    narkumar    05/11/05 - added type display in select list 
Rem    paachary    03/31/05 - Bug # 4252834 SCALABILITY: TARGETSELECTOR TAKES A LONG TIME TO LOAD
Rem    narkumar    01/05/05 - SQL for aggregate Only 
Rem    narkumar    12/08/04 - Child Type Limiting
Rem    narkumar    12/02/04 - limiting the number of maximum rows 
Rem    narkumar    10/08/04 - set default privilege to view 
Rem    narkumar    09/22/04 - narkumar_targetselectoritem4
Rem    narkumar    08/27/04 - Created
Rem

CREATE OR REPLACE PACKAGE BODY  EM_TARGET_UI
AS

PROCEDURE get_targets_info(p_target_guids   IN MGMT_USER_GUID_ARRAY,
                           p_targets_cursor OUT cursor_type)  IS
BEGIN
  OPEN  p_targets_cursor  FOR

     SELECT /*+ CARDINALITY (guids 10)*/ tgt.target_guid,
            tgt.target_name,
            tgt.target_type,
            tgt.load_timestamp,
            tgt.timezone_delta,
            tgt.display_name,
            tgt.type_display_name,
            tgt.service_type,
            tgt.host_name,
            tgt.emd_url,
            tgt.last_load_time,
            tgt.is_group,
            tgt.broken_str,
            tgt.broken_reason,
            tgt.last_rt_load_time,
            tgt.timezone_region,
            NVL(avail.current_status, MGMT_GLOBAL.G_STATUS_UNKNOWN) cur_status
      FROM TABLE(CAST(p_target_guids AS MGMT_USER_GUID_ARRAY)) guids,
           MGMT_TARGETS tgt, 
           MGMT_CURRENT_AVAILABILITY avail
     WHERE tgt.target_guid = avail.target_guid (+) AND
           tgt.target_guid = guids.column_value;


END get_targets_info;

FUNCTION get_value_string(p_package_variable IN VARCHAR2)
RETURN VARCHAR2
IS
l_value VARCHAR2(256) ;
-- this function is to find out the value of the package variables
BEGIN
  EXECUTE IMMEDIATE ' begin :x :=
  '||EM_CHECK.QUALIFIED_SQL_NAME(p_package_variable)||
      '; end ;' USING  OUT l_value ;
  RETURN(l_value) ;
EXCEPTION
WHEN OTHERS THEN
  raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
  'Invalid Value '||p_package_variable) ;
END get_value_string;

FUNCTION get_target_list(p_sql_query IN VARCHAR2)
RETURN MGMT_TARGET_GUID_ARRAY
IS
l_target_list MGMT_TARGET_GUID_ARRAY ;
l_tgt_cur cursor_Type;
-- this function is to find out the list of targets guids
BEGIN
--Fix part of Bug-4359323 SQL Coding Violation
--1)Changed Execute Immediate to Open Cursor which ensure only SELECT statements
--are executed
--2)l_target_list is not returned, but returned as part of
--mgmt_target_guid_array which ensures that only target_guid is selected

   OPEN l_tgt_cur FOR p_sql_query;
   FETCH l_tgt_cur BULK COLLECT INTO l_target_list;
   CLOSE l_tgt_cur;
  RETURN(l_target_list);
EXCEPTION
WHEN OTHERS THEN
  raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
  'Invalid Value '||p_sql_query) ;
END get_target_list;

FUNCTION  get_targets_by_host_with_priv(p_priv_name         IN VARCHAR2,
                                        p_target_type       IN VARCHAR2,
                                        p_target_name       IN VARCHAR2,
                                        p_host_name         IN VARCHAR2,
                                        p_target_types      IN MGMT_SHORT_STRING_ARRAY,
                                        p_max_row_num       IN NUMBER,
                                        p_sql_query            VARCHAR2 DEFAULT NULL,
                                        p_sql_for_aggregate IN NUMBER)
RETURN TARGETS_CURSOR
IS
    l_targets_cursor TARGETS_CURSOR;
    l_include_agents BOOLEAN := FALSE;
    l_tguids         MGMT_USER_GUID_ARRAY;
    l_group_members  MGMT_USER_GUID_ARRAY;
    l_user_name      mgmt_created_users.user_name%type := mgmt_user.get_current_em_user();
    l_uname          mgmt_created_users.user_name%type ;
    l_pname          mgmt_privs.priv_name%type;
    max_row_num      NUMBER := p_max_row_num;
    l_target_list    MGMT_TARGET_GUID_ARRAY;
    l_ret NUMBER;

BEGIN
    l_uname := UPPER(l_user_name);
    BEGIN
        l_ret := mgmt_target.get_type_property(p_target_type,
                                               MGMT_GLOBAL.G_IS_AGGREGATE_PROP,
                                               '0');
        IF l_ret=0 and p_sql_for_aggregate=1 THEN
            l_target_list :=  null;
        ELSE
            l_target_list := get_target_list(p_sql_query);
        END IF;

    EXCEPTION
        WHEN OTHERS THEN
        l_target_list :=  null;
    END ;

    BEGIN
        l_pname := EM_TARGET_UI.get_value_string(p_priv_name);
    EXCEPTION
            WHEN OTHERS THEN
            --raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
            -- 'Invalid Privilege') ;
            l_pname := MGMT_USER.VIEW_TARGET;
    END ;

  -- If this is a SUPER_USER or the privilege is VIEW_TARGET and the user has
  -- the VIEW_ANY_TARGET privilege, then return all targets
    IF MGMT_USER.HAS_PRIV(l_uname, MGMT_USER.SUPER_USER) = MGMT_USER.USER_HAS_PRIV OR
       (l_pname = MGMT_USER.VIEW_TARGET AND
       MGMT_USER.HAS_PRIV(l_uname, MGMT_USER.VIEW_ANY_TARGET) = MGMT_USER.USER_HAS_PRIV)
    THEN
        IF  p_target_types.COUNT > 0
        THEN
            -- Broke the existing SQL into two parts to enhance the performance:
            -- 1. If data for "ALL" target types are being fetched, then, force the usage of the
            -- index on the primay key column target type in the mgmt_targets table by 
            -- using a sub-query returning all the target types.
            -- 2. If data for a specific target type is being fetched, then force the usage of the 
            -- index on the primay key column target type in the mgmt_targets table by narrowing the
            -- search using "IN" clause.
            IF (p_target_type = '%') THEN
                OPEN l_targets_cursor FOR
                SELECT 
                    tgt.target_guid,
                    tgt.target_name,
                    tgt.target_type,
                    tgt.host_name, tgt.emd_url,tgt.type_display_name,tgt.display_name,tgt.timezone_region,
                    NVL(avail.current_status,MGMT_GLOBAL.G_STATUS_UNKNOWN) cur_status
                FROM
                    MGMT_TARGETS tgt,
                    MGMT_CURRENT_AVAILABILITY avail
                WHERE
                    --tgt.target_type IN (SELECT target_type FROM mgmt_target_types) AND 
		    upper(NVL(tgt.host_name,'%')) lIKE upper(p_host_name)
                    AND upper(tgt.target_name) LIKE upper(p_target_name)
                    AND avail.target_guid (+) = tgt.target_guid 
                    AND tgt.target_type IN (select column_value from TABLE(CAST(p_target_types AS MGMT_SHORT_STRING_ARRAY)))
                    AND rownum < max_row_num
                    AND  (l_target_list is null or  tgt.target_guid IN (select column_value from TABLE(l_target_list)));    
            ELSE
                OPEN l_targets_cursor FOR
                SELECT 
                    tgt.target_guid,
                    tgt.target_name,
                    tgt.target_type,
                    tgt.host_name, tgt.emd_url,tgt.type_display_name,tgt.display_name,tgt.timezone_region,
                    NVL(avail.current_status,MGMT_GLOBAL.G_STATUS_UNKNOWN) cur_status
                FROM 
                    MGMT_TARGETS tgt,
                    MGMT_CURRENT_AVAILABILITY avail
                WHERE 
                    tgt.target_type in (p_target_type)
                    AND upper(NVL(tgt.host_name,'%')) lIKE upper(p_host_name)
                    AND upper(tgt.target_name) LIKE upper(p_target_name)
                    AND tgt.target_guid = avail.target_guid (+)
                    --AND tgt.target_type IN (SELECT column_value from TABLE(CAST(p_target_types AS MGMT_SHORT_STRING_ARRAY)))
                    AND rownum < max_row_num
                    AND  (l_target_list is null or  tgt.target_guid IN (select column_value from TABLE(l_target_list)));    
            END IF;
        ELSE
            -- Broke the existing SQL into two parts to enhance the performance:
            -- 1. If data for "ALL" target types are being fetched, then, force the usage of the
            -- index on the primay key column target type in the mgmt_targets table by 
            -- using a sub-query returning all the target types.
            -- 2. If data for a specific target type is being fetched, then force the usage of the 
            -- index on the primay key column target type in the mgmt_targets table by narrowing the
            -- search using "IN" clause.
            IF (p_target_type = '%') THEN
                OPEN l_targets_cursor FOR
                SELECT 
                    tgt.target_guid,
                    tgt.target_name,
                    tgt.target_type,
                    tgt.host_name, tgt.emd_url,tgt.type_display_name,tgt.display_name,tgt.timezone_region,
                    NVL(avail.current_status,MGMT_GLOBAL.G_STATUS_UNKNOWN) cur_status
                FROM 
                    MGMT_TARGETS tgt,
                    MGMT_CURRENT_AVAILABILITY avail
                WHERE 
                    tgt.target_type IN (SELECT target_type FROM  mgmt_target_types)
                    AND upper(NVL(tgt.host_name,'%')) lIKE upper(p_host_name)
                    AND upper(tgt.target_name)  LIKE upper(p_target_name)
                    AND tgt.target_guid = avail.target_guid (+)
                    AND rownum < max_row_num
                    AND  (l_target_list is null or tgt.target_guid IN (select column_value from TABLE(l_target_list)));
            ELSE
                OPEN l_targets_cursor FOR
                SELECT 
                    tgt.target_guid,
                    tgt.target_name,
                    tgt.target_type,
                    tgt.host_name, tgt.emd_url,tgt.type_display_name,tgt.display_name,tgt.timezone_region,
                    NVL(avail.current_status,MGMT_GLOBAL.G_STATUS_UNKNOWN) cur_status
                FROM 
                    MGMT_TARGETS tgt,
                    MGMT_CURRENT_AVAILABILITY avail
                WHERE 
                    tgt.target_type IN (p_target_type)
                    AND upper(NVL(tgt.host_name,'%')) lIKE upper(p_host_name)
                    AND upper(tgt.target_name) LIKE upper(p_target_name)
                    AND tgt.target_guid = avail.target_guid (+)
                    AND rownum < max_row_num
                    AND  (l_target_list is null or tgt.target_guid IN (select column_value from TABLE(l_target_list)));
            END IF;
        END IF;
    ELSE
    -- not a super user
        IF  p_target_types.COUNT > 0 THEN
            -- Broke the existing SQL into two parts to enhance the performance:
            -- 1. If data for "ALL" target types are being fetched, then, force the usage of the
            -- index on the primay key column target type in the mgmt_targets table by 
            -- using a sub-query returning all the target types.
            -- 2. If data for a specific target type is being fetched, then force the usage of the 
            -- index on the primay key column target type in the mgmt_targets table by narrowing the
            -- search using "IN" clause.
               
            IF  (p_target_type = '%') THEN 
                OPEN l_targets_cursor FOR
                SELECT  
                    tgt.target_guid,
                    tgt.target_name,
                    tgt.target_type,
                    tgt.host_name, tgt.emd_url,tgt.type_display_name,tgt.display_name,tgt.timezone_region,
                    NVL(avail.current_status, MGMT_GLOBAL.G_STATUS_UNKNOWN) cur_status
                FROM 
                    MGMT_TARGETS tgt,
                    MGMT_CURRENT_AVAILABILITY avail
                WHERE 
                    tgt.target_guid = avail.target_guid (+)
                   -- AND tgt.target_type IN (SELECT target_type FROM  mgmt_target_types)
                    AND  UPPER(NVL(host_name,'%'))  like p_host_name
                    AND UPPER(tgt.target_name) like p_target_name
                    AND
                    MGMT_USER.HAS_PRIV(l_uname,l_pname,tgt.target_guid) = MGMT_USER.USER_HAS_PRIV  AND
                    tgt.target_type IN (select column_value from TABLE(CAST(p_target_types AS MGMT_SHORT_STRING_ARRAY)))
                    AND rownum < max_row_num
                    AND  
                    (l_target_list is null or  tgt.target_guid IN (SELECT column_value from TABLE(l_target_list)));
            ELSE
                OPEN l_targets_cursor FOR
                SELECT 
                    tgt.target_guid,
                    tgt.target_name,
                    tgt.target_type,
                    tgt.host_name, tgt.emd_url,tgt.type_display_name,tgt.display_name,tgt.timezone_region,
                    NVL(avail.current_status, MGMT_GLOBAL.G_STATUS_UNKNOWN) cur_status
                FROM 
                    MGMT_TARGETS tgt,
                    MGMT_CURRENT_AVAILABILITY avail
                WHERE 
                    tgt.target_guid = avail.target_guid (+)
                    AND tgt.target_type IN (p_target_type)
                    AND  UPPER(NVL(host_name,'%'))  like p_host_name
                    AND UPPER(tgt.target_name) like p_target_name
                    AND
                    MGMT_USER.HAS_PRIV(l_uname,l_pname,tgt.target_guid) = MGMT_USER.USER_HAS_PRIV  
		    --AND  tgt.target_type IN (select column_value from TABLE(CAST(p_target_types AS MGMT_SHORT_STRING_ARRAY)))
                    AND rownum < max_row_num
                    AND  (l_target_list is null or  tgt.target_guid IN (SELECT column_value FROM TABLE(l_target_list)));
            END IF;
        ELSE
            -- Broke the existing SQL into two parts to enhance the performance:
            -- 1. If data for "ALL" target types are being fetched, then, force the usage of the
            -- index on the primay key column target type in the mgmt_targets table by 
            -- using a sub-query returning all the target types.
            -- 2. If data for a specific target type is being fetched, then force the usage of the 
            -- index on the primay key column target type in the mgmt_targets table by narrowing the
            -- search using "IN" clause.
            
            IF (p_target_type = '%') THEN
                OPEN l_targets_cursor FOR
                SELECT tgt.target_guid,
                    tgt.target_name,
                    tgt.target_type,
                    tgt.host_name, tgt.emd_url,tgt.type_display_name,tgt.display_name,tgt.timezone_region,
                    NVL(avail.current_status, MGMT_GLOBAL.G_STATUS_UNKNOWN) cur_status
                FROM MGMT_TARGETS tgt,
                    MGMT_CURRENT_AVAILABILITY avail
                WHERE 
                    tgt.target_guid = avail.target_guid (+)
                    AND tgt.target_type IN (SELECT target_type FROM mgmt_target_types)
                    AND  UPPER(NVL(host_name,'%'))  like p_host_name
                    AND UPPER(tgt.target_name) like p_target_name
                    AND
                    MGMT_USER.HAS_PRIV(l_uname,l_pname,tgt.target_guid) = MGMT_USER.USER_HAS_PRIV
                    AND rownum < max_row_num
                    AND  (l_target_list is null or  tgt.target_guid IN (SELECT column_value FROM TABLE(l_target_list)));
            ELSE
                OPEN l_targets_cursor FOR
                SELECT 
                    tgt.target_guid,
                    tgt.target_name,
                    tgt.target_type,
                    tgt.host_name, tgt.emd_url,tgt.type_display_name,tgt.display_name,tgt.timezone_region,
                    NVL(avail.current_status, MGMT_GLOBAL.G_STATUS_UNKNOWN) cur_status
                FROM 
                    MGMT_TARGETS tgt,
                    MGMT_CURRENT_AVAILABILITY avail
                WHERE 
                    tgt.target_guid = avail.target_guid (+)
                    AND tgt.target_type IN (p_target_type)
                    AND  UPPER(NVL(host_name,'%'))  like p_host_name
                    AND UPPER(tgt.target_name) like p_target_name
                    AND
                    MGMT_USER.HAS_PRIV(l_uname,l_pname,tgt.target_guid) = MGMT_USER.USER_HAS_PRIV
                    AND rownum < max_row_num
                    AND  (l_target_list is null or  tgt.target_guid IN (select column_value from TABLE(l_target_list)));
            END IF;
        END IF;
    END IF;
    RETURN   l_targets_cursor;

END get_targets_by_host_with_priv;

FUNCTION  get_targets_by_host_assoc(p_priv_name IN VARCHAR2,
                                    p_target_type IN VARCHAR2,
                                    p_target_name IN VARCHAR2,
                                    p_host_name IN VARCHAR2,
				    p_target_types IN   MGMT_SHORT_STRING_ARRAY,
				    p_max_row_num IN NUMBER,
				    p_sql_query varchar2 default null,
				    p_sql_for_aggregate IN NUMBER,
				    p_child_type IN VARCHAR2,
				    p_child_name IN VARCHAR2,
				    p_child_types IN   MGMT_SHORT_STRING_ARRAY)	
RETURN TARGETS_CURSOR
IS
  l_targets_cursor TARGETS_CURSOR;
  l_include_agents BOOLEAN := FALSE;
  l_tguids         MGMT_USER_GUID_ARRAY;
  l_group_members  MGMT_USER_GUID_ARRAY;
  l_user_name      mgmt_created_users.user_name%type := mgmt_user.get_current_em_user();
  l_uname          mgmt_created_users.user_name%type ;
  l_pname          mgmt_privs.priv_name%type;
  max_row_num    NUMBER := p_max_row_num;
  l_child_type_count    NUMBER := 0;
  l_target_list MGMT_TARGET_GUID_ARRAY ;
  l_ret NUMBER;

BEGIN
  EMDW_LOG.REFRESH_CONTEXT ;
  EMDW_LOG.ERROR('one','target_selector');
  l_uname := UPPER(l_user_name);
 BEGIN
        l_ret := mgmt_target.get_type_property(p_target_type,
                                         MGMT_GLOBAL.G_IS_AGGREGATE_PROP,
                                         '0');
        IF l_ret=0 and p_sql_for_aggregate=1 THEN
          l_target_list :=  null;
        ELSE
           l_target_list := get_target_list(p_sql_query);
        END IF;
 EXCEPTION
 WHEN OTHERS THEN
    --raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
    -- 'Invalid Privilege') ;
    l_target_list :=  null;
  END ;
  BEGIN
  l_pname := EM_TARGET_UI.get_value_string(p_priv_name);
  IF  p_child_types.COUNT > 0
    THEN
      l_child_type_count :=1;
  END IF;

  EXCEPTION
  WHEN OTHERS THEN
    --raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
    -- 'Invalid Privilege') ;
  l_pname := MGMT_USER.VIEW_TARGET;
  END ;

  -- If this is a SUPER_USER or the privilege is VIEW_TARGET and the user has
  -- the VIEW_ANY_TARGET privilege, then return all targets
  IF MGMT_USER.HAS_PRIV(l_uname, MGMT_USER.SUPER_USER) = MGMT_USER.USER_HAS_PRIV OR
     (l_pname = MGMT_USER.VIEW_TARGET AND 
      MGMT_USER.HAS_PRIV(l_uname, MGMT_USER.VIEW_ANY_TARGET) = MGMT_USER.USER_HAS_PRIV)
  THEN
    IF  p_target_types.COUNT > 0 
    THEN
      OPEN l_targets_cursor FOR
         SELECT distinct  tgt.target_guid,
                 tgt.target_name,
                 tgt.target_type,
                 tgt.host_name, tgt.emd_url,tgt.type_display_name,tgt.display_name,tgt.timezone_region,
                 NVL(avail.current_status,MGMT_GLOBAL.G_STATUS_UNKNOWN) cur_status
            FROM MGMT_TARGETS tgt, 
                 MGMT_CURRENT_AVAILABILITY avail,
		 mgmt_flat_target_assoc a ,
		 mgmt_targets mt 
            WHERE a.is_membership =1 
	      AND tgt.target_guid = a.source_target_guid 
              AND mt.target_guid = a.assoc_target_guid
              AND  tgt.target_type LIKE p_target_type
              AND UPPER(NVL(tgt.host_name,'%')) lIKE p_host_name
              AND UPPER(tgt.target_name) LIKE p_target_name
              AND upper(mt.target_type) LIKE p_child_type
              AND UPPER(mt.target_name) LIKE p_child_name
              AND tgt.target_guid = avail.target_guid (+) 
              AND tgt.target_type IN (select column_value from TABLE(CAST(p_target_types AS MGMT_SHORT_STRING_ARRAY)))
	      AND ( l_child_type_count = 0 OR mt.target_type IN (select column_value from TABLE(CAST(p_child_types AS MGMT_SHORT_STRING_ARRAY))))
	      AND rownum < max_row_num
              AND  (l_target_list is null or  tgt.target_guid IN (select column_value from TABLE(l_target_list)));

    ELSE
       OPEN l_targets_cursor FOR
        SELECT distinct tgt.target_guid,
                 tgt.target_name,
                 tgt.target_type,
                 tgt.host_name, tgt.emd_url,tgt.type_display_name,tgt.display_name,tgt.timezone_region,
                 NVL(avail.current_status,MGMT_GLOBAL.G_STATUS_UNKNOWN) cur_status
            FROM MGMT_TARGETS tgt, 
                 MGMT_CURRENT_AVAILABILITY avail,
		 mgmt_flat_target_assoc a ,
		 mgmt_targets mt 
            WHERE a.is_membership =1 
	      AND tgt.target_guid = a.source_target_guid 
            AND mt.target_guid = a.assoc_target_guid
            AND  tgt.target_type LIKE p_target_type
		AND upper(mt.target_type) LIKE p_child_type
		AND UPPER(mt.target_name) LIKE p_child_name
            AND UPPER(NVL(tgt.host_name,'%')) lIKE p_host_name
            AND UPPER(tgt.target_name) LIKE p_target_name
            AND tgt.target_guid = avail.target_guid (+)
	    AND ( l_child_type_count = 0 OR mt.target_type IN (select column_value from TABLE(CAST(p_child_types AS MGMT_SHORT_STRING_ARRAY))))       
	    AND rownum < max_row_num
            AND  (l_target_list is null or  tgt.target_guid IN (select column_value from TABLE(l_target_list)));

    END IF;
  ELSE
    -- not a super user
   IF  p_target_types.COUNT > 0
    THEN
      OPEN l_targets_cursor FOR
        SELECT distinct tgt.target_guid,
               tgt.target_name,
               tgt.target_type,
               tgt.host_name, tgt.emd_url,tgt.type_display_name,tgt.display_name,tgt.timezone_region,
               NVL(avail.current_status, MGMT_GLOBAL.G_STATUS_UNKNOWN) cur_status
          FROM MGMT_TARGETS tgt, 
                 MGMT_CURRENT_AVAILABILITY avail,
		 mgmt_flat_target_assoc a ,
		 mgmt_targets mt 
            WHERE a.is_membership =1 
	    AND tgt.target_guid = a.source_target_guid 
            AND mt.target_guid = a.assoc_target_guid
            AND tgt.target_guid = avail.target_guid (+)
            AND tgt.target_type like p_target_type
       	    AND upper(mt.target_type) LIKE p_child_type
	    AND UPPER(mt.target_name) LIKE p_child_name
            AND  UPPER(NVL(tgt.host_name,'%'))  like p_host_name
            AND UPPER(tgt.target_name) like p_target_name
            AND 
            MGMT_USER.HAS_PRIV(l_uname,l_pname,tgt.target_guid) = MGMT_USER.USER_HAS_PRIV  
            AND 
            tgt.target_type IN (select column_value from TABLE(CAST(p_target_types AS MGMT_SHORT_STRING_ARRAY)))
   	    AND ( l_child_type_count = 0 OR mt.target_type IN (select column_value from TABLE(CAST(p_child_types AS MGMT_SHORT_STRING_ARRAY))))
            AND rownum < max_row_num
            AND  (l_target_list is null or  tgt.target_guid IN (select column_value from TABLE(l_target_list)));
    ELSE
      OPEN l_targets_cursor FOR
 
      SELECT distinct tgt.target_guid,
               tgt.target_name,
               tgt.target_type,
               tgt.host_name, tgt.emd_url,tgt.type_display_name,tgt.display_name,tgt.timezone_region,
               NVL(avail.current_status, MGMT_GLOBAL.G_STATUS_UNKNOWN) cur_status
           FROM MGMT_TARGETS tgt, 
                 MGMT_CURRENT_AVAILABILITY avail,
		 mgmt_flat_target_assoc a ,
		 mgmt_targets mt 
           WHERE a.is_membership =1 
	   AND tgt.target_guid = a.source_target_guid 
           AND mt.target_guid = a.assoc_target_guid
           AND tgt.target_guid = avail.target_guid (+)
           AND tgt.target_type like p_target_type
           AND  UPPER(NVL(tgt.host_name,'%'))  like p_host_name
           AND UPPER(tgt.target_name) like p_target_name
	   AND UPPER(mt.target_type) LIKE p_child_type
	   AND UPPER(mt.target_name) LIKE p_child_name  
           AND
            MGMT_USER.HAS_PRIV(l_uname,l_pname,tgt.target_guid) = MGMT_USER.USER_HAS_PRIV
           AND ( l_child_type_count = 0 OR mt.target_type IN (select column_value from TABLE(CAST(p_child_types AS MGMT_SHORT_STRING_ARRAY))))  
	AND rownum < max_row_num
  	AND  (l_target_list is null or  tgt.target_guid IN (select column_value from TABLE(l_target_list)));  
    END IF;
  END IF;

  RETURN   l_targets_cursor;

END GET_TARGETS_BY_HOST_ASSOC;

PROCEDURE get_types_in_target_selection(p_priv_name IN VARCHAR2,
                                        p_target_types_cur OUT cursor_type
                                        ) 
IS
BEGIN
  -- find the target types the user can see
  p_target_types_cur := MGMT_USER.GET_TARGET_TYPES(p_priv_name);
 EXCEPTION
  WHEN OTHERS THEN
  raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
  'Invalid Privilege') ;
END get_types_in_target_selection;


END  EM_TARGET_UI;
/
show err