Rem
Rem $Header: monitoring_outofbox_reports.sql 15-sep-2005.12:21:43 nanand Exp $
Rem
Rem monitoring_outofbox_reports.sql
Rem
Rem Copyright (c) 2005, 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    nanand      09/15/05 - Fix for bug 4608048 
Rem    nanand      09/14/05 - Fix for bug 4608048 
Rem    mswamyt     08/31/05 - fix for 4546951,4561174 
Rem    snathan     08/24/05 - revert fix 4490044
Rem    snathan     08/23/05 - fix to comment unnecessary l_target_types initialisation
Rem    snathan     08/19/05 - fix for using mgmt_target_types 
Rem    mkm         07/25/05 - fix for 4490033&4490044 
Rem    mkm         07/20/05 - optimization for common alerts&policies 
Rem    mkm         07/12/05 - removing notif/comment column 
Rem    mkm         07/11/05 - fix for errors count 
Rem    lgloyd      06/21/05 - consolidate category/subcategory strings 
Rem    tjana       06/19/05 - fix for help topic id 
Rem    mkm         06/03/05 - mkm_t0519
Rem    mkm         06/02/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_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;
    --l_index         NUMBER;

/**
 --creating cursor for having target types excluding System and Group
    CURSOR noSvcNGrpCur IS
       select distinct B.target_type Target_type from mgmt_target_types B where B.target_type not in(
          select A.target_type from mgmt_type_properties A
          where ((A.property_name ='is_system' and A.property_value=1)
          or(A.property_name ='is_group' and A.property_value=1))
        );
**/

 BEGIN

/**
      l_index :=0;
      l_types_for_target := MGMT_IP_TARGET_TYPES();
      FOR i in noSvcNGrpCur
      LOOP
         l_index := l_index+1;
         l_types_for_target.extend(1);
         l_types_for_target(l_index):= i.Target_type;
      END LOOP;
**/
------------------------------------- tgtAvailHistory.sql --------------------------------------------- 
    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
        FROM mgmt$availability_history av,MGMT$TARGET B WHERE 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
    );


------------------------------------- grpAvailHistory.sql --------------------------------------------- 
    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
	      FROM mgmt$availability_history av,MGMT$TARGET B WHERE 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
     );

------------------------------------- memAvailReport.sql --------------------------------------------- 
    BEGIN
       MGMT_IP.delete_report_by_title('SYSMAN', 'EMR_Availability_Report_Member', 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_Availability_Report_Member',
      p_description_nlsid             => 'EMR_Availability_Reports_Member_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_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_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(6);

    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT SUM(Total_Members) TOTAL_MEMBERS_ID,SUM(Critical_Alerts) CRITICAL_ALERTS_ID,SUM(Warning_Alerts) WARNING_ALERTS_ID,SUM(Metric_Errors) METRIC_ERRORS_ID FROM (
      SELECT 0 Total_Members,0 Critical_Alerts,0 Warning_Alerts,0 Metric_Errors FROM DUAL UNION ALL
      SELECT SUM(DECODE(LOWER(col_name),''total_members'', SUM(col_val))) Total_Members,   
             SUM(DECODE(LOWER(col_name),''critical'', SUM(col_val))) Critical_Alerts, 
	     SUM(DECODE(LOWER(col_name),''warning'', SUM(col_val))) Warning_Alerts, 
	     SUM(DECODE(LOWER(col_name),''metric_errors'', SUM(col_val))) Metric_Errors 
	     FROM (
	       SELECT ''total_members'' col_name, count(*) col_val FROM mgmt$target_flat_members WHERE aggregate_target_guid=??EMIP_BIND_TARGET_GUID?? 
	       UNION ALL SELECT A.alert_state col_name, count(*) col_val FROM mgmt$alert_history A,MGMT$TARGET B WHERE A.TARGET_GUID=B.TARGET_GUID AND
	             A.target_guid in( select member_target_guid from mgmt$target_flat_members where aggregate_target_guid = ??EMIP_BIND_TARGET_GUID?? ) 
                     AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and
                        DECODE(A.ALERT_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)
                        ,A.collection_timestamp+(A.ALERT_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)
                        OR
                        (A.ALERT_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) 
                           or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) 
                        )
                      )
		     AND A.alert_state in (''Critical'',''Warning'') GROUP BY A.alert_state 
	       UNION ALL SELECT ''metric_errors'' col_name, count(*) col_val FROM mgmt$metric_error_history A,MGMT$TARGET B WHERE A.TARGET_GUID=B.TARGET_GUID AND
	            A.target_guid in(select member_target_guid from mgmt$target_flat_members where aggregate_target_guid = ??EMIP_BIND_TARGET_GUID??) 
		    AND A.collection_timestamp >= 
		    MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and A.collection_timestamp <= 
		    MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) ) GROUP BY col_name
      ) 
      ');

    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',
      '0');
    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.nameValueDisplay',
      '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_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(5);

    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT NVL(A.availability_status,''n/a''),count(*) FROM mgmt$availability_current A,
       mgmt$target_flat_members B WHERE B.aggregate_target_guid = ??EMIP_BIND_TARGET_GUID??
       and A.target_guid(+) = B.member_target_guid
       GROUP BY A.availability_status');

    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.ChartParamController.width',
      '250');
    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.pieShowSlicePercentLabels',
      '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 C.target_name TARGET_ID, C.type_display_name TYPE_ID
      ,DECODE (A.availability_status,''Target Down'',''0'',''Target Up'',''1'',''Metric Error'',''2'', 
               ''Agent Down'',''3'',''Unreachable'',''4'',''Blackout'',''5'',''Pending/Unknown'',''6'',''n/a'') STATUS_ID 
      FROM mgmt$availability_current A, mgmt$target_flat_members B, mgmt$target C 
      WHERE B.aggregate_target_guid = ??EMIP_BIND_TARGET_GUID?? 
      and A.target_guid(+) = B.member_target_guid
      and B.member_target_guid = c.target_guid
      ORDER BY C.type_display_name,C.target_name');

    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',
      '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.availabilityStatusColumn',
      '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_Member_Summary',
      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
     );

