Rem
Rem $Header: monitoring_outofbox_reports.sql 24-aug-2007.02:56:19 denath Exp $
Rem
Rem monitoring_outofbox_reports.sql
Rem
Rem Copyright (c) 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      monitoring_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    denath      08/21/07 - Backport denath_bug-6274425 from main
Rem    tsubrama    05/31/07 - adding 20 most common performance fix
Rem    denath      08/13/07 - Fix 6274425.EMR_Alert_Violation_History_Group.
Rem    mkm         05/25/07 - monitoring reports
Rem    mkm         05/25/07 - Created
Rem

DECLARE 
    l_target_types  MGMT_IP_TARGET_TYPES;
    --l_types_for_target  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_order         NUMBER;
    l_elem_order    NUMBER;
    l_curr_row      NUMBER;
BEGIN

-------------------- 20 MOST COMMON ALERTS AND VIOLATIONS REPORT BEGINS --------------------------------------------------
    BEGIN
       MGMT_IP.delete_report_by_title('SYSMAN', 'EMR_Common_AlertsViolations_All', 0);
       EXCEPTION WHEN OTHERS THEN NULL;
    END;

    -- 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');



    l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'EMR_Common_AlertsViolations_All',
      p_description_nlsid             => 'EMR_Common_AlertsViolations_All_Desc',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'IPMSG_MONITORING_CATEGORY', 
      p_sub_category_nlsid            => 'IPMSG_ALERTS_AND_POLICY_VIOLATIONS_CATEGORY',
      p_late_binding_target_types     =>  null,
      p_late_binding_multi_targets    =>  0,
      p_show_table_of_contents        =>  0, 
      p_system_report                 =>  1,
      p_parameters                    =>  l_param_values,
      p_help_topic_id                 =>  'info_pub_gc_cs_mcav_report'
      );
   l_elem_order := 1;
   l_curr_row := 1;

 -------------------- REPORT DESCRIPTION --------------------------------------------------
    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.TextParamBean.textMessage',
      'COMM_ALRT_VIO_DESC');
    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle',
      'OraInstructionText');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName',
      'oracle.sysman.resources.eml.ip.OOTBResourceBundle');
    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_header_nlsid        => null,
      p_element_order       => l_elem_order,
      p_element_row         => l_curr_row,
      p_parameters          => l_param_values,
      p_targets             => null
        );

-------------------- ADDING TABLE FROM SQL --------------------------------------------------
    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.sqlStatement',
      '
       select * from(
         SELECT 
           met.column_label METRIC_ID,
           met.target_type TARGET_TYPE_ID,
           vio.violation_level SEVERITY_ID,
           COUNT(*) NEW_ID,
          NVL(
            Round( 
             COUNT(DISTINCT vio.target_guid)/( select COUNT(*) from mgmt_targets tgt where tgt.target_type=met.target_type)*100
            ,2)
         ,0) REL_TAR_AFFFECT_PER_ID
        FROM mgmt_violations vio , (select distinct metric_guid, target_type, column_label from mgmt_metrics) met
        WHERE  met.metric_guid = vio.policy_guid
        AND vio.violation_type = 0 
        AND vio.violation_level in( 20,25 )
        AND vio.collection_timestamp >=  ??EMIP_BIND_START_DATE??
        AND vio.collection_timestamp <= ??EMIP_BIND_END_DATE??
        GROUP BY met.column_label, met.target_type, vio.violation_level
        ORDER BY 4 DESC
       ) where rownum <21
      ');

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

    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
      '3');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
      'descending');
    l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow',
      '10');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.severityColumn',
      '2');

    l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.targetTypeColumns',
      'TARGET_TYPE_ID');

    l_elem_order := l_elem_order +1; 
    l_curr_row := l_curr_row+1;
    l_element_guid := mgmt_ip.add_element_to_report_def ( 
      p_report_guid         => l_report_guid,
      p_element_name_nlsid  => 'IPMSG_TABLE_FROM_SQL',
      p_element_type_nlsid  => 'IPMSG_ANY_TARGET_TYPE',
      p_header_nlsid        => 'EMR_20_Most_Common_Alerts_by_AlertCount',
      p_element_order       => l_elem_order, 
      p_element_row         => l_curr_row, 
      p_parameters          => l_param_values,
      p_targets             => null
      );


-------------------- ADDING TABLE FROM SQL --------------------------------------------------
    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.sqlStatement',
      '
       SELECT * from(
         SELECT 
           NVL(PM.MESSAGE, NVL(PEM.MESSAGE, policy.POLICY_NAME)) POLICY_RULE_ID,
           policy.target_type TARGET_TYPE_ID,
           NVL(CM.MESSAGE, NVL(CEM.MESSAGE, policy.category)) CATEGORY_ID, 
           vio.violation_level SEVERITY_ID,
           count(vio.violation_level) NEW_ID,
           NVL(
             Round( 
               COUNT(DISTINCT vio.target_guid)/( select COUNT(*) from mgmt_targets tgt where tgt.target_type=policy.target_type)*100
              ,2)
          ,0) REL_TAR_AFFFECT_PER_ID
        FROM mgmt_violations vio , mgmt$policies policy,
	(SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY'' AND LANGUAGE_CODE=??EMIP_BIND_LOCALE_LANGUAGE?? AND COUNTRY_CODE=??EMIP_BIND_LOCALE_COUNTRY??) PM,
	(SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY'' AND LANGUAGE_CODE=''en''AND COUNTRY_CODE='' '') PEM,
	(SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''CATEGORY'' AND LANGUAGE_CODE=??EMIP_BIND_LOCALE_LANGUAGE?? AND COUNTRY_CODE=??EMIP_BIND_LOCALE_COUNTRY??) CM,
	(SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''CATEGORY'' AND LANGUAGE_CODE=''en'' AND COUNTRY_CODE='' '') CEM 
      WHERE  policy.policy_guid = vio.policy_guid
       AND vio.violation_type = 3 
       AND vio.violation_level in(20,25,18)
       AND vio.collection_timestamp >=  ??EMIP_BIND_START_DATE??
       AND vio.collection_timestamp <= ??EMIP_BIND_END_DATE??
       AND policy.POLICY_LABEL_NLSID=PM.MESSAGE_ID(+) AND policy.POLICY_LABEL_NLSID=PEM.MESSAGE_ID(+)
       AND policy.CATEGORY_NLSID=CM.MESSAGE_ID(+) AND policy.CATEGORY_NLSID=CEM.MESSAGE_ID(+)
     GROUP BY NVL(PM.MESSAGE, NVL(PEM.MESSAGE, policy.POLICY_NAME)), 
              policy.target_type, 
	      NVL(CM.MESSAGE, NVL(CEM.MESSAGE, policy.category)),
	      vio.violation_level
     ORDER BY 5 DESC
   ) WHERE ROWNUM <21

      ');

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
      'oracle.sysman.resources.eml.ip.OOTBResourceBundle');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
      '4');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
      'descending');
    l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow',
      '10');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.severityColumn',
      '3');
    l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.targetTypeColumns',
      'TARGET_TYPE_ID');

    l_elem_order := l_elem_order +1; 
    l_curr_row := l_curr_row+1;
    l_element_guid := mgmt_ip.add_element_to_report_def ( 
      p_report_guid         => l_report_guid,
      p_element_name_nlsid  => 'IPMSG_TABLE_FROM_SQL',
      p_element_type_nlsid  => 'IPMSG_ANY_TARGET_TYPE',
      p_header_nlsid        => 'EMR_20_Most_Common_Policy_Violations_by_ViolationCount',
      p_element_order       => l_elem_order, 
      p_element_row         => l_curr_row, 
      p_parameters          => l_param_values,
      p_targets             => null
      );


