Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ecm_homepatch_reports.sql - Out-of-box reports for Home Patching.
Rem
Rem    DESCRIPTION
Rem      Report definitions for Oracle Home 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    pdasika     12/23/04 - pdasika_patching_reports
Rem    pdasika     12/13/04 - Created
Rem






 DECLARE
   l_target_types  MGMT_IP_TARGET_TYPES;
   l_param_classes MGMT_IP_PARAM_CLASSES;
   l_param_values  MGMT_IP_PARAM_VALUE_LIST;
   l_targets       MGMT_IP_TARGET_LIST;
   l_report_guid   RAW(16);
   l_element_guid  RAW(16);
   l_order         NUMBER;
   l_target        VARCHAR(256);



BEGIN

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


  
--Advisory Report For Alerts Summary
BEGIN
       SELECT REPORT_GUID
       INTO l_report_guid
       FROM MGMT_IP_REPORT_DEF
       WHERE title_nlsid = 'HOMEPATCH_ADVISORY_SUMMARY_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;       
 
   l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'HOMEPATCH_ADVISORY_SUMMARY_NLSID',
      p_description_nlsid             => 'HOMEPATCH_ADVISORY_SUMMARY_DESC_NLSID',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'IPMSG_PATCHING',
      p_sub_category_nlsid            => 'IPMSG_OH_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);

     l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
     'oracle.sysman.eml.ip.render.elem.sqlStatement',

'select advisory_name as "ADVISORY",
	   impact as "IMPACT",
	abstract as "ABSTRACT",
	count(distinct host_name) as HOSTS,
	count(distinct host_name||home_location) as HOMES  
from mgmt$patch_advisories
group by advisory_name,impact,abstract');

 l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.eml.ecm.patch.util.PatchResourceBundle');

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

 
-- Advisory Reports :Single Host

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

BEGIN   
       SELECT REPORT_GUID
       INTO l_report_guid
       FROM MGMT_IP_REPORT_DEF
       WHERE title_nlsid = 'HOMEPATCH_ADVISORY_SINGLE_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;  

 l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'HOMEPATCH_ADVISORY_SINGLE_HOST_NLSID',
      p_description_nlsid             => 'HOMEPATCH_ADVISORY_SINGLE_HOST_DESC_NLSID',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'IPMSG_PATCHING',
      p_sub_category_nlsid            => 'IPMSG_OH_PATCHING',
      p_late_binding_target_types     =>  l_target_types,
      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);

     l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
     'oracle.sysman.eml.ip.render.elem.sqlStatement',

'select advisory_name as  ADVISORY,
	  impact as IMPACT,
	  abstract as ABSTRACT,
	  host_name as AFFECTED_HOST,
	  home_location as DIRECTORY	   
from  mgmt$patch_advisories 
where target_guid = ??EMIP_BIND_TARGET_GUID??');

l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
  'true'); 

 l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.eml.ecm.patch.util.PatchResourceBundle');

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



--Patch Advisory: All Hosts

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


BEGIN   
       SELECT REPORT_GUID
       INTO l_report_guid
       FROM MGMT_IP_REPORT_DEF
       WHERE title_nlsid = 'HOMEPATCH_ADVISORY_ALL_HOSTS_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;  

 l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'HOMEPATCH_ADVISORY_ALL_HOSTS_NLSID',
      p_description_nlsid             => 'HOMEPATCH_ADVISORY_ALL_HOSTS_DESC_NLSID',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'IPMSG_PATCHING',
      p_sub_category_nlsid            => 'IPMSG_OH_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);

     l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
     'oracle.sysman.eml.ip.render.elem.sqlStatement',

'select advisory_name as ADVISORY,
	   impact as IMPACT,
	abstract as ABSTRACT,
	  host_name as AFFECTED_HOST,
	  home_location as HOME_DIRECTORY	   
from  mgmt$patch_advisories');
	 