------------------------------------- memCommonTest.sql --------------------------------------------- 
-------------------- COMMON ALERTS AND VIOLATIONS TEST REPORT 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(
	WITH tblData2 AS (
	  select A.column_label COL_LABEL,A.alert_state ALERT_ST
	   ,count(*) CLEARED_COUNT from MGMT$ALERT_HISTORY A, MGMT$TARGET T
	   where A.target_guid=T.target_guid
	   and A.TARGET_GUID=??EMIP_BIND_TARGET_GUID??
	   and A.ALERT_STATE in(''Critical'',''Warning'')
	   and A.alert_duration IS NOT NULL 
	   and (A.collection_timestamp+(A.alert_duration/24) BETWEEN 
	      MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.timezone_region) and 
	      MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.timezone_region) )
	   group by A.column_label, A.alert_state
	),
	tblData3 AS (
	  select A.column_label COL_LABEL, A.alert_state ALERT_ST
	    ,count(*) NEW_COUNT ,count(DISTINCT A.target_name) TGT_COUNT from 
	    MGMT$ALERT_HISTORY A, MGMT$TARGET T
	    where A.target_guid=T.target_guid 
	    and A.TARGET_GUID=??EMIP_BIND_TARGET_GUID??
	    and A.ALERT_STATE in(''Critical'',''Warning'')
	    and (
	      A.collection_timestamp >= MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.timezone_region)
	      and A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.timezone_region) 
	      or A.alert_duration = 0 and A.collection_timestamp = MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.timezone_region)
	       )
	   group by A.column_label, A.alert_state
	)
	SELECT NVL(tb2.COL_LABEL,tb3.COL_LABEL) METRIC_ID, 
	decode(NVL(tb2.ALERT_ST,tb3.ALERT_ST),''Critical'',25,''Warning'',20) SEVERITY_ID, 
	NVL(CLEARED_COUNT,0) CLEARED_ID, NVL(NEW_COUNT,0) NEW_ID,
	NVL((select count(*) from MGMT$ALERT_HISTORY A 
	     where A.ALERT_STATE in(''Critical'',''Warning'') 
	     and A.TARGET_GUID=??EMIP_BIND_TARGET_GUID??
	     and A.alert_duration is null
	     and A.column_label=NVL(tb2.COL_LABEL,tb3.COL_LABEL)
	     and A.alert_state=NVL(tb2.ALERT_ST ,tb3.ALERT_ST )
	),0) CURRENT_ID
	FROM tblData2 tb2 FULL OUTER JOIN tblData3 tb3  
	ON (tb2.COL_LABEL=tb3.COL_LABEL and tb2.ALERT_ST=tb3.ALERT_ST)
	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',
      '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, R.POLICY_NAME)) POLICY_RULE_ID,
	NVL(CM.MESSAGE, NVL(CEM.MESSAGE, E.CATEG)) CATEGORY_ID, E.SEVERITY SEVERITY_ID,
	E.CLEARED CLEARED_ID,E.NEW_COL NEW_ID,E.CURRENT_COL CURRENT_ID
	FROM( 
		 select * from(
		  WITH tblData2 AS(
		  select A.policy_guid POLICY_GUID,
			 A.category CATEG, 
			 A.violation_level SEVERITY,
			 count(*) CLEARED
		  from MGMT$POLICY_VIOLATION_HISTORY A, MGMT$TARGET T
		  where A.target_guid=T.target_guid 
                  and A.target_name = T.target_name
		  and A.TARGET_GUID=??EMIP_BIND_TARGET_GUID??
		  and A.violation_duration IS NOT NULL 
		  and A.violation_level in (20,25,18)
		  and (A.collection_timestamp+(A.violation_duration/24) BETWEEN 
		      MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.timezone_region) and 
		      MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.timezone_region) )
		  group by A.policy_guid,A.category,A.violation_level
		 )
		 ,tblData3 AS(
		  select A.policy_guid POLICY_GUID,
			 A.category CATEG, 
			 A.violation_level SEVERITY,
			 count(*) NEW_COL
		  from MGMT$POLICY_VIOLATION_HISTORY A, MGMT$TARGET T
		  where A.target_guid=T.target_guid 
                  and A.target_name = T.target_name
		  and A.TARGET_GUID=??EMIP_BIND_TARGET_GUID??
		  and A.violation_level in (20,25,18)
		  and A.collection_timestamp >= MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.timezone_region)
		  and A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.timezone_region)
		  group by A.policy_guid,A.category,A.violation_level
		 )
		 select 
		  NVL(tb2.POLICY_GUID,tb3.POLICY_GUID) POLICY_GUID 
		  ,NVL(tb2.CATEG,tb3.CATEG) CATEG
		  ,NVL(tb2.SEVERITY,tb3.SEVERITY) SEVERITY
		  ,NVL(tb2.CLEARED,0) CLEARED
		  ,NVL(tb3.NEW_COL,0) NEW_COL
		  ,(select count(*) from mgmt$policy_violation_current A where A.violation_level in (20,25,18)
		    and A.TARGET_GUID=??EMIP_BIND_TARGET_GUID??
		    and A.policy_guid=NVL(tb2.POLICY_GUID,tb3.POLICY_GUID)
		    and A.category=NVL(tb2.CATEG,tb3.CATEG)
		    and A.violation_level=NVL(tb2.SEVERITY,tb3.SEVERITY)
		  )CURRENT_COL
		 from tblData2 tb2 FULL OUTER JOIN tblData3 tb3
		 ON (tb2.POLICY_GUID=tb3.POLICY_GUID and tb2.CATEG=tb3.CATEG and tb2.SEVERITY=tb3.SEVERITY
		  ) Order by 5 desc
		) where rownum<21
	)E, mgmt$policies R,
	(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 E.POLICY_GUID=R.POLICY_GUID 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(+)
	)
      ');

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




-------------------------------------CommonAlertsViolationsTest.sql --------------------------------------------- 