-------------------- 20 MOST COMMON ALERTS AND VIOLATIONS FOR A TARGET BEGINS --------------------------------------------------
    BEGIN
       MGMT_IP.delete_report_by_title('SYSMAN', 'EMR_Common_AlertsViolations', 0);
       EXCEPTION WHEN OTHERS THEN NULL;
    END;


    l_target_types := MGMT_IP_TARGET_TYPES();
    l_target_types.extend(1);

    -------- TARGET TYPES TO BE DISPLAYED FOR SELECTION -------------------------------------
    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');


    l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'EMR_Common_AlertsViolations',
      p_description_nlsid             => 'EMR_Common_AlertsViolations_Desc',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'IPMSG_MONITORING_CATEGORY', 
      p_sub_category_nlsid            => 'IPMSG_ALERTS_AND_POLICY_VIOLATIONS_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_parameters                    =>  l_param_values,
      p_help_topic_id                 =>  'info_pub_gc_cs_mcav_target_report'
      );

    l_elem_order :=1;
    l_curr_row :=1;

 -------------------- REPORT DESCRIPTION --------------------------------------------------
    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.TextParamBean.textMessage',
      'COMM_ALRT_VIO_TGT_DESC');
    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle',
      'OraInstructionText');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName',
      'oracle.sysman.resources.eml.ip.OOTBResourceBundle');
    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_header_nlsid        => null,
      p_element_order       => l_elem_order,
      p_element_row         => l_curr_row,
      p_parameters          => l_param_values,
      p_targets             => null
        );

-------------------- ADDING TABLE FROM SQL --------------------------------------------------
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();
    l_param_values.extend(6);

    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      '
       SELECT * from (
          SELECT
             column_label METRIC_ID,
             decode(alert_state,''Critical'',25,''Warning'',20) SEVERITY_ID, 
             count(alert_state) NEW_ID
          FROM mgmt$alert_history 
          WHERE alert_state in(''Critical'',''Warning'')
            AND target_guid = ??EMIP_BIND_TARGET_GUID??
            AND collection_timestamp >= ??EMIP_BIND_START_DATE?? 
            AND collection_timestamp <= ??EMIP_BIND_END_DATE??
         GROUP BY column_label,decode(ALERT_STATE,''Critical'',25,''Warning'',20)
         ORDER BY 3 DESC
       ) WHERE ROWNUM <21

      ');

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
      'oracle.sysman.resources.eml.ip.OOTBResourceBundle');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
      '2');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
      'descending');
    l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow',
      '10');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.severityColumn',
      '1');

    l_elem_order := l_elem_order +1; 
    l_curr_row := l_curr_row+1;
    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_nlsid        => 'EMR_20_Most_Common_Alerts_by_AlertCount',
      p_element_order       => l_elem_order, 
      p_element_row         => l_curr_row, 
      p_parameters          => l_param_values,
      p_targets             => null
      );


-------------------- ADDING TABLE FROM SQL --------------------------------------------------
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();
    l_param_values.extend(6);

    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      '
       SELECT * FROM(
       SELECT 
          NVL(PM.MESSAGE, NVL(PEM.MESSAGE, policy.POLICY_NAME)) POLICY_RULE_ID,
          NVL(CM.MESSAGE, NVL(CEM.MESSAGE, vio.category)) CATEGORY_ID, 
          vio.violation_level SEVERITY_ID,
          count(vio.violation_level) NEW_ID
       FROM MGMT$POLICY_VIOLATION_HISTORY vio, mgmt$policies policy,
	(SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY'' AND LANGUAGE_CODE=??EMIP_BIND_LOCALE_LANGUAGE?? AND COUNTRY_CODE=??EMIP_BIND_LOCALE_COUNTRY??) PM,
	(SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY'' AND LANGUAGE_CODE=''en''AND COUNTRY_CODE='' '') PEM,
	(SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''CATEGORY'' AND LANGUAGE_CODE=??EMIP_BIND_LOCALE_LANGUAGE?? AND COUNTRY_CODE=??EMIP_BIND_LOCALE_COUNTRY??) CM,
	(SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''CATEGORY'' AND LANGUAGE_CODE=''en'' AND COUNTRY_CODE='' '') CEM 
       WHERE vio.policy_guid = policy.policy_guid
         AND vio.target_guid = ??EMIP_BIND_TARGET_GUID??
         AND collection_timestamp >= ??EMIP_BIND_START_DATE??
         AND collection_timestamp <= ??EMIP_BIND_END_DATE??
         AND policy.POLICY_LABEL_NLSID=PM.MESSAGE_ID(+) AND policy.POLICY_LABEL_NLSID=PEM.MESSAGE_ID(+)
         AND policy.CATEGORY_NLSID=CM.MESSAGE_ID(+) AND policy.CATEGORY_NLSID=CEM.MESSAGE_ID(+) 
      GROUP BY  
	NVL(PM.MESSAGE, NVL(PEM.MESSAGE, policy.POLICY_NAME)),
	NVL(CM.MESSAGE, NVL(CEM.MESSAGE, vio.category)) , vio.violation_level
      ORDER BY 4 DESC
      ) WHERE rownum<21
      ');

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
      'oracle.sysman.resources.eml.ip.OOTBResourceBundle');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
      '3');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
      'descending');
    l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow',
      '10');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.severityColumn',
      '2');

    l_elem_order := l_elem_order +1; 
    l_curr_row := l_curr_row+1;
    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_nlsid        => 'EMR_20_Most_Common_Policy_Violations_by_ViolationCount',
      p_element_order       => l_elem_order, 
      p_element_row         => l_curr_row, 
      p_parameters          => l_param_values,
      p_targets             => null
      );



---------------------------------------Aavailability History for Target------------------------
    BEGIN
       MGMT_IP.delete_report_by_title('SYSMAN', 'EMR_Availability_History_Target', 0);
       EXCEPTION WHEN OTHERS THEN NULL;
    END;

    l_target_types := MGMT_IP_TARGET_TYPES();
    l_target_types.extend(1);

    -------- TARGET TYPES TO BE DISPLAYED FOR SELECTION -------------------------------------
    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');

