Rem DO NOT PUT HEADER HERE BECAUSE THIS IS REFERENCED BY
Rem ip_data_upgrade.sql ALREADY.
Rem
Rem $Header: sysdash_outofbox_reports.sql 28-aug-2005.22:33:45 vmotamar Exp $
Rem
Rem sysdash_outofbox_reports.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      sysdash_outofbox_reports.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    vmotamar    08/28/05 - Moving hidden report creation to v102010 path
Rem    paachary    07/31/05 - Adding the header
Rem    paachary    07/18/05 - paachary_bug-4492521
Rem    paachary    07/15/05 - Created
Rem

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

DECLARE 
    l_target_types  MGMT_IP_TARGET_TYPES;
    l_param_classes MGMT_IP_PARAM_CLASSES;
    l_param_values  MGMT_IP_PARAM_VALUE_LIST;
    l_targets       MGMT_IP_TARGET_LIST;
    iCounter        NUMBER DEFAULT 0;
    l_report_guid   RAW(16);
    l_element_guid  RAW(16);
    l_order         NUMBER;    
    l_count         NUMBER;    

--------------------SYSTEM MONITORING DASHBOARD REPORT BEGIN----------------------

  BEGIN

      SELECT  COUNT(*)
      INTO    l_count
      FROM    MGMT_IP_REPORT_DEF
      WHERE   TITLE_NLSID ='Group'
      AND     DESCRIPTION_NLSID = 'Use this report to see the Dashboard for a Group, you choose';

      IF (l_count > 0 ) THEN
          SELECT  REPORT_GUID 
          INTO   l_report_guid
          FROM    MGMT_IP_REPORT_DEF
          WHERE   TITLE_NLSID ='Group'
          AND     DESCRIPTION_NLSID = 'Use this report to see the Dashboard for a Group, you choose';

          DELETE MGMT_IP_REPORT_ELEM_PARAMS
          WHERE REPORT_GUID = l_report_guid;

          DELETE MGMT_IP_REPORT_DEF_ELEMENTS
          WHERE REPORT_GUID = l_report_guid;

          DELETE MGMT_IP_REPORT_ELEM_TARGETS
          WHERE REPORT_GUID = l_report_guid;

          DELETE MGMT_IP_REPORT_DEF
          WHERE REPORT_GUID = l_report_guid;
      END IF;

      SELECT  COUNT(*)
      INTO    l_count
      FROM    MGMT_IP_REPORT_DEF
      WHERE   TITLE_NLSID ='System'
      AND     DESCRIPTION_NLSID = 'Use this report to see the Dashboard for a System, you choose';

      IF (l_count > 0 ) THEN
          SELECT  REPORT_GUID 
          INTO    l_report_guid
          FROM    MGMT_IP_REPORT_DEF
          WHERE   TITLE_NLSID ='System'
          AND     DESCRIPTION_NLSID = 'Use this report to see the Dashboard for a System, you choose';

          DELETE MGMT_IP_REPORT_ELEM_PARAMS
          WHERE REPORT_GUID = l_report_guid;

          DELETE MGMT_IP_REPORT_DEF_ELEMENTS
          WHERE REPORT_GUID = l_report_guid;

          DELETE MGMT_IP_REPORT_ELEM_TARGETS
          WHERE REPORT_GUID = l_report_guid;

         DELETE MGMT_IP_REPORT_DEF
         WHERE REPORT_GUID = l_report_guid;
     END IF;

    l_target_types := MGMT_IP_TARGET_TYPES();
    FOR c1 IN
    (
        SELECT distinct target_type
        FROM mgmt_type_properties
        WHERE (property_name = 'is_group' AND
              property_value = '1')
        OR (property_name = 'is_system' AND
              property_value = '1')

    )
    LOOP
        iCounter := iCounter + 1;
        l_target_types.extend(1);
        l_target_types(iCounter) := c1.target_type;
    END LOOP; 

    l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'IPMSG_SYSTEM_DASHBOARD_TITLE',
      p_description_nlsid             => 'IPMSG_SYSTEM_DASHBOARD_REPORT_DESC',
      p_owner                         =>  mgmt_user.GET_REPOSITORY_OWNER,
      p_category_nlsid                => 'IPMSG_MONITORING_CATEGORY', 
      p_sub_category_nlsid            => 'IPMSG_DASHBOARDS_CATEGORY',
      p_late_binding_target_types     =>  l_target_types,
      p_late_binding_multi_targets    =>  0,
      p_show_table_of_contents        =>  0, 
      p_system_report                 =>  1,
      p_style                         => 'DASHBOARD',
      p_help_topic_id                 => 'info_pub_gc_cs_ob_smd',
      p_show_navigation               => 0
        );

    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.SysDashSelectedTarget',
      'BEGIN
        DECLARE
           TYPE CURSOR_TYPE IS REF CURSOR;
           result_cursor_out CURSOR_TYPE;
           tgt_guid_in  MGMT_IP_TGT_GUID_ARRAY;
           start_date_in DATE DEFAULT NULL;
           end_date_in  DATE DEFAULT NULL;
           query_string   VARCHAR(6000);
        BEGIN
            result_cursor_out := ?;
            tgt_guid_in := ?;
            start_date_in := ?;
            end_date_in := ?;
 
        query_string := ''SELECT object_name AS PRIVILEGE,
                            COUNT(principal) as "No. of users"
                            FROM mgmt_esa_any_dict_report
                            WHERE target_guid = :1
                            GROUP BY object_name '';
 
          OPEN result_cursor_out for query_string using tgt_guid_in(1);
      END;
    END;');

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.SysDashRefreshTime',
      '2');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.SysDashTargetViewType',
      'Full');
     
     l_element_guid := mgmt_ip.add_element_to_report_def (
      p_report_guid         => l_report_guid,
      p_element_name_nlsid  => 'IPMSG_SYSTEM_DASHBOARD',
      p_element_type_nlsid  => 'IPMSG_SYSTEM_DASHBOARD_TARGET_TYPE',
      p_header_nlslid       => null,
      p_element_order       => 1, 
      p_element_row         => 1, 
      p_parameters          => l_param_values,
      p_targets             => null
        ); 
      
COMMIT;  

--------------------SYSTEM MONITORING DASHBOARD REPORT END---------------------------


END;
/