-------------------- COMMON ALERTS AND VIOLATIONS TEST 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(6);
    --,(round((((COUNT(DISTINCT(A.TARGET_NAME)))/ (select COUNT(*) from MGMT$TARGET E  where E.TYPE_DISPLAY_NAME = A.TYPE_DISPLAY_NAME )) *100),2) ) REL_TAR_AFFFECT_PER_ID2 

    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      '
	select * from(
	WITH tblData2 AS (
	  select A.column_label COL_LABEL, A.type_display_name TYP_DISP_NAME,A.alert_state ALERT_ST
	   ,count(*) CLEARED_COUNT from MGMT$ALERT_HISTORY A, MGMT$TARGET T
	   where A.target_guid=T.target_guid
	   and A.ALERT_STATE in(''Critical'',''Warning'')
	   and A.alert_duration IS NOT NULL 
	   and (A.collection_timestamp+(A.alert_duration/24) BETWEEN 
	      MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.timezone_region) and 
	      MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.timezone_region) )
	   group by A.column_label, A.type_display_name, A.alert_state
	),
	tblData3 AS (
	  select A.column_label COL_LABEL, A.type_display_name TYP_DISP_NAME,A.alert_state ALERT_ST
	    ,count(*) NEW_COUNT ,count(DISTINCT A.target_name) TGT_COUNT from 
	    MGMT$ALERT_HISTORY A, MGMT$TARGET T
	    where A.target_guid=T.target_guid 
	    and A.ALERT_STATE in(''Critical'',''Warning'')
	    and (
	      A.collection_timestamp >= MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.timezone_region)
	      and A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.timezone_region) 
	      or A.alert_duration = 0 and A.collection_timestamp = MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.timezone_region)
	       )
	   group by A.column_label, A.type_display_name, A.alert_state
	)
	SELECT NVL(tb2.COL_LABEL,tb3.COL_LABEL) METRIC_ID, 
	NVL(tb2.TYP_DISP_NAME,tb3.TYP_DISP_NAME) TARGET_TYPE_ID, 
	decode(NVL(tb2.ALERT_ST,tb3.ALERT_ST),''Critical'',25,''Warning'',20) SEVERITY_ID, 
	NVL(CLEARED_COUNT,0) CLEARED_ID, NVL(NEW_COUNT,0) NEW_ID,
	NVL((select count(*) from MGMT$ALERT_HISTORY A 
	     where A.ALERT_STATE in(''Critical'',''Warning'') 
	     and alert_duration is null
	     and A.column_label=NVL(tb2.COL_LABEL,tb3.COL_LABEL)
	     and A.type_display_name=NVL(tb2.TYP_DISP_NAME,tb3.TYP_DISP_NAME) 
	     and A.alert_state=NVL(tb2.ALERT_ST ,tb3.ALERT_ST )
	),0) CURRENT_ID,
	NVL(Round(tb3.TGT_COUNT/(Select COUNT(*) from MGMT$TARGET G where 
	     G.TYPE_DISPLAY_NAME=NVL(tb2.TYP_DISP_NAME,tb3.TYP_DISP_NAME))*100,2),0) REL_TAR_AFFFECT_PER_ID
	FROM tblData2 tb2 FULL OUTER JOIN tblData3 tb3  
	ON (tb2.COL_LABEL=tb3.COL_LABEL 
	    and tb2.TYP_DISP_NAME=tb3.TYP_DISP_NAME
	    and tb2.ALERT_ST=tb3.ALERT_ST)
	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',
      '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_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, R.POLICY_NAME)) POLICY_RULE_ID,E.TYP_DISP_NAME TARGET_TYPE_ID,
	NVL(CM.MESSAGE, NVL(CEM.MESSAGE, E.CATEG)) CATEGORY_ID, E.SEVERITY SEVERITY_ID,
	E.CLEARED CLEARED_ID,E.NEW_COL NEW_ID,E.CURRENT_COL CURRENT_ID,E.MEMBER_TGTS_AFFECT REL_TAR_AFFFECT_PER_ID
	FROM( 
		 select * from(
		  WITH tblData2 AS(
		  select A.policy_guid POLICY_GUID,
			 A.type_display_name TYP_DISP_NAME,
			 A.category CATEG, 
			 A.violation_level SEVERITY,
			 count(*) CLEARED
		  from MGMT$POLICY_VIOLATION_HISTORY A, MGMT$TARGET T
		  where A.target_guid=T.target_guid 
                    and A.target_name=T.target_name
		  and A.violation_duration IS NOT NULL 
		  and A.violation_level in (20,25,18)
		  and (A.collection_timestamp+(A.violation_duration/24) BETWEEN 
		      MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.timezone_region) and 
		      MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.timezone_region) )
		  group by A.policy_guid,A.type_display_name,A.category,A.violation_level
		 )
		 ,tblData3 AS(
		  select A.policy_guid POLICY_GUID,
			 A.type_display_name TYP_DISP_NAME,
			 A.category CATEG, 
			 A.violation_level SEVERITY,
			 count(*) NEW_COL,
			 COUNT(DISTINCT(A.TARGET_NAME)) TGT_COUNT
		  from MGMT$POLICY_VIOLATION_HISTORY A, MGMT$TARGET T
		  where A.target_guid=T.target_guid 
                    and A.target_name=T.target_name
		  and A.violation_level in (20,25,18)
		  and A.collection_timestamp >= MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.timezone_region)
		  and A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,T.timezone_region)
		  group by A.policy_guid,A.type_display_name,A.category,A.violation_level
		 )
		 select 
		  NVL(tb2.POLICY_GUID,tb3.POLICY_GUID) POLICY_GUID 
		  ,NVL(tb2.TYP_DISP_NAME,tb3.TYP_DISP_NAME) TYP_DISP_NAME
		  ,NVL(tb2.CATEG,tb3.CATEG) CATEG
		  ,NVL(tb2.SEVERITY,tb3.SEVERITY) SEVERITY
		  ,NVL(tb2.CLEARED,0) CLEARED
		  ,NVL(tb3.NEW_COL,0) NEW_COL
		  ,(select count(*) from mgmt$policy_violation_current A where A.violation_level in (20,25,18)
		    and A.policy_guid=NVL(tb2.POLICY_GUID,tb3.POLICY_GUID)
		    and A.type_display_name=NVL(tb2.TYP_DISP_NAME,tb3.TYP_DISP_NAME)
		    and A.category=NVL(tb2.CATEG,tb3.CATEG)
		    and A.violation_level=NVL(tb2.SEVERITY,tb3.SEVERITY)
		  )CURRENT_COL
		  ,NVL(Round(tb3.TGT_COUNT/(select COUNT(*) from MGMT$TARGET G 
		     where G.TYPE_DISPLAY_NAME=NVL(tb2.TYP_DISP_NAME,tb3.TYP_DISP_NAME))*100
		     ,2),0) MEMBER_TGTS_AFFECT
		 from tblData2 tb2 FULL OUTER JOIN tblData3 tb3
		 ON (tb2.POLICY_GUID=tb3.POLICY_GUID and tb2.TYP_DISP_NAME=tb3.TYP_DISP_NAME 
		   and tb2.CATEG=tb3.CATEG and tb2.SEVERITY=tb3.SEVERITY
		  ) Order by 6 desc
		) where rownum<21
	)E, mgmt$policies R,
	(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 E.POLICY_GUID=R.POLICY_GUID 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(+)
	)
      ');

    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',
      '5');
    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_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
      );