-------------------- AVAILABILITY TEST REPORT BEGINS --------------------------------------------------
    l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'EMR_Availability_History_Target',
      p_description_nlsid             => 'EMR_Availability_History_Target_Desc',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'IPMSG_MONITORING_CATEGORY', 
      p_sub_category_nlsid            => 'EMR_Availability_Reports',
      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_parameters                    =>  l_param_values,
      p_help_topic_id                 =>  'info_pub_gc_cs_ah_target_report'
      );
    l_elem_order := 1;
    l_curr_row := 1;

 -------------------- REPORT DESCRIPTION --------------------------------------------------
    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.TextParamBean.textMessage',
      'AVAIL_HIST_TGT_DESC');
    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle',
      'OraInstructionText');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName',
      'oracle.sysman.resources.eml.ip.OOTBResourceBundle');
    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_header_nlsid        => null,
      p_element_order       => l_elem_order,
      p_element_row         => l_curr_row,
      p_parameters          => l_param_values,
      p_targets             => null
        );

 -------------------- ADDING TABLE FROM SQL --------------------------------------------------
    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.sqlStatement',
      'SELECT sum(Avail) OVERALL_AVAILABILITY_PER_ID , sum(T_Uptime) TOTAL_UPTIME_HRS_ID , sum(T_Downtime) TOTAL_DOWNTIME_HRS_ID , sum(T_Blackouttime) TOTAL_BLACKOUT_TIME_HRS_ID, sum(T_Unmonitoredtime) TOTAL_UNMONITORED_TIME_HRS_ID from(
      select 0 Avail, 0 T_Uptime, 0 T_Downtime, 0 T_Blackouttime, 0 T_Unmonitoredtime from dual UNION ALL
      SELECT round( st_dur.tgt_up/(decode(st_dur.tgt_up+st_dur.agent_down+st_dur.tgt_down,0,1,st_dur.tgt_up+st_dur.agent_down+st_dur.tgt_down))*100,2) Avail
      ,round((st_dur.tgt_up*24),2) T_Uptime, round((st_dur.tgt_down*24),2) T_Downtime, round((st_dur.blackout*24),2) T_Blackouttime, round(((st_dur.agent_down+st_dur.metric_error+st_dur.pend_unknown+st_dur.unreach)*24),2) T_Unmonitoredtime
      FROM (
        SELECT SUM( decode(AVAI_STATUS,''agent down'',DURATION,0)) agent_down,
	SUM(decode(AVAI_STATUS,''blackout'',DURATION,0)) blackout,
	SUM(decode(AVAI_STATUS,''metric error'',DURATION,0)) metric_error,
	SUM(decode(AVAI_STATUS,''pending/unknown'',DURATION,0)) pend_unknown,
	SUM(decode(AVAI_STATUS,''target down'',DURATION,0)) tgt_down,
	SUM(decode(AVAI_STATUS,''target up'',DURATION,0)) tgt_up,
	SUM(decode(AVAI_STATUS,''unreachable'',DURATION,0)) unreach FROM(
		SELECT LOWER(A.AVAILABILITY_STATUS) AVAI_STATUS
		,ROUND(least(nvl(A.end_timestamp,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE))), MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) )
		-greatest(A.start_timestamp,MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) ),4) DURATION
		FROM mgmt$availability_history A,MGMT$TARGET B WHERE A.target_guid=??EMIP_BIND_TARGET_GUID?? AND A.TARGET_GUID=B.TARGET_GUID 
	      and A.start_timestamp>=(select min(NVL(end_timestamp,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))) from mgmt$availability_history where target_guid=??EMIP_BIND_TARGET_GUID?? )
	      and ((A.start_timestamp>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) AND NVL(A.end_timestamp,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))<MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION))
		   OR(MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) between A.start_timestamp AND NVL(A.end_timestamp,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE))))OR
		   (MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) between A.start_timestamp and NVL(A.end_timestamp,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))))
	 ) 
        )st_dur)');

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
      'oracle.sysman.resources.eml.ip.OOTBResourceBundle');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
      '1');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
      'descending');
    l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow',
      '10');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.nameValueDisplay',
      '1');
    l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.annotationMessageMsgid',
      'AVAILABILITY_FORMULA_ID');


    l_elem_order := l_elem_order +1; 
    l_curr_row := l_curr_row+1;
    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_nlsid        => 'EMR_Summary',
      p_element_order       => l_elem_order, 
      p_element_row         => l_curr_row, 
      p_parameters          => l_param_values,
      p_targets             => null
    );


 -------------------- ADDING CHART FROM SQL --------------------------------------------------
    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.sqlStatement',
	'SELECT a.AVAILABILITY_STATUS,SUM(a.VALUE ) FROM((select ''DOWN_TIME_TXT_ID'' AVAILABILITY_STATUS,0 VALUE, 2 ORDER_COL from dual union all select ''UP_TIME_TXT_ID'' AVAILABILITY_STATUS,0 VALUE, 1 ORDER_COL from dual union all select ''SYS_ERR_TIME_TXT_ID'' AVAILABILITY_STATUS,0 VALUE, 5 ORDER_COL from dual union all select ''AGENT_DOWN_TIME_TXT_ID'' AVAILABILITY_STATUS,0 VALUE, 4 ORDER_COL from dual union all select ''BLACKOUT_TIME_TXT_ID'' AVAILABILITY_STATUS,0 VALUE, 3 ORDER_COL from dual union all select ''STATUS_PEND_TIME_TXT_ID'' AVAILABILITY_STATUS,0 VALUE, 6 ORDER_COL from dual )
	UNION ALL
	SELECT decode(LOWER(AVAILABILITY_STATUS),''target down'',''DOWN_TIME_TXT_ID'',''target up'',''UP_TIME_TXT_ID''
               ,''metric error'',''SYS_ERR_TIME_TXT_ID'',''agent down'',''AGENT_DOWN_TIME_TXT_ID'',''unreachable'',''UNREACHABLE_TXT_ID''
               ,''blackout'',''BLACKOUT_TIME_TXT_ID'',''pending/unknown'',''STATUS_PEND_TIME_TXT_ID''
       )AVAILABILITY_STATUS
	,round(SUM( least(nvl(end_timestamp,(CAST(systimestamp at time zone T.TIMEZONE_REGION AS DATE)))
	,MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.TIMEZONE_REGION))-greatest(start_timestamp
	,MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.TIMEZONE_REGION)))*100,4 ) as VALUE,  
	decode(LOWER(AVAILABILITY_STATUS),''target down'',2,''target up'',1,''metric error'',5
              ,''agent down'',4,''unreachable'',7,''blackout'',3,''pending/unknown'',6
       )ORDER_COL
	from mgmt$availability_history b,MGMT$TARGET T
	WHERE b.target_guid=??EMIP_BIND_TARGET_GUID?? and b.target_guid=T.TARGET_GUID and LOWER(AVAILABILITY_STATUS)!=''unreachable''
	and b.start_timestamp >= 
	(select min(NVL(end_timestamp,(CAST(systimestamp at time zone T.TIMEZONE_REGION AS DATE)))) 
		from mgmt$availability_history where target_guid = ??EMIP_BIND_TARGET_GUID?? ) and((b.start_timestamp > 
	   MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.TIMEZONE_REGION) and NVL(b.end_timestamp,(CAST(systimestamp at time zone T.TIMEZONE_REGION AS DATE))) 
	   < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.TIMEZONE_REGION) )
	   OR
	   ( MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.TIMEZONE_REGION) between b.start_timestamp and NVL(b.end_timestamp,(CAST(systimestamp at time zone T.TIMEZONE_REGION AS DATE))))
	   OR
	   ( MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.TIMEZONE_REGION) between b.start_timestamp and NVL(b.end_timestamp,(CAST(systimestamp at time zone T.TIMEZONE_REGION AS DATE))))
	) group by LOWER(AVAILABILITY_STATUS),
	 decode(LOWER(AVAILABILITY_STATUS),''target down'',2,''target up'',1,''metric error'',5,''agent down'',4
                ,''unreachable'',7,''blackout'',3,''pending/unknown'',6
         )
	) a
	GROUP BY a.AVAILABILITY_STATUS,a.ORDER_COL ORDER BY a.ORDER_COL'
	);

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.resourceBundle',
      'oracle.sysman.resources.eml.ip.OOTBResourceBundle');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.width',
      '150');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.height',
      '150');

     l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.pieValuesInLegend',
      'percent');
