Rem $Header: ecm_hostpatch_reports.sql 20-dec-2005.02:03:59 ranmath Exp $
Rem
Rem ecm_hostpatch_reports.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ecm_hostpatch_reports.sql - Out-of-box reports for Host Patching.
Rem
Rem    DESCRIPTION
Rem      Report definitions for OS Patching.
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    ranmath     12/20/05 - Backport ranmath_bug-4746646 from main 
Rem    ranmath     12/12/05 - bug-4746646: Fix pie-chart headers for i18n. 
Rem    pdasika     08/26/05 - Removing the header 
Rem    gsbhatia    07/03/05 - New repmgr header impl 
Rem    lgloyd      06/21/05 - consolidate category/subcategory strings 
Rem    achugh      05/20/05 - UI review fixes
Rem    achugh      12/20/04 - achugh_bug-4046097
Rem    achugh      12/13/04 - Created
Rem

DECLARE

  l_target_types_1  MGMT_IP_TARGET_TYPES;
  l_target_types_2  MGMT_IP_TARGET_TYPES;
  l_param_values    MGMT_IP_PARAM_VALUE_LIST;
  l_report_guid     RAW(16);
  l_title           VARCHAR2(256);
  l_element_guid    RAW(16);
 
BEGIN
  l_target_types_1 := MGMT_IP_TARGET_TYPES(1);
  l_target_types_1.extend(1);
  l_target_types_1(1) := 'host';

  l_target_types_2 := MGMT_IP_TARGET_TYPES(1);
  l_target_types_2.extend(1);
  l_target_types_2(1) := 'composite';

  
-- Deleting additional reports which were present in Beta and not in 10.2 main line
BEGIN   
       SELECT REPORT_GUID
       INTO l_report_guid
       FROM MGMT_IP_REPORT_DEF
       WHERE title_nlsid = 'HOSTPATCH_COMPL_HOST_NLSID'
	and owner = 'SYSMAN'
        and system_report = 1;
       mgmt_ip.delete_report(l_report_guid,1);
       EXCEPTION
       WHEN NO_DATA_FOUND
           THEN
           l_report_guid := NULL;
END;
  
BEGIN   
       SELECT REPORT_GUID
       INTO l_report_guid
       FROM MGMT_IP_REPORT_DEF
       WHERE title_nlsid = 'HOSTPATCH_COMPL_GROUP_NLSID'
	and owner = 'SYSMAN'
        and system_report = 1;
       mgmt_ip.delete_report(l_report_guid,1);
       EXCEPTION
       WHEN NO_DATA_FOUND
           THEN
           l_report_guid := NULL;
END;
-- End of deletion. Start of creation
           

