Rem drv: <migrate type="data_upgrade" version="10.2.0.3"/>
Rem
Rem $Header: esm_core_reports.sql 20-jun-2006.04:46:14 niramach Exp $
Rem
Rem esm_core_reports.sql
Rem
Rem Copyright (c) 2005, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      esm_core_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    niramach    06/17/06 - Fix for bug 5331253
Rem    dsukhwal    11/18/05 - handle timezones 
Rem    dsukhwal    09/02/05 - fix timeSeriesChart
Rem    gsbhatia    07/23/05 - Fix repmgr header 
Rem    gsbhatia    07/03/05 - New repmgr header impl 
Rem    lgloyd      06/24/05 - normalize category strings 
Rem    dsukhwal    06/22/05 - add instruction text 
Rem    dsukhwal    05/27/05 - using builtin elements, bug 4359156 
Rem    dkjain      03/31/05 - Fix for elements appeared on the ADD page 
Rem    dsukhwal    02/01/05 - dsukhwal_reports2
Rem    dsukhwal    01/24/05 - Created
Rem

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;
  l_report_guid   RAW(16);
  l_element_guid  RAW(16);
  l_curr_order    NUMBER;
  l_curr_row      NUMBER;
BEGIN
    l_curr_order := 1;
    l_curr_row := 1;

----------------Violation Flux Start----------------------------------------
    -- late binding for any target type
    l_target_types := MGMT_IP_TARGET_TYPES();
    l_target_types.extend(1);
    l_target_types(1) := 'any_type';

    -- this report has a time period and the user can customize it
    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.TimePeriodOption',
      'true');
    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TimePeriodUserCust',
      'true');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TimePeriodParam',
      '0:0');
      
    --Delete earlier version of OOB system report
    BEGIN
      mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,
        'TIMED_VIOLATION_FLUX_NLSID',
        1,
        0);      
    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;
	
	l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'TIMED_VIOLATION_FLUX_NLSID',
      p_description_nlsid             => 'TIMED_VIOLATION_FLUX_DESC_NLSID',
      p_owner                         => mgmt_user.get_repository_owner,
      p_category_nlsid                => 'IPMSG_SECURITY_CATEGORY', 
      p_sub_category_nlsid            =>  'Security_Policy_NLSID',
      p_late_binding_target_types     =>  null,--l_target_types,
      p_late_binding_multi_targets    =>  0,
      p_show_table_of_contents        =>  0, 
      p_system_report                 =>  1,
      p_parameters                    =>  l_param_values
        );
--instruction text begin--------------------------------
l_param_values := MGMT_IP_PARAM_VALUE_LIST();
  l_param_values.extend(3);


  --The text to be displayed
  l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage',
  'VIOL_FLUX_INSTR_TEXT');

  --The resource bundle containing the text
  l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName',
  'oracle.sysman.resources.eml.esa.EsaMsg');

  --Specifies the style class
  l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass',
  'OraInstructionText');

  --Adding instruction text to the created report definition and passing the above parameters
  l_element_guid := mgmt_ip.add_element_to_report_def (
    p_report_guid => l_report_guid,
    p_element_name_nlsid => 'IPMSG_STYLED_TEXT',
    p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
    p_element_order => l_curr_order,
    p_element_row => l_curr_row,
    p_parameters => l_param_values,
    p_targets => null);
    
    l_curr_order := l_curr_order + 1;
    l_curr_row := l_curr_row  + 1;