l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.eml.ecm.patch.util.PatchResourceBundle');
        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        => '',
      p_element_order       => 1,
      p_element_row         => 1,
      p_parameters          => l_param_values,
      p_targets             => null
        );




--Applicable Patches:Summary Report

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

BEGIN   
       SELECT REPORT_GUID
       INTO l_report_guid
       FROM MGMT_IP_REPORT_DEF
       WHERE title_nlsid = 'HOMEPATCH_APPLICABLE_PATCHES_SUMMARY_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;  
 
   l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'HOMEPATCH_APPLICABLE_PATCHES_SUMMARY_NLSID',
      p_description_nlsid             => 'HOMEPATCH_APPLICABLE_PATCHES_SUMMARY_DESC_NLSID',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'IPMSG_PATCHING',
      p_sub_category_nlsid            => 'IPMSG_OH_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);

     l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
     'oracle.sysman.eml.ip.render.elem.sqlStatement',

' select 
	TO_CHAR(patch_id) as PATCH,
	product as PRODUCT,
	patch_release as PATCH_RELEASE,
	platform as PLATFORM,
	advisory as ADVISORY,	
	count (distinct home_location) as HOMES 
from mgmt$appl_patch_and_patchset
where type = ''Patch''
group by patch_id,product,patch_release,platform,advisory');

l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.eml.ecm.patch.util.PatchResourceBundle');

	  

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




-- Applicable Patches :Single Host Report

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

BEGIN   
       SELECT REPORT_GUID
       INTO l_report_guid
       FROM MGMT_IP_REPORT_DEF
       WHERE title_nlsid = 'HOMEPATCH_APPLICABLE_PATCHES_SINGLE_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;  
 
  l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'HOMEPATCH_APPLICABLE_PATCHES_SINGLE_HOST_NLSID',
      p_description_nlsid             => 'HOMEPATCH_APPLICABLE_PATCHES_SINGLE_HOST_DESC_NLSID',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'IPMSG_PATCHING',
      p_sub_category_nlsid            => 'IPMSG_OH_PATCHING',
      p_late_binding_target_types     =>  l_target_types,
      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);

     l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
     'oracle.sysman.eml.ip.render.elem.sqlStatement',

'select 
	TO_CHAR(patch_id) as PATCH,
	product as PRODUCT,
	patch_release PATCH_RELEASE,
	platform as PLATFORM,
	advisory as ADVISORY,	
	host_name as HOST,
	home_location as HOME_DIRECTORY
from mgmt$appl_patch_and_patchset
where type = ''Patch''
and target_guid =  ??EMIP_BIND_TARGET_GUID??');

l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
  'true'); 
l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.eml.ecm.patch.util.PatchResourceBundle');

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


--Applicable Patches: All Hosts Report

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

BEGIN   
       SELECT REPORT_GUID
       INTO l_report_guid
       FROM MGMT_IP_REPORT_DEF
       WHERE title_nlsid = 'HOMEPATCH_APPLICABLE_PATCHES_ALL_HOSTS_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; 
 l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'HOMEPATCH_APPLICABLE_PATCHES_ALL_HOSTS_NLSID',
      p_description_nlsid             => 'HOMEPATCH_APPLICABLE_PATCHES_ALL_HOSTS_DESC_NLSID',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'IPMSG_PATCHING',
      p_sub_category_nlsid            => 'IPMSG_OH_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);

     l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
     'oracle.sysman.eml.ip.render.elem.sqlStatement',

' select 
	TO_CHAR(patch_id) as PATCH,
	product as PRODUCT,
	patch_release as PATCH_RELEASE,
	platform as PLATFORM,
	advisory as ADVISORY,	
	host_name as HOST,
	home_location as HOME_DIRECTORY