------------------------------------- alert_vio_history.sql --------------------------------------------- 
    BEGIN
       MGMT_IP.delete_report_by_title('SYSMAN', 'EMR_Alert_Violation_History_All_Targets', 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');
 
-------------------- AVAILABILITY TEST REPORT BEGINS --------------------------------------------------
    l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'EMR_Alert_Violation_History_All_Targets',
      p_description_nlsid             => 'EMR_Alert_Violation_History_All_Targets_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_av_hist_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_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(5);

    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT to_char(DURATION,''Mon-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.ALERT_STATE,''Critical'',1,0) CRITICAL    
        ,decode(A.ALERT_STATE,''Warning'',1,0) WARNING from MGMT$alert_history A,MGMT$TARGET B
        WHERE A.TARGET_GUID=B.TARGET_GUID AND A.alert_state in (''Critical'',''Warning'')
        AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and
            DECODE(A.ALERT_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)
            ,A.collection_timestamp+(A.ALERT_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)
            OR
           (A.ALERT_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) 
            or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) 
           )
         )
      ) 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_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 CHART--------------------------------------------------
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();
    l_param_values.extend(5);

    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT TO_CHAR(DURATION,''Mon-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(A.violation_level,25,1,0) CRITICAL,decode(A.violation_level,20,1,0) WARNING
	,decode(A.violation_level,18,1,0) INFORMATIONAL FROM mgmt$policy_violation_history A,MGMT$TARGET B
	where A.TARGET_GUID=B.TARGET_GUID AND A.violation_level in (20,25,18)  
        AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and
            DECODE(A.VIOLATION_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)
           ,A.collection_timestamp+(A.VIOLATION_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)
           OR
           (A.VIOLATION_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) 
             or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) 
           )
         )
	) 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_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(6);

   l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'select A.target_name TARGET_NAME_ID,A.type_display_name TARGET_TYPE_ID,
	A.column_label METRIC_ID,decode(A.alert_state,''Critical'',25,''Warning'',20) 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,
		(
		ltrim(to_char(trunc( NVL(((A.collection_timestamp + (A.alert_duration/24)))
					,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))- A.collection_timestamp ),''009''))||''d''
		||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.alert_duration/24)))
					,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))- A.collection_timestamp)*24 ),24),''09''))||''h''
		||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.alert_duration/24)))
					,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))- A.collection_timestamp)*24*60 ),60),''09''))||''m''
		||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.alert_duration/24)))
					,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))- A.collection_timestamp)*24*60*60 ),60),''09''))||''s''
	) DURATION_ID,message MESSAGE_ID
	FROM MGMT$alert_history A,MGMT$TARGET B where A.TARGET_GUID=B.TARGET_GUID AND A.alert_state in(''Critical'',''Warning'') 
        AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and
            DECODE(A.ALERT_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)
            ,A.collection_timestamp+(A.ALERT_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)
            OR
           (A.ALERT_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) 
            or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) 
           )
         )
	 order by A.collection_timestamp'); 

    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_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(6);

   l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'WITH tab1 AS(
       SELECT A.target_name TARGET_NAME_ID,A.type_display_name TARGET_TYPE_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, 
        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,
	(
		ltrim(to_char(trunc( NVL(((A.collection_timestamp+(A.violation_duration/24)))
						,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp),''009''))||''d''
		||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.violation_duration/24)))
						,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24),24),''09''))||''h''
		||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.violation_duration/24)))
						,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24*60),60),''09''))||''m''
		||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.violation_duration/24)))
						,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24*60*60),60),''09''))||''s''
	) DURATION_ID, A.POLICY_GUID POLICY_GUID, A.MESSAGE, A.MESSAGE_NLSID, A.collection_timestamp 
       FROM mgmt$policy_violation_history A,MGMT$TARGET B
       WHERE A.TARGET_GUID=B.TARGET_GUID AND A.violation_level in(20,25,18) 
       AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and
         DECODE(A.VIOLATION_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)
         ,A.collection_timestamp+(A.VIOLATION_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)
         OR
         (A.VIOLATION_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) 
           or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) 
          )
        )
      )
      select TARGET_NAME_ID, TARGET_TYPE_ID,
       NVL(PM.MESSAGE, NVL(PEM.MESSAGE, R.POLICY_NAME)) POLICY_RULE_ID,
       NVL(CM.MESSAGE, NVL(CEM.MESSAGE, R.CATEGORY)) CATEGORY_ID,  
       OBJECTS_ID, SEVERITY_ID, START_TIME_ID, END_TIME_ID, DURATION_ID,
       NVL(DM.MESSAGE,NVL(DEM.MESSAGE,T1.MESSAGE)) MESSAGE_ID
       from tab1 T1, mgmt$policies R,
       (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 T1.POLICY_GUID=R.POLICY_GUID 
       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 T1.MESSAGE_NLSID=DM.MESSAGE_ID(+) AND T1.MESSAGE_NLSID=DEM.MESSAGE_ID(+)
       ORDER BY T1.collection_timestamp
      ');


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



------------------------------------- alert_vio_history_grp.sql --------------------------------------------- 
    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(5);

   l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT to_char(DURATION,''Mon-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.ALERT_STATE,''Critical'',1,0) CRITICAL,decode(A.ALERT_STATE,''Warning'',1,0) WARNING
	FROM MGMT$alert_history A,MGMT$TARGET B WHERE A.TARGET_GUID=B.TARGET_GUID AND A.alert_state in(''Critical'',''Warning'') 
	and A.TARGET_GUID In(select MEMBER_TARGET_GUID from MGMT$TARGET_MEMBERS WHERE aggregate_target_guid=??EMIP_BIND_TARGET_GUID??) 
        AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and
            DECODE(A.ALERT_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)
            ,A.collection_timestamp+(A.ALERT_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)
            OR
           (A.ALERT_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) 
            or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) 
           )
         )
	)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_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(5);

   l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT TO_CHAR(DURATION,''Mon-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,MGMT$TARGET B
      WHERE A.TARGET_GUID=B.TARGET_GUID AND A.violation_level in (20,25,18) 
      AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and
        DECODE(A.VIOLATION_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)
        ,A.collection_timestamp+(A.VIOLATION_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)
        OR
        (A.VIOLATION_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) 
           or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) 
         )
       )
      AND A.target_guid IN(SELECT member_target_guid FROM mgmt$target_members WHERE aggregate_target_guid=??EMIP_BIND_TARGET_GUID??)  
      )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_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(6);

   l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT A.target_name TARGET_NAME_ID,A.type_display_name TARGET_TYPE_ID,
	A.column_label METRIC_ID,decode(A.alert_state,''Critical'',25,''Warning'',20) 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,
	(
	ltrim(to_char(trunc( NVL(((A.collection_timestamp+(A.alert_duration/24)))
				,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp ),''009''))||''d''
	||'':''||ltrim(to_char(MOD(trunc( (NVL(((A.collection_timestamp+(A.alert_duration/24)))
				,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24 ),24),''09''))||''h''
	||'':''||ltrim(to_char(MOD(trunc( (NVL(((A.collection_timestamp+(A.alert_duration/24)))
				,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24*60 ),60),''09''))||''m''
	||'':''||ltrim(to_char(MOD(trunc( (NVL(((A.collection_timestamp+(A.alert_duration/24)))
				,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24*60*60 ),60),''09''))||''s''
	) DURATION_ID,A.message MESSAGE_ID FROM MGMT$alert_history A,MGMT$TARGET B
        WHERE A.TARGET_GUID=B.TARGET_GUID AND A.alert_state in (''Critical'',''Warning'') and
        A.TARGET_GUID In(select MEMBER_TARGET_GUID from MGMT$TARGET_MEMBERS WHERE aggregate_target_guid=??EMIP_BIND_TARGET_GUID??) 
        AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and
            DECODE(A.ALERT_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)
            ,A.collection_timestamp+(A.ALERT_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)
            OR
           (A.ALERT_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) 
            or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) 
           )
         )
	order by A.collection_timestamp'); 


    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_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(6);

   l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'WITH tab1 AS(
        SELECT A.target_name TARGET_NAME_ID,A.type_display_name TARGET_TYPE_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, 
		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,
		(ltrim(to_char(trunc(NVL(((A.collection_timestamp+(A.violation_duration/24)))
			,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp ),''009''))||''d''
		||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.violation_duration/24)))
			,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24 ),24),''09''))||''h''
		||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.violation_duration/24)))
			,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24*60),60),''09''))||''m''
		||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.violation_duration/24)))
			,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24*60*60),60),''09''))||''s''
		) DURATION_ID,
		A.collection_timestamp,
		A.MESSAGE,
                A.MESSAGE_NLSID,
		A.POLICY_GUID
	FROM mgmt$policy_violation_history A, MGMT$TARGET B
       WHERE A.TARGET_GUID=B.TARGET_GUID AND A.violation_level in(20,25,18) 
       AND A.target_guid IN(SELECT member_target_guid FROM mgmt$target_members WHERE aggregate_target_guid=??EMIP_BIND_TARGET_GUID??) 
       AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and
            DECODE(A.VIOLATION_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)
           ,A.collection_timestamp+(A.VIOLATION_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)
           OR
           (A.VIOLATION_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) 
             or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) 
           )
         )
     )
     select TARGET_NAME_ID, TARGET_TYPE_ID, 
       NVL(PM.MESSAGE, NVL(PEM.MESSAGE, R.POLICY_NAME)) POLICY_RULE_ID,
       OBJECTS_ID, 
       NVL(CM.MESSAGE, NVL(CEM.MESSAGE, R.CATEGORY)) CATEGORY_ID,
       SEVERITY_ID, 
       START_TIME_ID, 
       END_TIME_ID,
       DURATION_ID,
       NVL(DM.MESSAGE,NVL(DEM.MESSAGE,T1.MESSAGE)) MESSAGE_ID
     from tab1 T1,mgmt$policies R,
       (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 T1.POLICY_GUID=R.POLICY_GUID
       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 T1.MESSAGE_NLSID=DM.MESSAGE_ID(+) AND T1.MESSAGE_NLSID=DEM.MESSAGE_ID(+) 
       order by T1.collection_timestamp
      ');


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


------------------------------------- alert_vio_history_tgt.sql --------------------------------------------- 
    BEGIN
       MGMT_IP.delete_report_by_title('SYSMAN', 'EMR_Alert_Violation_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_Alert_Violation_History_Target',
      p_description_nlsid             =>'EMR_Alert_Violation_History_Target_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_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',
      'ALRT_VIO_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 CHART FROM SQL ALERT HISTORY CHART--------------------------------------------------
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();
    l_param_values.extend(5);

   l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT to_char(DURATION,''Mon-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.ALERT_STATE,''Critical'',1,0) CRITICAL, decode(A.ALERT_STATE,''Warning'',1,0) WARNING
	  FROM MGMT$alert_history A,MGMT$TARGET B
   	  WHERE A.TARGET_GUID=B.TARGET_GUID AND A.target_guid=??EMIP_BIND_TARGET_GUID?? AND A.alert_state in(''Critical'',''Warning'')
          AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and
             DECODE(A.ALERT_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)
             ,A.collection_timestamp+(A.ALERT_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)
             OR
             (A.ALERT_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) 
               or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) 
             )
           )
	)
	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_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 CHART--------------------------------------------------
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();
    l_param_values.extend(5);

   l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT TO_CHAR(DURATION,''Mon-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(A.violation_level,25,1,0) CRITICAL,decode(A.violation_level,20,1,0) WARNING,
	  decode(A.violation_level,18,1,0) INFORMATIONAL FROM mgmt$policy_violation_history A,MGMT$TARGET B
	  WHERE A.TARGET_GUID=B.TARGET_GUID AND A.violation_level in(20,25,18) AND A.target_guid=??EMIP_BIND_TARGET_GUID?? 
          AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and
           DECODE(A.VIOLATION_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)
           ,A.collection_timestamp+(A.VIOLATION_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)
           OR
           (A.VIOLATION_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) 
             or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) 
           )
          )
	)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_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(6);

   l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT A.column_label METRIC_ID,decode(A.alert_state,''Critical'',25,''Warning'',20) 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,
	(
	ltrim(to_char(trunc(NVL(((A.collection_timestamp+(A.alert_duration/24))),sysdate)-A.collection_timestamp ),''009''))||''d''
	||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.alert_duration/24))),sysdate)-A.collection_timestamp)*24 ),24),''09''))||''h''
	||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.alert_duration/24))),sysdate)-A.collection_timestamp)*24*60 ),60),''09''))||''m''
	||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.alert_duration/24))),sysdate)-A.collection_timestamp)*24*60*60 ),60),''09''))||''s''
	) DURATION_ID, A.message MESSAGE_ID FROM MGMT$alert_history A,MGMT$TARGET B
       WHERE A.TARGET_GUID=B.TARGET_GUID AND A.target_guid = ??EMIP_BIND_TARGET_GUID??  AND A.alert_state in (''Critical'',''Warning'')
       AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and
            DECODE(A.ALERT_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)
            ,A.collection_timestamp+(A.ALERT_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)
            OR
            (A.ALERT_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) 
              or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) 
            )
          )
       ORDER BY A.collection_timestamp ');


    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',
      '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',
      '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 ( 
      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(6);

   l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      '
      WITH tab1 AS(
      SELECT 
      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, 
	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,
	(
	ltrim(to_char(trunc( NVL(((A.collection_timestamp+(A.violation_duration/24)))
	,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp ),''009''))||''d''
	||'':''||ltrim(to_char(MOD(trunc( (NVL(((A.collection_timestamp+(A.violation_duration/24)))
	,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24 ),24),''09''))||''h''
	||'':''||ltrim(to_char(MOD(trunc( (NVL(((A.collection_timestamp+(A.violation_duration/24)))
	,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24*60 ),60),''09''))||''m''
	||'':''||ltrim(to_char(MOD(trunc( (NVL(((A.collection_timestamp+(A.violation_duration/24)))
	,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24*60*60 ),60),''09''))||''s''
	) DURATION_ID,
	A.MESSAGE,
        A.MESSAGE_NLSID,
	A.POLICY_GUID,
	A.collection_timestamp
	FROM mgmt$policy_violation_history A,mgmt$policies R,MGMT$TARGET 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.POLICY_GUID = R.POLICY_GUID AND A.target_guid = ??EMIP_BIND_TARGET_GUID?? AND A.TARGET_GUID=B.TARGET_GUID AND A.violation_level in(20,25,18) 
       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.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and
           DECODE(A.VIOLATION_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)
           ,A.collection_timestamp+(A.VIOLATION_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)
           OR
           (A.VIOLATION_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) 
              or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) 
           )
         )
       )
       select 
         NVL(PM.MESSAGE, NVL(PEM.MESSAGE, R.POLICY_NAME)) POLICY_RULE_ID,
         OBJECTS_ID,
	 NVL(CM.MESSAGE, NVL(CEM.MESSAGE, R.CATEGORY)) CATEGORY_ID,
         SEVERITY_ID,
	 START_TIME_ID,
	 END_TIME_ID,
	 DURATION_ID,
	NVL(DM.MESSAGE,NVL(DEM.MESSAGE,T1.MESSAGE)) MESSAGE_ID
	from tab1 T1, mgmt$policies R, 
        (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 T1.POLICY_GUID = R.POLICY_GUID
         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 T1.MESSAGE_NLSID=DM.MESSAGE_ID(+)
         AND T1.MESSAGE_NLSID=DEM.MESSAGE_ID(+) 
	ORDER BY T1.collection_timestamp
      ');


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

------------------------------------- outstanding_alerts_vio.sql --------------------------------------------- 
    BEGIN
       MGMT_IP.delete_report_by_title('SYSMAN', 'EMR_Outstanding_Alerts_Violations', 0);
       EXCEPTION WHEN OTHERS THEN NULL;
    END;

    l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'EMR_Outstanding_Alerts_Violations',
      p_description_nlsid             => 'EMR_Outstanding_Alerts_Violations_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_help_topic_id                 =>  'info_pub_gc_cs_outav_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',
      'OUTST_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 SUMMARY --------------------------------------------------
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();
    l_param_values.extend(4);

   l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
       'SELECT ''METRIC_ALERTS_TXT_ID'' ALERT_TYPE_ID,
        NVL(SUM(decode(ALERT_STATE,''Critical'',1,0)),0) CRITICAL_ID,
	NVL(SUM(decode(ALERT_STATE,''Warning'',1,0)),0) WARNING_ID, 
        ''NA_TXT_ID'' INFORMATIONAL_ID,
	COUNT(UNIQUE target_guid) TARGETS_AFFECTED_ID from MGMT$ALERT_CURRENT B 
	where B.violation_type in(''Resource'',''Threshold Violation'')
	AND B.ALERT_STATE in (''Critical'',''Warning'')
	UNION ALL
	SELECT ''NON_SUPP_POLICY_VIO_TXT_ID'' ALERT_TYPE_ID,
	NVL(sum(decode(violation_level,25,1,0)),0) CRITICAL_ID,
	NVL(sum(decode(violation_level,20,1,0)),0) WARNING_ID,
	NVL(sum(decode(violation_level,18,1,0)),0)||'''' INFORMATIONAL_ID,
	count(unique target_guid) TARGETS_AFFECTED_ID
	FROM mgmt$policy_violation_current WHERE violation_level in(20,25,18) 
	AND SUPPRESS_CODE = 0
	UNION ALL
	SELECT ''SUPP_POLICY_VIO_TXT_ID'' ALERT_TYPE_ID,
	NVL(sum(decode(violation_level,25,1,0)),0) CRITICAL_ID,
	NVL(sum(decode(violation_level,20,1,0)),0) WARNING_ID,
	NVL(sum(decode(violation_level,18,1,0)),0)||'''' INFORMATIONAL_ID,
	count(unique target_guid) TARGETS_AFFECTED_ID
	FROM mgmt$policy_violation_current WHERE violation_level in(20,25,18) AND SUPPRESS_CODE > 0');

    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.numRowsToShow',
      '10');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.translateColumnValueColumns',
      'INFORMATIONAL_ID,ALERT_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_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 TABLE FROM SQL METRIC ALERTS ----------------------------------------
    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 A.TARGET_NAME TARGET_NAME_ID,A.TYPE_DISPLAY_NAME TARGET_TYPE_ID,
	A.COLUMN_LABEL METRIC_ID,decode(A.ALERT_STATE,''Critical'',25,''Warning'',20) SEVERITY_ID,
	MGMT_VIEW_UTIL.ADJUST_TZ(A.COLLECTION_TIMESTAMP,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) OPEN_SINCE_ID,
	A.MESSAGE MESSAGE_ID from MGMT$ALERT_CURRENT A,MGMT$TARGET B where A.TARGET_GUID=B.TARGET_GUID AND 
	A.violation_type in(''Resource'',''Threshold Violation'') and A.ALERT_STATE in (''Critical'',''Warning'')
	order by collection_timestamp');


    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_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_Metric_Alerts',
      p_element_order       => l_elem_order, 
      p_element_row         => l_curr_row, 
      p_parameters          => l_param_values,
      p_targets             => null);



-------------------- ADDING TABLE FROM SQL NON-EXEMPTED POLICY VIOLATIONS ----------------------------------------
    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',
      'WITH tab1 AS(
        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,
	A.COLLECTION_TIMESTAMP OPEN_SINCE_ID,
        NVL(DM.MESSAGE,NVL(DEM.MESSAGE,A.MESSAGE)) COMMENTS_ID,
	A.TARGET_GUID 
      FROM mgmt$policy_violation_current A ,mgmt$policies R,
       (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.POLICY_GUID=R.POLICY_GUID 
	     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.violation_level in(20,25,18) AND A.SUPPRESS_CODE=0 
      )
      select TARGET_NAME_ID, TARGET_TYPE_ID, POLICY_RULE_ID, OBJECTS_ID, CATEGORY_ID, SEVERITY_ID, 
      MGMT_VIEW_UTIL.ADJUST_TZ(T1.OPEN_SINCE_ID,T2.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) as OPEN_SINCE_ID,
      COMMENTS_ID FROM tab1 T1, mgmt$target T2  WHERE T1.TARGET_GUID=T2.TARGET_GUID 
      ORDER BY T1.OPEN_SINCE_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.numRowsToShow',
      '10');

    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
      '6');
    l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
      'ascending');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.severityColumn',
      '5');

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

   -------------------- ADDING TABLE FROM SQL EXEMPTED POLICY VIOLATIONS --------------------------------------
    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 tab1 AS(
        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,  
	A.COLLECTION_TIMESTAMP OPEN_SINCE_ID,
        NVL(DM.MESSAGE,NVL(DEM.MESSAGE,A.MESSAGE)) COMMENTS_ID,A.SUPPRESS_BY SUPPRESSED_BY_ID, 
        A.SUPPRESS_CODE, A.TARGET_GUID, A.SUPPRESS_UNTIL 
      FROM  mgmt$policy_violation_current A, mgmt$policies R, 
       (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.POLICY_GUID=R.POLICY_GUID 
	     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.violation_level in(20,25,18) AND A.SUPPRESS_CODE>0 
      )
      select TARGET_NAME_ID, TARGET_TYPE_ID, POLICY_RULE_ID, OBJECTS_ID,
      CATEGORY_ID, SEVERITY_ID, 
      MGMT_VIEW_UTIL.ADJUST_TZ(T1.OPEN_SINCE_ID,T2.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) as OPEN_SINCE_ID,
      COMMENTS_ID, SUPPRESSED_BY_ID,
      decode(T1.SUPPRESS_CODE,1,''MANUALLY_REMOVED_TXT_ID'',2, to_char(
         MGMT_VIEW_UTIL.ADJUST_TZ(T1.SUPPRESS_UNTIL,T2.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??)
	 ,''Mon DD, YYYY HH12:MI:SS PM''),3,''NEXT_POLICY_EVAL_TXT_ID'') SUPPRESS_UNTIL_ID 
      from tab1 T1, mgmt$target T2 where T1.TARGET_GUID=T2.TARGET_GUID
      ORDER BY T1.OPEN_SINCE_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.translateColumnValueColumns',
      'SUPPRESS_UNTIL_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_Exempted_Policy_Violations',
      p_element_order       => l_elem_order, 
      p_element_row         => l_curr_row, 
      p_parameters          => l_param_values,
      p_targets             => null);