--instruction text end--------------------------------  

    l_param_values := MGMT_IP_PARAM_VALUE_LIST();
    l_param_values.extend(8);
    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql',
      'true');
    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'BEGIN
        DECLARE
            rep_tz          VARCHAR2(200);
            user_tz_in          VARCHAR2(200);
            TYPE CURSOR_TYPE IS REF CURSOR;
            result_cursor_out CURSOR_TYPE;
            start_date_in DATE DEFAULT NULL;
            end_date_in  DATE DEFAULT NULL;
            query_string   VARCHAR(6000);            
        BEGIN
            result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??;
            start_date_in := ??EMIP_BIND_START_DATE??;
            end_date_in := ??EMIP_BIND_END_DATE??;
            user_tz_in := ??EMIP_BIND_TIMEZONE_REGION??;
            select TIMEZONE_REGION into rep_tz from mgmt$target where TARGET_TYPE = ''oracle_emrep'';
        
            query_string := ''WITH dates AS (SELECT 
          mgmt_view_util.adjust_tz(:1,:2,:3) as start_date, 
          mgmt_view_util.adjust_tz(:4,:5,:6) as end_date  
       from dual)
      SELECT 
           label, 
           time,
           sum(violations) --) as violations
       FROM(
           (
            SELECT
               ''''''||''NEW_LBL''||'''''' as label,
               mgmt_view_util.adjust_tz(rollup_timestamp, :7, :8) as time,
               average as violations --new violations
            FROM
               MGMT$METRIC_HOURLY
            WHERE
               key_value in (''''18'''',''''20'''',''''25'''') AND 
               ROLLUP_TIMESTAMP > (SELECT start_date FROM dates) AND
               ROLLUP_TIMESTAMP < (SELECT end_date FROM dates) AND
               target_type = ''''oracle_emrep'''' AND 
               metric_name = ''''TARGET_SECURITY_NEW_VIOLATIONS'''' AND
               metric_column = ''''NEW_VIOLATIONS'''' )
           UNION 
           (
            SELECT
               ''''''||''FIXED_LBL''||'''''' as label,
               mgmt_view_util.adjust_tz(rollup_timestamp,:9,:10) as time,
               average as violations --cleared violations
            FROM 
               MGMT$METRIC_HOURLY
            WHERE 
               key_value in (''''18'''',''''20'''',''''25'''') AND
               ROLLUP_TIMESTAMP > (SELECT start_date FROM dates) AND
               ROLLUP_TIMESTAMP < (SELECT end_date FROM dates) AND
               target_type = ''''oracle_emrep'''' AND 
               metric_name = ''''TARGET_SECURITY_CLEARED_VIOLATIONS'''' AND 
               metric_column = ''''CLEARED_VIOLATIONS''''  
             )
           )
       GROUP BY time, label
       ORDER BY time ASC'';
       OPEN result_cursor_out for query_string using start_date_in, user_tz_in, rep_tz, end_date_in, user_tz_in, rep_tz, user_tz_in, rep_tz, user_tz_in, rep_tz;
       END;
    END;');
       

    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.width',
      '400');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.height',
      '200');
    l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.chartType',
      'timeSeriesChart');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.fill',
      'cumulative');
    l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.translateKeyValues',
      'true');
    l_param_values(8) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.resourceBundle',
      'oracle.sysman.resources.eml.esa.EsaMsg');

    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_nlsid        => null,
      p_element_order       => l_curr_order,
      p_element_row         => l_curr_row,
      p_parameters	    => l_param_values, 
      p_targets             => null
    );

----------------Violation Flux End----------------------------------------


----------------Security Compliance Start----------------------------------------


    l_curr_order := 1;
    l_curr_row := 1;
    -- late binding for any target type
    l_target_types := MGMT_IP_TARGET_TYPES();
    l_target_types.extend(1);
    l_target_types(1) := 'any_type';

    -- this report has a time period and the user can customize it
    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.TimePeriodOption',
      'true');
    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TimePeriodUserCust',
      'true');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TimePeriodParam',
      '0:0');

  --Delete earlier version of OOB system report
  BEGIN
    mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,
      'TIMED_SECURITY_COMPLIANCE_NLSID',
      1,
      0);  
  EXCEPTION
    WHEN OTHERS THEN NULL;
  END; 
	
	l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'TIMED_SECURITY_COMPLIANCE_NLSID',
      p_description_nlsid             => 'TIMED_SECURITY_COMPLIANCE_DESC_NLSID',
      p_owner                         => mgmt_user.get_repository_owner,
      p_category_nlsid                => 'IPMSG_SECURITY_CATEGORY', 
      p_sub_category_nlsid            =>  'Security_Policy_NLSID',
      p_late_binding_target_types     =>  null,--l_target_types,
      p_late_binding_multi_targets    =>  0,
      p_show_table_of_contents        =>  0, 
      p_system_report                 =>  1,
      p_parameters                    =>  l_param_values
        );