from mgmt$appl_patch_and_patchset
where type = ''Patch''');

  l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.eml.ecm.patch.util.PatchResourceBundle');
 
 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        => ' ',
      p_element_order       => 1,
      p_element_row         => 1,
      p_parameters          => l_param_values,
      p_targets             => null
        );



--Applicable Patchsets:Summary Report

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

BEGIN   
       SELECT REPORT_GUID
       INTO l_report_guid
       FROM MGMT_IP_REPORT_DEF
       WHERE title_nlsid = 'HOMEPATCH_APPLICABLE_PATCHSETS_SUMMARY_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; 
l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'HOMEPATCH_APPLICABLE_PATCHSETS_SUMMARY_NLSID',
      p_description_nlsid             => 'HOMEPATCH_APPLICABLE_PATCHSETS_SUMMARY_DESC_NLSID',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'IPMSG_PATCHING',
      p_sub_category_nlsid            => 'IPMSG_OH_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);

     l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
     'oracle.sysman.eml.ip.render.elem.sqlStatement',

'select distinct
	TO_CHAR(patch_id) as PATCHSET,
	product as  PRODUCT,
	patch_release as RELEASE,
	platform as PLATFORM,
	advisory as ADVISORY,
	count(distinct home_location) as HOMES		
from mgmt$appl_patch_and_patchset
where type = ''Patchset''
group by patch_id,product,patch_release,platform,advisory');

l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.eml.ecm.patch.util.PatchResourceBundle');

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



-- Patchset Advisory Reports :Single Host

l_target_types := MGMT_IP_TARGET_TYPES(1);
   l_target_types.extend(1);
   l_target_types(1) := 'host';
BEGIN   
       SELECT REPORT_GUID
       INTO l_report_guid
       FROM MGMT_IP_REPORT_DEF
       WHERE title_nlsid = 'HOMEPATCH_APPLICABLE_PATCHSETS_SINGLE_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; 

l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'HOMEPATCH_APPLICABLE_PATCHSETS_SINGLE_HOST_NLSID',
      p_description_nlsid             => 'HOMEPATCH_APPLICABLE_PATCHSETS_SINGLE_HOST_DESC_NLSID',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'IPMSG_PATCHING',
      p_sub_category_nlsid            => 'IPMSG_OH_PATCHING',
      p_late_binding_target_types     =>  l_target_types,
      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);

     l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
     'oracle.sysman.eml.ip.render.elem.sqlStatement',

'select distinct
	TO_CHAR(patch_id) as PATCHSET,
	product as  PRODUCT,
	patch_release as RELEASE,
	platform as PLATFORM,
	advisory as ADVISORY,
	home_location as HOME_DIRECTORY,
	host_name as HOST	
from mgmt$appl_patch_and_patchset
where type = ''Patchset''
and target_guid =  ??EMIP_BIND_TARGET_GUID??');

l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
  'true'); 
l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.eml.ecm.patch.util.PatchResourceBundle');

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


--Patchset Advisory: All Hosts

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

BEGIN   
       SELECT REPORT_GUID
       INTO l_report_guid
       FROM MGMT_IP_REPORT_DEF
       WHERE title_nlsid = 'HOMEPATCH_APPLICABLE_PATCHSETS_ALL_HOSTS_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; 
l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'HOMEPATCH_APPLICABLE_PATCHSETS_ALL_HOSTS_NLSID',
      p_description_nlsid             => 'HOMEPATCH_APPLICABLE_PATCHSETS_ALL_HOSTS_DESC_NLSID',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'IPMSG_PATCHING',
      p_sub_category_nlsid            => 'IPMSG_OH_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);

     l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
     'oracle.sysman.eml.ip.render.elem.sqlStatement',

' select distinct
	TO_CHAR(patch_id) as PATCHSET,
	product as  PRODUCT,
	patch_release as RELEASE,
	platform as PLATFORM,
	advisory as ADVISORY,
	home_location as HOME_DIRECTORY,
	host_name as HOST	
from mgmt$appl_patch_and_patchset
where type = ''Patchset''');
l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.eml.ecm.patch.util.PatchResourceBundle');
	 
 
 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        => ' ',
      p_element_order       => 1,
      p_element_row         => 1,
      p_parameters          => l_param_values,
      p_targets             => null
        );
	 




