Rem
Rem $Header: ecm_odbsw_oob_report.sql 20-apr-2005.07:42:31 niramach Exp $
Rem
Rem ecm_hw_oob_report.sql
Rem ecm_odbsw_oob_reportRem Copyright (c)  2004, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ecm_odbsw_oob_report.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      Out of the box ecm oracle db reports that ship with EM
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem       nirama 09/02/05 - Moved the delta between beta->production to v102020.(bug 4582321) 
Rem       nirama 04/20/05 - niramach_repos_upgrade
Rem       agor   03/29/05 - fix 4257600 
Rem       agor   01/28/05 - agor_more_ecm_reports
Rem       agor   12/30/04 - agor_ecm_report
Rem       agor   11/19/04 - Creation
Rem

SET DEFINE OFF

BEGIN

  DECLARE 
    l_param_values  MGMT_IP_PARAM_VALUE_LIST;
    l_param_values1  MGMT_IP_PARAM_VALUE_LIST;
    l_target_types  MGMT_IP_TARGET_TYPES;    
    l_report_guid   RAW(16);
    l_element_guid  RAW(16);

  BEGIN
    l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'ENTDB_CONFIG',
      p_description_nlsid             => 'ENT_ODB_DESC',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'CONFIG_CAT',
      p_sub_category_nlsid            => 'ODBSW_SUBCAT',
      p_late_binding_target_types     =>  null,
      p_late_binding_multi_targets    =>  0,
      p_show_table_of_contents        =>  0,
      p_system_report                 =>  1
        );
 
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();

    l_param_values.extend(4);

    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT external_name || '' '' || version, count( distinct container_guid ) 
         from
         (
            SELECT /*+ USE_HASH(targs inst) */
                            inst.external_name as external_name,
                            inst.version as version,
                            inst.container_guid as container_guid,
                            targs.target_guid as tg,
                            inst.host_name as hn,
                            inst.htguid as htguid,
                            inst.num_patched as num_patched,
                            inst.target_type as itarget_type,
                            targs.target_type as ttarget_type
            from MGMT$ORACLE_SW_ENT_INSTALL inst, MGMT$ORACLE_SW_ENT_TARGETS 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.pname IS NULL and inst.pvalue IS NULL)
            )
            where 
              itarget_type = ''oracle_database''
            group by  external_name,version');

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.ChartParamController.height', '150');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.ChartParamController.width', '300');
      
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
             'oracle.sysman.eml.ip.render.elem.ChartParamController.pieShowSlicePercentLabels',
            'true');
      
    l_element_guid := mgmt_ip.add_element_to_report_def (
      p_report_guid         => l_report_guid,
      p_element_name_nlsid  => 'IPMSG_USER_CHART_FROM_SQL',
      p_element_type_nlsid  => 'IPMSG_ANY_TARGET_TYPE',
      p_header_nlslid       => 'ODB_CONFIG_INSTALL_SUMMARY',
      p_element_order       => 1,
      p_element_row         => 1,
      p_parameters          => l_param_values,
      p_targets             => null
        );
    
    commit;
   
   
    l_param_values1 := MGMT_IP_PARAM_VALUE_LIST();

    l_param_values1.extend(4);

    l_param_values1(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT external_name|| '' '' || version, count(distinct tg) 
      from
      (
      SELECT /*+ USE_HASH(targs inst) */
                      inst.external_name as external_name,
                      inst.version as version,
                      inst.container_guid as container_guid,
                      targs.target_guid as tg,
                      inst.host_name as hn,
                      inst.htguid as htguid,
                      inst.num_patched as num_patched,
                      inst.target_type as itarget_type,
                      targs.target_type as ttarget_type
      from MGMT$ORACLE_SW_ENT_INSTALL inst, MGMT$ORACLE_SW_ENT_TARGETS 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.pname IS NULL and inst.pvalue IS NULL)
      )
      where 
        itarget_type = ''oracle_database''
      group by  external_name,version');


    l_param_values1(2) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.ChartParamController.height', '150');
    l_param_values1(3) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.ChartParamController.width', '300');