/*
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.pieShowSlicePercentLabels',
      'true');
*/
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.annotationMessageMsgid',
      'CHART_DISCLAIMER_ID');
    l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.translateKeyValues',
      'true');


    l_elem_order := l_elem_order+1;
    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        => 'EMR_Breakdown_by_Availability_State',
      p_element_order       => l_elem_order, 
      p_element_row         => l_curr_row, 
      p_parameters          => l_param_values,
      p_targets             => null
      );

-------------------- ADDING TABLE FROM SQL --------------------------------------------------
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();
    l_param_values.extend(6);

    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT DECODE (availability_status,''Target Down'',0,''Target Up'',1,''Metric Error'', 2, 
            ''Agent Down'',3, ''Unreachable'',4,''Blackout'',5, ''Pending/Unknown'',6) AVAILABILITY_STATE_ID,
      MGMT_VIEW_UTIL.ADJUST_TZ(av.start_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) START_TIME_ID, 
      MGMT_VIEW_UTIL.ADJUST_TZ(av.end_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) END_TIME_ID, 
      ltrim(to_char(trunc( NVL(av.end_timestamp
	,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-av.start_timestamp ),''009''))||''d'' 
	||'':''||ltrim(to_char(MOD(trunc( (NVL(av.end_timestamp
	,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-av.start_timestamp)*24 ),24),''09''))||''h'' 
	||'':''||ltrim(to_char(MOD(trunc( (NVL(av.end_timestamp
	,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-av.start_timestamp)*24*60 ),60),''09''))||''m'' 
	||'':''||ltrim(to_char(MOD(trunc( (NVL(av.end_timestamp
	,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-av.start_timestamp)*24*60*60 ),60),''09''))||''s'' DURATION_ID,
	ah.message MESSAGE_ID,
	aa2.annotation_message ANNOTATION_ID,
	aa3.message NOTIF_MSG_ID
        FROM mgmt$availability_history av,
	     MGMT$TARGET B, 
	     mgmt$alert_history ah,
	     (select aa.source_obj_guid,aa.annotation_message 
              from mgmt$alert_annotations aa, (select source_obj_guid, MAX(annotation_timestamp)  latest_timestamp
                                               from mgmt$alert_annotations
					       where annotation_type IS NULL
					       and target_guid = ??EMIP_BIND_TARGET_GUID??
                                               group by source_obj_guid) la 
              where aa.source_obj_guid = la.source_obj_guid 
              and aa.annotation_timestamp = la.latest_timestamp) aa2,
	      (select source_obj_guid, delivery_message message from 
              mgmt$alert_notif_log where target_guid = ??EMIP_BIND_TARGET_GUID??) aa3
	WHERE 
	ah.violation_guid = av.severity_guid
	and aa2.source_obj_guid (+) = av.severity_guid
	and aa3.source_obj_guid (+) = av.severity_guid
	and av.target_guid=??EMIP_BIND_TARGET_GUID?? AND av.target_guid=B.TARGET_GUID 
	and av.start_timestamp >= (select min(NVL(end_timestamp,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))) from mgmt$availability_history where target_guid=??EMIP_BIND_TARGET_GUID??)
        and((av.start_timestamp > 
	    MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and NVL(av.end_timestamp
	    ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE))) < 
	    MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION))
	   OR
	   (MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) between av.start_timestamp and NVL(av.end_timestamp,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE))))
	   OR
	   (MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) between av.start_timestamp and NVL(av.end_timestamp,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE))))
	)ORDER BY av.start_timestamp DESC');

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
      'oracle.sysman.resources.eml.ip.OOTBResourceBundle');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
      '1');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
      'descending');
    l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow',
      '10');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.availabilityStatusColumn',
      '0');


    l_elem_order := l_elem_order+1;
    l_curr_row := l_curr_row+1;
    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_nlsid        => 'EMR_Availability_History_Details',
      p_element_order       => l_elem_order, 
      p_element_row         => l_curr_row, 
      p_parameters          => l_param_values,
      p_targets             => null
    );

---------------------------------------Aavailability History for Redundancy Group------------------------

    BEGIN
       MGMT_IP.delete_report_by_title('SYSMAN', 'EMR_Availability_History_Group', 0);
       EXCEPTION WHEN OTHERS THEN NULL;
    END;


    l_target_types := MGMT_IP_TARGET_TYPES();
    l_target_types.extend(1);

    -------- TARGET TYPES TO BE DISPLAYED FOR SELECTION -------------------------------------
    l_target_types(1) := 'generic_redundancy_group';
    
    -- 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');