--Applied Patches -All Hosts
  
   l_target_types := MGMT_IP_TARGET_TYPES(1);
   l_target_types.extend(1);
   l_target_types(1) := 'host';


 BEGIN   
       SELECT REPORT_GUID
       INTO l_report_guid
       FROM MGMT_IP_REPORT_DEF
       WHERE title_nlsid = 'HOMEPATCH_APPLIED_PATCHES_ALL_HOSTS_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; 

l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'HOMEPATCH_APPLIED_PATCHES_ALL_HOSTS_NLSID',
      p_description_nlsid             => 'HOMEPATCH_APPLIED_PATCHES_ALL_HOSTS_DESC_NLSID',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'IPMSG_PATCHING',
      p_sub_category_nlsid            => 'IPMSG_OH_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);

     l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
     'oracle.sysman.eml.ip.render.elem.sqlStatement',

'SELECT distinct
         patch as PATCH,
	 bugs as BUGS,
	 installation_time as TIMESTAMP,
	 host as HOST,
	 home_location as HOME_DIRECTORY,
	 platform as PLATFORM
from mgmt$applied_patches patch,
     mgmt$em_homes_platform  home
where home.HOME_ID = patch.CONTAINER_GUID');
 
  l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.eml.ecm.patch.util.PatchResourceBundle');
 
 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        => ' ',
      p_element_order       => 1,
      p_element_row         => 1,
      p_parameters          => l_param_values,
      p_targets             => null
        );
	 
  


-- Applied Patches Reports 

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


BEGIN   
       SELECT REPORT_GUID
       INTO l_report_guid
       FROM MGMT_IP_REPORT_DEF
       WHERE title_nlsid = 'HOMEPATCH_APPLIED_PATCHES_SINGLE_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; 


l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'HOMEPATCH_APPLIED_PATCHES_SINGLE_HOST_NLSID',
      p_description_nlsid             => 'HOMEPATCH_APPLIED_PATCHES_SINGLE_HOST_DESC_NLSID',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'IPMSG_PATCHING',
      p_sub_category_nlsid            => 'IPMSG_OH_PATCHING',
      p_late_binding_target_types     =>  l_target_types,
      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);

     l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
     'oracle.sysman.eml.ip.render.elem.sqlStatement',

'SELECT  distinct
	 patch as PATCH,
	 bugs as BUGS,
	 installation_time as TIMESTAMP,
	 host as HOST,
	 home_location as HOME_DIRECTORY,
	 platform as PLATFORM
from mgmt$applied_patches patch,
     mgmt$em_homes_platform  home
where home.HOME_ID = patch.CONTAINER_GUID and
      target_guid =  ??EMIP_BIND_TARGET_GUID??');

l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
  'true'); 
l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.eml.ecm.patch.util.PatchResourceBundle');
	 
 
 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        => ' ',
      p_element_order       => 1,
      p_element_row         => 1,
      p_parameters          => l_param_values,
      p_targets             => null
        );


--Applied Patchsets:All Hosts Report

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


   BEGIN   
       SELECT REPORT_GUID
       INTO l_report_guid
       FROM MGMT_IP_REPORT_DEF
       WHERE title_nlsid = 'HOMEPATCH_APPLIED_PATCHSETS_ALL_HOSTS_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; 

l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'HOMEPATCH_APPLIED_PATCHSETS_ALL_HOSTS_NLSID',
      p_description_nlsid             => 'HOMEPATCH_APPLIED_PATCHSETS_ALL_HOSTS_DESC_NLSID',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'IPMSG_PATCHING',
      p_sub_category_nlsid            => 'IPMSG_OH_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);

     l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
     'oracle.sysman.eml.ip.render.elem.sqlStatement',

'SELECT name as PATCHSET,
	version as VERSION,
	timestamp as TIMESTAMP,
	host as HOST,
	home_location as HOME_DIRECTORY,
	platform as PLATFORM
from mgmt$applied_patchsets patch,
	 mgmt$em_homes_platform home
