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 Rem Rem MODIFIED (MM/DD/YY) Rem pdasika 08/22/05 - removing the extra entry in l_target_types for late binding of targets Rem gsbhatia 07/03/05 - New repmgr header impl Rem pdasika 06/27/05 - Rem lgloyd 06/21/05 - consolidate category/subcategory strings Rem pdasika 06/20/05 - Adding Timeperiod Selector and help parameter 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(1) := 'host'; -- 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 = '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; 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; 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; 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; 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; -- End of deletion. Start of creation -- Applicable Patches :Single Host Report l_target_types := MGMT_IP_TARGET_TYPES(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_DEPLOYMENT_AND_CONFIGURATION_CATEGORY', 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, p_help_topic_id => 'info_pub_gc_cs_homepatch_applicable_patches' ); 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_APPLICABLE_PATCHES_SINGLE_HOST'); --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.eml.ecm.patch.util.PatchResourceBundle'); --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(5); 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, 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'); --sort the table in desc order l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); --number of rows is 25 l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); 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 => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); --Applicable Patches: All Hosts Report l_target_types := MGMT_IP_TARGET_TYPES(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_DEPLOYMENT_AND_CONFIGURATION_CATEGORY', 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, p_help_topic_id => 'info_pub_gc_cs_homepatch_applicable_patches' ); 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_APPLICABLE_PATCHES_ALL_HOSTS'); --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.eml.ecm.patch.util.PatchResourceBundle'); --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(4); 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'); --sort the table in desc order l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); --number of rows is 25 l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); 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 => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); -- Patchset Advisory Reports :Single Host l_target_types := MGMT_IP_TARGET_TYPES(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_DEPLOYMENT_AND_CONFIGURATION_CATEGORY', 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, p_help_topic_id => 'info_pub_gc_cs_homepatch_applicable_patchsets' ); 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_APPLICABLE_PATCHSETS_SINGLE_HOST'); --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.eml.ecm.patch.util.PatchResourceBundle'); --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(5); 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 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'); --sort the table in desc order l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); --number of rows is 25 l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); 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 => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); --Patchset Advisory: All Hosts l_target_types := MGMT_IP_TARGET_TYPES(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_DEPLOYMENT_AND_CONFIGURATION_CATEGORY', 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, p_help_topic_id => 'info_pub_gc_cs_homepatch_applicable_patchsets' ); 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_APPLICABLE_PATCHSETS_ALL_HOSTS'); --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.eml.ecm.patch.util.PatchResourceBundle'); --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(4); 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'); --sort the table in desc order l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); --number of rows is 25 l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); 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 => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); --Applied Patches -All Hosts l_target_types := MGMT_IP_TARGET_TYPES(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_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:2'); 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_DEPLOYMENT_AND_CONFIGURATION_CATEGORY', 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, p_parameters => l_param_values, p_help_topic_id => 'info_pub_gc_cs_homepatch_applied_patches' ); 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_APPLIED_PATCHES_ALL_HOSTS'); --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.eml.ecm.patch.util.PatchResourceBundle'); --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(4); 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, mgmt$target tgt where home.HOME_ID = patch.CONTAINER_GUID and patch.target_guid = tgt.target_guid and patch.installation_time>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION) and patch.installation_time<= MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.eml.ecm.patch.util.PatchResourceBundle'); --sort the table in desc order l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); --number of rows is 25 l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); 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 => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); -- Applied Patches Reports l_target_types := MGMT_IP_TARGET_TYPES(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_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:2'); 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_DEPLOYMENT_AND_CONFIGURATION_CATEGORY', 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, p_parameters => l_param_values, p_help_topic_id => 'info_pub_gc_cs_homepatch_applied_patches' ); 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_APPLIED_PATCHES_SINGLE_HOST'); --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.eml.ecm.patch.util.PatchResourceBundle'); --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(5); 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, home_location as HOME_DIRECTORY, platform as PLATFORM from mgmt$applied_patches patch, mgmt$em_homes_platform home, mgmt$target tgt where home.HOME_ID = patch.CONTAINER_GUID and patch.target_guid = ??EMIP_BIND_TARGET_GUID?? and tgt.target_guid = patch.target_guid and patch.installation_time>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION) and patch.installation_time<= MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)'); 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'); --sort the table in desc order l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); --number of rows is 25 l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); 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 => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); --Applied Patchsets:All Hosts Report l_target_types := MGMT_IP_TARGET_TYPES(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_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:2'); 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_DEPLOYMENT_AND_CONFIGURATION_CATEGORY', 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, p_parameters => l_param_values, p_help_topic_id => 'info_pub_gc_cs_homepatch_applied_patchsets' ); 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_APPLIED_PATCHSETS_ALL_HOSTS'); --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.eml.ecm.patch.util.PatchResourceBundle'); --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(4); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'SELECT DISTINCT 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, mgmt$target tgt where home.home_id = patch.container_guid and patch.target_guid = tgt.target_guid and patch.timestamp>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION) and patch.timestamp<= MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.eml.ecm.patch.util.PatchResourceBundle'); --sort the table in desc order l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); --number of rows is 25 l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); 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 => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); -- Applied Patchsets Reports l_target_types := MGMT_IP_TARGET_TYPES(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_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:2'); 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_DEPLOYMENT_AND_CONFIGURATION_CATEGORY', 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, p_parameters => l_param_values, p_help_topic_id => 'info_pub_gc_cs_homepatch_applied_patchsets' ); 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_APPLIED_PATCHSETS_SINGLE_HOST'); --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.eml.ecm.patch.util.PatchResourceBundle'); --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(5); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'SELECT DISTINCT patch.name as PATCHSET, version as VERSION, timestamp as TIMESTAMP, home_location as HOME_DIRECTORY, platform as PLATFORM from mgmt$applied_patchsets patch, mgmt$em_homes_platform home, mgmt$target tgt where home.home_id = patch.container_guid and patch.timestamp>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION) and patch.timestamp<= MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION) and tgt.target_guid = patch.target_guid and patch.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'); --sort the table in desc order l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); --number of rows is 25 l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); 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 => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); --Affected Homes:All Hosts Report l_target_types := MGMT_IP_TARGET_TYPES(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_DEPLOYMENT_AND_CONFIGURATION_CATEGORY', 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, p_help_topic_id => 'info_pub_gc_cs_homepatch_affected_homes' ); 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_AFFECTED_HOMES_ALL_HOSTS'); --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.eml.ecm.patch.util.PatchResourceBundle'); --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(4); 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'); --sort the table in desc order l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); --number of rows is 25 l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); 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 => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); -- Affected Homes:Single Host l_target_types := MGMT_IP_TARGET_TYPES(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_DEPLOYMENT_AND_CONFIGURATION_CATEGORY', 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, p_help_topic_id => 'info_pub_gc_cs_homepatch_affected_homes' ); 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_AFFECTED_HOMES_SINGLE_HOST'); --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.eml.ecm.patch.util.PatchResourceBundle'); --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(5); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'select 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'); --sort the table in desc order l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); --number of rows is 25 l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); 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 => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); end; / commit;