--    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.eml.ecm.policy.util.PolicyMsg');

    l_param_values1(4) := MGMT_IP_PARAM_VALUE_RECORD(
             'oracle.sysman.eml.ip.render.elem.ChartParamController.pieShowSlicePercentLabels',
            'true');

    l_element_guid := mgmt_ip.add_element_to_report_def (
      p_report_guid         => l_report_guid,
      p_element_name_nlsid  => 'IPMSG_USER_CHART_FROM_SQL',
      p_element_type_nlsid  => 'IPMSG_ANY_TARGET_TYPE',
      p_header_nlslid       => 'ODB_CONFIG_TARGETS_SUMMARY',
      p_element_order       => 2,
      p_element_row         => 1,
      p_parameters          => l_param_values1,
      p_targets             => null
        );

    COMMIT;  
    
 --table (summary)
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();

    l_param_values.extend(2);

    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
              'SELECT  external_name || '' '' ||  version as "Database Installations",
                count(distinct tg) as "FUNC_INSTANCES_LBL",
                count( distinct container_guid ) as "FUNC_INSTALLATIONS_LBL",
                case sum(num_patched) when 0 then ''No'' else ''Yes'' end as "Patched"
        from
        (
        SELECT /*+ USE_HASH(targs inst) */
                        inst.external_name as external_name,
                        inst.version as version,
                        inst.container_guid as container_guid,
                        targs.target_guid as tg,
                        inst.host_name as hn,
                        inst.htguid as htguid,
                        inst.num_patched as num_patched,
                        inst.target_type as itarget_type,
                        targs.target_type as ttarget_type
        from MGMT$ORACLE_SW_ENT_INSTALL inst, MGMT$ORACLE_SW_ENT_TARGETS 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.pname IS NULL and inst.pvalue IS NULL)
        )
        where 
          itarget_type = ''oracle_database''
        group by itarget_type, external_name, version 
        ORDER BY external_name');

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.eml.ecm.util.EcmResourceBundle');

    l_element_guid := mgmt_ip.add_element_to_report_def (
      p_report_guid         => l_report_guid,
      p_element_name_nlsid  => 'IPMSG_USER_TABLE_FROM_SQL',
      p_element_type_nlsid  => 'IPMSG_ANY_TARGET_TYPE',
      p_header_nlslid       => 'ODB_CONFIG_SUMMARY',
      p_element_order       => 4,
      p_element_row         => 3,
      p_parameters          => l_param_values,
      p_targets             => null
        );

    COMMIT;      
    
-- All Installations - details..
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();

    l_param_values.extend(3);

    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'select inst.external_name || '' '' || inst.version as "Database Installation", 
              inst.host_name as "HC_TARGET_NAME" , 
              inst.container_location as "Oracle Home"
      from MGMT$ORACLE_SW_ENT_INSTALL inst
      where inst.target_type=''oracle_database'' order by inst.external_name || '' '' || inst.version ');

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.eml.ecm.resources.HCResourceBundle');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.TableRender.tableSplitColumn', '0');

    l_element_guid := mgmt_ip.add_element_to_report_def (
      p_report_guid         => l_report_guid,
      p_element_name_nlsid  => 'IPMSG_USER_TABLE_FROM_SQL',
      p_element_type_nlsid  => 'IPMSG_ANY_TARGET_TYPE',
      p_header_nlslid       => 'ODB_CONFIG_INSTALL_DETAILS',
      p_element_order       => 5,
      p_element_row         => 3,
      p_parameters          => l_param_values,
      p_targets             => null
        );
    COMMIT;          
    