-------------------- AVAILABILITY TEST REPORT BEGINS --------------------------------------------------
    l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'EMR_Availability_History_Group',
      p_description_nlsid             => 'EMR_Availability_History_Group_Desc',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'IPMSG_MONITORING_CATEGORY', 
      p_sub_category_nlsid            => 'EMR_Availability_Reports',
      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_parameters                    =>  l_param_values,
      p_help_topic_id                 =>  'info_pub_gc_cs_ah_red_group_report'
      );
     l_elem_order := 1;
     l_curr_row := 1;

 -------------------- REPORT DESCRIPTION --------------------------------------------------
    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.TextParamBean.textMessage',
      'AVAIL_HIST_REDUN_GRP_DESC');
    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle',
      'OraInstructionText');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName',
      'oracle.sysman.resources.eml.ip.OOTBResourceBundle');
    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_header_nlsid        => null,
      p_element_order       => l_elem_order,
      p_element_row         => l_curr_row,
      p_parameters          => l_param_values,
      p_targets             => null
        );
 -------------------- ADDING TABLE FROM SQL --------------------------------------------------
    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.sqlStatement',
      'SELECT sum(Avail) OVERALL_AVAILABILITY_PER_ID , sum(T_Uptime) TOTAL_UPTIME_HRS_ID , sum(T_Downtime) TOTAL_DOWNTIME_HRS_ID , sum(T_Blackouttime) TOTAL_BLACKOUT_TIME_HRS_ID, sum(T_Unmonitoredtime) TOTAL_UNMONITORED_TIME_HRS_ID from(
      select 0 Avail, 0 T_Uptime, 0 T_Downtime, 0 T_Blackouttime, 0 T_Unmonitoredtime from dual UNION ALL
      SELECT round( st_dur.tgt_up/(decode(st_dur.tgt_up+st_dur.agent_down+st_dur.tgt_down,0,1,st_dur.tgt_up+st_dur.agent_down+st_dur.tgt_down))*100,2) Avail
      ,round((st_dur.tgt_up*24),2) T_Uptime, round((st_dur.tgt_down*24),2) T_Downtime, round((st_dur.blackout*24),2) T_Blackouttime, round(((st_dur.agent_down+st_dur.metric_error+st_dur.pend_unknown+st_dur.unreach)*24),2) T_Unmonitoredtime
      FROM (
        SELECT SUM( decode(AVAI_STATUS,''agent down'',DURATION,0)) agent_down,
	SUM(decode(AVAI_STATUS,''blackout'',DURATION,0)) blackout,
	SUM(decode(AVAI_STATUS,''metric error'',DURATION,0)) metric_error,
	SUM(decode(AVAI_STATUS,''pending/unknown'',DURATION,0)) pend_unknown,
	SUM(decode(AVAI_STATUS,''target down'',DURATION,0)) tgt_down,
	SUM(decode(AVAI_STATUS,''target up'',DURATION,0)) tgt_up,
	SUM(decode(AVAI_STATUS,''unreachable'',DURATION,0)) unreach FROM(
		SELECT LOWER(A.AVAILABILITY_STATUS) AVAI_STATUS
		,ROUND(least(nvl(A.end_timestamp,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE))), MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) )
		-greatest(A.start_timestamp,MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) ),4) DURATION
		FROM mgmt$availability_history A,MGMT$TARGET B WHERE A.target_guid=??EMIP_BIND_TARGET_GUID?? AND A.TARGET_GUID=B.TARGET_GUID 
	        and A.start_timestamp>=(select min(NVL(end_timestamp,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))) from mgmt$availability_history where target_guid=??EMIP_BIND_TARGET_GUID?? )
	        and ((A.start_timestamp>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) AND NVL(A.end_timestamp,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))
		<MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION))
		OR(MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) between A.start_timestamp AND NVL(A.end_timestamp,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE))))OR
		(MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) between A.start_timestamp and NVL(A.end_timestamp,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))))
	 ) 
        )st_dur)');

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
      'oracle.sysman.resources.eml.ip.OOTBResourceBundle');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
      '1');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
      'descending');
    l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow',
      '10');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.nameValueDisplay',
      '1');
    l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.annotationMessageMsgid',
      'AVAILABILITY_FORMULA_ID');

   l_elem_order := l_elem_order +1; 
   l_curr_row := l_curr_row+1;
   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_nlsid        => 'EMR_Summary',
      p_element_order       => l_elem_order, 
      p_element_row         => l_curr_row, 
      p_parameters          => l_param_values,
      p_targets             => null
    );

 -------------------- ADDING CHART FROM SQL --------------------------------------------------
    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.sqlStatement',
	'SELECT a.AVAILABILITY_STATUS, SUM( a.VALUE ) FROM((select ''DOWN_TIME_TXT_ID'' AVAILABILITY_STATUS,0 VALUE, 2 ORDER_COL from dual union all select ''UP_TIME_TXT_ID'' AVAILABILITY_STATUS,0 VALUE, 1 ORDER_COL from dual union all select ''SYS_ERR_TIME_TXT_ID'' AVAILABILITY_STATUS,0 VALUE, 5 ORDER_COL from dual union all select ''AGENT_DOWN_TIME_TXT_ID'' AVAILABILITY_STATUS,0 VALUE, 4 ORDER_COL from dual union all select ''BLACKOUT_TIME_TXT_ID'' AVAILABILITY_STATUS,0 VALUE, 3 ORDER_COL from dual union all select ''STATUS_PEND_TIME_TXT_ID'' AVAILABILITY_STATUS,0 VALUE, 6 ORDER_COL from dual)
	UNION ALL
	SELECT decode(LOWER(AVAILABILITY_STATUS),''target down'',''DOWN_TIME_TXT_ID''
               ,''target up'',''UP_TIME_TXT_ID'',''metric error'',''SYS_ERR_TIME_TXT_ID''
               ,''agent down'',''AGENT_DOWN_TIME_TXT_ID'',''unreachable'',''UNREACHABLE_TXT_ID''
               ,''blackout'',''BLACKOUT_TIME_TXT_ID'',''pending/unknown'',''STATUS_PEND_TIME_TXT_ID''
       ) AVAILABILITY_STATUS
	,round( SUM( least(nvl(end_timestamp,(CAST(systimestamp at time zone T.TIMEZONE_REGION AS DATE)))
	,MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.TIMEZONE_REGION))-greatest(start_timestamp
	,MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.TIMEZONE_REGION)))*100,4 ) as VALUE,
	decode(LOWER(AVAILABILITY_STATUS),''target down'',2,''target up'',1,''metric error'',5,''agent down'',4
              ,''unreachable'',7,''blackout'',3,''pending/unknown'',6
       )ORDER_COL
	from mgmt$availability_history b,MGMT$TARGET T
	WHERE b.target_guid=??EMIP_BIND_TARGET_GUID?? and b.target_guid=T.TARGET_GUID and LOWER(AVAILABILITY_STATUS)!=''unreachable''
	and b.start_timestamp >= 
	(select min(NVL(end_timestamp,(CAST(systimestamp at time zone T.TIMEZONE_REGION AS DATE)))) 
		from mgmt$availability_history where target_guid = ??EMIP_BIND_TARGET_GUID?? ) and((b.start_timestamp > 
	   MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.TIMEZONE_REGION) and NVL(b.end_timestamp,(CAST(systimestamp at time zone T.TIMEZONE_REGION AS DATE))) 
	   < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.TIMEZONE_REGION) )
	   OR
	   ( MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.TIMEZONE_REGION) between b.start_timestamp and NVL(b.end_timestamp,(CAST(systimestamp at time zone T.TIMEZONE_REGION AS DATE))))
	   OR
	   ( MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.TIMEZONE_REGION) between b.start_timestamp and NVL(b.end_timestamp,(CAST(systimestamp at time zone T.TIMEZONE_REGION AS DATE))))
	) group by LOWER(AVAILABILITY_STATUS),
	 decode(LOWER(AVAILABILITY_STATUS),''target down'',2,''target up'',1,''metric error'',5,''agent down'',4
              ,''unreachable'',7,''blackout'',3,''pending/unknown'',6 )
	) a GROUP BY a.AVAILABILITY_STATUS , a.ORDER_COL ORDER BY a.ORDER_COL '
	);

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.resourceBundle',
      'oracle.sysman.resources.eml.ip.OOTBResourceBundle');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.width',
      '150');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.height',
      '150');
     l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.pieValuesInLegend',
      'percent');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.annotationMessageMsgid',
      'CHART_DISCLAIMER_ID');
    l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.translateKeyValues',
      'true');

    l_elem_order := l_elem_order+1;
    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        => 'EMR_Breakdown_by_Availability_State',
      p_element_order       => l_elem_order, 
      p_element_row         => l_curr_row, 
      p_parameters          => l_param_values,
      p_targets             => null
      );

