Rem
Rem $Header: ecm_os_oob_report.sql 08-sep-2005.01:23:35 niramach Exp $
Rem
Rem ecm_os_oob_report.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ecm_os_oob_report.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      Out of the box ecm hw reports that ship with EM
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem       nirama 09/08/05 - niramach_bug-4582321
Rem       nirama 09/02/05 - Moved the delta between beta->production to v102020.(bug 4582321) 
Rem       lgloyd 06/22/05 - normalize category strings 
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_target_types  MGMT_IP_TARGET_TYPES;    
    l_report_guid   RAW(16);
    l_element_guid  RAW(16);

  BEGIN
   BEGIN
        SELECT REPORT_GUID
        INTO l_report_guid
        FROM MGMT_IP_REPORT_DEF
        WHERE title_nlsid = 'ENTOS_CONFIG';
        mgmt_ip.delete_report(l_report_guid,1);
        EXCEPTION
        WHEN NO_DATA_FOUND
            THEN
            l_report_guid := NULL;
    END;
    l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'ENTOS_CONFIG',
      p_description_nlsid             => 'ENT_OS_DESC',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'IPMSG_DEPLOYMENT_AND_CONFIGURATION_CATEGORY',
      p_sub_category_nlsid            => 'OS_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(2);

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

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT OS_VENDOR, COUNT(OS_VENDOR) from mgmt$os_hw_summary GROUP BY OS_VENDOR');
      
    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       => 'OS_TITLE',
      p_element_order       => 1,
      p_element_row         => 1,
      p_parameters          => l_param_values,
      p_targets             => null
        );
    
    commit;
      
 --table
    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 name || '' '' || base_version || '' '' || update_level as "OS_SNAPSHOT_COMPONENT_NAME", 
            count(*) as "HC_TARGET_NAME" 
      from  mgmt$os_summary group by name, base_version, update_level');

   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       => 'OS_CONFIG_SUMMARY',
      p_element_order       => 2,
      p_element_row         => 2,
      p_parameters          => l_param_values,
      p_targets             => null
        );

    COMMIT;  


--table 2
    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 UNIQUE OS_SUMMARY as "OS_SUMMARY.OS" , 
              host_name as "HC_TARGET_NAME", 
            SYSTEM_CONFIG || '' '' || ma  as "HW_SNAPSHOT_COMPONENT_NAME" 
          FROM  mgmt$os_hw_summary');

    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       => 'OS_CONFIG_DETAILS',
      p_element_order       => 3,
      p_element_row         => 3,
      p_parameters          => l_param_values,
      p_targets             => null
        );

    COMMIT;  
    
    -- Group Level OS Reports..
    l_target_types := MGMT_IP_TARGET_TYPES();
    l_target_types.extend(1);
    l_target_types(1) := 'composite';
    
    BEGIN
        SELECT REPORT_GUID
        INTO l_report_guid
        FROM MGMT_IP_REPORT_DEF
        WHERE title_nlsid = 'TARGETOS_CONFIG';
        mgmt_ip.delete_report(l_report_guid,1);
        EXCEPTION
        WHEN NO_DATA_FOUND
            THEN
            l_report_guid := NULL;
    END;
    
    l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'TARGETOS_CONFIG',
      p_description_nlsid             => 'TARGET_OS_DESC',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'IPMSG_DEPLOYMENT_AND_CONFIGURATION_CATEGORY',
      p_sub_category_nlsid            => 'OS_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(2);

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

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT OS_VENDOR, COUNT(OS_VENDOR) from 
mgmt$os_hw_summary v1,    (
              select unique
                t.host_name as hname
              FROM
                mgmt$target t,
                (
                    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 
                     TARGET_GUID = ??EMIP_BIND_TARGET_GUID??
                ) lt1
              WHERE
                lt1.mguid = t.target_guid
      ) hosts
where hosts.hname = v1.host_name
GROUP BY OS_VENDOR');
      
    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       => 'OS_TITLE',
      p_element_order       => 1,
      p_element_row         => 1,
      p_parameters          => l_param_values,
      p_targets             => null
        );
    
    commit;
      
 --table
    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 name || '' '' || base_version || '' '' || update_level as "OS_SNAPSHOT_COMPONENT_NAME", 
          count(*) as "HC_TARGET_NAME"
      from  mgmt$os_summary v1, (
              select unique
                t.host_name as hname
              FROM
                mgmt$target t,
                (
                    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 
                     TARGET_GUID = ??EMIP_BIND_TARGET_GUID??
                ) lt1
              WHERE
                lt1.mguid = t.target_guid
      ) hosts
where hosts.hname = v1.host
group by name, base_version, update_level');

    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       => 'OS_CONFIG_SUMMARY',
      p_element_order       => 2,
      p_element_row         => 2,
      p_parameters          => l_param_values,
      p_targets             => null
        );

    COMMIT;  


--table 2
    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 UNIQUE OS_SUMMARY as "OS_SUMMARY.OS", 
               host_name as "HC_TARGET_NAME", 
              SYSTEM_CONFIG || '' '' || ma   as "HW_SNAPSHOT_COMPONENT_NAME"
    FROM  mgmt$os_hw_summary v1,(
              select unique
                t.host_name as hname
              FROM
                mgmt$target t,
                (
                    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 
                     TARGET_GUID = ??EMIP_BIND_TARGET_GUID??
                ) lt1
              WHERE
                lt1.mguid = t.target_guid
      ) hosts
where hosts.hname = v1.host_name');

    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       => 'OS_CONFIG_DETAILS',
      p_element_order       => 3,
      p_element_row         => 3,
      p_parameters          => l_param_values,
      p_targets             => null
        );

    COMMIT;  
    
 
    
  END;

END;

/

SET DEFINE ON