-- All DB Targets - details..
    l_param_values1 := MGMT_IP_PARAM_VALUE_LIST();

    l_param_values1.extend(3);

    l_param_values1(1) := MGMT_IP_PARAM_VALUE_RECORD(
          'oracle.sysman.eml.ip.render.elem.sqlStatement',
          'select inst.external_name || '' '' || inst.version as "Database Installation", 
           targs.target_name as "Target", 
           targs.host_target_name as "HC_TARGET_NAME", 
           targs.home_location as "Oracle Home"
    from MGMT$ORACLE_SW_ENT_TARGETS targs, 
    MGMT$ORACLE_SW_ENT_INSTALL inst
    where 
     targs.home_location = inst.container_location and targs.target_type=''oracle_database''
     order by inst.external_name || '' '' || inst.version');

    l_param_values1(2) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.eml.ecm.resources.HCResourceBundle');
    l_param_values1(3) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.TableRender.tableSplitColumn', '0');

    l_element_guid := mgmt_ip.add_element_to_report_def (
      p_report_guid         => l_report_guid,
      p_element_name_nlsid  => 'IPMSG_USER_TABLE_FROM_SQL',
      p_element_type_nlsid  => 'IPMSG_ANY_TARGET_TYPE',
      p_header_nlslid       => 'ODB_CONFIG_TARGETS_DETAILS',
      p_element_order       => 6,
      p_element_row         => 4,
      p_parameters          => l_param_values1,
      p_targets             => null
        );
    COMMIT;    
      
    
-- Group Level Oracle Database Reports..
    l_target_types := MGMT_IP_TARGET_TYPES();
    l_target_types.extend(1);
    l_target_types(1) := 'composite';

    l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'TARGETDB_CONFIG',
      p_description_nlsid             => 'TARGET_ODB_DESC',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'CONFIG_CAT',
      p_sub_category_nlsid            => 'ODBSW_SUBCAT',
      p_late_binding_target_types     =>  l_target_types,
      p_late_binding_multi_targets    =>  0,
      p_show_table_of_contents        =>  0,
      p_system_report                 =>  1
        );
 
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();

    l_param_values.extend(4);

    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.ChartParamController.height', '150');
    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.ChartParamController.width', '300');

    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT external_name || '' '' || version,  count(distinct container_guid)
      fROM
      (
      SELECT 
           external_name,
           version, 
           inst_container_guid as container_guid,
           v1.target_guid as tg,
           v1.target_type as tt,
           v1.mtype as mt
      FROM MGMT$ORACLE_SW_GRP_TARGETS V1,
           (
        SELECT UNIQUE m.member_target_guid as mguid
        FROM
             mgmt$target_flat_members m
        WHERE
            m.AGGREGATE_TARGET_GUID = ??EMIP_BIND_TARGET_GUID??
          UNION
            SELECT UNIQUE t.target_guid as mguid
               FROM  mgmt$target t
            WHERE 
              t.target_guid =  ??EMIP_BIND_TARGET_GUID??
        )ts
       where v1.mtype = ''oracle_database'' and
             ts.mguid = v1.target_guid
      UNION
      SELECT 
           external_name,
           version, 
           inst_container_guid as container_guid,
           NULL as tg,
           v2.target_type as tt,
           v2.mtype as mt
      FROM MGMT$ORACLE_SW_GRP_INSTALL V2,
           (
        SELECT UNIQUE m.member_target_guid as mguid
        FROM
             mgmt$target_flat_members m
        WHERE
            m.AGGREGATE_TARGET_GUID = ??EMIP_BIND_TARGET_GUID??
          UNION
            SELECT UNIQUE t.target_guid as mguid
               FROM  mgmt$target t
            WHERE 
              t.target_guid =  ??EMIP_BIND_TARGET_GUID??
        )ts
       where v2.target_guid = ts.mguid and v2.mtype = ''oracle_database'' 
      ) v1
      GROUP BY external_name, version');
            
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
             'oracle.sysman.eml.ip.render.elem.ChartParamController.pieShowSlicePercentLabels',
            'true');
            
          l_element_guid := mgmt_ip.add_element_to_report_def (
            p_report_guid         => l_report_guid,
            p_element_name_nlsid  => 'IPMSG_USER_CHART_FROM_SQL',
            p_element_type_nlsid  => 'IPMSG_ANY_TARGET_TYPE',
            p_header_nlslid       => 'ODB_CONFIG_INSTALL_SUMMARY',
            p_element_order       => 1,
            p_element_row         => 1,
            p_parameters          => l_param_values,
            p_targets             => null);