where home.home_id = patch.container_guid');

  l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.eml.ecm.patch.util.PatchResourceBundle');

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



-- Applied Patchsets Reports 

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

BEGIN   
       SELECT REPORT_GUID
       INTO l_report_guid
       FROM MGMT_IP_REPORT_DEF
       WHERE title_nlsid = 'HOMEPATCH_APPLIED_PATCHSETS_SINGLE_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; 


l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'HOMEPATCH_APPLIED_PATCHSETS_SINGLE_HOST_NLSID',
      p_description_nlsid             => 'HOMEPATCH_APPLIED_PATCHSETS_SINGLE_HOST_DESC_NLSID',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'IPMSG_PATCHING',
      p_sub_category_nlsid            => 'IPMSG_OH_PATCHING',
      p_late_binding_target_types     =>  l_target_types,
      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);

     l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
     'oracle.sysman.eml.ip.render.elem.sqlStatement',

'SELECT patch.name as PATCHSET,
	version as VERSION,
	timestamp as TIMESTAMP,
	host as HOST,
	home_location as HOME_DIRECTORY,
	platform as PLATFORM
from mgmt$applied_patchsets patch,
	 mgmt$em_homes_platform home
where home.home_id = patch.container_guid and
      target_guid =  ??EMIP_BIND_TARGET_GUID??');

l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
  'true'); 

l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.eml.ecm.patch.util.PatchResourceBundle');
  
 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        => '',
      p_element_order       => 1,
      p_element_row         => 1,
      p_parameters          => l_param_values,
      p_targets             => null
      );




--Affected Homes:All Hosts Report

   l_target_types := MGMT_IP_TARGET_TYPES(1);
   l_target_types.extend(1);
   l_target_types(1) := 'host';
BEGIN   
       SELECT REPORT_GUID
       INTO l_report_guid
       FROM MGMT_IP_REPORT_DEF
       WHERE title_nlsid = 'HOMEPATCH_AFFECTED_HOMES_ALL_HOSTS_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; 
l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'HOMEPATCH_AFFECTED_HOMES_ALL_HOSTS_NLSID',
      p_description_nlsid             => 'HOMEPATCH_AFFECTED_HOMES_ALL_HOSTS_DESC_NLSID',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'IPMSG_PATCHING',
      p_sub_category_nlsid            => 'IPMSG_OH_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);

     l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
     'oracle.sysman.eml.ip.render.elem.sqlStatement',
'select  host as HOST,
	 home_directory as HOME_DIRECTORY, 
	 alerts as ALERTS  
from mgmt$homes_affected');
	 
  l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.eml.ecm.patch.util.PatchResourceBundle');
 
     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        => ' ',
      p_element_order       => 1,
      p_element_row         => 1,
      p_parameters          => l_param_values,
      p_targets             => null
        );
 

 -- Affected Homes:Single Host

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


BEGIN   
       SELECT REPORT_GUID
       INTO l_report_guid
       FROM MGMT_IP_REPORT_DEF
       WHERE title_nlsid = 'HOMEPATCH_AFFECTED_HOMES_SINGLE_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; 

l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'HOMEPATCH_AFFECTED_HOMES_SINGLE_HOST_NLSID',
      p_description_nlsid             => 'HOMEPATCH_AFFECTED_HOMES_SINGLE_HOST_DESC_NLSID',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'IPMSG_PATCHING',
      p_sub_category_nlsid            => 'IPMSG_OH_PATCHING',
      p_late_binding_target_types     =>  l_target_types,
      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);

   l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
     'oracle.sysman.eml.ip.render.elem.sqlStatement',
'select host as HOST,
	home_directory as HOME_DIRECTORY, 
	alerts as ALERTS 
from 	mgmt$homes_affected
where 	target_guid =  ??EMIP_BIND_TARGET_GUID??');


l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
  'true'); 
l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
  'oracle.sysman.eml.ecm.patch.util.PatchResourceBundle');

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