-------------------- ADDING TABLE FROM SQL --------------------------------------------------
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();
    l_param_values.extend(6);

    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT DECODE(av.availability_status,''Target Down'',0,''Target Up'',1,''Metric Error'',2, 
                     ''Agent Down'',3,''Unreachable'',4,''Blackout'',5,''Pending/Unknown'',6 ) AVAILABILITY_STATE_ID,
      MGMT_VIEW_UTIL.ADJUST_TZ(av.start_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) START_TIME_ID, 
      MGMT_VIEW_UTIL.ADJUST_TZ(av.end_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) END_TIME_ID, 
	    ltrim(to_char(trunc( NVL(av.end_timestamp,sysdate)-av.start_timestamp ),''009''))||''d'' 
	    ||'':''||ltrim(to_char(MOD(trunc( (NVL(av.end_timestamp
	    ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-av.start_timestamp)*24 ),24),''09''))||''h'' 
	    ||'':''||ltrim(to_char(MOD(trunc( (NVL(av.end_timestamp
	    ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-av.start_timestamp)*24*60 ),60),''09''))||''m'' 
	    ||'':''||ltrim(to_char(MOD(trunc( (NVL(av.end_timestamp
	    ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-av.start_timestamp)*24*60*60 ),60),''09''))||''s'' DURATION_ID,
	    ah.message MESSAGE_ID,
	    aa2.annotation_message ANNOTATION_ID,
            aa3.message NOTIF_MSG_ID
	    FROM mgmt$availability_history av,
	         MGMT$TARGET B,
		 mgmt$alert_history ah,
	        (select aa.source_obj_guid,aa.annotation_message 
                 from mgmt$alert_annotations aa, (select source_obj_guid, MAX(annotation_timestamp)  latest_timestamp
                                                  from mgmt$alert_annotations
					          where annotation_type IS NULL
					          and target_guid = ??EMIP_BIND_TARGET_GUID??
                                                  group by source_obj_guid) la 
                where aa.source_obj_guid = la.source_obj_guid 
                and aa.annotation_timestamp = la.latest_timestamp) aa2,
		(select source_obj_guid, delivery_message message from 
                mgmt$alert_notif_log where target_guid = ??EMIP_BIND_TARGET_GUID??) aa3
	    WHERE 
	      ah.violation_guid = av.severity_guid
	      and aa2.source_obj_guid (+) = av.severity_guid
	      and aa3.source_obj_guid (+) = av.severity_guid
	      and av.target_guid=??EMIP_BIND_TARGET_GUID?? 
	      AND av.target_guid=B.TARGET_GUID 
	      and av.start_timestamp >= (select min(NVL(end_timestamp,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))) from mgmt$availability_history where target_guid=??EMIP_BIND_TARGET_GUID?? )
	      and((av.start_timestamp > 
		    MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and NVL(av.end_timestamp
		    ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE))) < 
		    MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) )
		   OR
		   ( MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) between av.start_timestamp and NVL(av.end_timestamp,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE))) )
		   OR
		   ( MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) between av.start_timestamp and NVL(av.end_timestamp,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE))) )
		)ORDER BY av.start_timestamp DESC');

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
      'oracle.sysman.resources.eml.ip.OOTBResourceBundle');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
      '1');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
      'descending');
    l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow',
      '10');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.availabilityStatusColumn',
      '0');

    l_elem_order := l_elem_order+1;
    l_curr_row := l_curr_row+1;
    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_nlsid        => 'EMR_Availability_History_Details',
      p_element_order       => l_elem_order, 
      p_element_row         => l_curr_row, 
      p_parameters          => l_param_values,
      p_targets             => null
    );

    l_elem_order := l_elem_order+1;
    l_curr_row := l_curr_row+1;
    l_element_guid := mgmt_ip.add_element_to_report_def (
      p_report_guid         => l_report_guid,
      p_element_name_nlsid  => 'IPMSG_AVAILABILITY_TIMELINE',
      p_element_type_nlsid  => 'IPMSG_GROUP_TARGET_TYPES',
      p_header_nlsid        => 'EMR_Availability_History_Elem',
      p_element_order       => l_elem_order,
      p_element_row         => l_curr_row,
      p_parameters          => null,
      p_targets             => null
     );


    BEGIN
       MGMT_IP.delete_report_by_title('SYSMAN', 'EMR_Alert_Violation_History_Group', 0);
       EXCEPTION WHEN OTHERS THEN NULL;
    END;

    l_target_types := MGMT_IP_TARGET_TYPES();
    l_target_types.extend(1);

    -------- TARGET TYPES TO BE DISPLAYED FOR SELECTION -------------------------------------
    l_target_types(1) := 'composite';
    
     -- 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');

 -------------------- AVAILABILITY TEST REPORT BEGINS --------------------------------------------------
    l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'EMR_Alert_Violation_History_Group',
      p_description_nlsid             => 'EMR_Alert_Violation_History_Group_Desc',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'IPMSG_MONITORING_CATEGORY', 
      p_sub_category_nlsid            => 'IPMSG_ALERTS_AND_POLICY_VIOLATIONS_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_parameters                    => l_param_values,
      p_help_topic_id                 =>  'info_pub_gc_cs_av_hist_group_report'
      );

   l_elem_order := 1;
   l_curr_row := 1;
 
-------------------- REPORT DESCRIPTION --------------------------------------------------
    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.TextParamBean.textMessage',
      'ALRT_VIO_HIST_GRP_DESC');
    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle',
      'OraInstructionText');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName',
      'oracle.sysman.resources.eml.ip.OOTBResourceBundle');
    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_header_nlsid        => null,
      p_element_order       => l_elem_order,
      p_element_row         => l_curr_row,
      p_parameters          => l_param_values,
      p_targets             => null
        );