-- Group Chart 2
    l_param_values1 := MGMT_IP_PARAM_VALUE_LIST();

    l_param_values1.extend(4);

    l_param_values1(1) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.ChartParamController.height', '150');
    l_param_values1(2) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.ChartParamController.width', '300');

    l_param_values1(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT external_name || '' '' || version,  count(distinct tg)
        FROM
        (
        SELECT 
             external_name,
             version, 
             inst_container_guid as inst_container_guid,
             v1.target_guid as tg,
             v1.target_type as tt,
             v1.mtype as mt
        FROM MGMT$ORACLE_SW_GRP_TARGETS V1,
             (
          SELECT UNIQUE m.member_target_guid as mguid
          FROM
               mgmt$target_flat_members m
          WHERE
               m.AGGREGATE_TARGET_GUID = ??EMIP_BIND_TARGET_GUID??
            UNION
              SELECT UNIQUE t.target_guid as mguid
                 FROM  mgmt$target t
              WHERE 
                  t.target_guid = ??EMIP_BIND_TARGET_GUID??
          )ts
         where v1.mtype = ''oracle_database'' and
               ts.mguid = v1.target_guid
        UNION
        SELECT 
             external_name,
             version, 
             inst_container_guid as inst_container_guid,
             NULL as tg,
             v2.target_type as tt,
             v2.mtype as mt
        FROM MGMT$ORACLE_SW_GRP_INSTALL V2,
             (
          SELECT UNIQUE m.member_target_guid as mguid
          FROM
               mgmt$target_flat_members m
          WHERE
               m.AGGREGATE_TARGET_GUID = ??EMIP_BIND_TARGET_GUID??
            UNION
              SELECT UNIQUE t.target_guid as mguid
                 FROM  mgmt$target t
              WHERE 
                t.TARGET_GUID = ??EMIP_BIND_TARGET_GUID??
          )ts
         where v2.target_guid = ts.mguid and v2.mtype = ''oracle_database'' 
        ) v1
        GROUP BY external_name, version');
        

--    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.eml.ecm.policy.util.PolicyMsg');
    l_param_values1(4) := MGMT_IP_PARAM_VALUE_RECORD(
             'oracle.sysman.eml.ip.render.elem.ChartParamController.pieShowSlicePercentLabels',
            'true');

    l_element_guid := mgmt_ip.add_element_to_report_def (
      p_report_guid         => l_report_guid,
      p_element_name_nlsid  => 'IPMSG_USER_CHART_FROM_SQL',
      p_element_type_nlsid  => 'IPMSG_ANY_TARGET_TYPE',
      p_header_nlslid       => 'ODB_CONFIG_TARGETS_SUMMARY',
      p_element_order       => 2,
      p_element_row         => 1,
      p_parameters          => l_param_values1,
      p_targets             => null
        );

    COMMIT;  
    
 --Group level table (summary)
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();

    l_param_values.extend(2);

--       case sum(case when exists (SELECT * from mgmt_inv_patch p where p.container_guid = inst_container_guid) then 1 else 0 end) when 0 then ''No'' else ''Yes'' end as patched
    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
          'SELECT external_name || '' '' || version as "Database Installation", 
                  count(distinct tg) "Targets", 
                  count(distinct inst_container_guid) as "Installations"
      fROM
      (
      SELECT 
           external_name,
           version, 
           inst_container_guid as inst_container_guid,
           v1.target_guid as tg,
           v1.target_type as tt,
           v1.mtype as mt
      FROM MGMT$ORACLE_SW_GRP_TARGETS V1,
           (
        SELECT UNIQUE m.member_target_guid as mguid
        FROM
             mgmt$target_flat_members m
        WHERE
             m.AGGREGATE_TARGET_GUID = ??EMIP_BIND_TARGET_GUID??
          UNION
            SELECT UNIQUE t.target_guid as mguid
               FROM  mgmt$target t
            WHERE 
                t.target_guid = ??EMIP_BIND_TARGET_GUID??
        )ts
       where v1.mtype = ''oracle_database'' and
             ts.mguid = v1.target_guid
      UNION
      SELECT 
           external_name,
           version, 
           inst_container_guid as inst_container_guid,
           NULL as tg,
           v2.target_type as tt,
           v2.mtype as mt
      FROM MGMT$ORACLE_SW_GRP_INSTALL V2,
           (
        SELECT UNIQUE m.member_target_guid as mguid
        FROM
             mgmt$target_flat_members m
        WHERE
             m.AGGREGATE_TARGET_GUID = ??EMIP_BIND_TARGET_GUID??
          UNION
            SELECT UNIQUE t.target_guid as mguid
               FROM  mgmt$target t
            WHERE 
              t.TARGET_GUID = ??EMIP_BIND_TARGET_GUID??
        )ts
       where v2.target_guid = ts.mguid and v2.mtype = ''oracle_database'' 
      ) v1
      GROUP BY external_name, version');

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.eml.ecm.resources.HCResourceBundle');

    l_element_guid := mgmt_ip.add_element_to_report_def (
      p_report_guid         => l_report_guid,
      p_element_name_nlsid  => 'IPMSG_USER_TABLE_FROM_SQL',
      p_element_type_nlsid  => 'IPMSG_ANY_TARGET_TYPE',
      p_header_nlslid       => 'ODB_CONFIG_SUMMARY',
      p_element_order       => 4,
      p_element_row         => 3,
      p_parameters          => l_param_values,
      p_targets             => null
        );

    COMMIT;      
    
-- All Installations - details..
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();

    l_param_values.extend(3);

    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT external_name || '' '' || version as "Database Installation",  
       host_name as "HC_TARGET_NAME",
       container_location as "Oracle Home"
        FROM
        (
        SELECT 
             external_name,
             version, 
             inst_container_guid as inst_container_guid,
             v1.target_guid as tg,
             v1.target_type as tt,
             v1.mtype as mt,
             v1.host_name as host_name,
             v1.container_location as container_location
        FROM MGMT$ORACLE_SW_GRP_TARGETS V1,
             (
          SELECT UNIQUE m.member_target_guid as mguid
          FROM
               mgmt$target_flat_members m
          WHERE
               m.AGGREGATE_TARGET_GUID = ??EMIP_BIND_TARGET_GUID??
            UNION
              SELECT UNIQUE t.target_guid as mguid
                 FROM  mgmt$target t
              WHERE 
                  t.target_guid = ??EMIP_BIND_TARGET_GUID??
          )ts
         where v1.mtype = ''oracle_database'' and
               ts.mguid = v1.target_guid
        UNION
        SELECT 
             external_name,
             version, 
             inst_container_guid as inst_container_guid,
             NULL as tg,
             v2.target_type as tt,
             v2.mtype as mt,
             v2.host_name as host_name,
             v2.container_location as container_location
        FROM MGMT$ORACLE_SW_GRP_INSTALL V2,
             (
          SELECT UNIQUE m.member_target_guid as mguid
          FROM
               mgmt$target_flat_members m
          WHERE
               m.AGGREGATE_TARGET_GUID = ??EMIP_BIND_TARGET_GUID?? 
            UNION
              SELECT UNIQUE t.target_guid as mguid
                 FROM  mgmt$target t
              WHERE 
                  t.target_guid = ??EMIP_BIND_TARGET_GUID??
          )ts
         where v2.target_guid = ts.mguid and 
               v2.mtype = ''oracle_database''
        ) v1 order by external_name || '' '' || version');

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.eml.ecm.resources.HCResourceBundle');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.TableRender.tableSplitColumn', '0');

    l_element_guid := mgmt_ip.add_element_to_report_def (
      p_report_guid         => l_report_guid,
      p_element_name_nlsid  => 'IPMSG_USER_TABLE_FROM_SQL',
      p_element_type_nlsid  => 'IPMSG_ANY_TARGET_TYPE',
      p_header_nlslid       => 'ODB_CONFIG_INSTALL_DETAILS',
      p_element_order       => 5,
      p_element_row         => 3,
      p_parameters          => l_param_values,
      p_targets             => null
        );
    COMMIT;          
    