-- COMPLIANCE: ALL HOSTS


  --Delete earlier report of this name if any
  BEGIN
    SELECT title_nlsid
      INTO l_title
      FROM MGMT_IP_REPORT_DEF
     WHERE title_nlsid = 'HOSTPATCH_COMPL_ALL_HOSTS_NLSID';
    mgmt_ip.delete_report_by_title('SYSMAN',l_title,1);
  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    l_title := NULL;
  END;


  --Creates a definition for reporting compliance of all hosts

  l_report_guid := mgmt_ip.create_report_definition (
    p_title_nlsid => 'HOSTPATCH_COMPL_ALL_HOSTS_NLSID',
    p_description_nlsid => 'HOSTPATCH_COMPL_ALL_HOSTS_DESC_NLSID',
    p_owner => 'SYSMAN',
    p_category_nlsid => 'IPMSG_DEPLOYMENT_AND_CONFIGURATION_CATEGORY', 
    p_sub_category_nlsid => 'IPMSG_OS_PATCHING',
    p_late_binding_target_types => null,
    p_late_binding_multi_targets => 0,
    p_show_table_of_contents => 0,
    p_system_report => 1);


  l_param_values := MGMT_IP_PARAM_VALUE_LIST();
  l_param_values.extend(4);

  --The SQL query passed to retrieve data
  l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.sqlStatement',
  'select ty, num from ( select '||'''COMPLIANT'' ty, count(host_name) num from MGMT$HOSTPATCH_HOSTS where out_of_date_packages = 0 and rogue_packages = 0 union select '||'''NON_COMPLIANT'' ty, count(host_name) num from MGMT$HOSTPATCH_HOSTS where out_of_date_packages > 0 or rogue_packages > 0 union select '||'''COMPLIANCE_UNKNOWN'' ty, count(host_name) num from MGMT$HOSTPATCH_HOSTS where out_of_date_packages = -1 or rogue_packages = -1)');

  --The resource bundle containing column headers
  l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.resources.eml.ecm.hostpatch.HostPatchResourceBundle');

  --bug-4746646: We want stuff to be translated.
  l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.ChartParamController.translateKeyValues',
  'true');
  l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.ChartParamController.resourceBundle',
  'oracle.sysman.resources.eml.ecm.hostpatch.HostPatchResourceBundle');

  --Adding a pie-chart to the created report definition and passing the above parameters
  l_element_guid := mgmt_ip.add_element_to_report_def (
    p_report_guid => l_report_guid,
    p_element_name_nlsid => 'IPMSG_USER_CHART_FROM_SQL',
    p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
    p_header_nlsid => 'HOSTPATCH_HOST_COMPL_HEADER',
    p_element_order => 1,
    p_element_row => 1,
    p_parameters => l_param_values,
    p_targets => null);




  l_param_values := MGMT_IP_PARAM_VALUE_LIST();
  l_param_values.extend(4);

  --The SQL query passed to retrieve data
  l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.sqlStatement',
  'SELECT host_name, group_name, DECODE(out_of_date_packages+rogue_packages,-2,'||'''NA'',out_of_date_packages+rogue_packages) as non_compliant_packages, DECODE(out_of_date_packages, -1,'||'''NA'',out_of_date_packages) as out_of_date_packages, DECODE(rogue_packages,-1,'||'''NA'',rogue_packages) as rogue_packages from MGMT$HOSTPATCH_HOSTS');

  --The resource bundle containing column headers
  l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.resources.eml.ecm.hostpatch.HostPatchResourceBundle');
  
  --The initial sort column
  l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
  'NON_COMPLIANT_PACKAGES');


  --The initial sort order
  l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
  'descending');

  
  --Adding a table to the created report definition and passing the above parameters
  l_element_guid := mgmt_ip.add_element_to_report_def (
    p_report_guid => l_report_guid,
    p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL',
    p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
    p_header_nlsid => 'HOSTPATCH_NON_COMPL_HOST_HEADER',
    p_element_order => 2,
    p_element_row => 2,
    p_parameters => l_param_values,
    p_targets => null);







  -- COMPLIANCE: ALL GROUPS


  --Delete earlier report of this name if any
  BEGIN
    SELECT title_nlsid
      INTO l_title
      FROM MGMT_IP_REPORT_DEF
     WHERE title_nlsid = 'HOSTPATCH_COMPL_ALL_GROUPS_NLSID';
    mgmt_ip.delete_report_by_title('SYSMAN',l_title,1);
  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    l_title := NULL;
  END;

  --Creates a definition for reporting compliance history of all groups
  l_report_guid := mgmt_ip.create_report_definition (
    p_title_nlsid => 'HOSTPATCH_COMPL_ALL_GROUPS_NLSID',
    p_description_nlsid => 'HOSTPATCH_COMPL_ALL_GROUPS_DESC_NLSID',
    p_owner => 'SYSMAN',
    p_category_nlsid => 'IPMSG_DEPLOYMENT_AND_CONFIGURATION_CATEGORY', 
    p_sub_category_nlsid => 'IPMSG_OS_PATCHING',
    p_late_binding_target_types => null,
    p_late_binding_multi_targets => 0,
    p_show_table_of_contents => 0,
    p_system_report => 1);



  l_param_values := MGMT_IP_PARAM_VALUE_LIST();
  l_param_values.extend(4);

  --The SQL query passed to retrieve data
  l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.sqlStatement',
  'select ty, num from (select '||'''COMPLIANT'' ty, count(g.group_name)-count(distinct(h.group_name))-count(distinct(u.group_name)) num from mgmt$hostpatch_hosts h, mgmt$hostpatch_groups g, mgmt$hostpatch_hosts u where (h.out_of_date_packages > 0 or h.rogue_packages > 0) and (u.out_of_date_packages = -1 or u.rogue_packages = -1 and u.group_name not in (select distinct(group_name) from mgmt$hostpatch_hosts where out_of_date_packages > 0 or rogue_packages > 0 )) union select '||'''NON_COMPLIANT'' ty, count(distinct(group_name)) num from mgmt$hostpatch_hosts where out_of_date_packages > 0 or rogue_packages > 0 union select '||'''COMPLIANCE_UNKNOWN'' ty, count(distinct(group_name)) num from mgmt$hostpatch_hosts where out_of_date_packages = -1 or rogue_packages = -1 and group_name not in (select distinct(group_name) from mgmt$hostpatch_hosts where out_of_date_packages > 0 or rogue_packages > 0 ))');

  --The resource bundle containing column headers
  l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.resources.eml.ecm.hostpatch.HostPatchResourceBundle');


  --bug-4746646: We want stuff to be translated.
  l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.ChartParamController.translateKeyValues',
  'true');
  l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.ChartParamController.resourceBundle',
  'oracle.sysman.resources.eml.ecm.hostpatch.HostPatchResourceBundle');


  --Adding a chart to the created report definition and passing the above parameters
  l_element_guid := mgmt_ip.add_element_to_report_def (
    p_report_guid => l_report_guid,
    p_element_name_nlsid => 'IPMSG_USER_CHART_FROM_SQL',
    p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
    p_header_nlsid => 'HOSTPATCH_GROUP_COMPL_HEADER',
    p_element_order => 1,
    p_element_row => 1,
    p_parameters => l_param_values,
    p_targets => null);





  l_param_values := MGMT_IP_PARAM_VALUE_LIST();
  l_param_values.extend(4);
   
  --The SQL query passed to retrieve data
  l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.sqlStatement',
  'SELECT group_name, total_hosts-compliant_hosts as non_compliant_hosts, compliant_hosts, total_hosts, last_checked_on from MGMT$HOSTPATCH_GRP_COMPL_HIST');

  --The resource bundle containing column headers
  l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.resources.eml.ecm.hostpatch.HostPatchResourceBundle');



  --The initial sort column
  l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
  'NON_COMPLIANT_HOSTS');


  --The initial sort order
  l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
  'descending');





  --Adding a table to the created report definition and passing the above parameters
  l_element_guid := mgmt_ip.add_element_to_report_def (
    p_report_guid => l_report_guid,
    p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL',
    p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
    p_header_nlsid => 'HOSTPATCH_COMPL_SUMM_HEADER',
    p_element_order => 2,
    p_element_row => 2,
    p_parameters => l_param_values,
    p_targets => null);


  



  -- NON COMPLIANT PACKAGES: SINGLE HOST


  --Delete earlier report of this name if any
  BEGIN
    SELECT title_nlsid
      INTO l_title
      FROM MGMT_IP_REPORT_DEF
     WHERE title_nlsid = 'HOSTPATCH_NON_COMPL_NLSID';
    mgmt_ip.delete_report_by_title('SYSMAN',l_title,1);
  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    l_title := NULL;
  END;

  --Creates a definition for reporting non-compliant packages on a particular host
  l_report_guid:= mgmt_ip.create_report_definition (
    p_title_nlsid => 'HOSTPATCH_NON_COMPL_NLSID',
    p_description_nlsid => 'HOSTPATCH_NON_COMPL_DESC_NLSID',
    p_owner => 'SYSMAN',
    p_category_nlsid => 'IPMSG_DEPLOYMENT_AND_CONFIGURATION_CATEGORY', 
    p_sub_category_nlsid => 'IPMSG_OS_PATCHING',
    p_late_binding_target_types => l_target_types_1,
    p_late_binding_multi_targets => 0,
    p_show_table_of_contents => 0,
    p_system_report => 1);


  l_param_values := MGMT_IP_PARAM_VALUE_LIST();
  l_param_values.extend(3);


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

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

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


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





  l_param_values := MGMT_IP_PARAM_VALUE_LIST();
  l_param_values.extend(3);

  --The SQL query passed to retrieve data
  l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.sqlStatement',
  'SELECT h.pkg_name as PACKAGE, h.version 
  from MGMT$HOSTPATCH_HOST_COMPL h, MGMT$TARGET t
  where h.host_name = t.target_name and t.target_guid = ??EMIP_BIND_TARGET_GUID??
  and (h.is_out_of_date = 1 or h.is_rogue = 1)');
  
  --Set to true to indicate that the sql accepts single target binding
  l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
  'true');
  
  --The resource bundle containing column headers
  l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.resources.eml.ecm.hostpatch.HostPatchResourceBundle');
  
  --Adding a table to the created report definition and passing the above parameters
  l_element_guid := mgmt_ip.add_element_to_report_def (
    p_report_guid => l_report_guid,
    p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL',
    p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
    p_header_nlsid => 'HOSTPATCH_NON_COMPL_HEADER',
    p_element_order => 2,
    p_element_row => 2,
    p_parameters => l_param_values,
    p_targets => null);




  l_param_values := MGMT_IP_PARAM_VALUE_LIST();
  l_param_values.extend(3);

  --The SQL query passed to retrieve data
  l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.sqlStatement',
  'SELECT h.pkg_name as PACKAGE, h.version 
  from MGMT$HOSTPATCH_HOST_COMPL h, MGMT$TARGET t where h.host_name = t.target_name and 
  t.target_guid = ??EMIP_BIND_TARGET_GUID??
  and h.is_out_of_date = 1');

  --Set to true to indicate that the sql accepts single target binding
  l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
  'true');
  
  --The resource bundle containing column headers
  l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.resources.eml.ecm.hostpatch.HostPatchResourceBundle');

  --Adding a table to the created report definition and passing the above parameters
 l_element_guid := mgmt_ip.add_element_to_report_def (
    p_report_guid => l_report_guid,
    p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL',
    p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
    p_header_nlsid => 'HOSTPATCH_OOD_HEADER',
    p_element_order => 3,
    p_element_row => 3,
    p_parameters => l_param_values,
    p_targets => null);


  l_param_values := MGMT_IP_PARAM_VALUE_LIST();
  l_param_values.extend(3);


  --The SQL query passed to retrieve data 
  l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.sqlStatement',
  'SELECT h.pkg_name as PACKAGE, h.version 
  from MGMT$HOSTPATCH_HOST_COMPL h, MGMT$TARGET t where h.host_name = t.target_name and 
  t.target_guid = ??EMIP_BIND_TARGET_GUID?? and h.is_rogue = 1');

  --Set to true to indicate that the sql accepts single target binding
  l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
  'true');
 
  --The resource bundle containing column headers
  l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.resources.eml.ecm.hostpatch.HostPatchResourceBundle');
  
  --Adding a table to the created report definition and passing the above parameters
 l_element_guid := mgmt_ip.add_element_to_report_def (
    p_report_guid => l_report_guid,
    p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL',
    p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
    p_header_nlsid => 'HOSTPATCH_ROGUE_HEADER',
    p_element_order => 4,
    p_element_row => 4,
    p_parameters => l_param_values,
    p_targets => null);



  -- NON COMPLIANT PACKAGES: ALL HOSTS


  --Delete earlier report of this name if any
  BEGIN
    SELECT title_nlsid
      INTO l_title
      FROM MGMT_IP_REPORT_DEF
     WHERE title_nlsid = 'HOSTPATCH_NON_COMPL_ALL_NLSID';
    mgmt_ip.delete_report_by_title('SYSMAN',l_title,1);
  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    l_title := NULL;
  END;

  --Creates a definition for reporting non-compliant packages on all hosts
  l_report_guid := mgmt_ip.create_report_definition (
    p_title_nlsid => 'HOSTPATCH_NON_COMPL_ALL_NLSID',
    p_description_nlsid => 'HOSTPATCH_NON_COMPL_ALL_DESC_NLSID',
    p_owner => 'SYSMAN',
    p_category_nlsid => 'IPMSG_DEPLOYMENT_AND_CONFIGURATION_CATEGORY', 
    p_sub_category_nlsid => 'IPMSG_OS_PATCHING',
    p_late_binding_target_types => null,
    p_late_binding_multi_targets => 0,
    p_show_table_of_contents => 0,
    p_system_report => 1);

  l_param_values := MGMT_IP_PARAM_VALUE_LIST();
  l_param_values.extend(3);


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

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

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



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



  l_param_values := MGMT_IP_PARAM_VALUE_LIST();
  l_param_values.extend(2);


  --The SQL query passed to retrieve data
  l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.sqlStatement',
  'SELECT h.host_name, g.group_name, h.pkg_name as PACKAGE, h.version from MGMT$HOSTPATCH_HOST_COMPL h, 
   MGMT$HOSTPATCH_HOSTS g where (h.is_out_of_date = 1 or h.is_rogue = 1) and g.host_name = h.host_name ');
  
  --The resource bundle containing column headers
  l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.resources.eml.ecm.hostpatch.HostPatchResourceBundle');

  --Adding a table to the created report definition and passing the above parameters
  l_element_guid := mgmt_ip.add_element_to_report_def (
    p_report_guid => l_report_guid,
    p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL',
    p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
    p_header_nlsid => 'HOSTPATCH_NON_COMPL_HEADER',
    p_element_order => 2,
    p_element_row => 2,
    p_parameters => l_param_values,
    p_targets => null);


  l_param_values := MGMT_IP_PARAM_VALUE_LIST();
  l_param_values.extend(2);


  --The SQL query passed to retrieve data
  l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.sqlStatement',
  'SELECT h.host_name, g.group_name, h.pkg_name as PACKAGE, h.version from MGMT$HOSTPATCH_HOST_COMPL h, 
   MGMT$HOSTPATCH_HOSTS g where h.is_out_of_date = 1 and g.host_name = h.host_name');
  
  --The resource bundle containing column headers
  l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.resources.eml.ecm.hostpatch.HostPatchResourceBundle');

  --Adding a table to the created report definition and passing the above parameters
  l_element_guid := mgmt_ip.add_element_to_report_def (
    p_report_guid => l_report_guid,
    p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL',
    p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
    p_header_nlsid => 'HOSTPATCH_OOD_HEADER',
    p_element_order => 3,
    p_element_row => 3,
    p_parameters => l_param_values,
    p_targets => null);


 

  l_param_values := MGMT_IP_PARAM_VALUE_LIST();
  l_param_values.extend(2);

  --The SQL query passed to retrieve data
  l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.sqlStatement',
  'SELECT h.host_name, g.group_name, h.pkg_name as PACKAGE, h.version from 
  MGMT$HOSTPATCH_HOST_COMPL h, MGMT$HOSTPATCH_HOSTS g where h.is_rogue = 1 
  and g.host_name = h.host_name');
  
  --The resource bundle containing column headers
  l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.resources.eml.ecm.hostpatch.HostPatchResourceBundle');

  
  --Adding a table to the created report definition and passing the above parameters
  l_element_guid := mgmt_ip.add_element_to_report_def (
    p_report_guid => l_report_guid,
    p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL',
    p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
    p_header_nlsid => 'HOSTPATCH_ROGUE_HEADER',
    p_element_order => 4,
    p_element_row => 4,
    p_parameters => l_param_values,
    p_targets => null);





END;
/
commit;