Rem DO NOT PUT HEADER HERE BECAUSE THIS IS REFERENCED BY Rem ip_data_upgrade.sql ALREADY. Rem Rem $Header: licensing_outofbox_reports.sql 09-dec-2005.02:26:14 vmotamar Exp $ Rem Rem licensing_outofbox_reports.sql Rem Rem Copyright (c) 2005, Oracle. All rights reserved. Rem Rem NAME Rem licensing_outofbox_reports.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem vmotamar 08/10/05 - Bug 4544907 Rem vmotamar 12/09/05 - Backport vmotamar_bug-4544907 from main Rem andyao 12/07/05 - Backport andyao_bug-4739983 from main Rem andyao 11/15/05 - fix SLM licensing report Rem andyao 09/23/05 - fixes service level management pack access report Rem yxie 09/22/05 - add service level management pack access report Rem vmotamar 08/29/05 - Bug No 4579123 Rem paachary 07/31/05 - Adding the header Rem paachary 07/18/05 - paachary_bug-4492521 Rem paachary 07/15/05 - Created Rem SET ECHO ON SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 DECLARE l_target_types MGMT_IP_TARGET_TYPES; l_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_count NUMBER; --------------------LICENSABLE TARGETS WITH PACK ACCESS AGREED REPORT BEGIN-------------------------------------------------- BEGIN BEGIN MGMT_IP.delete_report_by_title('SYSMAN', 'IPMSG_LICENSING_TITLE', 0); EXCEPTION WHEN OTHERS THEN NULL; END; SELECT COUNT(*) INTO l_count FROM MGMT_IP_REPORT_DEF WHERE TITLE_NLSID ='Management Pack Access' AND DESCRIPTION_NLSID = 'Licensable targets with management pack access'; IF (l_count > 0 ) THEN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE TITLE_NLSID ='Management Pack Access' AND description_nlsid = 'Licensable targets with management pack access'; DELETE MGMT_IP_REPORT_ELEM_PARAMS WHERE REPORT_GUID = l_report_guid; DELETE MGMT_IP_REPORT_DEF_ELEMENTS WHERE REPORT_GUID = l_report_guid; DELETE MGMT_IP_REPORT_ELEM_TARGETS WHERE REPORT_GUID = l_report_guid; DELETE MGMT_IP_REPORT_DEF WHERE REPORT_GUID = l_report_guid; END IF; l_target_types := MGMT_IP_TARGET_TYPES(); l_target_types.extend(1); l_target_types(1) := 'any_type'; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'IPMSG_LICENSING_TITLE', p_description_nlsid => 'IPMSG_LICENSING_REPORT_DESC', p_owner => mgmt_user.GET_REPOSITORY_OWNER, p_category_nlsid => 'IPMSG_LICENSING_CATEGORY', p_sub_category_nlsid => 'IPMSG_LICENSING_SUBCATEGORY', 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 ); --Instruction text element l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'LICENSABLE_TGTS_PACK_ACCESS_AGREED_DESC'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle', 'OraInstructionText'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.eml.license.LicenseMsg'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_STYLED_TEXT', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => null, p_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null ); -- table 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.licensing.LicensingNoOfRows', '10'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.licensing.LicensingReportType', '1'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_LICENSING', p_element_type_nlsid => 'IPMSG_LICENSING_TARGET_TYPE', p_header_nlslid => 'IPMSG_LICENSING_HEADER_AGREED', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); --Separator element l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_SEPARATOR', p_element_type_nlsid => 'IPMSG_NO_TARGET_TYPE', p_header_nlsid => null, p_element_order => 3, p_element_row => 3, p_parameters => l_param_values, p_targets => null ); --Instruction text element l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'LICENSABLE_TGTS_PACK_ACCESS_NOT_AGREED_DESC'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle', 'OraInstructionText'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.eml.license.LicenseMsg'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_STYLED_TEXT', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => null, p_element_order => 4, p_element_row => 4, p_parameters => l_param_values, p_targets => null ); -- table 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.licensing.LicensingNoOfRows', '10'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.licensing.LicensingReportType', '2'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_LICENSING', p_element_type_nlsid => 'IPMSG_LICENSING_TARGET_TYPE', p_header_nlslid => 'IPMSG_LICENSING_HEADER_NT_AGREED', p_element_order => 5, p_element_row => 5, p_parameters => l_param_values, p_targets => null ); --------------------LICENSABLE TARGETS WITH PACK ACCESS AGREED REPORT END-------------------------------------------------- --------------------SERVICE LEVEL MANAGEMENT PACK ACCESS REPORT BEGIN------------------------------------------------------ BEGIN MGMT_IP.delete_report_by_title('SYSMAN', 'IPMSG_SERV_LICENSE_REP', 0); DELETE FROM MGMT_IP_SQL_STATEMENTS WHERE name = 'Service License Tests Summary Table SQL'; DELETE FROM MGMT_IP_SQL_STATEMENTS WHERE name = 'Service License Tests Beacons Summary Table SQL'; DELETE FROM MGMT_IP_SQL_STATEMENTS WHERE name = 'Service License Tests Summary Table by Test Type SQL'; EXCEPTION WHEN OTHERS THEN NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'IPMSG_SERV_LICENSE_REP', p_description_nlsid => 'IPMSG_SERV_LICENSE_REP_DESC', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_LICENSING_CATEGORY', p_sub_category_nlsid => 'IPMSG_LICENSING_SUBCATEGORY'); -- Instruction Text l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(4); l_param_values(1) := new mgmt_ip_param_value_record('oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage','SVC_LICENSE_REP_MSG'); l_param_values(2) := new mgmt_ip_param_value_record('oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle','OraInstructionText'); l_param_values(3) := new mgmt_ip_param_value_record('oracle.sysman.eml.ip.render.elem.TextParamBean.textDestination',''); l_param_values(4) := new mgmt_ip_param_value_record('oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.ip.emr.webapps.IPWebappsResource'); l_element_guid := MGMT_IP.add_element_to_report_def( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_STYLED_TEXT', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => '', p_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null ); mgmt_ip.add_element_definition( p_element_name_nlsid => 'IPMSG_TABLE_WITH_TEST_TYPE', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_description_nlsid => 'IPMSG_USER_TABLE_FROM_SQL_DESC', p_element_class_name => 'oracle.sysman.eml.gensvc.test.ctrl.TestTableRenderController', p_internal_use_only => 1, p_param_classes => null, p_default_parameters => null, p_pack_name => null ); mgmt_ip.add_element_definition( p_element_name_nlsid => 'IPMSG_TABLE_WITH_BEACONS', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_description_nlsid => 'IPMSG_USER_TABLE_FROM_SQL_DESC', p_element_class_name => 'oracle.sysman.eml.gensvc.test.ctrl.SLMBeaconsRenderController', p_internal_use_only => 1, p_param_classes => null, p_default_parameters => null, p_pack_name => null ); -- Explanation Text l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(4); l_param_values(1) := new mgmt_ip_param_value_record('oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage','SVC_LICENSE_REP_EXP_MSG2'); l_param_values(2) := new mgmt_ip_param_value_record('oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle','OraTipText'); l_param_values(3) := new mgmt_ip_param_value_record('oracle.sysman.eml.ip.render.elem.TextParamBean.textDestination',''); l_param_values(4) := new mgmt_ip_param_value_record('oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.ip.emr.webapps.IPWebappsResource'); l_element_guid := MGMT_IP.add_element_to_report_def( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_STYLED_TEXT', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => '', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); -- Summary of Tests and Beacons l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); mgmt_ip.register_sql_statement ( p_name =>'Service License Tests Beacons Summary Table SQL', p_sql_statement => 'begin GENSVC_VIEW.GET_SLM_PACK_REPORT_SUMMARY( ??EMIP_BIND_RESULTS_CURSOR??); end;'); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.nameValueDisplay', '1'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.NamedSqlStatement', 'Service License Tests Beacons Summary Table SQL'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '1'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.ip.emr.webapps.IPWebappsResource'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.annotationMessageMsgid', 'SVC_LICENSE_REP_EXP_MSG1'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.annotationMessageStyle', 'OraTipText'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'IPMSG_SERV_LICENSE_TST_BCN_TXT', p_element_order => 3, p_element_row => 3, p_parameters => l_param_values, p_targets => null ); -- Beacons Summary 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 target_name AS NAME, host_name as HOST_NAME, load_timestamp AS ACTIVATION_TIME FROM mgmt_targets WHERE target_type = ''oracle_beacon''' ); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'ascending'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.ip.emr.webapps.IPWebappsResource'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_TABLE_WITH_BEACONS', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'IPMSG_SERV_LICENSE_BCN_TXT', p_element_order => 4, p_element_row => 4, p_parameters => l_param_values, p_targets => null ); -- Tests Summary Table l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); mgmt_ip.register_sql_statement ( p_name =>'Service License Tests Summary Table by Test Type SQL', p_sql_statement => 'begin GENSVC_VIEW.GET_SLM_PACK_TEST_TYPE_LIST( ??EMIP_BIND_RESULTS_CURSOR??); end;'); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.NamedSqlStatement', 'Service License Tests Summary Table by Test Type SQL'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'ascending'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.ip.emr.webapps.IPWebappsResource'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.gensvc.test.data.ui.TestTableRenderBean.testTypeColumns', 'TYPE'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_TABLE_WITH_TEST_TYPE', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'IPMSG_SERV_LICENSE_TEST_TYPE_TXT', p_element_order => 5, p_element_row => 5, p_parameters => l_param_values, p_targets => null ); l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(8); mgmt_ip.register_sql_statement ( p_name =>'Service License Tests Summary Table SQL', p_sql_statement => 'begin GENSVC_VIEW.GET_SLM_PACK_TGT_TEST_LIST( ??EMIP_BIND_RESULTS_CURSOR??); end;'); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.NamedSqlStatement', 'Service License Tests Summary Table SQL'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'ascending'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.ip.emr.webapps.IPWebappsResource'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.targetTypeColumns', 'TARGET_TYPE'); l_param_values(8) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.gensvc.test.data.ui.TestTableRenderBean.testTypeColumns', 'TYPE'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_TABLE_WITH_TEST_TYPE', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'IPMSG_SERV_LICENSE_TEST_TXT', p_element_order => 6, p_element_row => 6, p_parameters => l_param_values, p_targets => null ); COMMIT; --------------------SERVICE LEVEL MANAGEMENT PACK ACCESS REPORT END-------------------------------------------------------- END; /