-------------------- ADDING CHART FROM SQL ALERT HISTORY CHART--------------------------------------------------

    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.sqlStatement',
      'WITH grp_tgt AS
       (
         SELECT target_guid, 
         CAST(systimestamp at time zone t.TIMEZONE_REGION AS DATE) tgt_sysdate, t.TIMEZONE_REGION,
         MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,t.TIMEZONE_REGION) tgt_start_date,
         MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??  ,??EMIP_BIND_TIMEZONE_REGION??,t.TIMEZONE_REGION) tgt_end_date
         FROM   MGMT$TARGET t, MGMT$TARGET_MEMBERS m
         WHERE  m.aggregate_target_guid=HEXTORAW(??EMIP_BIND_TARGET_GUID??)
         AND  t.TARGET_GUID = m.MEMBER_TARGET_GUID 
       )
       SELECT to_date(to_char(DURATION,''Mon-yy''), ''MM-yy'') DURATION_ID, sum(CRITICAL) CRITICAL_ID, sum(WARNING) WARNING_ID
       FROM   
         (
           SELECT MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,b.timezone_region,??EMIP_BIND_TIMEZONE_REGION??) DURATION,
                  DECODE(a.violation_level,25,1,0) CRITICAL, DECODE(a.violation_level,20,1,0) WARNING
           FROM   mgmt$alert_history A, grp_tgt B
           WHERE  a.target_guid = b.target_guid
             AND  a.violation_level in (25,20)
             AND  a.collection_timestamp < b.tgt_end_date
             AND
                  (
                    DECODE(a.alert_duration,NULL,b.tgt_sysdate,a.collection_timestamp+(a.alert_duration/24)) > b.tgt_start_date
                   OR
                    (
                       A.ALERT_DURATION=0
                     AND
                     (
                       A.collection_timestamp=b.tgt_start_date
                      OR
                       A.collection_timestamp=b.tgt_end_date
                     )
                    )
                  )
         )
         GROUP BY TO_CHAR(DURATION,''Mon-yy'') , TO_CHAR(DURATION,''yyyy/MM'')
         ORDER BY TO_CHAR(DURATION,''yyyy/MM'')'); 


    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.resourceBundle',
      'oracle.sysman.resources.eml.ip.OOTBResourceBundle');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.width',
      '380');
    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',
      'barChart');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.barChartConvertDateStringLocale', 'yyyy-MMM');
    l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.translateKeyValues', 'true');

    l_elem_order := l_elem_order +1; 
    l_curr_row := l_curr_row+1;
    l_element_guid := mgmt_ip.add_element_to_report_def ( 
      l_report_guid,
      'IPMSG_USER_CHART_FROM_SQL',
      'IPMSG_ANY_TARGET_TYPE',
      'EMR_Alert_History',
      l_elem_order,
      l_curr_row,
      l_param_values,
      null);