------------------------------------- outstanding_alerts_vio_grp.sql --------------------------------------------- 
    BEGIN
       MGMT_IP.delete_report_by_title('SYSMAN', 'EMR_Outstanding_Alerts_Violations_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';

-------------------- AVAILABILITY TEST REPORT BEGINS --------------------------------------------------
    l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'EMR_Outstanding_Alerts_Violations_Group',
      p_description_nlsid             => 'EMR_Outstanding_Alerts_Violations_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_help_topic_id                 =>  'info_pub_gc_cs_outav_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',
      'OUTST_ALRT_VIO_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 SUMMARY --------------------------------------------------
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();
    l_param_values.extend(4);

   l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT ''METRIC_ALERTS_TXT_ID'' ALERT_TYPE_ID,	NVL(SUM(decode(ALERT_STATE,''Critical'',1,0)),0) CRITICAL_ID,
	NVL(SUM(decode(ALERT_STATE,''Warning'',1,0)),0) WARNING_ID,''NA_TXT_ID'' INFORMATIONAL_ID,
	COUNT(UNIQUE target_guid) TARGETS_AFFECTED_ID from MGMT$ALERT_CURRENT B where 
	target_guid IN( select  MEMBER_TARGET_GUID FROM  MGMT$TARGET_MEMBERS where aggregate_target_guid = ??EMIP_BIND_TARGET_GUID??)
	AND B.violation_type in(''Resource'',''Threshold Violation'') AND B.ALERT_STATE in (''Critical'',''Warning'')
	UNION ALL
	SELECT ''NON_SUPP_POLICY_VIO_TXT_ID'' ALERT_TYPE_ID,
		NVL(sum(decode(violation_level,25,1,0)),0) CRITICAL_ID,
		NVL(sum(decode(violation_level,20,1,0)),0) WARNING_ID,
		NVL(sum(decode(violation_level,18,1,0)),0)||'''' INFORMATIONAL_ID,
		count(unique target_guid) TARGETS_AFFECTED_ID
	FROM mgmt$policy_violation_current WHERE SUPPRESS_CODE=0 AND violation_level in(20,25,18)
	AND target_guid IN(select MEMBER_TARGET_GUID from MGMT$TARGET_MEMBERS where aggregate_target_guid=??EMIP_BIND_TARGET_GUID??)
	UNION ALL
	SELECT ''SUPP_POLICY_VIO_TXT_ID'' ALERT_TYPE_ID,
		NVL(sum(decode(violation_level,25,1,0)),0) CRITICAL_ID,
		NVL(sum(decode(violation_level,20,1,0)),0) WARNING_ID,
		NVL(sum(decode(violation_level,18,1,0)),0)||'''' INFORMATIONAL_ID,
		count(unique target_guid) TARGETS_AFFECTED_ID
	FROM mgmt$policy_violation_current WHERE SUPPRESS_CODE>0 and violation_level in(20,25,18) 
	AND target_guid IN(select MEMBER_TARGET_GUID from MGMT$TARGET_MEMBERS where aggregate_target_guid=??EMIP_BIND_TARGET_GUID??)');

    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.numRowsToShow',
      '10');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.translateColumnValueColumns',
      'INFORMATIONAL_ID,ALERT_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_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 TABLE FROM SQL METRIC ALERTS ----------------------------------------
    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 A.TARGET_NAME TARGET_NAME_ID,A.TYPE_DISPLAY_NAME TARGET_TYPE_ID,
	A.COLUMN_LABEL METRIC_ID,decode(A.ALERT_STATE,''Critical'',25,''Warning'',20) SEVERITY_ID,
	MGMT_VIEW_UTIL.ADJUST_TZ(A.COLLECTION_TIMESTAMP,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) OPEN_SINCE_ID,
	A.MESSAGE MESSAGE_ID FROM MGMT$ALERT_CURRENT A,MGMT$TARGET B
        WHERE A.TARGET_GUID=B.TARGET_GUID AND A.TARGET_GUID In( select MEMBER_TARGET_GUID from MGMT$TARGET_MEMBERS WHERE aggregate_target_guid=??EMIP_BIND_TARGET_GUID??)                   
        AND A.violation_type in(''Resource'',''Threshold Violation'') and A.ALERT_STATE in (''Critical'',''Warning'')
	ORDER BY A.collection_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',
      '7');
    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_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_Metric_Alerts',
      p_element_order       => l_elem_order, 
      p_element_row         => l_curr_row, 
      p_parameters          => l_param_values,
      p_targets             => null);