-- All DB Targets - details..
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();

    l_param_values.extend(3);

    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
          'oracle.sysman.eml.ip.render.elem.sqlStatement',
               'SELECT 
           v1.external_name || '' '' || v1.version as "Database Installation",
           v2.target_name as "Target",
           v2.host_name as "HC_TARGET_NAME",
           v2.container_location as "Oracle Home"
      FROM MGMT$ORACLE_SW_GRP_INSTALL V1, MGMT$ORACLE_SW_GRP_TARGETS V2,
           (
        SELECT UNIQUE m.member_target_guid as mguid
        FROM
             mgmt$target_flat_members m
        WHERE
             m.AGGREGATE_TARGET_GUID = ??EMIP_BIND_TARGET_GUID??
          UNION
            SELECT UNIQUE t.target_guid as mguid
               FROM  mgmt$target t
            WHERE 
               t.TARGET_GUID = ??EMIP_BIND_TARGET_GUID??
        )ts
       where v2.mtype = ''oracle_database'' and
             v1.mtype = ''oracle_database'' and 
             ts.mguid = v2.target_guid and
             v2.container_location = v1.container_location
             order by v1.external_name || '' '' || v1.version ');
      
    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.eml.ecm.resources.HCResourceBundle');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.TableRender.tableSplitColumn', '0');

    l_element_guid := mgmt_ip.add_element_to_report_def (
      p_report_guid         => l_report_guid,
      p_element_name_nlsid  => 'IPMSG_USER_TABLE_FROM_SQL',
      p_element_type_nlsid  => 'IPMSG_ANY_TARGET_TYPE',
      p_header_nlslid       => 'ODB_CONFIG_TARGETS_DETAILS',
      p_element_order       => 6,
      p_element_row         => 4,
      p_parameters          => l_param_values,
      p_targets             => null
        );
    

    commit;
    
  END;

END;

/

SET DEFINE ON