-------------------- ADDING CHART FROM SQL VIOLATION HISTORY --------------------------------------------------
    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.sqlStatement',
      'WITH grp_tgt AS
       (
           SELECT target_guid, 
           CAST(systimestamp at time zone t.TIMEZONE_REGION AS DATE) tgt_sysdate, t.TIMEZONE_REGION,
           MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,t.TIMEZONE_REGION) tgt_start_date,
           MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??  ,??EMIP_BIND_TIMEZONE_REGION??,t.TIMEZONE_REGION) tgt_end_date
           FROM   MGMT$TARGET t, MGMT$TARGET_MEMBERS m
           WHERE  m.aggregate_target_guid=HEXTORAW(??EMIP_BIND_TARGET_GUID??)
           AND  t.TARGET_GUID = m.MEMBER_TARGET_GUID 
       )
       SELECT TO_DATE(TO_CHAR(DURATION,''Mon-yy''), ''MM-yy'') DURATION_ID,SUM(CRITICAL) CRITICAL_ID,
           SUM(WARNING) WARNING_ID,SUM(INFORMATIONAL) INFORMATIONAL_ID
       FROM   
          (
            SELECT MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) DURATION,
                   decode(violation_level,25,1,0) CRITICAL,decode(violation_level,20,1,0) WARNING,
                   decode(violation_level,18,1,0) INFORMATIONAL
            FROM   mgmt$policy_violation_history A, grp_tgt B
            WHERE  A.TARGET_GUID=B.TARGET_GUID
              AND  A.violation_level in (20,25,18)
              AND  A.collection_timestamp < b.tgt_end_date
              AND
                   (
                     DECODE(A.VIOLATION_DURATION,NULL,b.tgt_sysdate,A.collection_timestamp+(A.VIOLATION_DURATION/24)) > b.tgt_start_date
                    OR
                     (
                       A.VIOLATION_DURATION=0
                     and
                       (
                         A.collection_timestamp = b.tgt_start_date
                        or
                         A.collection_timestamp = b.tgt_end_date
                       )
                     )
                   )
           )
           GROUP BY TO_CHAR(DURATION,''Mon-yy''),to_char(DURATION,''yyyy/MM'')
           ORDER BY to_char(DURATION,''yyyy/MM'')');       

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.resourceBundle',
      'oracle.sysman.resources.eml.ip.OOTBResourceBundle');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.width',
      '380');
    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',
      'barChart');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.barChartConvertDateStringLocale', 'yyyy-MMM');
    l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.ChartParamController.translateKeyValues', 'true');

    l_elem_order := l_elem_order +1; 
    l_element_guid := mgmt_ip.add_element_to_report_def ( 
      l_report_guid,
      'IPMSG_USER_CHART_FROM_SQL',
      'IPMSG_ANY_TARGET_TYPE',
      'EMR_Violation_History',
      l_elem_order,
      l_curr_row,
      l_param_values,
      null);


 -------------------- ADDING TABLE FROM SQL ALERTS --------------------------------------------------
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();
    l_param_values.extend(9);
    
    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'WITH grp_tgt AS
       (
        SELECT target_guid, 
           CAST(systimestamp at time zone t.TIMEZONE_REGION AS DATE) tgt_sysdate, t.TIMEZONE_REGION,
           MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,t.TIMEZONE_REGION) tgt_start_date,
           MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??  ,??EMIP_BIND_TIMEZONE_REGION??,t.TIMEZONE_REGION) tgt_end_date
        FROM   MGMT$TARGET t, MGMT$TARGET_MEMBERS m
        WHERE  m.aggregate_target_guid=HEXTORAW(??EMIP_BIND_TARGET_GUID??)
        AND  t.TARGET_GUID = m.MEMBER_TARGET_GUID 
       )
        SELECT TARGET_NAME_ID, TARGET_TYPE_ID, METRIC_ID, SEVERITY_ID, START_TIME_ID, END_TIME_ID,
           ltrim(to_char(trunc(duration),''009''))||''d''||'':''||
           ltrim(to_char(MOD(trunc(duration*24),24),''09''))||''h''||'':''||
           ltrim(to_char(MOD(trunc(duration*24*60),60),''09''))||''m''||'':''||
           ltrim(to_char(MOD(trunc(duration*24*60*60),60),''09''))||''s'' DURATION_ID,
           MESSAGE_ID
        FROM   
           (
             SELECT A.target_name TARGET_NAME_ID,A.type_display_name TARGET_TYPE_ID, A.column_label METRIC_ID,
                    a.violation_level SEVERITY_ID,
                    MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) START_TIME_ID,
                    MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??)+(A.alert_duration/24) END_TIME_ID,
                    NVL(a.collection_timestamp+(a.alert_duration/24),b.tgt_sysdate)-a.collection_timestamp duration,
                    A.message MESSAGE_ID
             FROM   MGMT$alert_history A, grp_tgt B
             WHERE  a.target_guid = b.target_guid
               AND  a.violation_level in (25,20)
               AND  a.collection_timestamp < b.tgt_end_date
               AND
                    (
                      DECODE(a.alert_duration,NULL,b.tgt_sysdate,a.collection_timestamp+(a.alert_duration/24)) > b.tgt_start_date
                     OR
                     (
                        A.ALERT_DURATION=0
                      AND
                      (
                        A.collection_timestamp=b.tgt_start_date
                       OR
                        A.collection_timestamp=b.tgt_end_date
                      )
                     )
                    )
           )
           ORDER BY start_time_id');

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
      'oracle.sysman.resources.eml.ip.OOTBResourceBundle');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
      '4');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
      'ascending');
    l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow',
      '10');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.severityColumn',
      '3');
   l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD(
    'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStartCol1',
    'DAYS_ID');
   l_param_values(8) := MGMT_IP_PARAM_VALUE_RECORD(
    'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol1',
    'SECS_ID');
   l_param_values(9) := MGMT_IP_PARAM_VALUE_RECORD(
    'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader1',
    'DURATION_ID');     

    l_elem_order := l_elem_order +1; 
    l_curr_row := l_curr_row+1;
    l_element_guid := mgmt_ip.add_element_to_report_def ( 
      l_report_guid,
      'IPMSG_USER_TABLE_FROM_SQL',
      'IPMSG_ANY_TARGET_TYPE',
      'EMR_Alerts',
      l_elem_order,
      l_curr_row,
      l_param_values,
      null);


 -------------------- ADDING TABLE FROM SQL POLICY VIOLATIONS --------------------------------------------------
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();
    l_param_values.extend(9);

   l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'WITH grp_tgt AS
       (
	  SELECT target_guid, 
		 CAST(systimestamp at time zone t.TIMEZONE_REGION AS DATE) tgt_sysdate, t.TIMEZONE_REGION,
		 MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,t.TIMEZONE_REGION) tgt_start_date,
		 MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??  ,??EMIP_BIND_TIMEZONE_REGION??,t.TIMEZONE_REGION) tgt_end_date
	  FROM   MGMT$TARGET t, MGMT$TARGET_MEMBERS m
	  WHERE  m.aggregate_target_guid=HEXTORAW(??EMIP_BIND_TARGET_GUID??)
	    AND  t.TARGET_GUID = m.MEMBER_TARGET_GUID 
	)
	  SELECT TARGET_NAME_ID, TARGET_TYPE_ID, POLICY_RULE_ID, OBJECTS_ID, CATEGORY_ID, SEVERITY_ID, START_TIME_ID, END_TIME_ID, 
		 ltrim(to_char(trunc(duration),''009''))||''d''||'':''||
		 ltrim(to_char(MOD(trunc(duration*24),24),''09''))||''h''||'':''||
		 ltrim(to_char(MOD(trunc(duration*24*60),60),''09''))||''m''||'':''||
		 ltrim(to_char(MOD(trunc(duration*24*60*60),60),''09''))||''s'' DURATION_ID,
		 MESSAGE_ID
	  FROM   (
		  SELECT A.target_name TARGET_NAME_ID,A.type_display_name TARGET_TYPE_ID,
			 NVL(PM.MESSAGE, NVL(PEM.MESSAGE, R.POLICY_NAME)) POLICY_RULE_ID,
			 DECODE(ltrim(A.KEY_VALUE),NULL||'''',NULL,A.KEY_VALUE)||DECODE(ltrim(A.KEY_VALUE2),NULL||'''',NULL,'' , ''||A.KEY_VALUE2)||DECODE(ltrim(A.KEY_VALUE3),NULL||'''',NULL,'' , ''||A.KEY_VALUE3)||DECODE(ltrim(A.KEY_VALUE4),NULL||'''',NULL,'' , ''||A.KEY_VALUE4) OBJECTS_ID,
			 NVL(CM.MESSAGE, NVL(CEM.MESSAGE, R.CATEGORY)) CATEGORY_ID,
			 A.violation_level SEVERITY_ID,
			 MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) START_TIME_ID,
			 MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??)+(A.violation_duration/24) END_TIME_ID,
			 NVL(DM.MESSAGE,NVL(DEM.MESSAGE,a.MESSAGE)) MESSAGE_ID,
			 NVL(a.collection_timestamp+(a.violation_duration/24),b.tgt_sysdate)-a.collection_timestamp duration
		  FROM   mgmt$policy_violation_history A, mgmt$policies R, grp_tgt b,
			 (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY''   AND LANGUAGE_CODE=??EMIP_BIND_LOCALE_LANGUAGE?? AND COUNTRY_CODE=??EMIP_BIND_LOCALE_COUNTRY??) PM,
			 (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY''   AND LANGUAGE_CODE=''en''AND COUNTRY_CODE='' '') PEM,
			 (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''CATEGORY'' AND LANGUAGE_CODE=??EMIP_BIND_LOCALE_LANGUAGE?? AND COUNTRY_CODE=??EMIP_BIND_LOCALE_COUNTRY??) CM,
			 (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''CATEGORY'' AND LANGUAGE_CODE=''en'' AND COUNTRY_CODE='' '') CEM ,
			 (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY''   AND LANGUAGE_CODE=??EMIP_BIND_LOCALE_LANGUAGE?? AND COUNTRY_CODE=??EMIP_BIND_LOCALE_COUNTRY??) DM,
			 (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY''   AND LANGUAGE_CODE=''en'' AND COUNTRY_CODE='' '') DEM
		  WHERE  A.TARGET_GUID=B.TARGET_GUID
		    AND  A.violation_level in(20,25,18)
		    AND  A.collection_timestamp < b.tgt_end_date
		    AND
			 (
			   DECODE(A.VIOLATION_DURATION,NULL,b.tgt_sysdate,A.collection_timestamp+(A.VIOLATION_DURATION/24)) > b.tgt_start_date
			  OR
			   (
			      A.VIOLATION_DURATION=0
			    and
			    (
			       A.collection_timestamp = b.tgt_start_date
			     or
			       A.collection_timestamp = b.tgt_end_date
			    )
			   )
			 )
		    AND  R.POLICY_LABEL_NLSID=PM.MESSAGE_ID(+)
		    AND  R.POLICY_LABEL_NLSID=PEM.MESSAGE_ID(+)
		    AND  R.CATEGORY_NLSID=CM.MESSAGE_ID(+)
		    AND  R.CATEGORY_NLSID=CEM.MESSAGE_ID(+)
		    AND  a.MESSAGE_NLSID=DM.MESSAGE_ID(+)
		    AND  a.MESSAGE_NLSID=DEM.MESSAGE_ID(+)
		    AND  a.POLICY_GUID=R.POLICY_GUID
		 ) 
	  ORDER BY start_time_id
      	');

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
      'oracle.sysman.resources.eml.ip.OOTBResourceBundle');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
      '6');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
      'ascending');
    l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow',
      '10');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.severityColumn',
      '5');
   l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD(
    'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStartCol1',
    'DAYS_ID');
   l_param_values(8) := MGMT_IP_PARAM_VALUE_RECORD(
    'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol1',
    'SECS_ID');
   l_param_values(9) := MGMT_IP_PARAM_VALUE_RECORD(
    'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader1',
    'DURATION_ID');     

    l_elem_order := l_elem_order +1; 
    l_curr_row := l_curr_row+1;
    l_element_guid := mgmt_ip.add_element_to_report_def ( 
      l_report_guid,
      'IPMSG_USER_TABLE_FROM_SQL',
      'IPMSG_ANY_TARGET_TYPE',
      'EMR_Policy_Violations',
      l_elem_order,
      l_curr_row,
      l_param_values,
      null);

COMMIT;
END;
/