--instruction text begin--------------------------------
l_param_values := MGMT_IP_PARAM_VALUE_LIST();
  l_param_values.extend(3);


  --The text to be displayed
  l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage',
  'SEC_COMPL_INSTR_TEXT');

  --The resource bundle containing the text
  l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName',
  'oracle.sysman.resources.eml.esa.EsaMsg');

  --Specifies the style class
  l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass',
  'OraInstructionText');

  --Adding instruction text to the created report definition and passing the above parameters
  l_element_guid := mgmt_ip.add_element_to_report_def (
    p_report_guid => l_report_guid,
    p_element_name_nlsid => 'IPMSG_STYLED_TEXT',
    p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
    p_element_order => l_curr_order,
    p_element_row => l_curr_row,
    p_parameters => l_param_values,
    p_targets => null);

    l_curr_order := l_curr_order + 1;
    l_curr_row := l_curr_row  + 1;

--instruction text end--------------------------------  

    l_curr_order := l_curr_order + 1;
    l_curr_row := l_curr_row  + 1;
    
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();
    l_param_values.extend(7);
    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql',
      'true');
    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'BEGIN
        DECLARE
           rep_tz          VARCHAR2(200);
           user_tz_in          VARCHAR2(200);
           TYPE CURSOR_TYPE IS REF CURSOR;
           result_cursor_out CURSOR_TYPE;
           start_date_in DATE DEFAULT NULL;
           end_date_in  DATE DEFAULT NULL;
           query_string   VARCHAR(6000);
        BEGIN
           result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??;
           start_date_in := ??EMIP_BIND_START_DATE??;
           end_date_in := ??EMIP_BIND_END_DATE??;
           user_tz_in := ??EMIP_BIND_TIMEZONE_REGION??;
           select TIMEZONE_REGION into rep_tz from mgmt$target where TARGET_TYPE = ''oracle_emrep'';
        
           query_string := ''SELECT ''''COMPL_SCORE_GRAPH_LABEL'''' as COLUMN_LABEL,
           mgmt_view_util.adjust_tz(rollup_timestamp, :1, :2) as ROLLUP_TIMESTAMP,
           round(avg(average)) as AVERAGE
       FROM 
           MGMT$METRIC_HOURLY
       WHERE 
           key_value in (''''18'''',''''20'''',''''25'''') AND
           ROLLUP_TIMESTAMP > mgmt_view_util.adjust_tz(:3,:4,:5) AND
           ROLLUP_TIMESTAMP < mgmt_view_util.adjust_tz(:6,:7,:8) AND
           target_type = ''''oracle_emrep'''' AND 
           metric_name = ''''TARGET_SECURITY_COMPLIANCE'''' AND                            
           metric_column = ''''COMPLIANCE_SCORE''''  
       GROUP BY rollup_timestamp 
       ORDER BY rollup_timestamp asc'';
       OPEN result_cursor_out for query_string using user_tz_in, rep_tz, start_date_in, user_tz_in, rep_tz, end_date_in, user_tz_in, rep_tz;
       END;
    END;');
       
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.width',
      '400');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.height',
      '200');
    l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.chartType',
      'timeSeriesChart');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.resourceBundle',
      'oracle.sysman.resources.eml.esa.EsaMsg');
    l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.translateKeyValues',
      '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_nlsid        => null,
      p_element_order       => l_curr_order,
      p_element_row         => l_curr_row,
      p_parameters	    => l_param_values, 
      p_targets             => null
    );

----------------Security Compliance End----------------------------------------
END;
/
COMMIT;