-------------------- ADDING TABLE FROM SQL NON-EXEMPTED POLICY VIOLATIONS ----------------------------------------
    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',
      'WITH tab1 AS( 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,  
	A.COLLECTION_TIMESTAMP OPEN_SINCE_ID,
        NVL(DM.MESSAGE,NVL(DEM.MESSAGE,A.MESSAGE)) COMMENTS_ID,
	A.TARGET_GUID 
      FROM mgmt$policy_violation_current A, mgmt$policies R,
       (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.POLICY_GUID=R.POLICY_GUID 
	     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.violation_level in(20,25,18) AND A.SUPPRESS_CODE=0 
        AND A.target_guid IN(SELECT member_target_guid FROM mgmt$target_members WHERE aggregate_target_guid = ??EMIP_BIND_TARGET_GUID??) 
	)
	select TARGET_NAME_ID, TARGET_TYPE_ID, POLICY_RULE_ID, OBJECTS_ID, CATEGORY_ID, SEVERITY_ID,
        MGMT_VIEW_UTIL.ADJUST_TZ(T1.OPEN_SINCE_ID,T2.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) OPEN_SINCE_ID,
        COMMENTS_ID from tab1 T1, mgmt$target T2 where T1.TARGET_GUID=T2.TARGET_GUID
      ORDER BY T1.OPEN_SINCE_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_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_Non_Exempted_Policy_Violations',
      p_element_order       => l_elem_order, 
      p_element_row         => l_curr_row, 
      p_parameters          => l_param_values,
      p_targets             => null);

   -------------------- ADDING TABLE FROM SQL EXEMPTED POLICY VIOLATIONS --------------------------------------
    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 tab1 AS(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,  
	A.COLLECTION_TIMESTAMP OPEN_SINCE_ID,
        NVL(DM.MESSAGE,NVL(DEM.MESSAGE,A.MESSAGE)) COMMENTS_ID,A.SUPPRESS_BY SUPPRESSED_BY_ID, 
        A.SUPPRESS_CODE, A.TARGET_GUID, A.SUPPRESS_UNTIL 
      FROM  
        mgmt$policy_violation_current A, mgmt$policies R,
       (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.POLICY_GUID = R.POLICY_GUID 
	     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.violation_level in(20,25,18) AND A.SUPPRESS_CODE>0 
        AND A.target_guid IN(SELECT member_target_guid FROM mgmt$target_members WHERE aggregate_target_guid=??EMIP_BIND_TARGET_GUID??)
      )
      select TARGET_NAME_ID, TARGET_TYPE_ID, POLICY_RULE_ID, OBJECTS_ID, CATEGORY_ID, SEVERITY_ID,
      MGMT_VIEW_UTIL.ADJUST_TZ(T1.OPEN_SINCE_ID,T2.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) OPEN_SINCE_ID,
      COMMENTS_ID, SUPPRESSED_BY_ID,
      decode(T1.SUPPRESS_CODE,1,''MANUALLY_REMOVED_TXT_ID'',2,to_char(
        MGMT_VIEW_UTIL.ADJUST_TZ(T1.SUPPRESS_UNTIL,T2.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??),''Mon DD, YYYY HH12:MI:SS PM''),3,''NEXT_POLICY_EVAL_TXT_ID'' ) SUPPRESS_UNTIL_ID 
      from tab1 T1, mgmt$target T2 where T1.TARGET_GUID=T2.TARGET_GUID
      ORDER BY T1.OPEN_SINCE_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.translateColumnValueColumns',
      'SUPPRESS_UNTIL_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_Exempted_Policy_Violations',
      p_element_order       => l_elem_order, 
      p_element_row         => l_curr_row, 
      p_parameters          => l_param_values,
      p_targets             => null);


