Rem
Rem $Header: ecm_hostpatch_reports.sql 30-aug-2005.04:41:44 pdasika 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    pdasika     08/30/05 - pdasika_bug-4567546
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_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';

  
  -- COMPLIANCE: ALL HOSTS


  --Delete earlier report of this name if any
  BEGIN
    SELECT REPORT_GUID
      INTO l_report_guid
      FROM MGMT_IP_REPORT_DEF
     WHERE title_nlsid = 'HOSTPATCH_COMPL_ALL_HOSTS_NLSID';
    mgmt_ip.delete_report(l_report_guid,1);
  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    l_report_guid := 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_PATCHING', 
    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(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 * 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');
  
  
  --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_REPORT_HEADER',
    p_element_order => 1,
    p_element_row => 1,
    p_parameters => l_param_values,
    p_targets => null);




  -- COMPLIANCE: SINGLE GROUP



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

  -- Creates a report definition for reporting compliance history of a particular group
  l_report_guid := mgmt_ip.create_report_definition (
    p_title_nlsid => 'HOSTPATCH_COMPL_GROUP_NLSID',
    p_description_nlsid => 'HOSTPATCH_COMPL_GROUP_DESC_NLSID',
    p_owner => 'SYSMAN',
    p_category_nlsid => 'IPMSG_PATCHING', 
    p_sub_category_nlsid => 'IPMSG_OS_PATCHING',
    p_late_binding_target_types => l_target_types_2,
    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 SQL query passed to retrieve data
  l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.sqlStatement',
  'SELECT h.group_name, h.total_hosts, h.compliant_hosts, h.LAST_CHECKED_ON 
  from MGMT$HOSTPATCH_GRP_COMPL_HIST h, MGMT$TARGET t where h.group_name = t.target_name and 
  t.target_guid = ??EMIP_BIND_TARGET_GUID??');
  
  --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_COMPL_HIST_HEADER',
    p_element_order => 1,
    p_element_row => 1,
    p_parameters => l_param_values,
    p_targets => null);



  -- COMPLIANCE: ALL GROUPS


  --Delete earlier report of this name if any
  BEGIN
    SELECT REPORT_GUID
      INTO l_report_guid
      FROM MGMT_IP_REPORT_DEF
     WHERE title_nlsid = 'HOSTPATCH_COMPL_ALL_GROUPS_NLSID';
    mgmt_ip.delete_report(l_report_guid,1);
  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    l_report_guid := 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_PATCHING', 
    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(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 * 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');



  --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_HIST_HEADER',
    p_element_order => 1,
    p_element_row => 1,
    p_parameters => l_param_values,
    p_targets => null);


  
  -- COMPLIANCE: SINGLE HOST


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

  --Creates a definition for reporting compliance of a particular host
  l_report_guid := mgmt_ip.create_report_definition (
    p_title_nlsid => 'HOSTPATCH_COMPL_HOST_NLSID',
    p_description_nlsid => 'HOSTPATCH_COMPL_HOST_DESC_NLSID',
    p_owner => 'SYSMAN',
    p_category_nlsid => 'IPMSG_PATCHING', 
    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 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, h.group_name, h.OUT_OF_DATE_PACKAGES, h.ROGUE_PACKAGES 
  from MGMT$HOSTPATCH_HOSTS h, MGMT$TARGET t
  where h.host_name = t.target_name and t.target_guid = ??EMIP_BIND_TARGET_GUID??');
  
  --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_COMPL_REPORT_HEADER',
    p_element_order => 1,
    p_element_row => 1,
    p_parameters => l_param_values,
    p_targets => null);







  -- NON COMPLIANT PACKAGES: SINGLE HOST


  --Delete earlier report of this name if any
  BEGIN
    SELECT REPORT_GUID
      INTO l_report_guid
      FROM MGMT_IP_REPORT_DEF
     WHERE title_nlsid = 'HOSTPATCH_NON_COMPL_NLSID';
    mgmt_ip.delete_report(l_report_guid,1);
  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    l_report_guid := 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_PATCHING', 
    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 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 NON_COMPLIANT_PACKAGES, 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 => 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 OUT_OF_DATE_PACKAGES, 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 => 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 ROGUE_PACKAGES, 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 => 3,
    p_element_row => 3,
    p_parameters => l_param_values,
    p_targets => null);



  -- NON COMPLIANT PACKAGES: ALL HOSTS


  --Delete earlier report of this name if any
  BEGIN
    SELECT REPORT_GUID
      INTO l_report_guid
      FROM MGMT_IP_REPORT_DEF
     WHERE title_nlsid = 'HOSTPATCH_NON_COMPL_ALL_NLSID';
    mgmt_ip.delete_report(l_report_guid,1);
  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    l_report_guid := 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_PATCHING', 
    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(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 NON_COMPLIANT_PACKAGES, 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 => 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 OUT_OF_DATE_PACKAGES, 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 => 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 ROGUE_PACKAGES, 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 => 3,
    p_element_row => 3,
    p_parameters => l_param_values,
    p_targets => null);





END;
/
commit;