Rem PLEASE DO NOT PUT ANY HEADER Rem Rem $Header: licensing_outofbox_reports.sql 29-aug-2006.11:51:48 yxie Exp $ Rem Rem licensing_outofbox_reports.sql Rem Rem Copyright (c) 2006, 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 yxie 08/29/06 - Backport yxie_bug-5101411 from main Rem yxie 08/22/06 - fix bug 5101411 Rem paachary 04/18/06 - Created Rem DECLARE l_report_guid MGMT_IP_REPORT_DEF.REPORT_GUID%TYPE := NULL; l_count NUMBER := 0; l_param_values MGMT_IP_PARAM_VALUE_LIST; l_element_guid RAW(16); BEGIN BEGIN SELECT report_guid INTO l_report_guid FROM mgmt_ip_report_def WHERE title_nlsid = 'IPMSG_LICENSING_TITLE' AND description_nlsid = 'IPMSG_LICENSING_REPORT_DESC'; SELECT COUNT(*) INTO l_count FROM mgmt_ip_report_def_elements WHERE report_guid = l_report_guid AND header_nlsid = 'Licensable Targets With Pack Access Agreed'; IF l_count > 0 THEN UPDATE mgmt_ip_report_def_elements SET header_nlsid ='IPMSG_LICENSING_HEADER_AGREED' WHERE report_guid = l_report_guid AND header_nlsid ='Licensable Targets With Pack Access Agreed'; END IF; SELECT COUNT(*) INTO l_count FROM mgmt_ip_report_def_elements WHERE report_guid = l_report_guid AND header_nlsid = 'Licensable Targets With Pack Access Not Agreed'; IF l_count > 0 THEN UPDATE mgmt_ip_report_def_elements SET header_nlsid = 'IPMSG_LICENSING_HEADER_NT_AGREED' WHERE report_guid = l_report_guid AND header_nlsid ='Licensable Targets With Pack Access Not Agreed'; END IF; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; --------------------SERVICE LEVEL MANAGEMENT PACK ACCESS REPORT BEGIN------------------------------------------------------ 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; END; --------------------SERVICE LEVEL MANAGEMENT PACK ACCESS REPORT END-------------------------------------------------------- END; /