------------------------------------- outstanding_alerts_vio_tgt.sql --------------------------------------------- 
    BEGIN
       MGMT_IP.delete_report_by_title('SYSMAN', 'EMR_Outstanding_Alerts_Violations_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';

-------------------- AVAILABILITY TEST REPORT BEGINS (Outstanding Alerts and Violations by Target)--------------------------------------------------
    l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'EMR_Outstanding_Alerts_Violations_Target',
      p_description_nlsid             => 'EMR_Outstanding_Alerts_Violations_Target_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_help_topic_id                 =>  'info_pub_gc_cs_outav_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',
      'OUTST_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 Target Status --------------------------------------------------
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();
    l_param_values.extend(3);

   l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT (select availability_status ||'' ''||''since''||'' ''|| to_char(start_timestamp,''Month dd yyyy hh:mm:ss'') from mgmt$availability_current 
       WHERE target_guid = ??EMIP_BIND_TARGET_GUID??) STATUS_ID
	,(select ''''||to_char( round(NVL(AVG(compliance_score),0),2) ) from mgmt$target_policy_eval_summ 
	where target_guid = ??EMIP_BIND_TARGET_GUID??) COMPLIANCE_SCORE_ID
	from dual');

    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.nameValueDisplay',
      '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_Status',
      p_element_order       => l_elem_order, 
      p_element_row         => l_curr_row, 
      p_parameters          => l_param_values,
      p_targets             => null);


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

-- Changes done by verangan to produce a default row too;
   l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT ''METRIC_ALERTS_TXT_ID'' ALERT_TYPE_ID,NVL(SUM(decode(ALERT_STATE,''Critical'',1,0)),0) CRITICAL_ID,
	NVL(SUM(decode(ALERT_STATE,''Warning'',1,0)),0) WARNING_ID,''NA_TXT_ID'' INFORMATIONAL_ID 
	from MGMT$ALERT_CURRENT B where	target_guid = ??EMIP_BIND_TARGET_GUID??
	AND B.violation_type in(''Resource'',''Threshold Violation'') AND B.ALERT_STATE in (''Critical'',''Warning'')
	UNION ALL
	select ''NON_SUPP_POLICY_VIO_TXT_ID'' ALERT_TYPE_ID,
	NVL(sum(decode(violation_level,25,1,0)),0) CRITICAL_ID,
	NVL(sum(decode(violation_level,20,1,0)),0) WARNING_ID,
	NVL(sum(decode(violation_level,18,1,0)),0)||'''' INFORMATIONAL_ID
	FROM mgmt$policy_violation_current
	WHERE SUPPRESS_CODE = 0 AND violation_level in(20,25,18) AND target_guid = ??EMIP_BIND_TARGET_GUID??
	UNION ALL
	select ''SUPP_POLICY_VIO_TXT_ID'' ALERT_TYPE_ID,
	NVL(sum(decode(violation_level,25,1,0)),0) CRITICAL_ID,
	NVL(sum(decode(violation_level,20,1,0)),0) WARNING_ID,
	NVL(sum(decode(violation_level,18,1,0)),0)||'''' INFORMATIONAL_ID
	FROM mgmt$policy_violation_current
	WHERE SUPPRESS_CODE > 0 AND violation_level in(20,25,18) AND target_guid = ??EMIP_BIND_TARGET_GUID??');

    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',
      '0');
    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.translateColumnValueColumns',
      'INFORMATIONAL_ID,ALERT_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_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 TABLE for METRIC ALERTS   --------------------------------------------------
    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 A.COLUMN_LABEL METRIC_ID,decode(A.ALERT_STATE,''Critical'',25,''Warning'',20) SEVERITY_ID,
	MGMT_VIEW_UTIL.ADJUST_TZ(A.COLLECTION_TIMESTAMP,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) OPEN_SINCE_ID,
	A.MESSAGE MESSAGE_ID from MGMT$ALERT_CURRENT A,MGMT$TARGET B
	where A.TARGET_GUID=B.TARGET_GUID AND A.violation_type in(''Resource'',''Threshold Violation'')
	and A.ALERT_STATE in (''Critical'',''Warning'')
	AND A.target_guid = ??EMIP_BIND_TARGET_GUID?? order by A.collection_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',
      '2');
    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',
      '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 ( 
      l_report_guid,
      'IPMSG_USER_TABLE_FROM_SQL',
      'IPMSG_ANY_TARGET_TYPE',
      'EMR_Metric_Alerts',
      l_elem_order,
      l_curr_row,
      l_param_values,
      null); 

 -------------------- ADDING TABLE FROM SQL NON-EXEMPTED--------------------------------------------------
    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',
      'WITH tab1 AS( SELECT  
	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,  
	A.COLLECTION_TIMESTAMP OPEN_SINCE_ID,
        NVL(DM.MESSAGE,NVL(DEM.MESSAGE,A.MESSAGE)) MESSAGE_ID,
	A.TARGET_GUID 
      FROM  mgmt$policy_violation_current A, mgmt$policies R,
       (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.POLICY_GUID=R.POLICY_GUID 
	     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.violation_level in(20,25,18) AND A.SUPPRESS_CODE=0 AND A.target_guid = ??EMIP_BIND_TARGET_GUID?? 
    )
    select POLICY_RULE_ID, OBJECTS_ID, CATEGORY_ID, SEVERITY_ID, 
    MGMT_VIEW_UTIL.ADJUST_TZ(T1.OPEN_SINCE_ID,T2.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) OPEN_SINCE_ID,
    MESSAGE_ID
    from tab1 T1, mgmt$target T2 where T1.TARGET_GUID=T2.TARGET_GUID
    ORDER BY T1.OPEN_SINCE_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_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_Non_Exempted_Policy_Violations',
      l_elem_order,
      l_curr_row,
      l_param_values,
      null);
   -------------------- ADDING TABLE FROM SQL EXEMPTED--------------------------------------------------
    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 tab1 AS( SELECT 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,  
	A.COLLECTION_TIMESTAMP OPEN_SINCE_ID,
        NVL(DM.MESSAGE,NVL(DEM.MESSAGE,A.MESSAGE)) MESSAGE_ID,A.SUPPRESS_BY SUPPRESSED_BY_ID, 
	  A.TARGET_GUID, A.SUPPRESS_CODE, A.SUPPRESS_UNTIL 
      FROM mgmt$policy_violation_current A, mgmt$policies R,
       (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.POLICY_GUID=R.POLICY_GUID 
	     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.violation_level in(20,25,18) AND A.SUPPRESS_CODE>0 
        AND A.target_guid = ??EMIP_BIND_TARGET_GUID?? 
     )
     select POLICY_RULE_ID, OBJECTS_ID, CATEGORY_ID, SEVERITY_ID,
      MGMT_VIEW_UTIL.ADJUST_TZ(T1.OPEN_SINCE_ID,T2.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) OPEN_SINCE_ID,
      SUPPRESSED_BY_ID,
      decode(T1.SUPPRESS_CODE,1,''MANUALLY_REMOVED_TXT_ID'',2,to_char(
	  MGMT_VIEW_UTIL.ADJUST_TZ(T1.SUPPRESS_UNTIL,T2.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??),''Mon DD, YYYY HH12:MI:SS PM''),3,''NEXT_POLICY_EVAL_TXT_ID'' ) SUPPRESS_UNTIL_ID 
      from tab1 T1, mgmt$target T2 where T1.TARGET_GUID=T2.TARGET_GUID
     order by T1.OPEN_SINCE_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.translateColumnValueColumns',
      'SUPPRESS_UNTIL_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_Exempted_Policy_Violations',
      l_elem_order,
      l_curr_row,
      l_param_values,
      null);


COMMIT;

 END;
  /