Cisco Transport Manager Server Database Schema, 9.0 - Chapter 2: Database Schema Views [Cisco Transport Manager]

Database Schema Views

2.1 Common PM Views

2.1.1 NE_Module_Level_Debug_View

2.1.2 SDH_HO_FarEnd_PM_View

2.1.3 SDH_HO_NearEnd_PM_View

2.1.4 SDH_MS_FarEnd_PM_View

2.1.5 SDH_MS_NearEnd_PM_View

2.1.6 SDH_RS_NearEnd_PM_View

2.1.7 SONET_ENET_PM_View

2.1.8 SONET_Line_FarEnd_PM_View

2.1.9 SONET_Line_NearEnd_PM_View

2.1.10 SONET_Physical_PM_View

2.1.11 SONET_POS_PM_View

2.1.12 SONET_Section_NearEnd_PM_View

2.1.13 SONET_STSPath_FarEnd_PM_View

2.1.14 SONET_STSPath_NearEnd_PM_View

2.2 CTM Base Views

2.2.1 Active_Alarm_View_With_Ack

2.2.2 Admin_Job_View

2.2.3 Alarm_Count_View (Deprecated)

2.2.4 Alarm_Log_View

2.2.5 Audit_Log_View

2.2.6 BLSR_Table_View

2.2.7 Children_View

2.2.8 Circuit_Ctpspan_View

2.2.9 Circuit_Ctp_Drop_View

2.2.10 Circuit_Ctp_Sec_View

2.2.11 Circuit_Ctp_Src_View

2.2.12 Circuit_Ctp_View

2.2.13 Circuit_Ctp_XC_View

2.2.14 Circuit_Node_GUI_View

2.2.15 Circuit_Node_GUI_View_CTM46

2.2.16 Circuit_Node_View

2.2.17 Circuit_Span_View

2.2.18 Circuit_Span_View_CTM46

2.2.19 Circuit_View

2.2.20 Circuit_VLAN_View

2.2.21 Ckt_Span_Dest_Mt_View

2.2.22 Ckt_Span_Src_Mt_View

2.2.23 Client_User_View

2.2.24 CTM_Unknown_User_View

2.2.25 Data_Card_View

2.2.26 Domain_NE_Inventory_View

2.2.27 DSP_Circuit_View

2.2.28 DSP_Eqpt_View

2.2.29 GWCORBA_PM_Mapping_View

2.2.30 INTF_Sub_Intf_View

2.2.31 Inventory_Table_View

2.2.32 IOS_Active_Cards

2.2.33 IOS_Users_View

2.2.34 Link_Utilization_View

2.2.35 Mcard_L2topology_Map_View

2.2.36 MLBCC_View

2.2.37 MLL_Inv_View

2.2.38 NE_Alarm_View

2.2.39 NE_Circuit_NodeID_View

2.2.40 NE_Circuit_View

2.2.41 NE_Circuit_Vlan_View

2.2.42 NE_Info_NP_View

2.2.43 NE_Inventory_View

2.2.44 NE_SNMPV3_FILTERS_VIEW

2.2.45 NE_SNMPV3_GROUPS_VIEW

2.2.46 NE_SNMPV3_LOCAL_USER_VIEW

2.2.47 NE_SNMPV3_PROXY_FWD_VIEW

2.2.48 NE_SNMPV3_PROXY_TRAP_FWD_VIEW

2.2.49 NE_SNMPV3_REMOTE_USER_VIEW

2.2.50 NE_SNMPV3_TRAP_DEST_VIEW

2.2.51 NE_SNMPV3_VIEWS_VIEW

2.2.52 NE_Swimage_View

2.2.53 NGNE_Info_View

2.2.54 OSS_Alarm_Fltr_AidDeny_View

2.2.55 OSS_Alarm_Fltr_TidDeny_VIEW

2.2.56 OSS_Corba_User_View

2.2.57 OSS_User_View

2.2.58 POS_Ports_View

2.2.59 QoSTemplate_General_View

2.2.60 QoS_View

2.2.61 Roll_View

2.2.62 Self_Monitoring_View

2.2.63 Service_Availability_Log_View

2.2.64 SPR_View

2.2.65 SW_Active_Alarm_View

2.2.66 SW_User_Port_View

2.2.67 TL1_Active_Alarm_View

2.2.68 TL1_NE_View

2.2.69 Topology_Circuit_View

2.2.70 Topology_Circuit_View_CTM5_0

2.2.71 Topology_GUI_View

2.2.72 Trace_Vlan_View

2.2.73 Unmanaged_NE_Inventory_View

2.2.74 User_NE_View

2.2.75 User_View

2.2.76 Vlan_Drop_Ports_QoS_View

2.2.77 Vlan_Drop_Ports_View

2.2.78 Vlan_Service_View

2.3 Cisco MGX Media Gateway Views

2.3.5 MGX8880_Inventory_View

2.4 Cisco ONS 152xx Views

2.5 Cisco ONS 15216 Views

2.5.1 ONS15216_Active_User_View

2.5.2 ONS15216_Inventory_View

2.5.3 ONS15216_User_View

2.6 Cisco ONS 153xx Views

2.6.1 ONS1530x_Dcc_PM_View

2.6.2 ONS1530x_HO_FE_PM_View

2.6.3 ONS1530x_HO_PM_View

2.6.4 ONS1530x_Inventory_View

2.6.5 ONS1530x_Lan_PM_View

2.6.6 ONS1530x_LO_FE_PM_View

2.6.7 ONS1530x_LO_PM_View

2.6.8 ONS1530x_MS_FE_PM_View

2.6.9 ONS1530x_MS_PM_View

2.6.10 ONS1530x_RS_PM_View

2.6.11 ONS1530x_Wan_PM_View

2.7 Cisco ONS 15454 Views

2.7.1 APC_DOMAIN_NE_VIEW

2.7.2 Ctc_User_Profiles_View

2.7.3 L2_IPSLA_View

2.7.4 L2_Topology_Cards_View

2.7.5 NE_Audit_Trail_View

2.7.6 ONS15454SDH_E1_PM_FE_View

2.7.7 ONS15454SDH_E1_PM_View

2.7.8 ONS15454SDH_E3_PM_View

2.7.9 ONS15454SDH_E4_PM_View

2.7.10 ONS15454SDH_HO_PM_FE_View

2.7.11 ONS15454SDH_HO_PM_View

2.7.12 ONS15454SDH_LO_VC11_PM_FE_View

2.7.13 ONS15454SDH_LO_VC11_PM_View

2.7.14 ONS15454SDH_LO_VC12_PM_FE_View

2.7.15 ONS15454SDH_LO_VC12_PM_View

2.7.16 ONS15454SDH_LO_VC3_PM_FE_View

2.7.17 ONS15454SDH_LO_VC3_PM_View

2.7.18 ONS15454SDH_MS_PM_FE_View

2.7.19 ONS15454SDH_MS_PM_View

2.7.20 ONS15454SDH_RS_PM_View

2.7.21 APC_Domain_View

2.7.22 ONS15454_8B10B_PM_View

2.7.23 ONS15454_Active_User_View

2.7.24 ONS15454_APC_Result_View

2.7.25 ONS15454_BLSR_Config_View

2.7.26 ONS15454_BLSR_Squelch_View

2.7.27 ONS15454_CMS_Storage_View

2.7.28 ONS15454_COS_PM_View

2.7.29 ONS15454_DS1_PM_FE_View

2.7.30 ONS15454_DS1_PM_View

2.7.31 ONS15454_DS3_PM_FE_View

2.7.32 ONS15454_DS3_PM_View

2.7.33 ONS15454_DWDM_Ring_View

2.7.34 ONS15454_Enet_PM_View

2.7.35 ONS15454_ESCON_PM_View

2.7.36 ONS15454_FC_PM_View

2.7.37 ONS15454_GFP_PM_View

2.7.38 ONS15454_Inventory_View

2.7.39 ONS15454_IPSLA_PM_View

2.7.40 ONS15454_ISC_PM_View

2.7.41 ONS15454_OPTICS_PM_VIEW

2.7.42 ONS15454_OSPF_Area_Range_View

2.7.43 ONS15454_OTN_Fec_PM_View

2.7.44 ONS15454_OTN_Path_PM_FE_View

2.7.45 ONS15454_OTN_Path_PM_View

2.7.46 ONS15454_OTN_Sec_PM_FE_View

2.7.47 ONS15454_OTN_Sec_PM_View

2.7.48 ONS15454_Phy_Layer_PM_View

2.7.49 ONS15454_POS_PM_View

2.7.50 ONS15454_Ring_Map_View

2.7.51 ONS15454_RPR_Client_In_PM_View

2.7.52 ONS15454_RPR_ClientOut_PM_View

2.7.53 ONS15454_RPR_Error_PM_View

2.7.54 ONS15454_RPR_PM_View

2.7.55 ONS15454_RPR_Span_In_PM_View

2.7.56 ONS15454_RPR_Span_Out_PM_View

2.7.57 ONS15454_Security_View

2.7.58 ONS15454_SNMP_Trap_Dest_View

2.7.59 ONS15454_SONET_Line_PM_FE_View

2.7.60 ONS15454_SONET_Line_PM_View

2.7.61 ONS15454_SONET_Path_PM_FE_View

2.7.62 ONS15454_SONET_Path_PM_View

2.7.63 ONS15454_SONET_Section_PM_View

2.7.64 ONS15454_SONET_VT2_PM_FE_View

2.7.65 ONS15454_SONET_VT2_PM_View

2.7.66 ONS15454_SONET_VT_PM_FE_View

2.7.67 ONS15454_SONET_VT_PM_View

2.7.68 ONS15454_Spanning_Trees_View

2.7.69 ONS15454_Static_Route_View

2.7.70 ONS15454_Threshold_View

2.7.71 ONS15454_User_View

2.7.72 ONS15454_Virtual_Link_View

2.7.73 PM_Param_ColNo_View

2.7.74 PM_Metadata_Attrid_View

2.7.75 PM_Metadata_Iinfo_View

2.7.76 PM_Metadata_View

2.8 Cisco ONS 155xx Views

2.8.1 ONS15501_Opt_Power_PM_View

2.8.2 ONS155xx_CDL_Flow_Config_View

2.8.3 ONS155xx_CDL_PM_View

2.8.4 ONS155xx_Circuit_CC_View

2.8.5 ONS155xx_Circuit_Dest_View

2.8.6 ONS155xx_Circuit_Final_View

2.8.7 ONS155xx_Circuit_Path_View

2.8.8 ONS155xx_Circuit_Segment_View

2.8.9 ONS155xx_Circuit_Span_View

2.8.10 ONS155xx_Circuit_SrcDest_View

2.8.11 ONS155xx_Circuit_Stack_View

2.8.12 ONS155xx_Circuit_View

2.8.13 ONS155xx_CKT_CC_Dest_MT_View

2.8.14 ONS155xx_CKT_CC_Detailed_View

2.8.15 ONS155xx_CKT_CC_Link_View

2.8.16 ONS155xx_CKT_CC_Path_View

2.8.17 ONS155XX_CKT_CC_Src_Dest_View

2.8.18 ONS155xx_CKT_CC_Src_MT_View

2.8.19 ONS155xx_CKT_Endpoint_View

2.8.20 ONS155XX_CKT_Link_Final_View

2.8.21 ONS155xx_CKT_Link_View

2.8.22 ONS155XX_CKT_NE_Final_View

2.8.23 ONS155xx_CKT_OPT_Pwr_View

2.8.24 ONS155xx_CKT_Path_Final_View

2.8.25 ONS155xx_CKT_Path_Join_View

2.8.26 ONS155xx_CKT_Path_Stack_View

2.8.27 ONS155xx_CKT_Segment_Path_View

2.8.28 ONS155xx_CKT_SEG_Detailed_View

2.8.29 ONS155xx_CKT_Span_Dest_MT_View

2.8.30 ONS155xx_CKT_Span_Det_View

2.8.31 ONS155xx_CKT_Span_Join_View

2.8.32 ONS155xx_CKT_Span_MT_View

2.8.33 ONS155xx_CKT_Span_Src_MT_View

2.8.34 ONS155xx_CKT_Span_View

2.8.35 ONS155xx_CKT_Srcdest_MT_View

2.8.36 ONS155xx_CKT_SRC_CDL_View

2.8.37 ONS155XX_CKT_Stack_Final_View

2.8.38 ONS155xx_Discovery_Info_View

2.8.39 ONS155xx_Dcrp_View

2.8.40 ONS155xx_Ether_Hist_PM_View

2.8.41 ONS155xx_FCM_PE_PM_View

2.8.42 Ons155xx_Flash_Part_View

2.8.43 ONS155xx_Inventory_View

2.8.44 ONS155XX_Link_Correlator

2.8.45 ONS155xx_Module_Type_View

2.8.46 ONS155xx_OPT_Power_PM_View

2.8.47 ONS155XX_Path_CKT_Final_View

2.8.48 ONS155xx_Path_Dest_Pwr_View

2.8.49 ONS155XX_Path_Link_Final_View

2.8.50 ONS155xx_Path_Link_View

2.8.51 ONS155xx_Path_Pwr_Monitor_View

2.8.52 ONS155xx_Path_Pwr_No_Ava_View

2.8.53 ONS155xx_Path_Pwr_No_Mon_View

2.8.54 ONS155xx_Path_Stack_Final_View

2.8.55 ONS155xx_Phy_PM_View

2.8.56 ONS155xx_SONET_Section_PM_View

2.8.57 ONS155xx_Tca_PM_View

2.9 Cisco ONS 156xx Views

2.9.1 ONS15600sdh_HO_PM_FE_View

2.9.2 ONS15600sdh_HO_PM_View

2.9.3 ONS15600sdh_MS_PM_FE_View

2.9.4 ONS15600sdh_MS_PM_View

2.9.5 ONS15600sdh_RS_PM_View

2.9.6 ONS15600_Enet_PM_View

2.9.7 ONS15600_Pos_PM_View

2.9.8 ONS15600_SONET_Line_FE_PM_View

2.9.9 ONS15600_SONET_Line_PM_View

2.9.10 ONS15600_SONET_Path_FE_PM_View

2.9.11 ONS15600_SONET_Path_PM_View

2.9.12 ONS15600_SONET_Sec_PM_View

2.10 Cisco ONS 158xx Views

2.10.1 ONS158xx_ADA_24h_PM_View

2.10.2 ONS158xx_ADA_PM_View

2.10.3 ONS158xx_AIU_24h_PM_View

2.10.4 ONS158xx_AIU_PM_View

2.10.5 ONS158xx_All_Ampl_24h_PM_View

2.10.6 ONS158xx_All_Ampl_Elh_PM_View

2.10.7 ONS158xx_All_Ampl_PM_View

2.10.8 ONS158xx_All_Mux_24h_PM_View

2.10.9 ONS158xx_All_Mux_PM_View

2.10.10 ONS158xx_All_Tran_24h_PM_View

2.10.11 ONS158xx_All_Tran_PM_View

2.10.12 ONS158xx_All_WD_24h_PM_View

2.10.13 ONS158xx_All_WD_PM_View

2.10.14 ONS158xx_Ampl_Elh_24h_PM_View

2.10.15 ONS158xx_Amplifier_24h_PM_View

2.10.16 ONS158xx_Amplifier_PM_View

2.10.17 ONS158xx_B1_24h_PM_View

2.10.18 ONS158xx_B1_Fec_24h_PM_View

2.10.19 ONS158xx_B1_Fec_PM_View

2.10.20 ONS158xx_B1_PM_View

2.10.21 ONS158xx_BBA_24h_PM_View

2.10.22 ONS158xx_BBA_PM_View

2.10.23 ONS158xx_BCS_24h_PM_View

2.10.24 ONS158xx_BCS_PM_View

2.10.25 ONS158xx_BT_24h_PM_View

2.10.26 ONS158xx_BT_PM_View

2.10.27 ONS158xx_CMP_24h_PM_View

2.10.28 ONS158xx_CMP_PM_View

2.10.29 ONS158xx_Demux_24h_PM_View

2.10.30 ONS158xx_Demux_PM_View

2.10.31 ONS158xx_Eth_24h_PM_View

2.10.32 ONS158xx_Eth_PM_View

2.10.33 ONS158xx_Fec_24h_PM_View

2.10.34 ONS158xx_Fec_PM_View

2.10.35 ONS158XX_Inventory_View

2.10.36 ONS158xx_IOC_Card_24h_PM_View

2.10.37 ONS158xx_IOC_Card_PM_View

2.10.38 ONS158xx_IRBA_24h_PM_View

2.10.39 ONS158xx_IRBA_PM_View

2.10.40 ONS158xx_LAC_Active_Users_View

2.10.41 ONS158xx_LAC_Log_Status_View

2.10.42 ONS158xx_LAC_Log_View

2.10.43 ONS158xx_LAC_Profile_View

2.10.44 ONS158xx_LAC_Users_View

2.10.45 ONS158xx_LEM_24h_PM_View

2.10.46 ONS158xx_LEM_B1_24h_PM_View

2.10.47 ONS158xx_LEM_B1_PM_View

2.10.48 ONS158xx_LEM_Fec_24h_PM_View

2.10.49 ONS158xx_LEM_Fec_PM_View

2.10.50 ONS158xx_LEM_PM_View

2.10.51 ONS158xx_LSM_24h_PM_View

2.10.52 ONS158xx_LSM_PM_View

2.10.53 ONS158xx_LT_24h_PM_View

2.10.54 ONS158xx_LT_PM_View

2.10.55 ONS158xx_Mux_24h_PM_View

2.10.56 ONS158xx_Mux_PM_View

2.10.57 ONS158xx_OA_Elh_24h_PM_View

2.10.58 ONS158xx_OA_Elh_PM_View

2.10.59 ONS158xx_OAD_24h_PM_View

2.10.60 ONS158xx_OAD_PM_View

2.10.61 ONS158xx_OADM_24h_PM_View

2.10.62 ONS158xx_OADM_PM_View

2.10.63 ONS158xx_OBA_C_24h_PM_View

2.10.64 ONS158xx_OBA_C_PM_View

2.10.65 ONS158xx_OCP_24h_PM_View

2.10.66 ONS158xx_OCP_PM_View

2.10.67 ONS158xx_OECP_24h_PM_View

2.10.68 ONS158xx_OECP_PM_View

2.10.69 ONS158xx_OEP_C_24h_PM_View

2.10.70 ONS158xx_OEP_C_PM_View

2.10.71 ONS158xx_OP_Elh_24h_PM_View

2.10.72 ONS158xx_OP_Elh_PM_View

2.10.73 ONS158xx_OPA_C_24h_PM_View

2.10.74 ONS158xx_OPA_C_PM_View

2.10.75 ONS158xx_ORP_Elh_24h_PM_View

2.10.76 ONS158xx_ORP_Elh_PM_View

2.10.77 ONS158xx_OSCM_24h_PM_View

2.10.78 ONS158xx_OSCM_PM_View

2.10.79 ONS158xx_OSU_24h_PM_View

2.10.80 ONS158xx_OSU_PM_View

2.10.81 ONS158xx_PM_Module_View

2.10.82 ONS158xx_Pre_L_IR_24h_PM_View

2.10.83 ONS158xx_Pre_L_IR_PM_View

2.10.84 ONS158xx_PreL_24h_PM_View

2.10.85 ONS158xx_PreL_PM_View

2.10.86 ONS158xx_RBA_24h_PM_View

2.10.87 ONS158xx_RBA_PM_View

2.10.88 ONS158xx_RT_24h_PM_View

2.10.89 ONS158xx_RT_PM_View

2.10.90 ONS158xx_Rxt_24h_PM_View

2.10.91 ONS158xx_Rxt_B1_24h_PM_View

2.10.92 ONS158xx_Rxt_B1_PM_View

2.10.93 ONS158xx_Rxt_Fec_24h_PM_View

2.10.94 ONS158xx_Rxt_Fec_PM_View

2.10.95 ONS158xx_Rxt_PM_View

2.10.96 ONS158xx_SCF_24h_PM_View

2.10.97 ONS158xx_SCF_PM_View

2.10.98 ONS158xx_SCU_24h_PM_View

2.10.99 ONS158xx_SCU_PM_View

2.10.100 ONS158xx_Simple_24h_PM_View

2.10.101 ONS158xx_Simple_PM_View

2.10.102 ONS158xx_TPA_24h_PM_View

2.10.103 ONS158xx_TPA_PM_View

2.10.104 ONS158xx_Trans_B1_24h_PM_View

2.10.105 ONS158xx_Trans_B1_PM_View

2.10.106 ONS158xx_Trans_Fec_24h_PM_View

2.10.107 ONS158xx_Transp_24h_PM_View

2.10.108 ONS158xx_Transp_Fec_PM_View

2.10.109 ONS158xx_Transponder_PM_View

2.10.110 ONS158xx_TT_24h_PM_View

2.10.111 ONS158xx_TT_PM_View

2.10.112 ONS158xx_WCM_24h_PM_View

2.10.113 ONS158xx_WCM_B1_24h_PM_View

2.10.114 ONS158xx_WCM_B1_PM_View

2.10.115 ONS158xx_WCM_Fec_24h_PM_View

2.10.116 ONS158xx_WCM_Fec_PM_View

2.10.117 ONS158xx_WCM_PM_View

2.10.118 ONS158xx_WD_24h_PM_View

2.10.119 ONS158xx_WD_PM_View

2.10.120 ONS158xx_WD_Rx_24h_PM_View

2.10.121 ONS158xx_WD_Rx_PM_View

2.10.122 Pirelli_Inventory_View

Database Schema Views

This chapter describes the database views that CTM creates and uses. The following CTM views are listed in alphabetical order by category:

•Common PM Views

•CTM Base Views

•Cisco MGX Media Gateway Views

•Cisco ONS 152xx Views

•Cisco ONS 15216 Views

•Cisco ONS 153xx Views

•Cisco ONS 15454 Views

•Cisco ONS 155xx Views

•Cisco ONS 156xx Views

•Cisco ONS 158xx Views

2.1 Common PM Views

2.1.1 NE_Module_Level_Debug_View

create or replace view NE_MODULE_LEVEL_DEBUG_VIEW AS

SELECT

	nit.NESysID AS NESYSID,

	nmld.NEDBACCESSID AS NEDBACCESSID,

	nmld.SERVICEINSTID AS SERVICEINSTID,

	nmld.SERVICEID AS SERVICEID,

	DECODE(LENGTH(RTRIM(LTRIM(nmld.MODULENAME))),0,'false',null,'false','true') AS 
ENABLED,

	nmld.MODULENAME AS MODULENAME

FROM

	NE_MODULE_LEVEL_DEBUG nmld,

	NE_INFO_TABLE nit

WHERE

	nit.NEDBAccessID = nmld.NEDBACCESSID(+)

union

SELECT

	'SIID' AS NESYSID,

	-100 AS NEDBACCESSID,

	SERVICEINSTID,

	SERVICEID,

	DECODE(LENGTH(RTRIM(LTRIM(MODULENAME))),0,'false',null,'false','true') AS ENABLED,

	MODULENAME

FROM

	NE_MODULE_LEVEL_DEBUG

WHERE

	NEDBACCESSID = -100 AND

	SERVICEINSTID > -100

union

SELECT

	'SIID' AS NESYSID,

	-100 AS NEDBACCESSID,

	-100 AS SERVICEINSTID,

	SERVICEID,

	DECODE(LENGTH(RTRIM(LTRIM(MODULENAME))),0,'false',null,'false','true') AS ENABLED,

	MODULENAME

FROM

	NE_MODULE_LEVEL_DEBUG

WHERE

	NEDBACCESSID = -100 AND

	SERVICEINSTID = -100;

2.1.2 SDH_HO_FarEnd_PM_View

CREATE OR REPLACE VIEW Sdh_Ho_FarEnd_pm_view AS

SELECT

       ne.NESysID,

       ne.NEDbaccessid,

       en.ModuleName,

       ho454sdh.ModuleType,

       ho454sdh.Physicalloc,

       ho454sdh.NEIfIndex,

       ho454sdh.TimeStamp,

       ho454sdh.NEIfPMStatus,

       ho454sdh.inMaintenance,

       ho454sdh.fehpeb as FEHPEB,

       ho454sdh.fehpes as FEHPES,

       ho454sdh.fehpses as FEHPSES,

       ho454sdh.fehpuas as FEHPUAS,

	ho454sdh.fehpfc as FEHPFC,

       ho454sdh.fehpbbe as FEHPBBE,

       ho454sdh.fehpesr as FEHPESR,

       ho454sdh.fehpsesr as FEHPSESR,

       ho454sdh.fehpbber as FEHPBBER,

       ho454sdh.thresholdList as ThresholdList,

       ho454sdh.IS24H as IS24H,

	 ho454sdh.ModelType,

       ho454sdh.ObjectType,

       ho454sdh.StrObjInstance,

       ho454sdh.INVALIDITYLIST,

       ho454sdh.rowid as Myrow,

	ne.NEAliasID as NEAliasID

FROM   ONS15454SDH_HO_PM_TABLE ho454sdh,

       ne_info_table ne,

       module_type_table en

WHERE

       ho454sdh.Nedbaccessid = ne.nedbaccessid AND

       ho454sdh.ModuleType   = en.ModuleType   AND

	ne.nemodeltype      = en.modeltype;

2.1.3 SDH_HO_NearEnd_PM_View

CREATE OR REPLACE VIEW Sdh_Ho_NearEnd_pm_view AS

SELECT

       ne.NESysID,

       ne.NEDbaccessid,

       getModuleName_pm(ne.nenodeid, ho454sdh.ModuleType) as ModuleName,

       decode(ho454sdh.ModuleType,'',ms454sdh.ModuleType,ho454sdh.ModuleType) as 
ModuleType,

       ho454sdh.Physicalloc,

       ho454sdh.NEIfIndex,

       decode(ho454sdh.TimeStamp,'',ms454sdh.timestamp,ho454sdh.Timestamp) as Timestamp,

       ho454sdh.NEIfPMStatus,

       ho454sdh.inMaintenance,

       ho454sdh.hpeb as HPEB,

       ho454sdh.hpes as HPES,

       ho454sdh.hpses as HPSES,

       ho454sdh.hpuas as HPUAS,

	ho454sdh.hpfc as HPFC,

       ho454sdh.hpbbe as HPBBE,

       GETPMPARAMETERVALUE(ho454sdh.hpppjcpdet,ms454sdh.msppjcpdet) as PPJC_PDET,

       GETPMPARAMETERVALUE(ho454sdh.hpnpjcpdet,ms454sdh.msnpjcpdet) as NPJC_PDET,

       GETPMPARAMETERVALUE(ho454sdh.hpppjcpgen,ms454sdh.msppjcpgen) as PPJC_PGEN,

       GETPMPARAMETERVALUE(ho454sdh.hpnpjcpgen,ms454sdh.msnpjcpgen) as NPJC_PGEN,

       ho454sdh.hppjdiff as PJ_DIFF,

       ho454sdh.hppjpsec as PJP_SEC,

       ho454sdh.hppjnsec as PJN_SEC,

       ho454sdh.hpesr as HPESR,

       ho454sdh.hpsesr as HPSESR,

       ho454sdh.hpbber as HPBBER,

       ho454sdh.thresholdList as ThresholdList,

       ho454sdh.IS24H as IS24H,

       ho454sdh.ModelType,

       ho454sdh.ObjectType,

       ho454sdh.StrObjInstance,

       ho454sdh.INVALIDITYLIST,

       ho454sdh.rowid as Myrow,

	ne.NEAliasID as NEAliasID

FROM   ONS15454SDH_HO_PM_TABLE ho454sdh,

       ONS15454SDH_MS_PM_TABLE ms454sdh,

       ne_info_table ne

WHERE

        ms454sdh.Nedbaccessid(+) = ho454sdh.nedbaccessid and

        ms454sdh.timestamp(+) = ho454sdh.Timestamp and

        ms454sdh.moduletype(+)   =ho454sdh.ModuleType and

        ms454sdh.physicalloc(+) = ho454sdh.physicalloc and

        ms454sdh.IS24H(+)      = ho454sdh.IS24H  and

2.1.4 SDH_MS_FarEnd_PM_View

CREATE OR REPLACE VIEW Sdh_Ms_FarEnd_pm_view AS

SELECT

       ne.NESysID,

       ne.NEDbaccessid,

       en.ModuleName,

       ms454sdh.ModuleType,

       ms454sdh.Physicalloc,

       ms454sdh.NEIfIndex,

       ms454sdh.TimeStamp,

       ms454sdh.NEIfPMStatus,

       ms454sdh.inMaintenance,

       ms454sdh.femseb as FEMSEB,

       ms454sdh.femses as FEMSES,

       ms454sdh.femsses as FEMSSES,

       ms454sdh.femsuas as FEMSUAS,

	ms454sdh.femsfc as FEMSFC,

       ms454sdh.femsbbe as FEMSBBE,

       ms454sdh.femsesr as FEMSESR,

       ms454sdh.femssesr as FEMSSESR,

       ms454sdh.femsbber as FEMSBBER,

       ms454sdh.thresholdList as ThresholdList,

       ms454sdh.IS24H as IS24H,

	 ms454sdh.ModelType,

       ms454sdh.ObjectType,

       ms454sdh.StrObjInstance,

       ms454sdh.INVALIDITYLIST,

       ms454sdh.rowid as Myrow,

	ne.NEAliasID as NEAliasID

FROM   ONS15454SDH_MS_PM_TABLE ms454sdh,

       ne_info_table ne,

       module_type_table en

WHERE

       ms454sdh.Nedbaccessid = ne.nedbaccessid AND

       ms454sdh.ModuleType    = en.ModuleType   AND

	ne.nemodeltype      = en.modeltype;

2.1.5 SDH_MS_NearEnd_PM_View

CREATE OR REPLACE VIEW Sdh_Ms_NearEnd_pm_view AS

SELECT

       ne.NESysID,

       ne.NEDbaccessid,

       en.ModuleName,

       ms454sdh.ModuleType,

       ms454sdh.Physicalloc,

       ms454sdh.NEIfIndex,

       ms454sdh.TimeStamp,

       ms454sdh.NEIfPMStatus,

       ms454sdh.inMaintenance,

       ms454sdh.mseb as MSEB,

       ms454sdh.mses as MSES,

       ms454sdh.msses as MSSES,

       ms454sdh.msuas as MSUAS,

       ms454sdh.msfc as MSFC,

       ms454sdh.msbbe as MSBBE,

       ms454sdh.mspsc as MSPSC,

       ms454sdh.mspsd as MSPSD,

       ms454sdh.mspscw as MSPSCW,

       ms454sdh.mspsdw as MSPSDW,

       ms454sdh.mspscs as MSPSCS,

       ms454sdh.mspsds as MSPSDS,

       ms454sdh.mspscr as MSPSCR,

       ms454sdh.mspsdr as MSPSDR,

       ms454sdh.msesr as MSESR,

       ms454sdh.mssesr as MSSESR,

       ms454sdh.msbber as MSBBER,

       ms454sdh.thresholdList as ThresholdList,

       ms454sdh.IS24H as IS24H,

       ms454sdh.ModelType,

       ms454sdh.ObjectType,

       ms454sdh.StrObjInstance,

       ms454sdh.INVALIDITYLIST,

       ms454sdh.rowid as Myrow,

	ne.NEAliasID as NEAliasID

FROM   ONS15454SDH_MS_PM_TABLE ms454sdh,

       ne_info_table ne,

       module_type_table en

WHERE

       ms454sdh.Nedbaccessid = ne.nedbaccessid AND

       ms454sdh.ModuleType    = en.ModuleType   AND

	ne.nemodeltype      = en.modeltype;

2.1.6 SDH_RS_NearEnd_PM_View

CREATE OR REPLACE VIEW Sdh_Rs_NearEnd_pm_view AS

SELECT

       ne.NESysID,

       ne.NEDbaccessid,

       en.ModuleName,

       rs454sdh.ModuleType,

       rs454sdh.Physicalloc,

       rs454sdh.NEIfIndex,

       rs454sdh.TimeStamp,

       rs454sdh.NEIfPMStatus,

       rs454sdh.inMaintenance,

       rs454sdh.rseb as RSEB,

       rs454sdh.rses as RSES,

       rs454sdh.rsses as RSSES,

       rs454sdh.rssefs as RSSEFS,

       rs454sdh.rsbbe as RSBBE,

       rs454sdh.rsesr as RSESR,

       rs454sdh.rssesr as RSSESR,

       rs454sdh.rsbber as RSBBER,

       rs454sdh.thresholdList as ThresholdList,

       rs454sdh.IS24H as IS24H,

       rs454sdh.ModelType,

       rs454sdh.ObjectType,

       rs454sdh.StrObjInstance,

       rs454sdh.INVALIDITYLIST,

       rs454sdh.rowid as Myrow,

	ne.NEAliasID as NEAliasID

FROM   ONS15454SDH_RS_PM_TABLE rs454sdh,

       ne_info_table ne,

       module_type_table en

WHERE

       rs454sdh.Nedbaccessid = ne.nedbaccessid AND

       rs454sdh.ModuleType    = en.ModuleType   AND

	ne.nemodeltype      = en.modeltype;

2.1.7 SONET_ENET_PM_View

CREATE OR REPLACE VIEW SONET_ENET_PM_VIEW  AS

SELECT

       ni.NESysID,

       ni.NEDbaccessid,

       en.ModuleName,

       en.ModuleType,

       enet.Physicalloc,

       enet.NEIfIndex,

       enet.TimeStamp,

       enet.NEIfPmStatus,

       enet.InMaintenance,

       enet.ThresholdList,

       enet.IfInOctets,

       enet.RxPackets,

       enet.IfInUcastPkts,

       enet.IfInMulticastPkts,

       enet.IfInBroadcastPkts,

       enet.IfInErrors,

       enet.IfOutOctets,

       decode(en.Moduletype,102,to_char(enet.TxPackets),

                            103,to_char(enet.TxPackets),

                            118,to_char(enet.TxPackets),

                            119,to_char(enet.TxPackets),

                            181,to_char(enet.TxPackets),

                            182,to_char(enet.TxPackets),

                            183,to_char(enet.TxPackets),

                            184,to_char(enet.TxPackets),

                            271,to_char(enet.TxPackets),

                            272,to_char(enet.TxPackets),

                            273,to_char(enet.TxPackets),

                                
get_enet_value2(enet.IfOutUcastPkts,enet.IfOutMulticastPkts,enet.IfOutBroadcastPkts)

             ) as TxPackets,

       enet.IfOutUcastPkts,

       enet.IfOutMulticastPkts,

       enet.IfOutBroadcastPkts,

       enet.Dot3StatsAlignmentErrors,

       enet.Dot3StatsFCSErrors,

       enet.Dot3StatsSingleCollisionFrames,

       enet.Dot3StatsMultiCollisionFrames,

       enet.Dot3StatsDeferredTransmissions,

       enet.Dot3StatsLateCollisions,

       enet.Dot3StatsExcessiveCollisions,

       enet.EtherStatsUndersizePkts,

       enet.EtherStatsFragments,

       enet.EtherStatsOversizePkts,

       enet.EtherStatsJabbers,

       decode(en.Moduletype,102, to_char(enet.RxTotalErrors),

                            103, to_char(enet.RxTotalErrors),

                            118, to_char(enet.RxTotalErrors),

                            119, to_char(enet.RxTotalErrors),

                             62, to_char(enet.RxTotalErrors),

                                 
get_enet_value2(enet.Dot3StatsFCSErrors,enet.Dot3StatsAlignmentErrors,enet.EtherStatsUnder
SizePKTS, enet.ETHERSTATSOVERSIZEPKTS,enet.ETHERSTATSFRAGMENTS,enet.ETHERSTATSJABBERS)

              )  as Rx_TOTAL_ERRORS,

       decode(en.Moduletype, 102, enet.RxRunts,

                             103, enet.RxRunts,

                             118, enet.RxRunts,

                             119, enet.RxRunts,

                              62, enet.RxRunts,

							187, enet.RxRunts,

				enet.ETHERSTATSFRAGMENTS

             ) as Rx_RUNTS,

       enet.RxGIANTS as rx_giants,

       decode(en.Moduletype, 102, enet.TxCollisions,

                             103, enet.TxCollisions,

			     118, enet.TxCollisions,

                             119, enet.TxCollisions,

                                  
get_enet_value(enet.Dot3StatsSingleCollisionFrames,enet.Dot3StatsMultiCollisionFrames)

             ) as Tx_Collisions,

       enet.RxPauseFrames,

       enet.TxPauseFrames,

       enet.RxPktsDropInternalCongestion,

       enet.TxPktsDropInternalCongestion,

       enet.IS24H,

       enet.EtherUtilizationStats,

       enet.RxGMACDropCounts,

       enet.RxThresholdOversizes,

       enet.PortDropCounts,

       enet.TxGiants,

       enet.HDLCPktDrops,

       enet.RxControlFrames,

       enet.RXUnknownOpCodeFrames,

       enet.IfInErrorBytePkts,

       enet.IfInFramingErrorPkts,

       enet.IfInJunkInterPkts,

       enet.IfInDiscards,

       enet.IfOutDiscards,

       enet.Dot3StatsFrameTooLong,

       enet.Dot3StatsCarrierSenseErrors,

       enet.Dot3StatsSQETestErrors,

       enet.EtherStatsPkts64Octets,

       enet.EtherStatsPkts65to127Octets,

       enet.EtherStatsPkts128to255Octets,

       enet.EtherStatsPkts256to511Octets,

       enet.EtherStatsPkts512to1023Octets,

       enet.EtherStatsPkts1024to1518Octets,

       enet.EtherStatsBroadcastPkts,

       enet.EtherStatsMulticastPkts,

       enet.EtherStatsOctets,

       enet.EtherStatsCollisions,

       enet.EtherStatsCollisionFrames,

       enet.EtherStatsCRCAlignErrors,

       enet.MediaIndStatsRxFramesTruncated,

       enet.MediaIndStatsRxFramesTooLong,

       enet.MediaIndStatsRxFramesBadCRC,

       enet.MediaIndStatsTxFramesBadCRC,

       enet.MediaIndStatsRxShortPkts,

       enet.MediaIndStatsOversizeDropped,

       enet.IfOutErrors,

       enet.EtherStatsPkts,

       enet.Dot3StatsInternalMacTxErrors,

       enet.Dot3StatsInternalMacRxErrors,

       enet.Dot3StatsSymbolErrors,

       enet.RxEtherUtilizationStats,

       enet.TxEtherUtilizationStats,

       enet.StatsLineLastClearedTime,

       enet.MEDIAINDSTATSTXFRAMESTOOLONG,

	decode(lag.LACPDUsRx,NULL, -1,lag.LACPDUsRx) as LACPDUSRX,

       decode(lag.MarkerPDUsRx,NULL, -1,lag.MarkerPDUsRx) as MARKERPDUS,

       decode(lag.MarkerResponsePDUsRx,NULL, -1,lag.MarkerResponsePDUsRx) as 
MARKERRESPONSEPDUSRX,

       decode(lag.UnknownRx,NULL, -1,lag.UnknownRx) as UNKNOWNRX,

       decode(lag.IllegalRx,NULL, -1,lag.IllegalRx) as ILLEGALRX,

       decode(lag.LACPDUsTx,NULL, -1,lag.LACPDUsTx) as LACPDUSTX,

       decode(lag.MarkerPDUsTx,NULL, -1,lag.MarkerPDUsTx) as MARKERPDUSTX,

       decode(lag.MarkerResponsePDUsTx,NULL, -1,lag.MarkerResponsePDUsTx) as 
MARKERRESPONSEPDUSTX,

       enet.ETHERSTATSTXFIFOOVERFLOWEVENTS,

       enet.ModelType,

       enet.ObjectType,

       enet.StrObjInstance,

       enet.InvalidityList,

       enet.rowid as myrow,

	ni.NEAliasID as NEAliasID

FROM

       ons15454_enet_pm_table enet, left outer join ons15454_LAG_pm_table lag

       on (enet.NEDBAccessID = lag.NEDBAccessID AND

           enet.TIMESTAMP = lag.TIMESTAMP AND

           enet.MODULETYPE = lag.MODULETYPE AND

           enet.PHYSICALLOC = lag.PHYSICALLOC AND

           enet.NEIFINDEX = lag.NEIFINDEX AND

           enet.IS24H = lag.IS24H),

		ne_info_table ni,

       module_type_table en

WHERE

       enet.ModuleType    = en.ModuleType   AND

       ni.NEModelType     = en.ModelType    AND

       enet.NEDBAccessID  = ni.NEDBAccessID

UNION ALL

SELECT

       ni.NESysID,

       ni.NEDbaccessid,

       en.ModuleName,

       en.ModuleType,

       enet.Physicalloc,

       enet.NEIfIndex,

       enet.TimeStamp,

       enet.NEIfPmStatus,

       enet.InMaintenance,

       enet.thresholdList as ThresholdList,

       enet.IfInOctets,

       to_number('-1') as RXPACKETS,

       enet.IfInUcastPkts,

       enet.IfInMulticastPkts,

       enet.IfInBroadcastPkts,

       enet.IfInErrors,

       enet.IfOutOctets,

       to_char(-1) as TXPACKETS,

       enet.IfOutUcastPkts,

       enet.IfOutMulticastPkts,

       enet.IfOutBroadcastPkts,

       enet.Dot3StatsAlignmentErrors,

       enet.Dot3StatsFCSErrors,

	to_number('-1') as DOT3STATSSINGLECOLLISIONFRAMES,

 	to_number('-1') as DOT3STATSMULTICOLLISIONFRAMES,

	to_number('-1') as DOT3STATSDEFERREDTRANSMISSIONS,

	to_number('-1') as DOT3STATSLATECOLLISIONS,

	to_number('-1') as DOT3STATSEXCESSIVECOLLISIONS,

       enet.EtherStatsUndersizePkts,

       enet.EtherStatsFragments,

	enet.EtherStatsOversizePkts,

	enet.EtherStatsJabbers,

	to_char(-1) as RX_TOTAL_ERRORS,

	to_number('-1') as RX_RUNTS,

	to_number('-1') as RX_GIANTS,

	to_number('-1') as TX_COLLISIONS,

 	enet.RxPauseFrames,

       enet.TxPauseFrames,

	to_number('-1') as RXPKTSDROPINTERNALCONGESTION,

	to_number('-1') as TXPKTSDROPINTERNALCONGESTION,

	enet.IS24H,

	to_number('-1') as ETHERUTILIZATIONSTATS,

	to_number('-1') as RXGMACDROPCOUNTS,

	to_number('-1') as RXTHRESHOLDOVERSIZES,

	enet.EtherStatsDropEvents,

	to_number('-1') as TXGIANTS,

	to_number('-1') as HDLCPKTDROPS,

	to_number('-1') as RXCONTROLFRAMES,

	to_number('-1') as RXUNKNOWNOPCODEFRAMES,

	to_number('-1') as IFINERRORBYTEPKTS,

	to_number('-1') as IFINFRAMINGERRORPKTS,

	to_number('-1') as IFINJUNKINTERPKTS,

	enet.IfInDiscards,

    	enet.IfOutDiscards,

	enet.Dot3StatsFrameTooLong,

	to_number('-1') as DOT3STATSCARRIERSENSEERRORS,

	to_number('-1') as DOT3STATSSQETESTERRORS,

       enet.EtherStatsPkts64Octets,

       enet.EtherStatsPkts65to127Octets,

       enet.EtherStatsPkts128to255Octets,

       enet.EtherStatsPkts256to511Octets,

       enet.EtherStatsPkts512to1023Octets,

       enet.EtherStatsPkts1024to1518Octets,

       enet.EtherStatsBroadcastPkts,

       enet.EtherStatsMulticastPkts,

       enet.EtherStatsOctets,

	to_number('-1') as ETHERSTATSCOLLISIONS,

	to_number('-1') as ETHERSTATSCOLLISIONFRAMES,

	to_number('-1') as ETHERSTATSCRCALIGNERRORS,

	to_number('-1') as MEDIAINDSTATSRXFRAMESTRUNCATED,

	to_number('-1') as MEDIAINDSTATSRXFRAMESTOOLONG,

	to_number('-1') as MEDIAINDSTATSRXFRAMESBADCRC,

	to_number('-1') as MEDIAINDSTATSTXFRAMESBADCRC,

	to_number('-1') as MEDIAINDSTATSRXSHORTPKTS,

	to_number('-1') as MEDIAINDSTATSOVERSIZEDROPPED,

       enet.IfOutErrors,

       enet.EtherStatsPkts,

       enet.Dot3StatsInternalMacTxErrors,

       enet.Dot3StatsInternalMacRxErrors,

        enet.Dot3StatsSymbolErrors,

       enet.RxEtherUtilizationStats,

       enet.TxEtherUtilizationStats,

       to_Date('') as STATSLINELASTCLEAREDTIME,

       to_number('-1') as MEDIAINDSTATSTXFRAMESTOOLONG,

	to_number('-1') as LACPDUSRX,

       to_number('-1') as MARKERPDUS,

       to_number('-1') as MARKERRESPONSEPDUSRX,

       to_number('-1') as UNKNOWNRX,

       to_number('-1') as ILLEGALRX,

       to_number('-1') as LACPDUSTX,

       to_number('-1') as MARKERPDUSTX,

       to_number('-1') as MARKERRESPONSEPDUSTX,

       to_number('-1') as ETHERSTATSTXFIFOOVERFLOWEVENTS,

       enet.ModelType,

       enet.ObjectType,

       enet.StrObjInstance,

       enet.InvalidityList,

       enet.rowid as myrow,

	ni.NEAliasID as NEAliasID

FROM

       ons15600_enet_pm_table enet,

       ne_info_table ni,

       module_type_table en

WHERE

       enet.ModuleType    = en.ModuleType   AND

       ni.NEModelType     = en.ModelType    AND

       enet.NEDBAccessID  = ni.NEDBAccessID;

2.1.8 SONET_Line_FarEnd_PM_View

CREATE OR REPLACE VIEW Sonet_Line_FarEnd_pm_view AS

SELECT

       ne.NESysID,

       ne.NEDbaccessid,

       en.ModuleName,

       line454.ModuleType,

       line454.Physicalloc,

       line454.NEIfIndex,

       line454.TimeStamp,

       line454.NEIfPMStatus,

       line454.inMaintenance,

       line454.fecodeviolations as CV_LFE,

       line454.feerrsecs as ES_LFE,

       line454.feseverrsecs as SES_LFE,

       line454.feunavailablesecs as UAS_LFE,

       line454.fefcl as FC_LFE,

       line454.thresholdList as ThresholdList,

       line454.IS24H as IS24H,

       line454.ModelType,

       line454.ObjectType,

       line454.StrObjInstance,

       line454.InvalidityList as InvalidityList,

       line454.rowid as Myrow,

	ne.NEAliasID as NEAliasID

FROM   ONS15454_SONET_LINE_PM_TABLE line454,

       ne_info_table ne,

       module_type_table en

WHERE

       line454.Nedbaccessid = ne.nedbaccessid AND

       line454.ModuleType    = en.ModuleType   AND

ne.nemodeltype      = en.modeltype;

2.1.9 SONET_Line_NearEnd_PM_View

CREATE OR REPLACE VIEW Sonet_Line_NearEnd_pm_view AS

SELECT

       ne.NESysID,

       ne.NEDbaccessid,

       en.ModuleName,

       line454.ModuleType,

       line454.Physicalloc,

       line454.NEIfIndex,

       line454.TimeStamp,

       line454.NEIfPMStatus,

       line454.inMaintenance,

       line454.codeviolations as CV_L,

       line454.errsecs as ES_L,

       line454.severrsecs as SES_L,

       line454.unavailablesecs as UAS_L,

       line454.PpjcPdet as PpjcPdet,

       line454.NpjcPdet as NpjcPdet,

       line454.PpjcPgen as PpjcPgen,

       line454.NpjcPgen as NpjcPgen,

       line454.FCL as FC_L,

       line454.PSC as PSC,

       line454.PSD as PSD,

       line454.PjDiff as PjDiff,

       line454.PjpSec as PjpSec,

       line454.PjnSec as PjnSec,

       line454.PSCW as PSC_W,

       line454.PSDW as PSD_W,

       line454.PSCR as PSC_R,

       line454.PSDR as PSD_R,

       line454.PSCS as PSC_S,

       line454.PSDS as PSD_S,

       line454.thresholdList as ThresholdList,

       line454.IS24H as IS24H,

       line454.ModelType,

       line454.ObjectType,

       line454.StrObjInstance,

       line454.InvalidityList as InvalidityList,

       line454.rowid as Myrow,

	ne.NEAliasID as NEAliasID

FROM   ONS15454_SONET_LINE_PM_TABLE line454,

       ne_info_table ne,

       module_type_table en

WHERE

       line454.Nedbaccessid = ne.nedbaccessid AND

       line454.ModuleType    = en.ModuleType   AND

       ne.nemodeltype      = en.modeltype;

2.1.10 SONET_Physical_PM_View

CREATE OR REPLACE VIEW SONET_PHYSICAL_PM_VIEW AS

SELECT

        ni.NESysID as NESYSID,

        en.ModuleName as MODULENAME,

        phy.PhysicalLoc as PHYSICALLOC,

        phy.NEIfIndex as NEIFINDEX,

        phy.TimeStamp as TIMESTAMP,

        phy.NEIfPMStatus as NEIFPMSTATUS,

        phy.inMaintenance as INMAINTENANCE,

        phy.LaserBiasCurrent,

        phy.OpticalPowerTransmitted,

        phy.OpticalPowerReceived,

        phy.MinLaserBias as MINLASERBIAS,

        phy.AvgLaserBias as AVGLASERBIAS,

        phy.MaxLaserBias as MAXLASERBIAS,

        phy.MinLaserTemp as MINLASERTEMP,

        phy.AvgLaserTemp as AVGLASERTEMP,

        phy.MaxLaserTemp as MAXLASERTEMP,

        phy.MinReceivedLaserTemp as MINRECEIVEDLASERTEMP,

        phy.AvgReceivedLaserTemp as AVGRECEIVEDLASERTEMP,

        phy.MaxReceivedLaserTemp as MAXRECEIVEDLASERTEMP,

	phy.MinTransReceiverVoltage as MINTRANSRECEIVERVOLTAGE,

        phy.AvgTransReceiverVoltage as AVGTRANSRECEIVERVOLTAGE,

        phy.MaxTransReceiverVoltage as MAXTRANSRECEIVERVOLTAGE,

        phy.MinTransmittedPower as MINTRANSMITTEDPOWER,

        phy.AvgTransmittedPower as AVGTRANSMITTEDPOWER,

        phy.MaxTransmittedPower as MAXTRANSMITTEDPOWER,

        phy.MinReceivedPower as MINRECEIVEDPOWER,

        phy.AvgReceivedPower as AVGRECEIVEDPOWER,

        phy.MaxReceivedPower as MAXRECEIVEDPOWER,

	phy.MinPassThroughPower as MINPASSTHROUGHPOWER,

	phy.AvgPassThroughPower as AVGPASSTHROUGHPOWER,

	phy.MaxPassThroughPower as MAXPASSTHROUGHPOWER,

        ni.NEDbaccessid as NEDBACCESSID,

        phy.thresholdList as ThresholdList,

        en.ModuleType as MODULETYPE,

        phy.IS24H  as IS24H,

        phy.ModelType,

        phy.ObjectType,

        phy.StrObjInstance,

        phy.InvalidityList as InvalidityList,

        phy.rowid as myrow,

	ne.NEAliasID as NEAliasID

FROM

        ONS15454_PHY_LAYER_PM_Table phy,

        ne_info_table ni,

        module_type_table en

WHERE

        phy.ModuleType   = en.ModuleType   AND

        ni.NEModelType   = en.ModelType    AND

        phy.NEDBAccessID = ni.NEDBAccessID;

2.1.11 SONET_POS_PM_View

CREATE OR REPLACE VIEW SONET_POS_PM_VIEW  AS

SELECT

       ni.NESysID,

       ni.NEDbaccessid,

       en.ModuleName,

       en.ModuleType,

       pos.Physicalloc,

       pos.NEIfIndex,

       pos.TimeStamp,

       pos.NEIfPmStatus,

       pos.InMaintenance,

       pos.PosStatsRxPreHdlcBytes,

       pos.PosStatsRxPostHdlcBytes,

       pos.PosStatsRxPackets,

       pos.PosStatsRxNormalPackets,

       pos.PosStatsRxShorts,

       pos.PosStatsRxRunts,

       pos.PosStatsRxLongs,

       pos.PosStatsRxTotalErrors,

       pos.PosStatsRxCrcErrors,

       pos.PosStatsRxInputDropPackets,

       pos.PosStatsRxInputAbortPackets,

       pos.PosStatsTxPreHdlcBytes,

       pos.PosStatsTxPostHdlcBytes,

       pos.PosStatsTxPackets,

       pos.PosStatsDropCounts,

       pos.EtherStatsDropEvents,

       pos.GfpStatsRxSBitErrors,

       pos.GfpStatsRxMBitErrors,

       pos.GfpStatsRxTypeInvalid,

       pos.GfpStatsRxCRCErrors,

       pos.GfpStatsRxCIDInvalid,

       pos.GfpStatsCSFRaised,

       pos.GfpStatsRxFrame,

       pos.GfpStatsTxFrame,

       pos.GfpStatsRxOctets,

       pos.GfpStatsTxOctets,

       pos.GfpStatsLFDRaised,

       pos.MediaIndStatsRxFramesTruncated,

       pos.MediaIndStatsRxFramesTooLong,

       pos.MediaIndStatsRxFramesBadCRC,

       pos.MediaIndStatsRxShortPkts,

       pos.hdlcInOctets,

       pos.hdlcRxAborts,

       pos.hdlcOutOctets,

       pos.ifInOctets,

       pos.ifOutOctets,

       pos.rxPktsDropInternalCongestion,

       pos.ifInDiscards,

       pos.RxEtherUtilizationStats as Rx_Utilization_Stats,

       pos.TxEtherUtilizationStats as Tx_Utilization_Stats,

       pos.StatsLineLastClearedTime,

       pos.ifInPayloadCrcErrors,

       pos.ifOutPayloadCrcErrors,

       pos.ifOutOversizePackets,

       pos.HDLCPKTDROPS,

	pos.ifOutDiscards,

       pos.ThresholdList,

       pos.IS24H,

       pos.ModelType,

       pos.ObjectType,

       pos.StrObjInstance,

       pos.InvalidityList,

       pos.rowid as myrow,

	ni.NEAliasID as NEAliasID

FROM

       ons15454_pos_pm_table pos,

       ne_info_table ni,

       module_type_table en

WHERE

       pos.ModuleType    = en.ModuleType   AND

       ni.NEModelType     = en.ModelType    AND

       pos.NEDBAccessID  = ni.NEDBAccessID

UNION ALL

SELECT

       ni.NESysID,

       ni.NEDbaccessid,

       en.ModuleName,

       en.ModuleType,

       pos.Physicalloc,

       pos.NEIfIndex,

       pos.TimeStamp,

       pos.NEIfPmStatus,

       pos.InMaintenance,

       to_number('-1') as PosStatsRxPreHdlcBytes,

       to_number('-1') as PosStatsRxPostHdlcBytes,

       pos.RXTOTALPACKETS,

       to_number('-1') as PosStatsRxNormalPackets,

       to_number('-1') as PosStatsRxShorts,

       to_number('-1') as PosStatsRxRunts,

       to_number('-1') as PosStatsRxLongs,

       to_number('-1') as PosStatsRxTotalErrors,

       to_number('-1') as PosStatsRxCrcErrors,

       to_number('-1') as PosStatsRxInputDropPackets,

       to_number('-1') as PosStatsRxInputAbortPackets,

       to_number('-1') as PosStatsTxPreHdlcBytes,

       to_number('-1') as PosStatsTxPostHdlcBytes,

       pos.TXTOTALPACKETS,

       to_number('-1') as PosStatsDropCounts,

       to_number('-1') as EtherStatsDropEvents,

       pos.GfpStatsRxSBitErrors,

       pos.GfpStatsRxMBitErrors,

       pos.GfpStatsRxTypeInvalid,

       pos.GfpStatsRxCRCErrors,

       to_number('-1') as GfpStatsRxCIDInvalid,

       to_number('-1') as GfpStatsCSFRaised,

       pos.GfpStatsRxFrame,

       pos.GfpStatsTxFrame,

       pos.GfpStatsRxOctets,

       pos.GfpStatsTxOctets,

       to_number('-1') as GfpStatsLFDRaised,

       pos.MediaIndStatsRxFramesTruncated,

       pos.MediaIndStatsRxFramesTooLong,

       pos.MediaIndStatsRxFramesBadCRC,

       pos.MediaIndStatsRxShortPkts,

       pos.hdlcInOctets,

       pos.hdlcRxAborts,

       pos.hdlcOutOctets,

       to_number('-1') as ifInOctets,

       to_number('-1') as ifOutOctets,

       pos.RXPKTSDROPPEDINERNALCONGESTION,

       to_number('-1') as ifInDiscards,

       pos.RxEtherUtilizationStats as Rx_Utilization_Stats,

       pos.TxEtherUtilizationStats as Tx_Utilization_Stats,

       to_Date('') as StatsLineLastClearedTime,

       to_number('-1') as ifInPayloadCrcErrors,

       to_number('-1') as ifOutPayloadCrcErrors,

       to_number('-1') as ifOutOversizePackets,

       to_number('-1') as HDLCPKTDROPS,

	to_number('-1') as ifOutDiscards

       pos.thresholdList as ThresholdList,

       pos.IS24H,

       pos.ModelType,

       pos.ObjectType,

       pos.StrObjInstance,

       pos.InvalidityList,

       pos.rowid as myrow,

	ni.NEAliasID as NEAliasID

FROM

       ons15600_pos_pm_table pos,

       ne_info_table ni,

       module_type_table en

WHERE

       pos.ModuleType    = en.ModuleType   AND

       ni.NEModelType     = en.ModelType    AND

       pos.NEDBAccessID  = ni.NEDBAccessID;

2.1.12 SONET_Section_NearEnd_PM_View

CREATE OR REPLACE VIEW Sonet_Section_NearEnd_pm_view AS

SELECT

       ne.NESysID,

       ne.NEDbaccessid,

       en.ModuleName,

       sec454.ModuleType,

       sec454.Physicalloc,

       sec454.NEIfIndex,

       sec454.TimeStamp,

       sec454.NEIfPMStatus,

       sec454.inMaintenance,

       sec454.severrframesecs as SEFS_S,

       sec454.codeviolations as CV_S,

       sec454.errsecs as ES_S,

       sec454.severrsecs as SES_S,

       sec454.thresholdList as ThresholdList,

       sec454.IS24H as IS24H,

       sec454.ModelType,

       sec454.ObjectType,

       sec454.StrObjInstance,

       sec454.InvalidityList as InvalidityList,

       sec454.rowid as Myrow,

	ne.NEAliasID as NEAliasID

FROM   ONS15454_SONET_SEC_PM_TABLE sec454,

       ne_info_table ne,

       module_type_table en

WHERE

       sec454.Nedbaccessid = ne.nedbaccessid AND

       sec454.ModuleType    = en.ModuleType   AND

	ne.nemodeltype      = en.modeltype;

2.1.13 SONET_STSPath_FarEnd_PM_View

CREATE OR REPLACE VIEW Sonet_STSPATH_FarEnd_pm_view AS

SELECT

       ne.NESysID,

       ne.NEDbaccessid,

       en.ModuleName,

       path454.ModuleType,

       path454.Physicalloc,

       path454.NEIfIndex,

       path454.TimeStamp,

       path454.NEIfPMStatus,

       path454.inMaintenance,

       path454.fesonetpathcodeviolations as CV_PFE,

       path454.fesonetpatherrsecs as ES_PFE,

       path454.fesonetpathseverrsecs as SES_PFE,

       path454.fesonetpathunavailablesecs as UAS_PFE,

       path454.fesonetpathfc as FC_PFE,

       path454.thresholdList as ThresholdList,

       path454.IS24H as IS24H,

       path454.ModelType,

       path454.ObjectType,

       path454.StrObjInstance,

       path454.InvalidityList as InvalidityList,

       path454.rowid as Myrow,

	ne.NEAliasID as NEAliasID

FROM   ONS15454_SONET_PATH_PM_TABLE path454,

       ne_info_table ne,

       module_type_table en

WHERE

       path454.Nedbaccessid = ne.nedbaccessid AND

       path454.ModuleType   = en.ModuleType   AND

	ne.nemodeltype      = en.modeltype;

2.1.14 SONET_STSPath_NearEnd_PM_View

CREATE OR REPLACE VIEW Sonet_STSPATH_NearEnd_pm_view AS

SELECT

       ne.NESysID,

       ne.NEDbaccessid,

       en.ModuleName,

       decode(path454.ModuleType,'',line454.ModuleType,

				    path454.ModuleType) as ModuleType,

       path454.Physicalloc,

       path454.NEIfIndex,

       decode(path454.TimeStamp,'',line454.timestamp,

					    path454.Timestamp) as Timestamp,

       path454.NEIfPMStatus,

       path454.inMaintenance,

       path454.sonetpathcodeviolations as CV_P,

       path454.sonetpatherrsecs as ES_P,

       path454.sonetpathseverrsecs as SES_P,

       path454.sonetpathunavailablesecs as UAS_P,

       path454.sonetpathfc as FC_P,

       GETPMPARAMETERVALUE(path454.ppjcpdet,line454.ppjcpdet) as PPJC_PDET,

       GETPMPARAMETERVALUE(path454.npjcpdet,line454.npjcpdet) as NPJC_PDET,

       GETPMPARAMETERVALUE(path454.ppjcpgen,line454.ppjcpgen) as PPJC_PGEN,

       GETPMPARAMETERVALUE(path454.npjcpgen,line454.npjcpgen) as NPJC_PGEN,

       GETPMPARAMETERVALUE(path454.pjdiff,line454.pjdiff) as PJ_DIFF,

       GETPMPARAMETERVALUE(path454.pjpsec,line454.pjpsec) as PJP_SEC,

       GETPMPARAMETERVALUE(path454.pjnsec,line454.pjnsec) as PJN_SEC,

       path454.StsPathWidth,

       mergedinvaliditylist(line454.pjnsec,line454.ThresholdList,path454.ThresholdList) as 
ThresholdList,

       path454.IS24H as IS24H,

       path454.ModelType,

       path454.ObjectType,

       path454.StrObjInstance,

       mergedinvaliditylist(line454.pjnsec,line454.invaliditylist,path454.invaliditylist) 
as InvalidityList,

       path454.rowid as Myrow,

	ne.NEAliasID as NEAliasID

FROM   ONS15454_SONET_PATH_PM_TABLE path454,

       ONS15454_SONET_LINE_PM_TABLE line454,

       ne_info_table ne,

       module_type_table en

WHERE

       (path454.Nedbaccessid = ne.nedbaccessid and

        path454.ModuleType   = en.ModuleType ) and

       (line454.Nedbaccessid(+) = path454.nedbaccessid and

        line454.moduletype(+)   =path454.ModuleType   and

        line454.timestamp(+) = path454.Timestamp and

        line454.physicalloc(+) = path454.physicalloc and

        line454.IS24H(+)      = path454.IS24H ) and

	ne.nemodeltype      = en.modeltype;

2.2 CTM Base Views

2.2.1 Active_Alarm_View_With_Ack

CREATE OR REPLACE VIEW active_alarm_view_with_ack AS

SELECT

       aa.ActiveAlarmSeverity AS severityIcon,

       ack.AckIndex AS AckIcon,

       aa.ActiveAlarmComment AS noteIcon,

       aa.ActiveAlarmServEff,

       aa.ActiveAlarmIndex AS ID,

       ni.NESysID AS NetworkElement,

       aa.PhysicalLoc,

       getModuleName(aa.NEDBAccessID, aa.ModuleType, en.EventType) AS ModuleType,

       getInterfaceId(aa.nedbaccessid,aa.ModuleOrIfIndex,aa.ObjectType,en.EnumString) AS 
InterfaceID,

       en.EnumString AS Type,

       aa.ActiveAlarmSeverity AS Severity,

       aa.ActiveAlarmTimeStamp AS RaisedTimeStamp,

       aa.ClearAlarmTimeStamp AS ClearTimeStamp,

       aa.ActiveAlarmAdditionalInfo AS Description,

       ack.AckUserName AS AckUser,

       
decode(to_char(ack.AckTimeStamp,'DD-MON-YYYY'),'01-JAN-0001',aa.ClearAlarmTimeStamp,

                                                      '','',

                                                         ack.acktimestamp) AS 
AckTimeStamp,

       aa.ActiveAlarmComment AS NoteDescription,

       aa.NEDBAccessID,

       aa.nealarmtimestamp,

       ack.IsAutoAck,

       aa.AlarmStatus,

       aa.NeAlarmClearTimestamp,

       aa.AlarmSeqNum,

       GetPortName(aa.nedbaccessid, aa.physicalloc, aa.ModuleorIfindex, aa.moduletype) as 
PortName,

       aa.ModuleOrIfIndex as IntegerInterfaceid,

       aa.ExternalCondition,

       aa.ActiveAlarmType,

       en.eventtype,

       aa.rowid AS myrow,

       aa.ModelType,

       aa.ObjectType,

       aa.StrObjInstance,

       ni.NEAliasID as NEAliasID

	decode(to_char(nvl(aa.Direction,'')),'','N/A',aa.Direction) AS Direction,

       decode(to_char(nvl(aa.Location,'')),'','N/A',aa.Location) AS Location

FROM

       ne_info_table ni,

       active_alarm_table aa,

       alarm_event_type_enum_table en,

       ack_table ack

WHERE

       aa.NEDBAccessID    = ni.NEDBAccessID AND

       aa.ActiveAlarmType = en.EnumIndex    AND

       aa.AckIndex        = ack.AckIndex    AND

       ((aa.alarmstatus=1 and aa.ackindex=0) or

       aa.alarmstatus=0 )

union all

SELECT

       aa.ActiveAlarmSeverity AS severityIcon,

       ack.AckIndex AS AckIcon,

       aa.ActiveAlarmComment AS noteIcon,

       aa.ActiveAlarmServEff,

       aa.ActiveAlarmIndex AS ID,

       (select activevalue from ctm_config_table where sectionname='transportManager' and 
propertyname='ems-sys-id') as  NetworkElement,

       aa.PhysicalLoc,

       getModuleName(aa.NEDBAccessID, aa.ModuleType, en.EventType) AS ModuleType,

       getInterfaceId(aa.nedbaccessid,aa.ModuleOrIfIndex,aa.ObjectType,en.EnumString) AS 
InterfaceID,

       en.EnumString AS Type,

       aa.ActiveAlarmSeverity AS Severity,

       aa.ActiveAlarmTimeStamp AS RaisedTimeStamp,

       aa.ClearAlarmTimeStamp AS ClearTimeStamp,

       aa.ActiveAlarmAdditionalInfo AS Description,

       ack.AckUserName AS AckUser,

       
decode(to_char(ack.AckTimeStamp,'DD-MON-YYYY'),'01-JAN-0001',aa.ClearAlarmTimeStamp,

                                                      '','',

                                                         ack.acktimestamp) AS 
AckTimeStamp,

       aa.ActiveAlarmComment AS NoteDescription,

       aa.NEDBAccessID,

       to_date('') AS NEAlarmTimestamp,

       ack.IsAutoAck,

       aa.AlarmStatus,

       aa.NEAlarmClearTimestamp,

       aa.AlarmSeqNum,

       '' as PortName,

       aa.ModuleOrIfIndex   as IntegerInterfaceId,

       aa.ExternalCondition,

       aa.ActiveAlarmType,

       en.eventtype,

       aa.rowid AS myrow,

       aa.ModelType,

       aa.ObjectType,

       aa.StrObjInstance,

(select activevalue from ctm_config_table where sectionname='transportManager' and 
propertyname='ems-sys-id') as NEAliasID

		decode(to_char(nvl(aa.Direction,'')),'','N/A',aa.Direction) AS Direction,

       decode(to_char(nvl(aa.Location,'')),'','N/A',aa.Location) AS Location

FROM

       active_alarm_table aa,

       alarm_event_type_enum_table en,

       ack_table ack

Where aa.ActiveAlarmType = en.EnumIndex   AND

      aa.AckIndex        = ack.AckIndex   AND

      ((aa.alarmstatus=1 and aa.ackindex=0) or

       aa.alarmstatus=0 )                 and

      aa.nedbaccessid=0;

2.2.2 Admin_Job_View

create or replace view Admin_Job_View as

select

        ajt.jobid,

        ajt.taskid,

        ajt.jobtype,

        ajt.jobowner,

        ni.nesysid,

        ajt.jobstatus,

        ajt.creationtime,

        ajt.scheduledtime,

        ajt.starttime,

        ajt.endtime,

        ajt.usercomments,

        ajt.additionalinfo,

        ajt.nedbaccessid,

        ni.nemodeltype,

        ajt.rowid as myrow,

        ajt.operation_info,

        ajt.display_info,

	    ni.NEAliasID

from

        Admin_Job_Table ajt,

        ne_info_Table ni

where

        ajt.nedbaccessid = ni.nedbaccessid;

2.2.3 Alarm_Count_View (Deprecated)

create or replace view alarm_count_view as

select

       mn.nedbaccessid,

       mn.minoralarmcount,

       mj.majoralarmcount,

       cr.criticalalarmcount,

       wr.warningalarmcount,

       cl.clearedalarmcount,

       ack.ackalarmcount

  from (select nedbaccessid, totalalarmcount as majoralarmcount

          from alarm_count_table

         where alarmcounttype=6) mj,

       (select nedbaccessid, totalalarmcount as minoralarmcount

          from alarm_count_table

         where alarmcounttype=5) mn,

       (select nedbaccessid, totalalarmcount as criticalalarmcount

          from alarm_count_table

         where alarmcounttype=7) cr,

       (select nedbaccessid, totalalarmcount as warningalarmcount

          from alarm_count_table

         where alarmcounttype=4) wr,

       (select nedbaccessid, totalalarmcount as clearedalarmcount

          from alarm_count_table

         where alarmcounttype=-1) cl,

       (select nedbaccessid, totalalarmcount as ackalarmcount

          from alarm_count_table

         where alarmcounttype=-2) ack

  where mj.nedbaccessid=mn.nedbaccessid

    and mj.nedbaccessid=cr.nedbaccessid

    and mj.nedbaccessid=wr.nedbaccessid

    and mj.nedbaccessid=cl.nedbaccessid

    and mj.nedbaccessid=ack.nedbaccessid;

2.2.4 Alarm_Log_View

create or replace view alarm_log_view AS

SELECT

       ae.AlarmEventSeverity AS severityIcon,

       0 as AckIcon,

       ''                as NoteIcon,

       ae.ServiceEffecting,

       ae.EventIndex as ID,

       nvl(ni.NESysID,'CTM')  AS NetworkElement,

       ae.PhysicalLoc as Physicalloc,

       GetModuleName(ae.nedbaccessid, ae.moduletype, en.EventType) AS ModuleType,

       GetInterfaceId(ae.nedbaccessid, ae.ModuleOrIfIndex, ae.ObjectType, en.EnumString) 
AS InterfaceID,

       en.EnumString AS Type,

       ec.EnumString AS Cause,

       ae.AlarmEventSeverity AS severity,

       ae.AlarmEventTimeStamp AS RaisedTimeStamp,

       to_date('')  as ClearTimestamp,

       ae.OtherInfo AS Description,

       '' as Ackuser,

       to_date('')  as AckTimestamp,

       ''           as NoteDescription,

       ae.Nedbaccessid as Nedbaccessid,

       decode(ae.nedbaccessid,0,to_date(''),

       ae.NEEventtimestamp) as NeAlarmTimestamp,

       -1 as Alarmstatus,

       2 as IsAutoAck,

       to_char('Event')      as EventOrAlarm,

       to_date('') as NEAlarmClearTimestamp,

       ''          as CTMDuration,

       GetPortName(ae.nedbaccessid, ae.physicalloc, ae.ModuleOrIfindex, ae.Moduletype) as 
PortName,

       ae.ModuleOrIfIndex  as IntegerInterfaceId,

       en.eventtype,

	   nvl(ni.NEAliasID,'CTM')  as NEAliasID,

       ae.rowid AS myrow,

       ae.ModelType,

       ae.ObjectType,

       ae.StrObjInstance,

	   decode(to_char(nvl(ae.Direction,'')),'','N/A',ae.Direction) AS Direction,        
       decode(to_char(nvl(ae.Location,'')),'','N/A',ae.Location) AS Location,

	''		as ExternalCondition

FROM

       ne_info_table ni,

       alarm_event_table ae,

       alarm_event_type_enum_table en,

       alarm_event_cause_table ec

WHERE

       ae.NEDBAccessID    = ni.NEDBAccessID(+) AND

       ae.AlarmEventType  = en.EnumIndex    AND

       ae.AlarmEventCause = ec.EnumIndex

UNION

SELECT

      aa.ActiveAlarmSeverity   as SeverityIcon,

      aa.AckIndex        as AckIcon,

      aa.ActiveAlarmComment       as NoteIcon,

      aa.ActiveAlarmServeff  as ServiceEffecting,

      aa.ActiveAlarmIndex    as ID,

      nvl(ni.NESysID,(select activevalue from ctm_config_table where 
sectionname='transportManager' and propertyname='ems-sys-id'))  as NetworkElement,

      aa.Physicalloc    as Physicalloc,

      GetModulename(aa.nedbaccessid, aa.moduletype, en.EventType)  as ModuleType,

      GetInterfaceId(aa.nedbaccessid, aa.ModuleOrIfIndex, aa.ObjectType, en.EnumString)       
as InterfaceId,

      en.EnumString           as Type,

      ''             as Cause,

      aa.ActiveAlarmSeverity       as Severity,

      aa.ActiveAlarmTimeStamp  as RaisedTimestamp,

      aa.ClearAlarmTimeStamp   as ClearTimestamp,

      aa.ActiveAlarmAdditionalInfo      as Description,

      ack.AckUserName          as AckUser,

      decode(to_char(ack.AckTimeStamp,'DD-MON-YYYY'),'01-JAN-0001',aa.ClearAlarmTimeStamp,

                                                     '','',

                                                        ack.acktimestamp) AS AckTimeStamp,

      aa.ActiveAlarmComment  as NoteDescription,

      aa.NEDBAccessID     as Nedbaccessid,

      decode(aa.nedbaccessid,0,to_date(''),

                               aa.NeAlarmTimestamp) as NeAlarmtimestamp,

      aa.AlarmStatus      as AlarmStatus,

      ack.IsAutoAck        as IsAutoAck,

      to_char('Alarm')          as EventOrAlarm,

      aa.NEAlarmClearTimestamp  as NEAlarmClearTimestamp,

      ctmduration(aa.ClearAlarmTimeStamp-aa.ActiveAlarmTimeStamp) as CTMDuration,

      GetPortName(aa.nedbaccessid, aa.physicalloc, aa.ModuleOrIfindex, aa.moduletype) as 
PortName,

      aa.ModuleOrIfIndex   as IntegerInterfaceId,

      en.eventtype,

nvl(ni.NEAliasID,(select activevalue from ctm_config_table where 
sectionname='transportManager' and propertyname='ems-sys-id'))  as NEAliasID,

      aa.rowid            as Myrow,

      aa.ModelType,

      aa.ObjectType,

      aa.StrObjInstance

	decode (to_char(nvl(aa.Direction,'')),'','N/A',aa.Direction) AS Direction,

      decode (to_char(nvl(aa.Location,'')),'','N/A',aa.Location) AS Location,

	aa.ExternalCondition

From

       ne_info_table ni,

       active_alarm_table aa,

       alarm_event_type_enum_table en,

       ack_table ack

Where

       aa.NEDBAccessID    = ni.NEDBAccessID(+) AND

       aa.ActiveAlarmType = en.EnumIndex    AND

       aa.AckIndex        = ack.AckIndex    AND

       aa.alarmstatus     = 1               AND

       aa.ackindex        <> 0;

2.2.5 Audit_Log_View

create or replace view audit_log_view AS

SELECT nvl(n.nesysid,'CTM') as NESYSID,

       nvl(t.username, 'Internal') as username,

       t.timestamp,

       t.module,

       t.classname,

       0 as linenumber,

       (t.actionname || t.logmessage) as LogMessage,

       nvl(t.nedbaccessid,0)  as nedbaccessid,

       t.service,

	   nvl(n.NEAliasID,'CTM') as NEAliasID,

       t.rowid as Myrow

FROM   transaction_log_table t,

       ne_info_table         n

WHERE  t.nedbaccessid=n.nedbaccessid(+);

2.2.6 BLSR_Table_View

create or replace view BLSR_TABLE_VIEW as

SELECT

        blsr.BLSRID as BLSRID,

    	blsr.RINGID as RINGID,

        blsr.RINGTYPE as RINGTYPE,

        blsr.LINERATE as LINERATE,

        blsr.STATUS as STATUS,

        blsr.RINGREVERSION as RINGREVERSION,

        blsr.SPANREVERSION as SPANREVERSION,

        blsr.NODES as NODES,

        node.NODEID as NODEID,

		blsr.rowid as myrow

FROM    BLSR_TABLE blsr,

        BLSR_NODE node

WHERE

        blsr.BLSRID = node.BLSRID;

2.2.7 Children_View

CREATE OR REPLACE VIEW children_view AS

SELECT

        eit.NEDBAccessId,

        eit.PhysicalLoc,

        eit.IfIndex,

        eit.ModuleType,

        mm.ParentIndex,

        eit.rowid as myrow,

        eit.ModelType,

        eit.ObjectIndex,

        eit.StrObjInstance

FROM

        eqpt_info_table eit,

        objecttypetable mm

WHERE

        eit.ObjectIndex = mm.ObjectIndex AND eit.ModelType = mm.ModelType;

2.2.8 Circuit_Ctpspan_View

create or replace view circuit_ctpspan_view as

select

       span.cktnodeid,

       span.cktuniqueid,

       ctpsrc.cktctpnodeid as spansrcnodeid,

       ctpdest.cktctpnodeid as spandestnodeid,

       span.cktlinkid as cktlinkid

  from circuit_ctp_tbl ctpsrc,

       circuit_ctp_tbl ctpdest,

       circuit_span_bs_tbl   span

 where ctpsrc.ctpaccessid=span.cktspansrcctpaccessid and

       ctpdest.ctpaccessid=span.cktspandstctpaccessid;

2.2.9 Circuit_Ctp_Drop_View

create or replace view circuit_ctp_drop_view as

select ne.nesysid,

       ctp.cktnodeid,

       ctp.cktuniqueid,

       ctp.cktctpnodeid,

       ctp.cktctpPhysicalLoc,

       ctp.CktctpIfIndex,

       ctp.cktctpModuleType,

       ctp.cktctpDropProt,

       ctp.CktctpPathProt,

       ctp.cktctpnedbaccessid,

       ctp.CktctpModelType,

       ctp.CktctpObjectType

from ne_info_table ne,

     circuit_ctp_tbl ctp

where  ne.nedbaccessid=ctp.CKTCTPNeDBAccessId

  and  ctp.ctp_type=2;

2.2.10 Circuit_Ctp_Sec_View

create or replace view circuit_ctp_sec_view as

select ne.nesysid,

       ctp.cktnodeid,

       ctp.cktuniqueid,

       ctp.cktctpnodeid,

       ctp.cktctpPhysicalLoc,

       ctp.CktctpIfIndex,

       ctp.cktctpModuleType,

       ctp.cktctpDropProt,

       ctp.cktctpnedbaccessid,

       ctp.CktctpModelType,

       ctp.CktctpObjectType

from ne_info_table ne,

     circuit_ctp_tbl ctp

where  ne.nedbaccessid=ctp.CKTCTPNeDBAccessId

  and  ctp.ctp_type=1;

2.2.11 Circuit_Ctp_Src_View

create or replace view circuit_ctp_src_view as

select ne.nesysid,

       ctp.cktnodeid,

       ctp.cktuniqueid,

       ctp.cktctpnodeid,

       ctp.cktctpPhysicalLoc,

       ctp.CktctpIfIndex,

       ctp.cktctpModuleType,

       ctp.cktctpDropProt,

       ctp.cktctpnedbaccessid,

       ctp.CktctpModelType,

       ctp.CktctpObjectType

from ne_info_table ne,

     circuit_ctp_tbl ctp

where  ne.nedbaccessid=ctp.CKTCTPNeDBAccessId

  and  ctp.ctp_type=0;

2.2.12 Circuit_Ctp_View

CREATE OR REPLACE VIEW CIRCUIT_CTP_VIEW AS

SELECT

        c.CTPACCESSID as CTPACCESSID,

        c.CTP_TYPE as CTP_TYPE,

        c.CKTNODEID as CKTNODEID,

        c.CKTUNIQUEID as CKTUNIQUEID,

        c.CKTCTPNODEID as CKTCTPNODEID,

        c.CKTCTPNEDBACCESSID as CKTCTPNEDBACCESSID,

        c.CKTCTPMODULETYPE as CKTCTPMODULETYPE,

        c.CKTCTPIFINDEX as CKTCTPIFINDEX,

        c.CKTCTPOBJECTTYPE as CKTCTPOBJECTTYPE,

        c.CKTCTPPHYSICALLOC as CKTCTPPHYSICALLOC,

        c.CKTCTPMODELTYPE as CKTCTPMODELTYPE,

        c.CKTCTPDROPPROT as CKTCTPDROPPROT,

        c.CKTCTPPATHPROT as CKTCTPPATHPROT,

        c.CKTCTPDROPPROTTYPE as CKTCTPDROPPROTTYPE,

        c.CKTCTPPORTNAME as CKTCTPPORTNAME,

        c.CKTCTPADDITIONALINFO as CKTCTPADDITIONALINFO,

        c.NPID as NPID,

        ckt.ROWID as MYROW

from    circuit_ctp_tbl c,

        circuit_bs_tbl ckt

where

        c.CktNodeId = ckt.CktNodeId(+) and

        c.CktUniqueId = ckt.CktUniqueId(+);

2.2.13 Circuit_Ctp_XC_View

CREATE OR REPLACE VIEW CIRCUIT_CTP_XC_VIEW AS

SELECT  DISTINCT

		b.CKTNAME as CktName,

		a.CKTNODEID as NodeId,

        a.CKTUNIQUEID as UniqueId,

		a.CKTCTPNODEID as NENodeId,

		a.CKTCTPNEDBACCESSID as NEDBAccessId,

		b.CKTDIRECTION as CktDirection,

		b.CKTTYPE as CktType,

		b.CKTSIZE as CktSize

from    circuit_ctp_tbl a,

		circuit_bs_tbl b,

		circuit_ctp_tbl z

where

		a.CTPACCESSID  != z.CTPACCESSID and

        a.CKTCTPNODEID =  z.CKTCTPNODEID and

		a.CKTNODEID    =  z.CKTNODEID and

		a.CKTUNIQUEID  =  z.CKTUNIQUEID and

		a.CKTNODEID    =  b.CKTNODEID and

		a.CKTUNIQUEID  =  b.CKTUNIQUEID and

		b.CKTSTATE     != 12 and

		b.CKTTYPE not in (5, 6, 21, 22);

2.2.14 Circuit_Node_GUI_View

CREATE OR REPLACE VIEW circuit_node_gui_view AS

WITH

   CTP AS ( select min(ctp_tb.CTPACCESSID)  from circuit_ctp_tbl ctp_tb, circuit_bs_tbl 
circ where ctp_tb.CktNodeId = circ.CktNodeId and ctp_tb.CktUniqueId = circ.CktUniqueId and 
ctp_tb.ctp_type = 2 group by  circ.cktname, circ.CktUniqueID

SELECT

        c.CktName as Name,

        c.CktType as Type,

        c.CktSize as CktSize,

        c.CktDirection as Direction,

        c.CktState as State,

        (select nesysid from ne_info_table where nedbaccessid=src.CKTCTPNeDBAccessId)  as 
SrcNodeid,

        src.cktctpnodeid as cktsrcnodeid,

        nvl(getModulename1(src.cktctpnodeid, src.cktctpModuleType),'SYSTEM')  as 
SrcModuleType,

        src.cktctpPhysicalLoc as SrcPhysicalLoc,

        src.CktctpIfIndex as SrcInterfaceId,

        nvl(getModulename1(sec.cktctpnodeid, sec.cktctpModuleType),'SYSTEM') as 
SecSrcModuleType,

        sec.CktctpPhysicalLoc as SecSrcPhysicalLoc,

        sec.CktctpIfIndex as SecSrcInterfaceId,

        collapseddestnodename(c.CKTNODEID,c.CKTUNIQUEID) as destnodename,

        collapsedmodulename(c.CKTNODEID,c.CKTUNIQUEID)as cktdestModuleName,

        collapsedphy(c.CKTNODEID,c.CKTUNIQUEID) as DestPhysicalLoc,

        collapsedifindex(c.CKTNODEID,c.CKTUNIQUEID) as DestInterfaceId,

        c.CktIsMonitor as Ismonitor,

        src.CktctpDropProt as SrcDropProt,

        c.CktNodeId as NodeId,

        c.CktUniqueId as UniqueId,

        c.CktDescription as Description,

        c.CustomerId as CustomerId,

        c.ServiceId as ServiceId,

        dest.cktctpnodeid as DestNodeId,

        c.CktEnhancedState,

        c.CktProtectionType,

        dest.CktctpPathProt  as DestPathProtection,

        c.cktComment as CircuitNote,

        get_nesysid(sec.CKTCTPNeDBAccessId) as SecSrcNodeid,

        sec.cktctpnodeid as cktsecsrcnodeid,

        eqptinfoport(src.cktctpnedbaccessid,src.cktctpphysicalloc,0,src.cktctpmoduletype) 
        as CktSrcPortName,

        eqptinfoport(sec.cktctpnedbaccessid,sec.cktctpphysicalloc,0,sec.cktctpmoduletype) 
        as CktSecSrcPortName,

collapsedeqptportname(c.CKTNODEID,c.CKTUNIQUEID) as CktDestPortName,

		 ( select count(*)  from circuit_vlan_tbl vlan where vlan.cktnodeid=c.cktnodeid 
and vlan.cktuniqueid=c.cktuniqueid ) as NoOfVlans,  /* *@INDEX A 5 */

        nvl(vcg.VCGDirection,-1) as VCGDirection,

        nvl(vcg.ParentCktNodeId,-1) as ParentCktNodeId,

        nvl(vcg.ParentCktUniqueId,-1) as ParentCktUniqueId,

        c.CktOchncChannel,

        c.CktOchncDir,

        src.CktctpModelType as SrcModelType,

        src.CktctpObjectType as SrcObjectType,

        sec.CktctpModelType as CktSecSrcModelType,

        sec.CktctpObjectType as SecSrcObjectType,

        collapsedmodeltype(c.CKTNODEID,c.CKTUNIQUEID) as DestModelType,

        collapsedobjectindex(c.CKTNODEID,c.CKTUNIQUEID) as DestObjectType,

        c.isDuplicateName,

        nvl(getPortModuleName(src.cktctpnodeid, src.cktctpModuleType),'SYSTEM')  as 
SrcPortModuleType,

        nvl(getPortModuleName(sec.cktctpnodeid, sec.cktctpModuleType),'SYSTEM') as 
SecSrcPortModuleType,

        collapsedportmodulename(c.CKTNODEID,c.CKTUNIQUEID)as cktdestPortModuleName,

        c.rowid as myrow,

        dest.cktctpPhysicalLoc as CktDestPhysicalLoc,

        nvl(getPortModuleName(dest.cktctpnodeid, dest.cktctpModuleType),'SYSTEM') as 
DestPortModuleType,

        nvl(vcg.VCATSTATE,-1) as VcatState,

        src.cktctpModuleType as CktSrcModuleType,

        (select count(*) from circuit_span_bs_tbl where cktnodeid=c.cktnodeid and 
cktuniqueid=c.cktuniqueid) as NoOfSpans,

	    c.CktAliasName as alias_name,

        c.CktAdditionalInfo,

	c.isOpenVcat,

        c.isOverlay,

		get_nesysid(dest.CKTCTPNeDBAccessId) as DestSysId

from    circuit_bs_tbl c,

        circuit_ctp_tbl src,

        circuit_ctp_tbl sec,

        circuit_ctp_tbl dest,

        circuit_vcg_tbl vcg

where

        c.CktNodeId             = src.CktNodeId(+)  and

        c.CktUniqueId           = src.CktUniqueId(+) and

	0 = src.ctp_type(+) and

        c.CktNodeId             = sec.CktNodeId(+) and

        c.CktUniqueId           = sec.CktUniqueId(+) and

	1 = sec.ctp_type(+) and

        c.CktNodeId             = dest.CktNodeId(+) and

        c.CktUniqueId           = dest.CktUniqueId(+) and

	2 = dest.ctp_type(+) and

	(dest.ctpaccessid IS NULL OR dest.ctpaccessid IN ( select * from CTP ))

and

        c.CktNodeId             = vcg.CktNodeId(+) and

        c.CktUniqueId           = vcg.CktUniqueId(+) and

        c.cktstate             !=12;

2.2.15 Circuit_Node_GUI_View_CTM46

CREATE OR REPLACE VIEW circuit_node_gui_view_ctm46 AS

SELECT

        c.CktName as Name,

        c.CktType as Type,

        c.CktSize as CktSize,

        c.CktDirection as Direction,

        c.CktState as State,

        (select nesysid from ne_info_table where nenodeid=c.cktsrcnodeid and 
c.cktsrcnodeid !=0 )  as SrcNodeid,

        c.cktsrcnodeid,

        nvl(getSlotModuleName(c.cktsrcnodeid, c.CktSrcModuleType),'SYSTEM')  as 
SrcModuleType,

        c.CktSrcPhysicalLoc as SrcPhysicalLoc,

        c.CktSrcIfIndex as SrcInterfaceId,

        nvl(getSlotModuleName(c.cktsecsrcnodeid, c.CktSecSrcModuleType),'SYSTEM') as 
SecSrcModuleType,

        c.CktSecSrcPhysicalLoc as SecSrcPhysicalLoc,

        c.CktSecSrcIfIndex as SecSrcInterfaceId,

        collapseddestnodename(c.CKTNODEID,c.CKTUNIQUEID) as destnodename,

        collapsedmodulename(c.CKTNODEID,c.CKTUNIQUEID)as cktdestModuleName,

        collapsedphy(c.CKTNODEID,c.CKTUNIQUEID) as DestPhysicalLoc,

        collapsedifindex(c.CKTNODEID,c.CKTUNIQUEID) as DestInterfaceId,

        c.CktIsMonitor as Ismonitor,

        c.CktSrcDropProt as SrcDropProt,

        c.CktNodeId as NodeId,

        c.CktUniqueId as UniqueId,

        c.CktDescription as Description,

        c.CustomerId as CustomerId,

        c.ServiceId as ServiceId,

        dest.cktdestnodeid as DestNodeId,

        spantbl.CktSpanSrcNodeId,

        spantbl.CktSpanDestNodeId,

        spantbl.cktlinkid,

        c.CktEnhancedState,

        c.CktProtectionType,

        dest.CktDestPathProt  as DestPathProtection,

        c.cktComment as CircuitNote,

        (select nesysid from ne_info_table where nenodeid=c.cktsecsrcnodeid and 
c.cktsecsrcnodeid !=0 )  as SecSrcNodeid,

        c.cktsecsrcnodeid,

        
eqptinfoport(getNEDBACCESSID(c.CKTSRCNODEID),c.CKTSRCPHYSICALLOC,0,c.CKTSRCMODULETYPE ) as 
CktSrcPortName,

        
eqptinfoport(getNEDBACCESSID(c.CKTSECSRCNODEID),c.CKTSECSRCPHYSICALLOC,0,c.CKTSECSRCMODULE
TYPE ) as CktSecSrcPortName,

        collapsedeqptportname(c.CKTNODEID,c.CKTUNIQUEID) as CktDestPortName,

       (select count(*) from circuit_vlan_tbl where cktnodeid=c.cktnodeid and 
cktuniqueid=c.cktuniqueid) as NoOfVlans,

        nvl(vcg.VCGDirection,-1) as VCGDirection,

        nvl(vcg.ParentCktNodeId,-1) as ParentCktNodeId,

        nvl(vcg.ParentCktUniqueId,-1) as ParentCktUniqueId,

        c.CktOchncChannel,

        c.CktOchncDir,

        c.CktSrcModelType as SrcModelType,

        c.CktSrcObjectType as SrcObjectType,

        c.CktSecSrcModelType as CktSecSrcModelType,

        c.CktSecSrcObjectType as SecSrcObjectType,

        collapsedmodeltype(c.CKTNODEID,c.CKTUNIQUEID) as DestModelType,

        collapsedobjectindex(c.CKTNODEID,c.CKTUNIQUEID) as DestObjectType,

        c.isDuplicateName,

        nvl(getPortModuleName(c.cktsrcnodeid, c.CktSrcModuleType),'SYSTEM')  as 
SrcPortModuleType,

        nvl(getPortModuleName(c.cktsecsrcnodeid, c.CktSecSrcModuleType),'SYSTEM') as 
SecSrcPortModuleType,

        collapsedportmodulename(c.CKTNODEID,c.CKTUNIQUEID)as cktdestPortModuleName,

        c.rowid as myrow,

        dest.cktdestphysicalloc as CktDestPhysicalLoc,

        nvl(getPortModuleName(dest.cktdestnodeid, dest.CktDestModuleType),'SYSTEM') as 
DestPortModuleType,

        spantbl.CktSpanSrcPhysicalLoc,

        spantbl.CktSpanDestPhysicalLoc

From

        circuit_tbl c,

        circuit_vcg_tbl vcg,

        circuit_dest_tbl dest,

        circuit_span_tbl spantbl

where

        c.CktNodeId             = dest.CktNodeId(+)  and

        c.CktUniqueId           = dest.CktUniqueId(+) and

        c.CktNodeId             = spantbl.CktNodeId(+) and

        c.CktUniqueId           = spantbl.CktUniqueId(+) and

        c.CktNodeId             = vcg.CktNodeId(+) and

        c.CktUniqueId           = vcg.CktUniqueId(+) and

        c.cktState              != 12 ;

2.2.16 Circuit_Node_View

CREATE OR REPLACE VIEW circuit_node_view AS

SELECT

        c.CktName as Name,

        c.CktType as Type,

        c.CktSize as CktSize,

        c.CktDirection as Direction,

        c.CktState as State,

        (select nesysid from ne_info_table where nenodeid=c.cktsrcnodeid and 
c.cktsrcnodeid !=0)  as SrcNodeid,

        nvl(getModulename1(c.cktsrcnodeid, c.CktSrcModuleType),'SYSTEM')  as 
SrcModuleType,

        c.CktSrcPhysicalLoc as SrcPhysicalLoc,

        c.CktSrcModelType as SrcModelType,

        c.CktSrcIfIndex as SrcInterfaceId,

        c.CktSrcObjectType as SrcObjectType,

        nvl(getModulename1(c.cktsecsrcnodeid, c.CktSecSrcModuleType),'SYSTEM') as 
SecSrcModuleType,

        c.CktSecSrcPhysicalLoc as SecSrcPhysicalLoc,

        c.CktSecSrcModelType as SecSrcModelType,

        c.CktSecSrcIfIndex as SecSrcInterfaceId,

        c.CktSecSrcObjectType as SecSrcObjectType,

        (select nesysid from ne_info_table where nenodeid=dest.cktdestnodeid and 
dest.cktdestnodeid != 0) as destnodeid,

        nvl(getModulename1(dest.cktdestnodeid, dest.CktDestModuleType),'SYSTEM') as 
DestModuleType,

        dest.CktDestPhysicalLoc as DestPhysicalLoc,

        dest.CktDestIfIndex as DestInterfaceId,

        dest.CktDestObjectType as DestObjectType,

        c.CktIsMonitor as Ismonitor,

        c.CktSrcDropProt as SrcDropProt,

        dest.CktDestDropProt as DestDropProt,

        dest.CktDestPathProt as DestPathProt,

        0  as Vlanid,

        c.CktName as vlanname,

        c.CktNodeId as NodeId,

        c.CktUniqueId as UniqueId,

        c.CktDescription as Description,

        c.CustomerId as CustomerId,

        c.ServiceId as ServiceId,

        spantbl.CktSpanSrcNodeId,

        spantbl.CktSpanDestNodeId,

        spantbl.cktlinkid,

        c.CktEnhancedState,

        c.CktProtectionType,

        c.rowid as myrow

from

        circuit_tbl c,

        circuit_dest_tbl dest,

        circuit_span_tbl spantbl

where

        c.CktNodeId             = dest.CktNodeId(+)  and

        c.CktUniqueId           = dest.CktUniqueId(+) and

        c.CktNodeId             = spantbl.CktNodeId(+) and

        c.CktUniqueId           = spantbl.CktUniqueId(+) and

        c.cktState              != 12 ;

2.2.17 Circuit_Span_View

create or replace view Circuit_Span_View as

select

        circuit.CktName as Name,

        circuit.CktType as Type,

        circuit.CktSize as CktSize,

        ne_src.NESYSID as SrcNodeId,

        nvl(getModuleName2( ctp_src.CKTCTPMODULETYPE, ctp_src.CKTCTPMODELTYPE ) ,'SYSTEM' 
) as SrcModuleType,

        ctp_src.CKTCTPPHYSICALLOC as SrcPhysicalLoc,

        ctp_src.CKTCTPIFINDEX as SrcInterfaceId,

        ne_dst.NESYSID as DestNodeId,

        nvl(getModuleName2( ctp_dst.CKTCTPMODULETYPE, ctp_dst.CKTCTPMODELTYPE ) ,'SYSTEM' 
) as DestModuleType,

        ctp_dst.CKTCTPPHYSICALLOC as DestPhysicalLoc,

        ctp_dst.CKTCTPIFINDEX as InterfaceId,

        spancircuit.CKTSPANSRCSTATE,

        spancircuit.CKTSPANDESTSTATE,

        spancircuit.IsCktForwarding as Forwarding,

        spancircuit.IsCktSpanInUPSR as InUpsr,

        spancircuit.IsCktSpanActive as active,

        spancircuit.CktSpanProtOp as ProtOp,

        spancircuit.CktNodeId as NodeId,

        spancircuit.CktUniqueId as UniqueId,

        ne_src.NEDBACCESSID as NEDBAccessid,

        ne_dst.NEDBACCESSID as DestNEDBAccessid,

        spancircuit.CktLinkId as linkid,

        ctp_src.CKTCTPNODEID as SrcNENodeId,

        ctp_dst.CKTCTPNODEID as DestNENodeID,

        ctp_src.CKTCTPMODULETYPE as SrcNEModuleType,

        ctp_dst.CKTCTPMODULETYPE as DestNEModuleType,

        ctp_src.CKTCTPPORTNAME as SrcPortName,

        ctp_dst.CKTCTPPORTNAME as DestPortName,

        ctp_src.CKTCTPMODELTYPE as SrcModelType,

        ctp_src.CKTCTPOBJECTTYPE as SrcObjectType,

        ctp_dst.CKTCTPMODELTYPE as DestModelType,

        ctp_dst.CKTCTPOBJECTTYPE as DestObjectType,

		nvl(getPortModuleName2( ctp_src.CKTCTPMODULETYPE, ctp_src.CKTCTPMODELTYPE ) 
,'SYSTEM' ) as SrcPortModuleType,

		nvl(getPortModuleName2( ctp_dst.CKTCTPMODULETYPE, ctp_dst.CKTCTPMODELTYPE ) 
,'SYSTEM' ) as DestPortModuleType,

        spancircuit.rowid as myrow

from

    CIRCUIT_BS_Tbl circuit,

    circuit_ctp_tbl ctp_src,

    circuit_ctp_tbl ctp_dst,

    circuit_span_BS_tbl spancircuit,

    ne_info_table ne_src,

    ne_info_table ne_dst

where

        ctp_src.CTPACCESSID = spancircuit.CKTSPANSRCCTPACCESSID

  and   ctp_dst.CTPACCESSID = spancircuit.CKTSPANDSTCTPACCESSID

  and   spancircuit.CktNodeId = circuit.CktNodeId

  and   spancircuit.CktUniqueId = circuit.CktUniqueId

  and   ne_src.nenodeid = ctp_src.CKTCTPNODEID

  and   ne_dst.nenodeid = ctp_dst.CKTCTPNODEID

  and	((ctp_src.ctp_type = 3 and ctp_dst.ctp_type = 4);

2.2.18 Circuit_Span_View_CTM46

create or replace view Circuit_Span_View_ctm46 as

select

        circuit.CktName as Name,

        circuit.CktType as Type,

        circuit.CktSize as CktSize,

        neInfo1.NESysID as SrcNodeId,

        nvl(getSlotModuleName(spancircuit.cktspansrcnodeid, 
spancircuit.CktspanSrcModuleType),'SYSTEM') as SrcModuleType,

        spancircuit.CktSpanSrcPhysicalLoc as SrcPhysicalLoc,

        spancircuit.CktSpanSrcIfIndex as SrcInterfaceId,

        neInfo2.NESysID as DestNodeId,

        nvl(getSlotModuleName(spancircuit.cktspandestnodeid, 
spancircuit.CktspandestModuleType),'SYSTEM') as DestModuleType,

        spancircuit.CktSpanDestPhysicalLoc as DestPhysicalLoc,

        spancircuit.CktSpanDestIfIndex as InterfaceId,

        spancircuit.CktSpanSrcState as SrcState,

        spancircuit.CktSpanDestState as DestState,

        spancircuit.IsCktForwarding as Forwarding,

        spancircuit.IsCktSpanInUPSR as InUpsr,

        spancircuit.IsCktSpanActive as active,

        spancircuit.CktSpanProtOp as ProtOp,

        spancircuit.CktNodeId as NodeId,

        spancircuit.CktUniqueId as UniqueId,

        neinfo1.NEDBAccessid,

        neinfo2.NEDBAccessid as DestNEDBAccessid,

        spancircuit.CktLinkId as linkid,

        spancircuit.CktSpanSrcNodeId as SrcNENodeId,

        spancircuit.CktSpanDestNodeId as DestNENodeID,

        spancircuit.CktspanSrcModuleType as SrcNEModuleType,

        spancircuit.CktspandestModuleType as DestNEModuleType,

 
eqptinfoport(getNEDBACCESSID(spancircuit.CKTSPANSRCNODEID),spancircuit.CKTSPANSRCPHYSICALL
OC,0,spancircuit.CKTSPANSRCMODULETYPE) as SrcPortName,

 
eqptinfoport(getNEDBACCESSID(spancircuit.CKTSPANDESTNODEID),spancircuit.CKTSPANDESTPHYSICA
LLOC,0,spancircuit.CKTSPANDESTMODULETYPE) as DestPortName,

        spancircuit.CktSpanSrcModelType as SrcModelType,

        spancircuit.CktSpanSrcObjectType as SrcObjectType,

        spancircuit.CktSpanDestModelType as DestModelType,

        spancircuit.CktSpanDestObjectType as DestObjectType,

        nvl(getPortModuleName(spancircuit.cktspansrcnodeid, 
spancircuit.CktspanSrcModuleType),'SYSTEM') as SrcPortModuleType,

        nvl(getPortModuleName(spancircuit.cktspandestnodeid, 
spancircuit.CktspandestModuleType),'SYSTEM') as DestPortModuleType,

        spancircuit.rowid as myrow

from

        CIRCUIT_TBL circuit,

        NE_INFO_TABLE neInfo1,

        NE_INFO_TABLE neInfo2,

        circuit_span_tbl spancircuit

where

        spancircuit.CktNodeId = circuit.CktNodeId and

        spancircuit.CktUniqueId = circuit.CktUniqueId and

        spancircuit.CktSpanSrcNodeID = neInfo1.NENodeID and

        spancircuit.CktSpanDestNodeId = neInfo2.NENodeID;

2.2.19 Circuit_View

create or replace view Circuit_View AS

select

        c.CktName as Name,

        c.CktType as Type,

        c.CktSize as CktSize,

        c.CktDirection as Direction,

        c.CktState as State,

        (select nesysid from ne_info_table where nenodeid=c.cktsrcnodeid and 
c.cktsrcnodeid !=0)  as SrcNodeid,

        nvl(getModulename1(c.cktsrcnodeid, c.CktSrcModuleType),'SYSTEM')  as 
SrcModuleType,

        c.CktSrcPhysicalLoc as SrcPhysicalLoc,

        c.CktSrcIfIndex as SrcInterfaceId,

        nvl(getModulename1(c.cktsecsrcnodeid, c.CktSecSrcModuleType),'SYSTEM') as 
SecSrcModuleType,

        c.CktSecSrcPhysicalLoc as SecSrcPhysicalLoc,

        c.CktSecSrcIfIndex as SecSrcInterfaceId,

        (select nesysid from ne_info_table where nenodeid=dest.cktdestnodeid and 
dest.cktdestnodeid !=0 ) as destnodeid,

        nvl(getModulename1(dest.cktdestnodeid, dest.CktDestModuleType),'SYSTEM') as 
DestModuleType,

        dest.CktDestPhysicalLoc as DestPhysicalLoc,

        dest.CktDestIfIndex as DestInterfaceId,

        c.CktIsMonitor as Ismonitor,

        c.CktSrcDropProt as SrcDropProt,

        dest.CktDestDropProt as DestDropProt,

        dest.CktDestPathProt as DestPathProt,

        vlan.Cktvlanid  as Vlanid,

        vlan.cktvlanname  as Vlanname,

        c.CktNodeId as NodeId,

        c.CktUniqueId as UniqueId,

        c.CktDescription as Description,

        c.CustomerId as CustomerId,

        c.ServiceId as ServiceId,

        dest.CKTDESTNODEID as DESTNENODEID,

        c.CKTSRCNODEID as SRCNENODEID,

        c.CKTSECSRCNODEID as SECSRCNENODEID,

        c.CktEnhancedState,

        c.CktProtectionType,

        c.CktOchncChannel,

        c.CktOchncDir,

        c.CktSrcModelType as SrcModelType,

        c.CktSrcObjectType as SrcObjectType,

        c.CktSecSrcModelType as CktSecSrcModelType,

        c.CktSecSrcObjectType as SecSrcObjectType,

        dest.CktDestModelType as CktDestModelType,

        dest.CktDestObjectType as CktDestObjectType,

        c.rowid as myrow

from

        circuit_tbl c,

        circuit_dest_tbl dest,

        circuit_vlan_view vlan

where

        c.CktNodeId             = dest.CktNodeId(+)  and

        c.CktUniqueId           = dest.CktUniqueId(+) and

        c.CktNodeId             = vlan.CktNodeid   and

        c.CktUniqueid            = vlan.CktUniqueid;

2.2.20 Circuit_VLAN_View

CREATE OR REPLACE VIEW circuit_vlan_view AS

SELECT

        b.CktVlanId,

        b.CktVlanName,

        b.CktNodeId,

        b.CktUniqueId,

        a.rowid as myrow

FROM

        circuit_vlan_tbl b ,

        circuit_bs_tbl a

WHERE

        a.CktNodeId   = b.CktNodeId   AND

        a.CktUniqueId = b.CktUniqueId

UNION

SELECT

0,

'',

        a.CktNodeId,

        a.CktUniqueId,

        a.rowid as myrow

FROM

        circuit_bs_tbl a

WHERE   a.rowid NOT IN

                       (  SELECT a.rowid

                          FROM   circuit_bs_tbl a,

                                 circuit_vlan_tbl b

                          WHERE  a.CktNodeId   = b.CktNodeId   AND

                                 a.CktUniqueId = b.CktUniqueId

) ;

2.2.21 Ckt_Span_Dest_Mt_View

CREATE OR REPLACE VIEW ckt_span_dest_mt_view AS

SELECT DISTINCT

       cst.cktspandestnodeid as cktspandestnodeid,

       nvl(spandest.modulename,'System') as Modulename,

       cst.cktspandestphysicalloc,

       spandest.moduletype as ModuleType

FROM

      (select distinct

              mtt.modulename   as modulename,

              mtt.moduletype   as moduletype,

              cstbl.cktspandestphysicalloc as cktspandestphysicalloc,

              cstbl.cktspandestnodeid as cktspandestnodeid

         from module_type_table mtt,

              ons15454_ne_inventory_table cni,

              circuit_span_tbl cstbl,

              ne_info_table   ne

        where mtt.moduletype= cni.equipmenttype

          and 
cni.physicalloc=round(cstbl.cktspandestphysicalloc/power(2,16))*(power(2,16))

          and ne.nenodeid = cstbl.cktspandestnodeid

          and cni.nedbaccessid=ne.nedbaccessid

          and ne.nemodeltype=mtt.modeltype

          and (mtt.modeltype=3 or mtt.modeltype=5 or mtt.modeltype=14 or mtt.modeltype=18)

      ) spandest,

      circuit_span_tbl cst

  WHERE cst.cktspandestnodeid = spandest.cktspandestnodeid(+)

    AND cst.cktspandestphysicalloc = spandest.cktspandestphysicalloc(+);

2.2.22 Ckt_Span_Src_Mt_View

CREATE OR REPLACE VIEW ckt_span_src_mt_view AS

SELECT DISTINCT

       cst.cktspansrcnodeid as cktspansrcnodeid,

       nvl(spansrc.modulename,'System') as Modulename,

       cst.cktspansrcphysicalloc,

       spansrc.moduletype as ModuleType

FROM

      (select distinct

              mtt.modulename   as modulename,

              mtt.moduletype   as moduletype,

              cstbl.cktspansrcnodeid as cktspansrcnodeid,

              cstbl.cktspansrcphysicalloc as cktspansrcphysicalloc

         from module_type_table mtt,

              ons15454_ne_inventory_table cni,

              circuit_span_tbl cstbl,

              ne_info_table ne

        where mtt.moduletype= cni.equipmenttype

          and cni.physicalloc=round(cstbl.cktspansrcphysicalloc/power(2,16))*(power(2,16))

          and ne.nenodeid=cstbl.cktspansrcnodeid

          and cni.nedbaccessid=ne.nedbaccessid

          and ne.nemodeltype=mtt.modeltype

          and (mtt.modeltype=3 or mtt.modeltype=5 or mtt.modeltype=14 or mtt.modeltype=18)

      ) spansrc,

      circuit_span_tbl cst

  WHERE cst.cktspansrcnodeid=spansrc.cktspansrcnodeid(+)

    AND cst.cktspansrcphysicalloc=spansrc.cktspansrcphysicalloc(+) ;

2.2.23 Client_User_View

create or replace view Client_User_VIEW As

select

       username  as Username

from user_table

union

select distinct

      username  as Username

from  deleted_user_table;

2.2.24 CTM_Unknown_User_View

create or replace view CTM_Unknown_User_View as

select

	CTMUnknownUserTable.IPAddress,

	CTMUnknownUserTable.FailedAttempts,

   CTMUnknownUserTable.LastLoginFailTime,

	decode(CTMUnknownUserTable.LockedState, 0, 'Unlocked',

                                           1, 'Locked') AS LockedState,

	CTMUnknownUserTable.ROWID AS MYROW

FROM

	CTM_Unknown_User_Table CTMUnknownUserTable;

2.2.25 Data_Card_View

CREATE OR REPLACE VIEW DATA_CARD_VIEW AS

SELECT

	B.NEID,

	B.SLOT,

	B.PORTNUM,

	B.SUBIF,

	B.BG,

	A.CIR,

	A.CIR_BURST,

	A.PIR,

	A.PIR_BURST,

	A.CCOSTRANSMIT

FROM

	INTF_SUB_INTF_VIEW B,

	QOS_VIEW A

WHERE

	A.NEID(+) = B.NEID and A.SLOT(+) = B.SLOT and A.PORTNUM(+) = B.PORTNUM and A.BG(+) = 
B.BG;

2.2.26 Domain_NE_Inventory_View

create or replace view domain_ne_inventory_view as

select

       distinct

	ne.NESysID,

       ne.NEModelType,

	getNEIPAddr(ne.NEDBAccessID) NEIPAddr,

	getNEIPAddr (gne.ne.NEDBAccessID) gneipaddr,

       ne.isNEConnected,

       ne.NEState,

       ne.NEPMEnabled,

       ne.NERobustPmEnabled,

       ne.NEVersion,

       ne.GNEID,

       ne.NESubNetworkID,

       np.NPId,

       ne.NESnmpCommString,

       ne.NEConfigMode,

       ne.NECLLICode,

       ne.NEDescription,

       ne.NEDbaccessid,

       ne.NEDiscoveryState,

       mod.MODELTYPENAME,

       sub.SUBNETNAME,

       np.NPNAME,

       gne.GNESYSID,

       ne.NEAliasID,

       ne.rowid as myrow

	ne.ISTUNNELREQUIRED,

       isIPV6(ne.NEDBAccessID) isipv6_ne,

       isIPV6(gne.NEDBAccessID) isipv6_gne

		ne.ISDSS

 from  ne_info_table ne,

       network_partition_table np,

       subnetwork_table sub,

       model_type_table mod,

       gne_table gne

where ne.NeSubNetworkid=sub.subnetid

  and sub.npid= np.npid

  and ne.NEMODELTYPE=mod.MODELTYPE

  and ne.GNEID=gne.GNEID(+);

2.2.27 DSP_Circuit_View

create or replace view DSP_CIRCUIT_VIEW AS

select

        src.CKTCTPNODEID as SRCNENODEID,

        src.CktCTPPhysicalLoc as SrcPhysicalLoc,

        src.CKTCTPMODULETYPE as SRCMODULETYPE,

        dest.CKTCTPNODEID as DESTNENODEID,

        dest.CktCTPPhysicalLoc as DestPhysicalLoc,

        dest.CKTCTPMODULETYPE as DESTMODULETYPE

from

	  circuit_bs_tbl c,

        circuit_ctp_src_view src,

        circuit_ctp_drop_view dest

where

        c.CKTNODEID = src.CKTNODEID(+) and

	  c.CKTUNIQUEID = src.CKTUNIQUEID(+) and

        c.CKTNODEID = dest.CKTNODEID(+) and

		c.CKTUNIQUEID = dest.CKTUNIQUEID(+) and

        ( src.CKTCTPMODULETYPE = 6684672 or src.CKTCTPMODULETYPE = 6750208 or 
src.CKTCTPMODULETYPE = 10747904 or src.CKTCTPMODULETYPE = 14483456 or 
dest.CKTCTPMODULETYPE = 6684672 or dest.CKTCTPMODULETYPE = 6750208 or 
dest.CKTCTPMODULETYPE = 10747904 or dest.CKTCTPMODULETYPE = 14483456) and

        c.cktState             != 12;

2.2.28 DSP_Eqpt_View

create or replace view DSP_EQPT_VIEW AS

select

        e.NEDBACCESSID as NEDBACCESSID,

        n.NENODEID as NENODEID,

        e.PHYSICALLOC as PHYSICALLOC,

        e.EQUIPMENTTYPE as MODULETYPE

from

        ONS15454_NE_INVENTORY_TABLE e,

        NE_INFO_TABLE n

where

        e.NEDBACCESSID = n.NEDBACCESSID(+) and

        ( e.EQUIPMENTTYPE = 102 or e.EQUIPMENTTYPE = 103 or e.EQUIPMENTTYPE = 164 or 
e.EQUIPMENTTYPE = 221) and

        ( n.NEMODELINDEX = 47 or n.NEMODELINDEX = 49 or n.NEMODELINDEX = 55 or 
n.NEMODELINDEX = 57 or n.NEMODELINDEX = 61 or n.NEMODELINDEX = 62 or n.NEMODELINDEX = 65 
or n.NEMODELINDEX = 77 or  n.NEMODELINDEX = 78 or  n.NEMODELINDEX = 75 or  n.NEMODELINDEX 
= 76 or n.NEMODELINDEX = 87 or n.NEMODELINDEX = 88 or n.NEMODELINDEX = 91 or 
n.NEMODELINDEX = 81);

2.2.29 GWCORBA_PM_Mapping_View

create or replace view GWCORBA_PM_MAPPING_VIEW AS

select

  	gwpm.MODELTYPE AS MODELTYPE,

	gwpm.LAYERRATE AS LAYERRATE,

	gwpm.OBJECTINDEX AS OBJECTINDEX,

	gwpm.LOCATION AS LOCATION,

	gwpm.DIRECTION AS DIRECTION,

	gwpm.TERMINATIONPOINT AS TERMINATIONPOINT,

	pam.IS24H AS IS24H,

	gwpm.PMVIEWNAME AS PMVIEWNAME,

	ot.METADATAINDEX AS METADATAINDEX,

	pam.PMPARAMID AS PMPARAMID,

	pam.PMNAME AS PMNAME,

	pci.TMFNAME AS TMFNAME,

	pam.ATTRINDEX AS ATTRINDEX,

	pam.VALIDITY_ATTRINDEX AS VALIDITY_ATTRINDEX,

	pci.PMTABLECOLNO AS PMTABLECOLNO

from

        GWCORBA_PM_MAPPING_TABLE gwpm,

	OBJECTTYPETABLE ot,

	PM_ATTRID_MAPPING_TABLE pam,

	PM_CERENT_INFO_TABLE pci

where

	gwpm.OBJECTINDEX = ot.OBJECTINDEX AND

	gwpm.MODELTYPE = ot.MODELTYPE AND

	gwpm.PMVIEWNAME = pam.PMVIEWNAME AND

	ot.METADATAINDEX = pam.METADATAINDEX AND

	pam.PMPARAMID = pci.PMPARAMID AND

	gwpm.LOCATION = pci.LOCATION AND

	gwpm.DIRECTION = pci.DIRECTION AND

	TMFNAME NOT LIKE ('N/A');

2.2.30 INTF_Sub_Intf_View

CREATE OR REPLACE VIEW INTF_SUB_INTF_VIEW AS

SELECT DISTINCT

	ert.NEDBACCESSID as NEID,

	getSlot(ert.RELATEDPHYSICALLOC) as SLOT,

	getPort(ert.RELATEDPHYSICALLOC) as PORTNUM,

	getBGFromBGObj(ert.SELFIFINDEX) as BG,

	getSubIf(ert.RELATEDIFINDEX) as SUBIF

FROM

	Eqpt_Info_Table eqpt,

	Eqpt_RelationShip_Table ert

WHERE

	eqpt.NEDBACCESSID = ert.NEDBACCESSID AND eqpt.PHYSICALLOC = ert.RELATEDPHYSICALLOC

	AND eqpt.IFINDEX = ert.RELATEDIFINDEX AND ert.SELFOBJECTINDEX  = 165

	AND (eqpt.OBJECTINDEX = 172 OR eqpt.OBJECTINDEX = 173  OR eqpt.OBJECTINDEX = 175 OR 
eqpt.OBJECTINDEX = 176);

2.2.31 Inventory_Table_View

create or replace view Inventory_Table_View as

select

	ni.NESysID,

	inv.ModuleName,

	inv.ModulePartNum,

	inv.ModuleCLEICode,

	inv.ModuleHWVersion,

	inv.ModuleSWVersion,

	inv.ModuleSerialNumber,

	inv.ModuleDateOfManuf,

	inv.ModuleDateOfEntry,

	inv.ModuleHoursInUse,

	ni.NEDBAccessId,

	inv.rowid as myrow

from

	NE_INFO_TABLE ni,

	NE_INVENTORY_TABLE inv

where

	ni.NEDBAccessID = inv.NEDBAccessID ;

2.2.32 IOS_Active_Cards

create or replace view IOS_ACTIVE_CARDS as

select distinct

	   a.NEDBACCESSID as NEDBACCESSID,

	   a.PHYSICALLOC as PHYSICALLOC,

	   a.EQUIPMENTTYPE as MODULETYPE,

	   a.NAME as MODULENAME,

	   b.NESYSID as NEID,

       b.NEMODELTYPE as NEMODELTYPE,

	   a.rowid as MYROW

from ONS15454_NE_INVENTORY_TABLE a, NE_INFO_TABLE  b

where EQUIPMENTTYPE in ( 102,103,164,221 ) and

	  EQUIPMENTSTATE = 5 and

	  a.NEDBACCESSID = b.NEDBACCESSID;

2.2.33 IOS_Users_View

CREATE OR REPLACE VIEW IOS_USERS_VIEW AS

select

       c.NEDBACCESSID as NEDBACCESSID,

	   c.MODULETYPE as MODULETYPE,

       c.PHYSICALLOC as PHYSICALLOC,

	   c.IFINDEX as IFINDEX,

	   c.COL2 as USERNAME,

       c.COL5 as PASSWORD,

	   c.COL3 as PRIVILEGE,

	   a.MODULENAME as MODULENAME,

       b.NESYSID as NEID,

       c.MODELTYPE as NEMODELTYPE,

	   c.rowid as MYROW,

	   b.NEAliasID as NEAliasID

from EQPT_INFO_TABLE c,NE_INFO_TABLE b,MODULE_TYPE_TABLE a

where c.OBJECTINDEX = 221 and

c.NEDBACCESSID = b.NEDBACCESSID and

c.MODELTYPE = a.MODELTYPE and

c.MODULETYPE = a.MODULETYPE;

2.2.34 Link_Utilization_View

CREATE OR REPLACE VIEW LINK_UTILIZATION_VIEW

AS SELECT     a.CKTCTPMODULETYPE as CKTMODULETYPE,

           a.CKTCTPPHYSICALLOC as CKTPHYSICALLOC,

           a.CKTCTPIFINDEX as CKTIFINDEX,

           b.CKTNAME as CKTNAME,

           b.CKTPROTECTIONTYPE as CKTPROTECTIONTYPE,

           b.CUSTOMERID as CKTCUSTOMERID,

           b.SERVICEID as CKTSERVICEID,

           b.CKTSIZE as CKTSIZE,

           a.CKTCTPIFINDEX as STRIPPED_IFINDEX,

           a.CKTCTPNEDBACCESSID as NEDBACCESSID,

           b.CKTTYPE as CKTTYPE,

           b.CKTUSELAP as CKTUSELAP,

           c.CKTCTPIFINDEX as STRIPPED_CKTSRC_IFINDEX,

           collapsedifindex(b.CKTNODEID,b.CKTUNIQUEID)  as CKTDEST_IFINDEX,

           b.CKTUNIQUEID as CKTUNIQUEID,

           b.CKTNODEID as CKTNODEID,

           a.CKTCTPOBJECTTYPE as CKTOBJECTTYPE,

           collapsedobjectindex(b.CKTNODEID,b.CKTUNIQUEID) as cktDestObjectType,

           c.CKTCTPMODELTYPE as modelType,

           d.CKTSPANPROTOP as SWITCHSTATE,

           b.Rowid as myrow

FROM   CIRCUIT_CTP_TBL a, CIRCUIT_BS_TBL b, CIRCUIT_CTP_TBL c, CIRCUIT_SPAN_BS_TBL d

WHERE

            a.CKTUNIQUEID = b.CKTUNIQUEID AND

            a.CKTNODEID = b.CKTNODEID AND

            c.CKTUNIQUEID = b.CKTUNIQUEID AND

            c.CKTNODEID = b.CKTNODEID AND

            b.CKTUNIQUEID = d.CKTUNIQUEID(+) AND

            b.CKTNODEID = d.CKTNODEID(+) AND

            c.CTP_TYPE = 0 AND

	( a.CTPACCESSID = d.CKTSPANDSTCTPACCESSID OR a.CTPACCESSID = 
d.CKTSPANSRCCTPACCESSID ) AND

            ( b.CKTTYPE != 5 AND b.CKTTYPE != 6 AND b.CKTTYPE != 21 AND b.CKTTYPE != 22 );

2.2.35 Mcard_L2topology_Map_View

CREATE OR REPLACE VIEW mcard_l2topology_map_view AS

SELECT

      l2tct.Toponodeid as Toponodeid,

      l2tct.Topouniqueid as Topouniqueid,

      nit.NeDbAccessId as NeDbId,

      cst.CktCTPPhysicalLoc as PhysicalLoc,

      cst.CktCTPModuleType as ModuleType

FROM  circuit_ctp_tbl cst,

      L2topology_Circuits_Table l2tct,

      Ne_Info_Table nit

WHERE nit.nedbaccessid=cdt.CKTCTPNeDBAccessId and

      cdt.ctp_type=2 AND

	cst.CktNodeId    = l2tct.NodeId AND

      cst.CktUniqueId  = l2tct.UniqueId AND

      cst.CktCTPNodeId = nit.NeNodeId AND

      cst.CktCTPModuleType IN (6684672,6750208,10747904,14483456)

UNION ALL

SELECT

      l2tct.Toponodeid as Toponodeid,

      l2tct.Topouniqueid as Topouniqueid,

      nit.NeDbAccessId as NeDbId,

      cdt.cktCTPPhysicalLoc as PhysicalLoc,

      cdt.cktCTPModuleType as ModuleType

FROM  circuit_ctp_tbl cdt,

      L2topology_Circuits_Table l2tct,

      Ne_Info_Table nit

WHERE nit.nedbaccessid=cdt.CKTCTPNeDBAccessId and

      cdt.ctp_type=2 AND

	cdt.CktNodeId     = l2tct.NodeId AND

      cdt.CktUniqueId   = l2tct.UniqueId AND

      cdt.CktCTPNodeId = nit.NeNodeId AND

      cdt.cktCTPModuleType IN (6684672,6750208,10747904,14483456);

2.2.36 MLBCC_View

create or replace force view MLBCC_VIEW AS

SELECT

	l2topocardview.TopoNodeId AS TopoNodeId,

	l2topocardview.TopoUniqueId AS TopoUniqueId,

        neInfo.NESysID AS NESysID,

        l2topocardview.NeDbID AS NEID,

        l2topocardview.SlotNumber AS SlotNumber,

        MODULE.ModuleName AS EquipmentType,

        l2topocardview.configState AS configState,

        l2topocardview.POS0_Status AS POS0_Status,

        l2topocardview.POS1_Status AS POS1_Status,

        l2topocardview.RPR0 AS RPR0,

        getMLcardAddnlInfo( l2topocardview.AdditionalInfo, inv.EquipmentState) AS 
AdditionalInfo,

        getProtectMLcardInfo( l2topocardview.ProtectCardNeDbId, 
l2topocardview.ProtectCardSlotNumber ) AS ProtectCard,

        getBccCosInfo(l2topocardview.NeDbID, inv.PhysicalLoc, 181, 'AVVID_VOICE_VIDEO') AS 
COS_AVVID_VOICEVIDEO,

        getBccCosInfo(l2topocardview.NeDbID, inv.PhysicalLoc, 181, 'SP_MANAGEMENT') AS 
COS_SP_MANAGEMENT,

        getBccCosInfo(l2topocardview.NeDbID, inv.PhysicalLoc, 181, 'CIR') AS 
COS_COMMITTED,

        getBccCosInfo(l2topocardview.NeDbID, inv.PhysicalLoc, 181, 'AVVID_CONTROL') AS 
COS_AVVID_CONTROL,

        getBccBwInfo(l2topocardview.NeDbID, 
getQOSPhLoc(l2topocardview.SlotNumber,inv.PhysicalLoc), 180, 'SP_MANAGEMENT') AS 
BW_SP_MANAGEMENT,

        getBccBwInfo(l2topocardview.NeDbID, 
getQOSPhLoc(l2topocardview.SlotNumber,inv.PhysicalLoc), 180, 'CIR') AS BW_COMMITTED,

        getBccBwInfo(l2topocardview.NeDbID, 
getQOSPhLoc(l2topocardview.SlotNumber,inv.PhysicalLoc), 180, 'AVVID_CONTROL') AS 
BW_AVVID_CONTROL,

        getBccBwInfo(l2topocardview.NeDbID, 
getQOSPhLoc(l2topocardview.SlotNumber,inv.PhysicalLoc), 180, 'BEST_EFFORT') AS 
BW_BEST_EFFORT,

        getBccCosCommitGrp(l2topocardview.NeDbID, inv.PhysicalLoc, 0, 189) AS COS_COMMIT,

        getBccCosCommitGrp(l2topocardview.NeDbID, inv.PhysicalLoc, 11, 189) AS BW_GROUP1,

        getBccCosCommitGrp(l2topocardview.NeDbID, inv.PhysicalLoc, 12, 189) AS COS_GROUP1,

        getBccCosCommitGrp(l2topocardview.NeDbID, inv.PhysicalLoc, 21, 189) AS BW_GROUP2,

        getBccCosCommitGrp(l2topocardview.NeDbID, inv.PhysicalLoc, 22, 189) AS COS_GROUP2,

        getBccClassInfo(l2topocardview.NeDbID, 
getQOSPhLoc(l2topocardview.SlotNumber,inv.PhysicalLoc), 180, 'SP_MANAGEMENT') AS 
CLASS_SP_MANAGEMENT,

        getBccClassInfo(l2topocardview.NeDbID, 
getQOSPhLoc(l2topocardview.SlotNumber,inv.PhysicalLoc), 180, 'CIR') AS CLASS_COMMITTED,

        getBccClassInfo(l2topocardview.NeDbID, 
getQOSPhLoc(l2topocardview.SlotNumber,inv.PhysicalLoc), 180, 'AVVID_CONTROL') AS 
CLASS_AVVID_CONTROL,

        getBccClassInfo(l2topocardview.NeDbID, 
getQOSPhLoc(l2topocardview.SlotNumber,inv.PhysicalLoc), 180, 'BEST_EFFORT') AS 
CLASS_BEST_EFFORT,

        getBccClassInfo(l2topocardview.NeDbID, 
getQOSPhLoc(l2topocardview.SlotNumber,inv.PhysicalLoc), 180, 'AVVID_VOICE_VIDEO') AS 
CLASS_AVVID_VOICE_VIDEO,

        getBccClassRPR80217(l2topocardview.NeDbID, 
getQOSPhLoc(l2topocardview.SlotNumber,inv.PhysicalLoc), 284, 3) AS BW_A0,

        getBccClassRPR80217(l2topocardview.NeDbID, 
getQOSPhLoc(l2topocardview.SlotNumber,inv.PhysicalLoc), 284, 1) AS BW_A1,

        getBccClassRPR80217(l2topocardview.NeDbID, 
getQOSPhLoc(l2topocardview.SlotNumber,inv.PhysicalLoc), 284, 2) AS BW_B_CIR

FROM

        NE_INFO_TABLE neInfo,

        ONS15454_NE_INVENTORY_TABLE inv,

        MODULE_TYPE_TABLE MODULE,

        L2_TOPOLOGY_CARDS_VIEW l2topocardview

WHERE

	l2topocardview.NESYSID = neInfo.NESysID AND

	inv.EquipmentType = MODULE.ModuleType AND

        neInfo.NEModelType = MODULE.ModelType; AND

	l2topocardview. NeDbID = inv.NeDbID AND

        l2topocardview.SlotNumber = getSlot(inv.PhysicalLoc);

2.2.37 MLL_Inv_View

create or replace view MLL_INV_VIEW AS

select distinct

       NEDBACCESSID as NEDBACCESSID,

       LAYERRATE as LAYERRATE

from ( select

        info.NEDBACCESSID as NEDBACCESSID,

        m.LAYERRATE as LAYERRATE

from

        MODULE_TYPE_MLL_INFO m,

        ne_info_table info,

        ONS15454_ne_inventory_table inv

where

        info.NEDBAccessID = inv.NEDBAccessID AND

        info.NEModelType  = m.ModelType AND

        inv.EquipmentType = m.ModuleType

UNION ALL

select

        ne.NEDBACCESSID as NEDBACCESSID,

        m.LAYERRATE as LAYERRATE

from

        MODULE_TYPE_MLL_INFO m,

        ne_info_table ne,

        eqpt_info_table eit

where

        ne.NEDBACCESSID = eit.NEDBACCESSID AND

        ne.NEModelType  = m.ModelType    AND

        eit.ModuleType  = m.ModuleType   AND

        ne.NEModelType in (4,6,10)       AND

        m.ModuleType  >  2003

UNION ALL

select

        t1.NEDBACCESSID as NEDBACCESSID,

        m.LAYERRATE as LAYERRATE

from

        MODULE_TYPE_MLL_INFO m,

        NE_INFO_TABLE t1,

        ONS15216_NE_INVENTORY_TABLE t2

where

        t1.NEDBACCESSID = t2.NEDBACCESSID AND

 	t1.NEModelType = m.MODELTYPE

UNION ALL

select

        n.NEDBACCESSID as NEDBACCESSID,

        0 as LAYERRATE

from

        NE_INFO_TABLE n

where

        n.NEMODELTYPE in (24,33)

UNION ALL

select

        n.NEDBACCESSID as NEDBACCESSID,

        m.LAYERRATE as LAYERRATE

from

        MODULE_TYPE_MLL_INFO m,

        NE_INFO_TABLE n,

        EQPT_INFO_TABLE eqpt

where

        n.NEDBACCESSID = eqpt.NEDBACCESSID and

        m.MODELTYPE = n.NEMODELTYPE and

        m.MODULETYPE = eqpt.MODULETYPE and

        ((n.NEMODELTYPE = 9) or (n.NEMODELTYPE<14 and n.NEMODELTYPE>10))

UNION ALL

select

        neInfo.NEDBACCESSID as NEDBACCESSID,

        mll.LAYERRATE as LAYERRATE

from

        MODULE_TYPE_MLL_INFO mll,

        NE_INFO_TABLE neInfo,

        ONS1530x_NE_INVENTORY_TABLE neInv

where

        neInfo.NEDBACCESSID = neInv.NEDBACCESSID and

        neInfo.NEModelType  = mll.ModelType and

        neInv.Type  = mll.ModuleType and

        neInfo.NEModelType in (21,22));

2.2.38 NE_Alarm_View

create or replace view NE_Alarm_View as

select

	aat.NEDBAccessID,

	aat.ActiveAlarmSeverity,

	aat.ModuleOrIfIndex,

	aat.AlarmSeqNum,

        aat.ModuleType,

        aat.PhysicalLoc,

        aat.alarmStatus,

	aat.rowid as myrow,

        aat.ModelType,

        aat.ObjectType,

        aat.StrObjInstance

from

	ACTIVE_ALARM_TABLE aat,

	NE_INFO_TABLE nit

where

	aat.NEDBAccessID = nit.NEDBAccessID ;

2.2.39 NE_Circuit_NodeID_View

create or replace view NE_Circuit_NodeID_View as

SELECT

       a.nedbaccessid, b.cktname

  FROM ne_info_table a, circuit_bs_tbl b

 WHERE a.NENodeID = b.CktNodeId and

       b.cktstate != 12;

2.2.40 NE_Circuit_View

CREATE OR REPLACE VIEW NE_CIRCUIT_VIEW AS

SELECT DISTINCT

        ckt.CKTNODEID as CKTNODEID,

        ckt.CKTUNIQUEID as CKTUNIQUEID,

        ckt.CKTNAME as CKTNAME,

        c.CKTCTPNODEID as CKTCTPNODEID

from    circuit_ctp_tbl c,

        circuit_bs_tbl ckt

where

        c.CktNodeId = ckt.CktNodeId(+) and

        ckt.cktstate != 12 and

        c.CktUniqueId = ckt.CktUniqueId(+);

2.2.41 NE_Circuit_Vlan_View

create or replace view NE_Circuit_Vlan_View as

SELECT

       a.NEDBACCESSID, c.CKTVLANID

  FROM NE_INFO_TABLE a, CIRCUIT_TBL b, CIRCUIT_VLAN_TBL c

 WHERE b.CKTUNIQUEID = c.CKTUNIQUEID

   AND b.CKTNODEID = c.CKTNODEID

   AND b.CKTNODEID = a.NENODEID;

2.2.42 NE_Info_NP_View

create or replace view NE_Info_NP_VIEW As

select ne.NEDBAccessID,

       ne.NESysID,

       getNEIPAddr(ne.NEDBAccessID) ne.NEIPAddr,

       ne.NENSAPAddr,

       ne.NEState,

       ne.isNEConnected,

       decode(ne.GNEID,-1,'Not Applicable',

                             gne.GNESysID ) as GneName,

       ne.NEDescription,

       ne.NEConfigMode,

       ne.NESnmpCommString,

       ne.NECLLICode,

       ne.NEStateChangeTime,

       ne.NEModelType,

       ne.NEModelIndex,

       ne.NEModelStatus,

       decode(ne.nesubnetworkID,-1,'Not Applicable',

                               sub.SubnetName) as SubnetworkName,

       ne.NETimeOffset,

       ne.NELEDStatus,

       ne.NESystemTitle,

       ne.NEUserLabel,

       ne.NEVendorName,

       ne.NEVersion,

       ne.NENumActiveCriticalErrors,

       ne.NENumActiveMajorErrors,

       ne.NENumActiveMinorErrors,

       ne.NEPmEnabled,

       ne.NENodeID,

       ne.NEInventoryLastChanged,

       ne.NESubnetMask,

       ne.NEDefaultGateway,

       ne.NENumUnActiveCriticalErrors,

       ne.NENumUnActiveMajorErrors,

       ne.NENumUnActiveMinorErrors,

       ne.NENumCleared,

       ne.DisplayModelName,

       ne.GWTL1USERNAME,

       ne.GWTL1PASSWD,

       ne.ServiceID,

       ne.DeleteState,

       np.NPId,

       np.NPName

	isIPV6(ne.NEDBAccessID) isipv6

from   ne_info_table ne,

       subnetwork_table sub,

       gne_table        gne,

       network_partition_table np

Where  ne.GNEID=gne.gneid(+) AND

       ne.NESubNetworkID=sub.subnetID AND

       sub.NPId=np.NPId;

2.2.43 NE_Inventory_View

create or replace view NE_Inventory_View as

select

	nit.NESysID,

	ninvt.ModuleIndex,

	ninvt.ModuleName,

	ninvt.ModulePartNum,

	ninvt.ModuleCLEICode,

	ninvt.ModuleHWVersion,

	ninvt.ModuleSWVersion,

	ninvt.ModuleSerialNumber,

	ninvt.ModuleDateOfManuf,

	ninvt.ModuleDateOfEntry,

	ninvt.ModuleHoursInUse,

	nit.NEDBAccessId,

	ninvt.rowid as myrow

from

	NE_INVENTORY_TABLE ninvt,

	NE_INFO_TABLE nit

where

	ninvt.NEDBAccessID = nit.NEDBAccessID ;

2.2.44 NE_SNMPV3_FILTERS_VIEW

NE_SNMPV3_FILTERS_VIEW aggregates rows from NE_SNMPV3_FILTERS_TABLE and NE_INFO_TABLE to show the NE SYSID. Select NEs that are In Service state and DISCOVERED only.

CREATE OR REPLACE VIEW NE_SNMPV3_FILTERS_VIEW AS

select

	NE_INFO_TABLE.NEDBACCESSID,

	NE_INFO_TABLE.NESYSID,

	NE_SNMPV3_NFILTER_TABLE.FILTER_PROFILE_NAME,

	NE_SNMPV3_NFILTER_TABLE.SUBTREEOID,

	NE_SNMPV3_NFILTER_TABLE.BIT_MASK,

	NE_SNMPV3_NFILTER_TABLE.FILTER_TYPE

from

	NE_SNMPV3_NFILTER_TABLE join NE_INFO_TABLE

	ON NE_SNMPV3_NFILTER_TABLE.NEDBACCESSID = NE_INFO_TABLE.NEDBACCESSID

WHERE

	NE_INFO_TABLE.NESTATE = 1

AND

	NE_INFO_TABLE.NEDISCOVERYSTATE = 4;

2.2.45 NE_SNMPV3_GROUPS_VIEW

CREATE OR REPLACE VIEW NE_SNMPV3_GROUPS_VIEW AS

select

	NE_INFO_TABLE.NEDBACCESSID,

	NE_INFO_TABLE.NESYSID,

	NE_SNMPV3_GROUPS_TABLE.GROUP_NAME,

	NE_SNMPV3_GROUPS_TABLE.SECURITY_LEVEL,

	NE_SNMPV3_GROUPS_TABLE.READ_VIEW_ACCESS,

	NE_SNMPV3_GROUPS_TABLE.WRITE_VIEW_ACCESS,

	NE_SNMPV3_GROUPS_TABLE.NOTIFY_VIEW_ACCESS

from

	NE_SNMPV3_GROUPS_TABLE join NE_INFO_TABLE

	ON NE_SNMPV3_GROUPS_TABLE.NEDBACCESSID = NE_INFO_TABLE.NEDBACCESSID

WHERE

	NE_INFO_TABLE.NESTATE = 1

AND

	NE_INFO_TABLE.NEDISCOVERYSTATE = 4;

2.2.46 NE_SNMPV3_LOCAL_USER_VIEW

VIEW NE_SNMPV3_LOCAL_USER_VIEW aggregates rows from NE_SNMPV3_USERS_TABLE and NE_INFO_TABLE to show the NE SYSID and select Local Users only. A join on Engine ID is used to identify sysid of remote NE. Then a selection is done comparing NEDBACCESSID. Select NEs that are In Service state and DISCOVERED only.

CREATE OR REPLACE VIEW NE_SNMPV3_LOCAL_USER_VIEW AS

select NE_INFO_TABLE.NEDBACCESSID,

NE_INFO_TABLE.NESYSID,

NE_SNMPV3_USERS_TABLE.USER_NAME,

NE_SNMPV3_USERS_TABLE.GROUP_NAME,

NE_SNMPV3_USERS_TABLE.AUTHENTICATION_PROTOCOL,

NE_SNMPV3_USERS_TABLE.PRIVACY_PROTOCOL,

NE_SNMPV3_USERS_TABLE.Engine_Id_Len,

NE_SNMPV3_USERS_TABLE.Boots

from NE_SNMPV3_USERS_TABLE, NE_INFO_TABLE

where NE_SNMPV3_USERS_TABLE.ENGINE_ID = NE_INFO_TABLE.NE_SNMP_EID

and NE_SNMPV3_USERS_TABLE.NEDBACCESSID = NE_INFO_TABLE.NEDBACCESSID

AND NE_INFO_TABLE.NESTATE = 1

AND NE_INFO_TABLE.NEDISCOVERYSTATE = 4;

2.2.47 NE_SNMPV3_PROXY_FWD_VIEW

VIEW NE_SNMPV3_PROXY_FWD_VIEW aggregates rows from NE_SNMPV3_PROXY_FWD_TABLE and NE_INFO_TABLE to show the NE SYSIDs for remote NEs. The left outer join is used for the same reason described above for the NE_SNMPV3_REMOTE_USER_VIEW. Select NEs that are In Service state and DISCOVERED only.

CREATE OR REPLACE VIEW NE_SNMPV3_PROXY_FWD_VIEW AS

select NE2.NEDBACCESSID,

NE2.NESYSID,

NE_SNMPV3_PROXY_FWD_TABLE.CONTEXT_ENGINE_ID,

NE_SNMPV3_PROXY_FWD_TABLE.TARGET_IP,

NE_SNMPV3_PROXY_FWD_TABLE.LOCAL_USER,

NE_SNMPV3_PROXY_FWD_TABLE.REMOTE_USER,

NE_SNMPV3_PROXY_FWD_TABLE.PROXY_TYPE,

NE_SNMPV3_PROXY_FWD_TABLE.SECURITY_LEVEL,

NE1.NESYSID AS REMOTE_NE,

NE_SNMPV3_PROXY_FWD_TABLE.ROW_ID

from (NE_SNMPV3_PROXY_FWD_TABLE left outer join NE_INFO_TABLE NE1

on NE_SNMPV3_PROXY_FWD_TABLE.CONTEXT_ENGINE_ID = NE1.NE_SNMP_EID)

join NE_INFO_TABLE NE2 on NE2.NEDBACCESSID = NE_SNMPV3_PROXY_FWD_TABLE.NEDBACCESSID

WHERE NE2.NESTATE = 1

AND NE2.NEDISCOVERYSTATE = 4;

2.2.48 NE_SNMPV3_PROXY_TRAP_FWD_VIEW

NE_SNMPV3_PROXY_TRAP_FWD_VIEW aggregates rows from NE_SNMPV3_PROXY_TRAP_FWD_TABLE and NE_INFO_TABLE to show the NE SYSID, and NESYSID for remote NE. Left outer join is used for the same reason described above (see comment for NE_SNMPV3_REMOTE_USER_VIEW). Select NEs that are In Service state and DISCOVERED only.

CREATE OR REPLACE VIEW NE_SNMPV3_PROXY_TRAP_FWD_VIEW AS

select NE2.NEDBACCESSID,

NE2.NESYSID,

NE_SNMPV3_PROXY_TRAP_FWD_TABLE.CONTEXT_ENGINE_ID,

NE_SNMPV3_PROXY_TRAP_FWD_TABLE.TAG,

NE_SNMPV3_PROXY_TRAP_FWD_TABLE.INCOMING_USER,

NE_SNMPV3_PROXY_TRAP_FWD_TABLE.SECURITY_LEVEL,

NE1.NESYSID AS REMOTE_NE,

NE_SNMPV3_PROXY_TRAP_FWD_TABLE.ROW_ID

from (NE_SNMPV3_PROXY_TRAP_FWD_TABLE left outer join NE_INFO_TABLE NE1

on NE_SNMPV3_PROXY_TRAP_FWD_TABLE.CONTEXT_ENGINE_ID = NE1.NE_SNMP_EID)

join NE_INFO_TABLE NE2 on NE2.NEDBACCESSID = NE_SNMPV3_PROXY_TRAP_FWD_TABLE.NEDBACCESSID

WHERE NE2.NESTATE = 1

AND NE2.NEDISCOVERYSTATE = 4;

2.2.49 NE_SNMPV3_REMOTE_USER_VIEW

VIEW NE_SNMPV3_REMOTE_USER_VIEW aggregates rows from NE_SNMPV3_USERS_TABLE and NE_INFO_TABLE to show the NE SYSID and select Remote Users only. Select NEs that are In Service state and DISCOVERED only. The query can be summarized as followS: NE_INFO_TABLE [join on DBACCESSID] NE_SNMPV3_USERS_TABLE [left outer join on ENGINEID] NE_INFO_TABLE. The first join produces the NESYSID of the NE described by NE_SNMPV3_USERS_TABLE row. The second join produces the NESYSID of the REMOTE NE. A left outer join is required because the ENGINEID cannot be found in NE_INFO_TABLE (see example below). In this case rows cannot be discarded. The where clause is used to select remote users only.

Example: Engine ID cannot match he one in NE_INFO_TABLE. EngineID in NE_INFO_TABLE is filled at synch time. Suppose you have

GNE --- ENE-2

ENE-1

Suppose you preprovision all NEs (GNE, ENE-1, ENE-2), but only put the GNE inservice state. The SnmpV3UserTable is filled for the GNE, but the query does not match the EngineId for remote users.

CREATE OR REPLACE VIEW NE_SNMPV3_REMOTE_USER_VIEW AS

select

	NE1.NEDBACCESSID,

	NE1.NESYSID,

	NE_SNMPV3_USERS_TABLE.ENGINE_ID,

	NE_SNMPV3_USERS_TABLE.USER_NAME,

	NE_SNMPV3_USERS_TABLE.AUTHENTICATION_PROTOCOL,

	NE_SNMPV3_USERS_TABLE.PRIVACY_PROTOCOL,

	NE_INFO_TABLE.NESYSID AS REMOTE_NE,

	NE_SNMPV3_USERS_TABLE.ENGINE_ID_LEN,

	NE_SNMPV3_USERS_TABLE.BOOTS

from

	(NE_SNMPV3_USERS_TABLE left outer join NE_INFO_TABLE

	on NE_SNMPV3_USERS_TABLE.ENGINE_ID = NE_INFO_TABLE.NE_SNMP_EID)

	join NE_INFO_TABLE NE1 on NE1.NEDBACCESSID = NE_SNMPV3_USERS_TABLE.NEDBACCESSID

where

	(NE_SNMPV3_USERS_TABLE.NEDBACCESSID != NE_INFO_TABLE.NEDBACCESSID

	or NE_INFO_TABLE.NEDBACCESSID is null)

AND

	NE1.NESTATE = 1

	NE1.NEDISCOVERYSTATE = 4;

2.2.50 NE_SNMPV3_TRAP_DEST_VIEW

NE_SNMPV3_TRAP_DEST_VIEW aggregates rows from NE_SNMPV3_TRAP_DEST_TABLE and NE_INFO_TABLE to show the NE SYSID. Select NEs that are In Service state and DISCOVERED only.

CREATE OR REPLACE VIEW NE_SNMPV3_TRAP_DEST_VIEW AS

select

	NE_INFO_TABLE.NEDBACCESSID,

	NE_INFO_TABLE.NESYSID,

	NE_SNMPV3_TRAP_DEST_TABLE.TARGET_ADDRESS,

	NE_SNMPV3_TRAP_DEST_TABLE.UDP_PORT,

	NE_SNMPV3_TRAP_DEST_TABLE.USER_NAME,

	NE_SNMPV3_TRAP_DEST_TABLE.SECURITY_LEVEL,

	NE_SNMPV3_TRAP_DEST_TABLE.FILTER_PROFILE_NAME,

	NE_SNMPV3_TRAP_DEST_TABLE.PROXY_TRAPS_ONLY,

	NE_SNMPV3_TRAP_DEST_TABLE.PROXY_TAG

from

	NE_SNMPV3_TRAP_DEST_TABLE join NE_INFO_TABLE

	ON NE_SNMPV3_TRAP_DEST_TABLE.NEDBACCESSID = NE_INFO_TABLE.NEDBACCESSID

WHERE

	NE_INFO_TABLE.NESTATE = 1

AND

	NE_INFO_TABLE.NEDISCOVERYSTATE = 4;

2.2.51 NE_SNMPV3_VIEWS_VIEW

NE_SNMPV3_VIEWS_VIEW aggregates rows from NE_SNMPV3_VIEWS_TABLE and NE_INFO_TABLE to show the NE SYSID. Select NEs that are In Service state and DISCOVERED only.

CREATE OR REPLACE VIEW NE_SNMPV3_VIEWS_VIEW AS

select

	NE_INFO_TABLE.NEDBACCESSID,

	NE_INFO_TABLE.NESYSID,

	NE_SNMPV3_VIEWS_TABLE.VIEW_NAME,

	NE_SNMPV3_VIEWS_TABLE.SUBTREEOID,

	NE_SNMPV3_VIEWS_TABLE.MASK,

	NE_SNMPV3_VIEWS_TABLE.TYPE

from

	NE_SNMPV3_VIEWS_TABLE join NE_INFO_TABLE

	ON NE_SNMPV3_VIEWS_TABLE.NEDBACCESSID = NE_INFO_TABLE.NEDBACCESSID

WHERE

	NE_INFO_TABLE.NESTATE = 1

AND

	NE_INFO_TABLE.NEDISCOVERYSTATE = 4;

2.2.52 NE_Swimage_View

create or replace view NE_SWIMAGE_VIEW as

SELECT

        ne.NESysId,

        mtt.modelTypeName AS ModelName,

        ne.NEState,

        sw.activeimagename,

        sw.standbyimagename,

        sw.defaultimagename,

        ne.nedbaccessid,

        sw.inprogress,

        sw.physicalloc,

        mtt.modeltype,

        ne.NEAliasID,

        sw.partialupgrade

        sw.rowid as myrow

from

        NE_SWIMAGE_TABLE sw,

        NE_INFO_TABLE ne,

        Model_Type_Table mtt

where

        sw.nedbaccessid = ne.nedbaccessid AND

        mtt.ModelType = ne.NEModelType;

2.2.53 NGNE_Info_View

CREATE OR REPLACE VIEW NGNE_INFO_VIEW(TNE_NE_DBACCESS_ID, TL1_PORT, NGNE_PKID, NGNE_TID, 
NGNE_IPADDRESS, NGNE_USERNAME, NGNE_USER_PASSWORD) AS

SELECT DISTINCT

        ti.TNE_NE_DBACCESS_ID,

        tg.NGNE_TL1PORT,

        tg.NGNE_PKID,

        tg.NGNE_TID,

        tg.NGNE_IPADDRESS,

        tg.NGNE_USERNAME,

        tg.NGNE_USER_PASSWORD

from

        TL1_TUNNEL_GATEWAY_TABLE   tg,

        TL1_TUNNEL_INFO_TABLE      ti

where

        tg.NGNE_PKID = ti.FK_NGNE_PKID ;

rem

rem NE_SNMPV3_GROUPS_VIEW aggregates rows from NE_SNMPV3_GROUPS_TABLE and NE_INFO_TABLE to 
show the NE SYSID

rem Select NEs that are In Service state and DISCOVERED only.

rem

2.2.54 OSS_Alarm_Fltr_AidDeny_View

create or replace view OSS_Alarm_Fltr_AidDeny_VIEW as

select

	alarmfltrtbl.AlarmFilterId,

	alarmenum.AIDString,

	alarmfltrtbl.RowId AS myrow

FROM

	OSS_Alarm_Fltr_AID_Deny_Tbl alarmfltrtbl,

	OSS_AID_ENUM_TABLE alarmenum

WHERE

	alarmfltrtbl.AidId = alarmenum.AIDId;

2.2.55 OSS_Alarm_Fltr_TidDeny_VIEW

create or replace view OSS_Alarm_Fltr_TidDeny_VIEW as

select

	neInfo.NEDBAccessId,

	alarmfltrtbl.AlarmFilterId,

	neInfo.NESysId,

	alarmfltrtbl.ROWID AS MYROW

FROM

	OSS_Alarm_Fltr_TID_Deny_Tbl alarmfltrtbl,

	NE_INFO_TABLE neInfo

WHERE

	alarmfltrtbl.NEDBAccessID = neInfo.NEDBAccessID;

2.2.56 OSS_Corba_User_View

create or replace view OSS_Corba_User_View as

select

	corbaUserTable.OSSName,

	corbaUserTable.IPAddress,

	corbaUserTable.OSSPasswd,

	corbaUserTable.ROWID AS MYROW

FROM

	OSS_CORBA_USER_TABLE corbaUserTable;

2.2.57 OSS_User_View

create or replace view OSS_User_View as

select

	userTable.OSSName,

	getOUTIPAddr(userTable.OSSName) IPAddress,

	userTable.ListenerModeFlag,

	userTable.ActUserDenyFlag,

	userTable.DomainMgrFlag,

	userTable.AlarmFilterFlag,

	filterTable.FilterName,

	userTable.OSSPASSWD,

	userTable.ALARMFILTERID,

	userTable.ROWID AS MYROW

FROM

	OSS_ALARM_FILTER_TABLE filterTable,

	OSS_USER_TABLE userTable

WHERE

	filterTable.AlarmFilterId = userTable.AlarmFilterId;

2.2.58 POS_Ports_View

CREATE OR REPLACE VIEW POS_PORTS_VIEW AS

select

       c.NEDBACCESSID as NEDBACCESSID,

       c.MODULETYPE as MODULETYPE,

       c.MODELTYPE as NEMODELTYPE,

       a.MODULENAME as MODULENAME,

       c.PHYSICALLOC as PHYSICALLOC,

       c.IFINDEX as IFINDEX,

       c.COL5 as POSSTATE,

       c.COL15 as MTUSIZE

from EQPT_INFO_TABLE c,NE_INFO_TABLE b,MODULE_TYPE_TABLE a

where (c.OBJECTINDEX = 166 OR c.OBJECTINDEX = 284) and

c.NEDBACCESSID = b.NEDBACCESSID and

c.MODELTYPE = a.MODELTYPE and

c.MODULETYPE = a.MODULETYPE;

2.2.59 QoSTemplate_General_View

CREATE OR REPLACE VIEW QOSTEMPLATE_GENERAL_VIEW AS

SELECT

  QOSTEMPLATEID as TID,

  QOSTEMPLATENAME as TNAME,

  QOSTEMPLATETYPE as TTYPE,

  QOSTEMPLATEDESCRIPTION as TDESC

FROM L2QOSTEMPLATE_TABLE;

2.2.60 QoS_View

CREATE OR REPLACE VIEW QOS_VIEW AS

SELECT DISTINCT

	eqpt.NEDBACCESSID as NEID,

	getSlot(eqpt.PHYSICALLOC) as SLOT,

	getPort(eqpt.PHYSICALLOC) as PORTNUM,

	getBGFromQoSObj(eqpt.IFINDEX) as BG,

	eqpt.COL4 as CIR,

	eqpt.COL5 as CIR_BURST,

	eqpt.COL17 as PIR,

	eqpt.COL18 as PIR_BURST,

	eqpt.COL11 as CCOSTRANSMIT

FROM

	Eqpt_Info_Table eqpt

WHERE

	eqpt.OBJECTINDEX = 180 AND (getPolicyMapType(eqpt.IFINDEX) = 3 OR 
getPolicyMapType(eqpt.IFINDEX) = 5);

2.2.61 Roll_View

CREATE OR REPLACE VIEW roll_view AS

SELECT

        (select CktName from circuit_bs_tbl where cktnodeid = r.rollfromcktnodeid and 
cktuniqueid=r.rollfromcktuniqueid) as rollFromCkt,

        (select CktName from circuit_bs_tbl where cktnodeid = r.rolltocktnodeid and 
cktuniqueid=r.rolltocktuniqueid) as rollToCkt,

        r.rollstate as rollState,

        r.rollvalidsignal as rollValidSignal,

        r.rollmode as rollMode,

        (select nesysid from ne_info_table where nenodeid=r.rollpathnodeid and 
r.rollpathnodeid !=0 )  as PathNodeId,

        r.rollpathnodeid,

        nvl(getSlotModuleName(r.rollpathnodeid, r.rollpathmoduletype),'SYSTEM')  as 
rollPathModuleType,

        r.rollpathphysicalloc as rollPathPhySicalLoc,

        r.rollpathifindex as rollPathIfIndex,

        (select nesysid from ne_info_table where nenodeid=r.rollfromnodeid and 
r.rollfromnodeid !=0 )  as FromNodeId,

        r.rollfromnodeid,

        nvl(getSlotModuleName(r.rollfromnodeid, r.rollfrommoduletype),'SYSTEM')  as 
rollFromModuleType,

        r.rollfromphysicalloc as rollFromPhySicalLoc,

        r.rollfromifindex as rollFromIfIndex,

        (select nesysid from ne_info_table where nenodeid=r.rolltonodeid and 
r.rolltonodeid !=0 )  as ToNodeId,

        r.rolltonodeid,

        nvl(getSlotModuleName(r.rolltonodeid, r.rolltomoduletype),'SYSTEM')  as 
rollToModuleType,

        r.rollTophysicalloc as rollToPhySicalLoc,

        r.rollToifindex as rollToIfIndex,

        r.rollNodeid as nodeId,

        r.Connectionindex as connectionIndex,

        r.canBeCompleted as canBeCompleted,

        r.canBeFinished as canBeFinished,

        r.canBeCancelled as canBeCancelled,

        r.rollPathModelType as rollPathModelType,

        r.rollpathobjectindex as rollPathObjectIndex,

        r.rollFromModelType as rollFromModelType,

        r.rollfromobjectindex as rollfromobjectindex,

        r.rollToModelType as rollToModelType,

        r.rolltoobjectindex as rollToObjectindex,

        nvl(getPortModuleName(r.rollpathnodeid, r.rollpathmoduletype),'SYSTEM')  as 
rollPathPortModuleType,

        nvl(getPortModuleName(r.rollfromnodeid, r.rollfrommoduletype),'SYSTEM')  as 
rollFromPortModuleType,

        nvl(getPortModuleName(r.rolltonodeid, r.rolltomoduletype),'SYSTEM')  as 
rollToPortModuleType,

        r.rollname as rollName,

        r.canBeForced as canBeForced,

        r.rowid as myrow

From

        roll_table r;

2.2.62 Self_Monitoring_View

create or replace view Self_Monitoring_view AS

SELECT sp.DisplayName,

       sp.ModelType,

       sm.CollectionTime,

       sm.Value,

       sm.nedbaccessid,

       ne.Nesysid as Nesysid,

       ne.NEAliasID as NEAliasID,

       sm.rowid as MyRow

FROM   SERVER_MONITOR_TABLE sm,

       SERVER_PARAMETER_TABLE sp,

       ne_info_table ne

WHERE  sp.ParameterIndex=sm.ParameterIndex

  and  sm.nedbaccessid=ne.nedbaccessid

union

SELECT sp.DisplayName,

       sp.ModelType,

       sm.CollectionTime,

       sm.Value,

       sm.nedbaccessid,

       pn.Nesysid as Nesysid,

       'CTM' as NEAliasID,

       sm.rowid as MyRow

FROM   SERVER_MONITOR_TABLE sm,

       SERVER_PARAMETER_TABLE sp,

       purged_ne_table pn

WHERE  sp.ParameterIndex=sm.ParameterIndex

  and  sm.nedbaccessid=pn.nedbaccessid

union

SELECT sp.DisplayName,

       sp.ModelType,

       sm.CollectionTime,

       sm.Value,

       sm.nedbaccessid,

       'CTM' as Nesysid,

       'CTM' as NEAliasID,

       sm.rowid as MyRow

FROM   SERVER_MONITOR_TABLE sm,

       SERVER_PARAMETER_TABLE sp

WHERE  sp.ParameterIndex=sm.ParameterIndex

  AND  sm.nedbaccessid=0;

2.2.63 Service_Availability_Log_View

CREATE OR REPLACE VIEW SERVICE_AVAILABILITY_LOG_VIEW AS

select

	SERVICE_INSTANCE_NAME AS SERVICE_INSTANCE_NAME,

        FIRST_ACTIVATED AS FIRST_ACTIVATED,

        DEACTIVATED AS DEACTIVATED,

	STATUS AS STATUS,

	LAST_CRASH AS LAST_CRASH,

	PAST_UPTIME AS TOTAL_UPTIME,

	PERC_UPTIME AS PERC_UPTIME

  FROM SERVICE_AVAILABILITY_TABLE WHERE STATUS = 0;

2.2.64 SPR_View

CREATE OR REPLACE VIEW SPR_VIEW AS

select

       c.NEDBACCESSID as NEDBACCESSID,

       c.MODULETYPE as MODULETYPE,

       c.MODELTYPE as NEMODELTYPE,

       a.MODULENAME as MODULENAME,

       c.PHYSICALLOC as PHYSICALLOC,

       c.IFINDEX as IFINDEX,

       c.COL15 as MTUSIZE

from EQPT_INFO_TABLE c,NE_INFO_TABLE b,MODULE_TYPE_TABLE a

where c.OBJECTINDEX = 186 and

c.NEDBACCESSID = b.NEDBACCESSID and

c.MODELTYPE = a.MODELTYPE and

c.MODULETYPE = a.MODULETYPE;

2.2.65 SW_Active_Alarm_View

create or replace view SW_Active_Alarm_View as

select distinct

	aa.ActiveAlarmSeverity,

	ni.NESysID,

	if.ifName,

	aa.ActiveAlarmTimeStamp,

	en.EnumString,

	aa.ActiveAlarmServEff,

	aa.ActiveAlarmAdditionalInfo,

	aa.NEDBAccessID,

	aa.ModuleOrIfIndex,

	aa.rowid as myrow

from

	NE_INFO_TABLE ni,

	INTERFACE_TABLE if,

	ACTIVE_ALARM_TABLE aa,

	ALARM_EVENT_TYPE_ENUM_TABLE en

where

	aa.NEDBAccessID = ni.NEDBAccessID AND

	aa.ModuleOrIfIndex= if.ifStaticIndex AND

	aa.ActiveAlarmType = en.EnumIndex ;

2.2.66 SW_User_Port_View

create or replace view SW_User_Port_View as

select

	p.userid,

	p.nedbaccessid,

	p.neportifindex,

	i.ifName,

	p.rowid as myrow

from

	sway_user_port_table p,

	interface_table i

where

	p.NEPortIfIndex = i.ifstaticindex;

2.2.67 TL1_Active_Alarm_View

CREATE OR REPLACE VIEW tl1_active_alarm_view AS

SELECT

       ni.NESysID,

       aa.ModuleOrIfIndex,

       aa.AlarmStatus,

       aa.ActiveAlarmSeverity,

       en.TL1String,

       aa.ActiveAlarmServEff,

       TO_CHAR(aa.ActiveAlarmTimeStamp, 'MM/DD/YYYY HH24:MI:SS') AS ActiveAlarmTimeStamp,

       en.EnumString,

       eq.ModuleName,

       aa.NEDBAccessID,

       ni.NEModelType,

       TO_CHAR(aa.PhysicalLoc) AS PhysicalLoc,

       ni.DeleteState,

       aa.ObjectType,

	   aa.STROBJINSTANCE

FROM

       ne_info_table ni,

       active_alarm_table aa,

       alarm_event_type_enum_table en,

       module_type_table eq

WHERE

       aa.ActiveAlarmSeverity > 4               AND

       aa.ModuleType          = eq.ModuleType   AND

       ni.NEModelType         = eq.ModelType    AND

       aa.NEDBAccessID        = ni.NEDBAccessID AND

       aa.ActiveAlarmType     = en.EnumIndex    AND

       aa.AlarmStatus         = 0;

2.2.68 TL1_NE_View

create or replace view TL1_NE_View as

select distinct

        si.SubnetName,

        ni.NEIPAddr,

	ni.NEIPV6Addr,

        ni.NESysID,

        ni.GNEID,

        ni.NEModelType,

        ni.NEVersion,

        (select GNESysID from GNE_Table where GNEID=ni.GNEID) as GNESysID,

        ni.NEState,

        ni.isNEConnected,

        ni.NESubNetworkID,

        ni.NEDBAccessID,

        ni.NEModelIndex,

        ni.DeleteState

			ni.IsTunnelRequired,

		isIPV6(ni.NEDBAccessID) isipv6

from

        NE_INFO_TABLE      ni,

        Subnetwork_Table   si,

        GNE_Table          gi

where

        ni.NESubnetworkID = si.SubnetID AND

        ni.GNEID          = gi.GNEID(+)

UNION

select distinct

        si.SubnetName,

        ni.NEIPAddr,

        ni.NESysID,

        ni.GNEID,

        ni.NEModelType,

        ni.NEVersion,

        si.SubnetName,

        ni.NEState,

        ni.isNEConnected,

        ni.NESubNetworkID,

        ni.NEDBAccessID,

        ni.NEModelIndex,

        ni.DeleteState

from

        NE_INFO_TABLE      ni,

        Subnetwork_Table   si

where

        ni.NESubnetworkID = si.SubnetID AND

        ni.NEModelType    = 34 ;

2.2.69 Topology_Circuit_View

create or replace view TOPOLOGY_CIRCUIT_VIEW as

WITH

   SRC  AS (select cktctpnedbaccessid,

                   CktUniqueId,

                   cktctpnodeid,

                   cktnodeid,

                   CktCtpModuleType,

                   cktctpPhysicalLoc,

                   CktctpModelType,

                   CktctpIfIndex,

                   cktctpDropProt from circuit_ctp_tbl where ctp_type = 0 ),

   SEC  AS (select cktctpnedbaccessid,

                   CktUniqueId,

                   cktctpnodeid,

                   cktnodeid,

                   CktCtpModuleType,

                   cktctpPhysicalLoc,

                   CktctpModelType,

                   CktctpIfIndex,

                   cktctpDropProt from circuit_ctp_tbl where ctp_type = 1 ),

   DEST AS ( select cktctpnedbaccessid,

                   CktUniqueId,

                   cktctpnodeid,

                   cktnodeid,

                   CktCtpModuleType,

                   cktctpPhysicalLoc,

                   CktctpModelType,

                   CktctpIfIndex,

                   CktCtpPathProt,

                   cktctpDropProt from circuit_ctp_tbl where ctp_type = 2 ),

   spantbl AS ( select spancircuit.CktNodeId as NodeId,

                       spancircuit.CktUniqueId as UniqueId

                from CIRCUIT_BS_Tbl circuit,

                     circuit_ctp_tbl ctp_src,

                     circuit_ctp_tbl ctp_dst,

                     circuit_span_BS_tbl spancircuit,

                     ne_info_table ne_src,

                     ne_info_table ne_dst

                where ctp_src.CTPACCESSID = spancircuit.CKTSPANSRCCTPACCESSID

                      and   ctp_dst.CTPACCESSID = spancircuit.CKTSPANDSTCTPACCESSID

                      and   spancircuit.CktNodeId = circuit.CktNodeId

                      and   spancircuit.CktUniqueId = circuit.CktUniqueId

                      and   ne_src.nenodeid = ctp_src.CKTCTPNODEID

                      and   ne_dst.nenodeid = ctp_dst.CKTCTPNODEID

                      and   (ctp_src.ctp_type = 3 and ctp_dst.ctp_type = 4)  ),

   span AS ( select span.cktnodeid, span.cktuniqueid, ctpsrc.cktctpnodeid asspansrcnodeid,

                    ctpdest.cktctpnodeid as spandestnodeid,

                    span.cktlinkid as cktlinkid

             from circuit_ctp_tbl ctpsrc,

                  circuit_ctp_tbl ctpdest,

                  circuit_span_bs_tbl   span

             where ctpsrc.ctpaccessid=span.cktspansrcctpaccessid and

                   ctpdest.ctpaccessid=span.cktspandstctpaccessid

SELECT

    	l2topo.Topology_Name,

        c.CktName as Name,

        c.CktAliasName as Alias,

        c.CktType as Type,

        c.CktSize as CktSize,

        c.CktDirection as Direction,

        c.CktState as State,

	    get_nesysid(src.cktctpnedbaccessid) as SrcNodeid,      /* *@INDEX A 4 */

	    src.cktctpnodeid as cktsrcnodeid,

		nvl(getModulename1(src.cktnodeid, src.CktCtpModuleType),'SYSTEM')  as 
SrcModuleType,

        src.CktCtpPhysicalLoc as SrcPhysicalLoc,

        src.CktCtpModelType as SrcModelType,

        src.CktCtpIfIndex as SrcInterfaceId,

        nvl(getModulename1(sec.cktnodeid, sec.CktCtpModuleType),'SYSTEM') as 
SecSrcModuleType,

        sec.CktCtpPhysicalLoc as SecSrcPhysicalLoc,

        sec.CktCtpModelType as CktSecSrcModelType,

        sec.CktCtpIfIndex as SecSrcInterfaceId,

        collapseddestnodename(c.CKTNODEID,c.CKTUNIQUEID) as destnodename,

        collapsedmodulename(c.CKTNODEID,c.CKTUNIQUEID) as cktdestModuleName,

		dest.CktCtpPhysicalLoc as DestPhysicalLoc,

        dest.CktCtpModelType as CktDestModelType,

        collapsedifindex(c.CKTNODEID,c.CKTUNIQUEID) as DestInterfaceId,

        c.CktIsMonitor as Ismonitor,

        src.CktCtpDropProt as SrcDropProt,

        c.CktNodeId as NodeId,

        c.CktUniqueId as UniqueId,

        c.CktDescription as Description,

        dest.cktctpnodeid as DestNodeId,

        span.cktlinkid,

        span.SpanSrcNodeId as CktSpanSrcNodeId,

        span.SpanDestNodeId as CktSpanDestNodeId,

        c.CktEnhancedState,

        c.CktProtectionType,

        dest.CktCtpPathProt  as DestPathProtection,

        l2topo.rowid as myrow,

	l2topo.TOPONODEID,

	l2topo.TOPOUNIQUEID,

	eqptinfoport(src.cktctpnedbaccessid,src.cktctpphysicalloc,0,src.cktctpmoduletype) as 
CktSrcPortName,

	eqptinfoport(sec.cktctpnedbaccessid,sec.cktctpphysicalloc,0,sec.cktctpmoduletype) as 
CktSecSrcPortName,

	collapsedeqptportname(c.CKTNODEID,c.CKTUNIQUEID) as CktDestPortName,

	sec.CktNodeId as CktSecSrcNodeId,

	c.customerId as CktCusiomerId,

	c.serviceId as CktServiceId,

	c.cktComment as CktNote,

	l2topo.TopoType,

	l2topo.TopoSize,

        l2topo.TopoAliasName as TopoAliasName,

	l2topo.TopoDescription as TopoDescription,

        l2topo.AlarmSeverity as TopoAlarmSeverity,

        l2topo.TopoStatus as TopoStatus,

        l2topo.TopoProtection as TopoProtection,

        l2topo.TopoOperStatus as TopoOprStatus,

        l2topo.TOPORESYNCSTATUS as TopoResyncStatus,

	l2topo.BW_SP_Management,

	l2topo.BW_Committed,

	l2topo.BW_Best_Effort,

	l2topo.COS_COMMIT,

        l2topo.BW_Utilized,

        l2topo.BW_Available,

        l2topo.BW_AVVID_CONTROL,

        l2topo.COS_SP_MANAGEMENT,

        l2topo.COS_COMMITTED,

        l2topo.COS_AVVID_VOICEVIDEO,

        l2topo.COS_AVVID_CONTROL,

        l2topo.BW_Group1,

	    l2topo.BW_Group2,

        l2topo.COS_GROUP1,

        l2topo.COS_GROUP2,

	    l2topo.BW_A0,

        l2topo.BW_A1,

        l2topo.BW_B_CIR,

        l2topo.CLASS_SP_MGMT,

        l2topo.CLASS_BEST_EFFORT,

        l2topo.CLASS_CIR,

        l2topo.CLASS_AVVID_CTRL,

        l2topo.CLASS_AVVID_VOICEVIDEO,

	    nvl(vcg.VCGDirection,-1) as VCGDirection,

        nvl(vcg.ParentCktNodeId,-1) as ParentCktNodeId,

        nvl(vcg.ParentCktUniqueId,-1) as ParentCktUniqueId

FROM

	L2Topology_Table l2topo,

    	L2Topology_Circuits_Table l2circuits,

        circuit_bs_tbl c,

		src,

		sec,

		dest,

		span,

		spantbl,

        circuit_vcg_tbl vcg

where

    l2topo.TopoNodeID = l2circuits.TopoNodeID(+) AND

    l2topo.TopoUniqueId = l2circuits.TopoUniqueId(+) AND

    l2circuits.NodeId = c.CktNodeId AND

    l2circuits.UniqueId = c.CktUniqueId and

    c.CktNodeId             = src.CktNodeId(+)  and

    c.CktUniqueId           = src.CktUniqueId(+) and

    c.CktNodeId             = sec.CktNodeId(+) and

    c.CktUniqueId           = sec.CktUniqueId(+) and

    c.CktNodeId             = dest.CktNodeId(+) and

    c.CktUniqueId           = dest.CktUniqueId(+) and

    c.CktNodeId             = span.CktNodeId(+) and

    c.CktUniqueId           = span.CktUniqueId(+) and

    c.CktNodeId             = vcg.CktNodeId(+) and

    c.CktUniqueId           = vcg.CktUniqueId(+) and

    c.CktNodeId             = spantbl.NodeId(+) and

    c.CktUniqueId           = spantbl.UniqueId(+) and

    c.cktState != 12 AND

	(l2topo.TopoStatus > 2 OR l2topo.TopoStatus < 2);

2.2.70 Topology_Circuit_View_CTM5_0

create or replace view TOPOLOGY_CIRCUIT_VIEW_CTM5_0 as

SELECT

    	l2topo.Topology_Name,

        c.CktName as Name,

        c.CktType as Type,

        c.CktSize as CktSize,

        c.CktDirection as Direction,

        c.CktState as State,

        (select nesysid from ne_info_table where nenodeid=c.cktsrcnodeid and 
c.cktsrcnodeid !=0 )  as SrcNodeid,

        c.cktsrcnodeid,

        nvl(getModulename1(c.cktsrcnodeid, c.CktSrcModuleType),'SYSTEM')  as 
SrcModuleType,

        c.CktSrcPhysicalLoc as SrcPhysicalLoc,

        c.CktSrcModelType as SrcModelType,

        c.CktSrcIfIndex as SrcInterfaceId,

        nvl(getModulename1(c.cktsecsrcnodeid, c.CktSecSrcModuleType),'SYSTEM') as 
SecSrcModuleType,

        c.CktSecSrcPhysicalLoc as SecSrcPhysicalLoc,

        c.CktSecSrcModelType as CktSecSrcModelType,

        c.CktSecSrcIfIndex as SecSrcInterfaceId,

        c.cktDestNodename as destnodename,

        c.cktdestModuleName,

        c.CktDestPhysicalLoc as DestPhysicalLoc,

        c.CktDestModelType as CktDestModelType,

        c.CktDestIfIndex  as DestInterfaceId,

        c.CktIsMonitor as Ismonitor,

        c.CktSrcDropProt as SrcDropProt,

        c.CktNodeId as NodeId,

        c.CktUniqueId as UniqueId,

        c.CktDescription as Description,

        dest.cktdestnodeid as DestNodeId,

        spantbl.cktlinkid,

        spantbl.CktSpanSrcNodeId,

        spantbl.CktSpanDestNodeId,

        c.CktEnhancedState,

        c.CktProtectionType,

        dest.CktDestPathProt  as DestPathProtection,

        l2topo.rowid as myrow,

	l2topo.TOPONODEID,

	l2topo.TOPOUNIQUEID,

	c.cktSrcPortName as CktSrcPortName,

	c.cktSecSrcPortName as CktSecSrcPortName,

	c.cktDestPortName as CktDestPortName,

	c.cktSecSrcNodeId as CktSecSrcNodeId,

	c.customerId as CktCusiomerId,

	c.serviceId as CktServiceId,

	c.cktComment as CktNote,

	l2topo.TopoType,

	l2topo.TopoSize,

	l2topo.TopoDescription as TopoDescription,

    l2topo.AlarmSeverity as TopoAlarmSeverity,

    l2topo.TopoStatus as TopoStatus,

    l2topo.TopoProtection as TopoProtection,

    l2topo.TopoOperStatus as TopoOprStatus,

    l2topo.TOPORESYNCSTATUS as TopoResyncStatus,

	l2topo.BW_SP_Management,

	l2topo.BW_Committed,

	l2topo.BW_Best_Effort,

	l2topo.COS_COMMIT,

        l2topo.BW_Utilized,

        l2topo.BW_Available,

        l2topo.BW_AVVID_CONTROL,

        l2topo.COS_SP_MANAGEMENT,

        l2topo.COS_COMMITTED,

        l2topo.COS_AVVID_VOICEVIDEO,

        l2topo.COS_AVVID_CONTROL,

        l2topo.BW_Group1,

	l2topo.BW_Group2,

        l2topo.COS_GROUP1,

        l2topo.COS_GROUP2

FROM

	L2Topology_Table l2topo,

    	L2Topology_Circuits_Table l2circuits,

        circuit_tbl c,

        circuit_dest_tbl dest,

        circuit_span_tbl spantbl

where

	l2topo.TopoNodeID = l2circuits.TopoNodeID(+) AND

   	l2topo.TopoUniqueId = l2circuits.TopoUniqueId(+) AND

    	l2circuits.NodeId = c.CktNodeId AND

    	l2circuits.UniqueId = c.CktUniqueId and

        c.CktNodeId = dest.CktNodeId(+)  and

        c.CktUniqueId = dest.CktUniqueId(+) and

        c.CktNodeId = spantbl.CktNodeId(+) and

        c.CktUniqueId = spantbl.CktUniqueId(+) and

        c.cktState != 12 AND

        l2topo.TopoStatus != 2;

2.2.71 Topology_GUI_View

CREATE OR REPLACE VIEW topology_gui_view AS

SELECT DISTINCT

        l2Topology.Topology_Name as Name,

        l2Topology.TopoAliasName as Alias,

	l2Topology.TopoNodeID as TopoNodeID,

	l2Topology.TopoUniqueId as TopoUniqueID,

        l2Topology.TopoType as TopoType,

        l2Topology.TopoSize as TopoSize,

        l2Topology.TopoDescription as Description,

        l2Topology.AlarmSeverity as Severity,

        l2Topology.TopoStatus as TopoStatus,

        l2Topology.TopoProtection as Protection,

        l2Topology.TopoOperStatus as OprStatus,

        l2Topology.TOPORESYNCSTATUS as ResyncStatus,

        l2Topology.rowId as myrow,

	c.CktType as CktType,

	l2Topology.BW_SP_Management,

	l2Topology.BW_Committed,

	l2Topology.BW_Best_Effort,

        l2Topology.BW_AVVID_CONTROL,

        l2Topology.COS_SP_MANAGEMENT,

        l2Topology.COS_COMMITTED,

        l2Topology.COS_AVVID_VOICEVIDEO,

        l2Topology.COS_AVVID_CONTROL,

        l2Topology.BW_Group1,

        l2Topology.BW_Group2,

        l2Topology.COS_GROUP1,

        l2Topology.COS_GROUP2

FROM

	L2Topology_Table l2Topology,

        L2Topology_Circuits_Table l2circuits,

        circuit_tbl c

WHERE

        l2Topology.TopoNodeID = l2circuits.TopoNodeID AND

        l2Topology.TopoUniqueId = l2circuits.TopoUniqueId AND

        l2circuits.NodeId = c.CktNodeId AND

        l2circuits.UniqueId = c.CktUniqueId AND

	l2Topology.TopoStatus != 2;

2.2.72 Trace_Vlan_View

CREATE OR REPLACE VIEW trace_vlan_view AS

SELECT DISTINCT

        eit1.COL250 as VLANID,

        eit2.COL10 as BRIDGEID,

        eit2.COL13 as DESIGNATEDROOT,

        eit1.NEDBACCESSID,

        ni.NESYSID,

        eit1.physicalloc,

        ni.CONFIGSYNCSTATUS as NODEMANAGERSTATE,

        eit1.COL13 as UNTAGGED,

        eit2.COL7 as BRIDGEMAXAGE,

        eit2.COL8 as BRIDGEHELLOTIME,

        eit2.COL15 as ROOTPORT,

        eit2.COL14 as ROOTCOST,

        eit1.COL5 as STPSTATE,

        eit1.COL11 as STPENABLED,

        st.SUBNETID,

        ni.rowid as myrow

FROM

        eqpt_info_table eit1,

        eqpt_info_table eit2,

        eqpt_info_table eit3,

        ne_info_table ni,

        subnetwork_table st

WHERE

        ni.NEDBACCESSID   = eit1.NEDBACCESSID AND

        ni.NEDBACCESSID   = eit2.NEDBACCESSID(+) AND

        ni.NEDBACCESSID   = eit3.NEDBACCESSID(+) AND

        (eit1.OBJECTINDEX=105 OR eit1.OBJECTINDEX=205) AND

        11=eit2.OBJECTINDEX(+) AND

        104=eit3.OBJECTINDEX(+) AND eit3.COL12!='LINEAR_NO_SWITCH' AND

        ni.CONFIGSYNCSTATUS=3 AND

        (ni.NESTATE=1 OR ni.NESTATE=4) AND

        st.SUBNETID = ni.NESUBNETWORKID;

2.2.73 Unmanaged_NE_Inventory_View

create or replace view UNMANAGED_NE_INVENTORY_VIEW as

SELECT DISTINCT

    t1.NEDBACCESSID,

    t1.NEMODELINDEX,

    t1.NESYSID,

    t1.NEDESCRIPTION,

    t3.MODELNAME,

    t1.NECONFIGMODE,

    t1.NEVERSION,

    t2.SERIALNUMBER,

    t1.NECLLICODE,

    t1.GNEID,

    t1.NESUBNETWORKID,

    t2.rowid as NYROW

FROM

    NE_INFO_TABLE t1,

    UNMANAGED_NE_INVENTORY_TABLE t2,

    VERSION_TABLE t3

WHERE

    t1.NEDBACCESSID = t2.NEDBACCESSID

AND

    t1.NEMODELINDEX = t3.NEMODELINDEX;

2.2.74 User_NE_View

create or replace view User_NE_View as

select

        distinct

        grouporneid  as GROUPORNEID,

        userid       as USERID,

        'AA' as MYROW

from   User_Ne_table;

2.2.75 User_View

create or replace view User_View as

select

	umt.UserID,

	umt.TreeNodeType,

	umt.GroupOrNEID,

	ut.UserName,

	ut.rowid as myrow

from

	USER_MAP_TABLE umt,

	USER_TABLE ut

where

	umt.UserID = ut.UserID ;

2.2.76 Vlan_Drop_Ports_QoS_View

create or replace view VLAN_DROP_PORTS_QOS_VIEW as

SELECT DISTINCT

  l2if_qos.MATCH_ANY,

  l2if_qos.MATCH_DSCP,

  l2if_qos.MATCH_COS,

  l2if_qos.MATCH_IP_PRECEDENCE,

  l2if_qos.MATCH_DSCP_VALUE,

  l2if_qos.MATCH_COS_VALUE,

  l2if_qos.MATCH_IP_PRECEDENCE_VALUE,

  l2if_qos.ANDOR,

  l2if_qos.CLASS_NAME,

  l2if_qos.CIR_TYPE,

  l2if_qos.MAX_RATE_KBPS,

  l2if_qos.BURST_SIZE_BPS,

  l2if_qos.CIR_COS_TYPE,

  l2if_qos.CIR_COS_VALUE,

  l2if_qos.EXCESS_ACTION,

  l2if_qos.PIR_BURST_BPS,

  l2if_qos.PIR_KBPS,

  l2if_qos.PIR_COS_TYPE,

  l2if_qos.PIR_COS_VALUE,

  l2if_qos.VIOLATE_ACTION,

  l2if_qos.VIOLATE_COS_VALUE,

  l2if_qos.BEFF_TYPE,

  l2if_qos.BEFF_RATE_KBPS,

  l2if_qos.BEFF_BURST_SIZE_BPS,

  l2if_qos.NEID,

  l2if_qos.PORT_NUMBER,

  l2if_qos.SLOT_NUMBER,

  l2if_qos.BRIDGE_GROUP_NUMBER,

  l2if_qos.rowId as myrow,

  l2if_qos.PORT_SUB_INTERFACE

FROM

    L2_Interface_Table l2if,

    L2_Interface_QOSClass_Table l2if_qos

WHERE

  l2if.NEID =  l2if_qos.NEID and

  l2if.SLOT_NUMBER =   l2if_qos.SLOT_NUMBER and

  l2if.BRIDGE_GROUP_NUMBER = l2if_qos.BRIDGE_GROUP_NUMBER and

  l2if.PORT_NUMBER = l2if_qos.PORT_NUMBER and

  l2if.PORT_SUB_INTERFACE = l2if_qos.PORT_SUB_INTERFACE;

  order by CLASS_NAME;

2.2.77 Vlan_Drop_Ports_View

create or replace view VLAN_DROP_PORTS_VIEW as

SELECT DISTINCT

    l2topo.Topology_Name,

    l2vlan.VLAN_Number,

    l2vlan.Customer_Id,

    l2vlan.Service_Id,

    l2if.NEID,

    l2if.PORT_NUMBER,

    l2if.SLOT_NUMBER,

    l2if.BRIDGE_GROUP_NUMBER,

    l2if.PORT_SUB_INTERFACE,

    l2if.INTERFACE_NAME,

    l2if.INTERFACE_TYPE,

    l2if.PORT_TYPE,

    l2if.CONNECTION_TYPE,

    l2if.PORT_STATE,

    l2if.QOSTEMPLATE_NAME,

    l2topo.TOPONODEID,

	l2topo.TOPOUNIQUEID,

	l2if.rowId as myrow,

	l2fp.PM_STATUS,

    neIf.NESYSID,

    l2if.RSTP_STATUS,

    bg.BRIDGE_PROTOCOL

	GETIPSLA_IPADDR(l2if.NEID,l2if.SLOT_NUMBER,l2if.BRIDGE_GROUP_NUMBER) as IP_ADDRESS,

    GETIPSLA_SUBNETMASK(l2if.NEID,l2if.SLOT_NUMBER,l2if.BRIDGE_GROUP_NUMBER) as 
SUBNET_MASK

FROM

    L2Topology_Table l2topo,

    L2_Vlan_Table l2vlan,

    L2_Frontport_Table l2fp,

    Bridge_Group_Table bg,

    NE_INFO_TABLE neIf,

	/* OUTER JOIN ADDED TO FIX THE PROBLEM ON THE EMPTY PORT CHANNEL TABLE */

    (L2_Interface_Table l2if LEFT OUTER JOIN Port_Channel_Table l2pc ON (l2pc.NE_NODE_ID =

			l2if.NEID AND l2pc.SLOT_NUMBER = l2if.SLOT_NUMBER AND l2pc.PORT_CHANNEL_ID =

			l2if.PORT_NUMBER AND l2if.INTERFACE_TYPE = 552))

WHERE

	l2vlan.TopoNodeID = l2topo.TopoNodeID AND

    l2vlan.TopoUniqueId = l2topo.TopoUniqueId AND

    l2if.INTERFACE_TYPE != 166 AND

    l2if.INTERFACE_TYPE != 174 AND

    l2if.INTERFACE_TYPE != 186 AND

    l2if.INTERFACE_TYPE != 190 AND

    l2if.INTERFACE_TYPE != 284 AND

    l2if.INTERFACE_TYPE != 285 AND

	bg.toponodeid = l2vlan.topoNodeid AND

	bg.topouniqueid = l2vlan.topouniqueid AND

	bg.vlan_number = l2vlan.vlan_number AND

	l2if.NEID = bg.NEID AND

	l2if.Slot_Number = bg.slot_number AND

	((l2fp.NEID = l2if.NEID AND

	l2fp.SLOT_NUMBER = l2if.SLOT_NUMBER AND

	l2fp.PORT_NUMBER = l2if.PORT_NUMBER AND

	l2if.INTERFACE_TYPE != 552) OR

	(l2pc.NE_NODE_ID = l2if.NEID AND

	l2pc.SLOT_NUMBER = l2if.SLOT_NUMBER AND

	l2pc.PORT_CHANNEL_ID = l2if.PORT_NUMBER AND

	l2fp.PM_STATUS = 0 AND

	l2if.INTERFACE_TYPE = 552)) AND

	l2if.BRIDGE_GROUP_NUMBER = bg.Bridge_group_number AND

    l2if.NEID = neIf.NEDBACCESSID;

2.2.78 Vlan_Service_View

create or replace view VLAN_SERVICE_VIEW as

SELECT DISTINCT

    l2topo.Topology_Name,

    l2topo.TopoNodeID,

    l2topo.TopoUniqueId,

    l2vlan.VLAN_Number,

    l2vlan.Customer_Id,

    l2vlan.Service_Id,

	l2vlan.VlanStatus,

	l2topo.TopoResyncStatus,

	l2topo.TopoType,

	l2topo.TopoStatus,

	l2topo.TopoSize,

	l2vlan.ManagedVlan,

	l2vlan.rowId as myrow

FROM

    L2Topology_Table l2topo,

    L2_Vlan_Table l2vlan

WHERE

    l2vlan.TopoNodeID = l2topo.TopoNodeID AND

    l2vlan.TopoUniqueId = l2topo.TopoUniqueId AND

    l2vlan.VlanStatus != 2;

2.3 Cisco MGX Media Gateway Views

2.3.1 Access_Node

create or replace view access_node 
(node_id,obj_id,type,subtype,ipaddress,p_ipaddress,p_slot,p_port,r_slot,r_port,device_id)

AS SELECT

    to_number(eit.COL1) as node_id ,

    to_number(eit.COL1) as obj_id ,

    to_number(eit.COL1) as type ,

    to_number(eit.COL1) as subtype ,

    to_number(eit.COL1) as ipaddress ,

    to_number(eit.COL1) as p_ipaddress ,

    to_number(eit.COL1) as p_slot ,

    to_number(eit.COL1) as p_port ,

    to_number(eit.COL1) as r_slot ,

    to_number(eit.COL1) as r_port ,

    to_number(eit.COL1) as device_id

FROM

    eqpt_info_table eit

WHERE

    eit.NEDBACCESSID = 10000;

2.3.2 Bis_Object

create or replace view bis_object 
(obj_id,name,type,subtype,node_id,ipaddress,p_node_id,p_ipaddress,p_bay,p_line,p_slot,p_po
rt,p_logicalport,f_slot,f_port,status,active,model) AS SELECT

    to_number(eit.COL1) as obj_id ,

    eit.COL1 as name ,

    to_number(eit.COL1) as type ,

    to_number(eit.COL1) as subtype ,

    to_number(eit.COL1) as node_id ,

    to_number(eit.COL1) as ipaddress ,

    to_number(eit.COL1) as p_node_id ,

    to_number(eit.COL1) as p_ipaddress ,

    to_number(eit.COL1) as p_bay ,

    to_number(eit.COL1) as p_line ,

    to_number(eit.COL1) as p_slot ,

    to_number(eit.COL1) as p_port ,

    to_number(eit.COL1) as p_logicalport ,

    to_number(eit.COL1) as f_slot ,

    to_number(eit.COL1) as f_port ,

    to_number(eit.COL1) as status ,

    to_number(eit.COL1) as active ,

    to_number(eit.COL1) as model

FROM

    eqpt_info_table eit

WHERE

    eit.NEDBACCESSID = 10000;

2.3.3 Card

CREATE OR REPLACE VIEW card AS

SELECT

       -1 as obj_id,

       eit.NEDBACCESSID as node_id,

       9999 as network_id,

       -1 as node_type,

       to_number(eit.COL1) as shelf,

       to_number(eit.COL2) as slot,

       to_number(eit.COL3) as fc_type,

       eit.Col4 as fc_dscrp,

       eit.Col5 as fc_serial_num,

       eit.Col6 as fc_hw_rev,

       eit.Col7 as fc_fw_rev,

       to_number(eit.Col8)  as fc_reset_reason,

       -1 as fc_fab_num,

       to_number(eit.Col9) as fc_state,

       -1 as mib_version,

       to_number(eit.COL10) as bc_type,

       eit.Col11 as bc_dscrp,

       eit.Col12 as bc_serial_num,

       eit.Col13 as bc_hw_rev,

       '' as bc_fw_rev,

       to_number(eit.Col14) as bc_state,

       to_number(eit.COL15) as sec_bc_type,

       eit.Col16 as sec_bc_dscrp,

       eit.Col17 as sec_bc_serial_num,

       eit.Col18 as sec_bc_hw_rev,

       '' as sec_bc_fw_rev,

       to_number(eit.Col19) as sec_bc_state,

       -1 as rate_up,

       -1 as rate_dn,

       -1 as fast_dn,

       -1 as measure_time,

       -1 as qir_timeout,

       -1 as chan_allowed,

       -1 as rate_ctrl_allowed,

       -1 as clk_conn_type,

       -1 as curr_clk_src,

       -1 as pri_clk_src,

       -1 as sec_clk_src,

       -1 as clk_impedence,

       to_number(eit.Col20) as out_of_sync,

       -1 as bnm_ln_format,

       -1 as aps_info,

       to_number(eit.Col35) as ml_chan_stat,

       to_number(eit.Col21) as logical_slot,

       -1 as egr_qos_feature,

       to_number(eit.Col22) as sct_id,

       to_number(eit.Col23) as sct_version,

       -1 as clk_err_reason,

       -1 as fc_admin_status,

       eit.ifindex as ent_phy_idx,

       to_number(eit.Col24) as bc_ent_phy_idx,

       to_number(eit.Col25) as sec_bc_ent_phy_idx,

       to_number(eit.Col26) as transaction_id,

       to_number(eit.Col27) as cac_mode,

       to_number(eit.Col28) as max_ima_grp,

       to_number(eit.Col29) as configured_ima_grp,

       to_number(eit.Col30) as min_grp_tx_ima_id,

       to_number(eit.Col31) as max_grp_tx_ima_id,

       to_number(eit.Col32) as ima_ver_fallback_enable,

       to_number(eit.Col33) as ima_restart_enable,

       to_number(eit.Col34) as ima_ent_phy_idx,

       -1 as reserved

FROM

       ne_info_table ne,

       eqpt_info_table eit,

       module_type_table mtt

WHERE

       ne.NEDBAccessId = eit.NEDBAccessId AND

       ne.NEModelType  = mtt.ModelType    AND

       eit.ModuleType  = mtt.ModuleType   AND

       mtt.ModuleType  > 20002            AND

       mtt.ModuleType  < 20030;

2.3.4 Line

CREATE OR REPLACE VIEW line AS

SELECT

       eit.NEDBACCESSID as node_id,

       to_number(eit.COL5) as obj_id,

       9999 as network_id,

       to_number(eit.COL1) as shelf,

       to_number(eit.COL2) as slot,

       to_number(eit.COL4) as line,

       to_number(eit.COL6) as line_type,

       to_number(eit.COL7) as connector,

       to_number(eit.COL8) as enable,

       to_number(eit.COL9) as subtype,

       to_number(eit.COL10) as coding,

       to_number(eit.COL17) as length,

       to_number(eit.COL11) as clock_src,

       to_number(eit.COL12) as loopback,

       to_number(eit.COL24) as line_bitmap,

       -1 as oof_criteria,

       -1 as aisc_check,

       -1 as tx_timing_marker,

       -1 as tx_payload_type,

       '' as commentc,

       -1 as red_severity,

       -1 as rai_severity,

       to_number(eit.COL23) as stat_severity,

       to_number(eit.COL18) as alarm_state,

       to_number(eit.COL19) as stat_alarm_state,

       to_number(eit.COL16) as agg_state,

       -1 as rate,

       -1 as hcs_masking,

       -1 as payload_scramble,

       -1 as frame_scramble,

       -1 as section_state,

       -1 as section_stat_sev,

       -1 as section_stat_state,

       -1 as line_state,

       -1 as line_stat_sev,

       -1 as line_stat_state,

       -1 as path_state,

       -1 as path_stat_sev,

       -1 as path_stat_state,

       -1 as atm_ln_format,

       -1 as aps_flag,

       eit.COL13 as name,

       -1 as chan_assignment,

       to_number(eit.COL3) as bay,

       eit.ifindex as if_index,

       to_number(eit.COL14) as parent_if_index,

       -1 as lpbk_code,

       -1 as invert_clock,

       -1 as subrate_enable,

       -1 as dsu_select,

       -1 as ln_intf_type,

       -1 as clk_freq_threshold,

       -1 as serl_ln_rate,

       -1 as serl_ln_rate_var,

       -1 as sonet_rdiv_type,

       -1 as sonet_rdip_type,

       -1 as sonet_trib_type,

       -1 as sonet_trib_map,

       -1 as sonet_trib_frm,

       -1 as sonet_sig_trnsp,

       -1 as sonet_trib_grp,

       to_number(eit.COL20) as sendcode,

       to_number(eit.COL15) as channel_bitmap,

       to_number(eit.COL25) as  sig_tx_mode,

       -1 as st_chg_trap_en,

       to_number(eit.COL21) as  lpbk_status,

       to_number(eit.COL22) as  channelize,

       to_number(eit.COL26) as  signal_mode,

       to_number(eit.COL27) as  trk_cond_en,

	eit.COL28 as circuit_identifier,

       -1 as reserved

FROM

       ne_info_table ne,

       eqpt_info_table eit,

       module_type_table mtt

WHERE

       ne.NEDBAccessId = eit.NEDBAccessId AND

       ne.NEModelType  = mtt.ModelType    AND

       eit.ModuleType  = mtt.ModuleType   AND

       mtt.ModuleType  = 20051

UNION ALL

SELECT

       eit.NEDBACCESSID as node_id,

       to_number(eit.COL5) as obj_id,

       9999 as network_id,

       to_number(eit.COL1) as shelf,

       to_number(eit.COL2) as slot,

       to_number(eit.COL4) as line,

       to_number(eit.COL6) as line_type,

       to_number(eit.COL7) as connector,

       to_number(eit.COL8) as enable,

       to_number(eit.COL9) as subtype,

       to_number(eit.COL10) as coding,

       to_number(eit.COL17) as length,

       to_number(eit.COL11) as clock_src,

       to_number(eit.COL12) as loopback,

       -1 as line_bitmap,

       to_number(eit.COL24) as oof_criteria,

       to_number(eit.COL25) as aisc_check,

       -1 as tx_timing_marker,

       -1 as tx_payload_type,

       '' as commentc,

       -1 as red_severity,

       to_number(eit.COL26) as rai_severity,

       to_number(eit.COL23) as stat_severity,

       to_number(eit.COL18) as alarm_state,

       to_number(eit.COL19) as stat_alarm_state,

       to_number(eit.COL16) as agg_state,

       to_number(eit.COL27) as rate,

       -1 as hcs_masking,

       -1 as payload_scramble,

       to_number(eit.COL28) as frame_scramble,

       -1 as section_state,

       -1 as section_stat_sev,

       -1 as section_stat_state,

       -1 as line_state,

       -1 as line_stat_sev,

       -1 as line_stat_state,

       -1 as path_state,

       -1 as path_stat_sev,

       -1 as path_stat_state,

       -1 as atm_ln_format,

       -1 as aps_flag,

       eit.COL13 as name,

       -1 as chan_assignment,

       to_number(eit.COL3) as bay,

       eit.ifindex as if_index,

       to_number(eit.COL14) as parent_if_index,

       -1 as lpbk_code,

       -1 as invert_clock,

       to_number(eit.COL29) as subrate_enable,

       to_number(eit.COL30) as dsu_select,

       -1 as ln_intf_type,

       -1 as clk_freq_threshold,

       -1 as serl_ln_rate,

       -1 as serl_ln_rate_var,

       -1 as sonet_rdiv_type,

       -1 as sonet_rdip_type,

       -1 as sonet_trib_type,

       -1 as sonet_trib_map,

       -1 as sonet_trib_frm,

       -1 as sonet_sig_trnsp,

       -1 as sonet_trib_grp,

       to_number(eit.COL20) as sendcode,

       to_number(eit.COL15) as channel_bitmap,

       -1 as  sig_tx_mode,

       -1 as st_chg_trap_en,

       to_number(eit.COL21) as  lpbk_status,

       to_number(eit.COL22) as  channelize,

       -1 as  signal_mode,

       -1 as  trk_cond_en,

	eit.COL31 as circuit_identifier,

       -1 as reserved

FROM

       ne_info_table ne,

       eqpt_info_table eit,

       module_type_table mtt

WHERE

       ne.NEDBAccessId = eit.NEDBAccessId AND

       ne.NEModelType  = mtt.ModelType    AND

       eit.ModuleType  = mtt.ModuleType   AND

       mtt.ModuleType  = 20052

UNION ALL

SELECT

       eit.NEDBACCESSID as node_id,

       to_number(eit.COL5) as obj_id,

       9999 as network_id,

       to_number(eit.COL1) as shelf,

       to_number(eit.COL2) as slot,

       to_number(eit.COL4) as line,

       to_number(eit.COL6) as line_type,

       to_number(eit.COL7) as connector,

       to_number(eit.COL8) as enable,

       to_number(eit.COL9) as subtype,

       to_number(eit.COL10) as coding,

       -1 as length,

       to_number(eit.COL11) as clock_src,

       to_number(eit.COL12) as loopback,

       -1 as line_bitmap,

       -1 as oof_criteria,

       -1 as aisc_check,

       -1 as tx_timing_marker,

       to_number(eit.COL17) as tx_payload_type,

       '' as commentc,

       -1 as red_severity,

       -1 as rai_severity,

       -1 as stat_severity,

       to_number(eit.COL34) as alarm_state,

       -1 as stat_alarm_state,

       to_number(eit.COL16) as agg_state,

       -1 as rate,

       -1 as hcs_masking,

       -1 as payload_scramble,

       -1 as frame_scramble,

       to_number(eit.COL18) as section_state,

       to_number(eit.COL19) as section_stat_sev,

       to_number(eit.COL20) as section_stat_state,

       to_number(eit.COL21) as line_state,

       to_number(eit.COL22) as line_stat_sev,

       to_number(eit.COL23) as line_stat_state,

       to_number(eit.COL24) as path_state,

       to_number(eit.COL25) as path_stat_sev,

       to_number(eit.COL26) as path_stat_state,

       -1 as atm_ln_format,

       -1 as aps_flag,

       eit.COL13 as name,

       -1 as chan_assignment,

       to_number(eit.COL3) as bay,

       eit.ifindex as if_index,

       to_number(eit.COL14) as parent_if_index,

       -1 as lpbk_code,

       -1 as invert_clock,

       -1 as subrate_enable,

       -1 as dsu_select,

       -1 as ln_intf_type,

       -1 as clk_freq_threshold,

       -1 as serl_ln_rate,

       -1 as serl_ln_rate_var,

       to_number(eit.COL27) as sonet_rdiv_type,

       to_number(eit.COL28) as sonet_rdip_type,

       to_number(eit.COL29) as sonet_trib_type,

       to_number(eit.COL30) as sonet_trib_map,

       to_number(eit.COL31) as sonet_trib_frm,

       to_number(eit.COL32) as sonet_sig_trnsp,

       to_number(eit.COL33) as sonet_trib_grp,

       to_number(eit.COL20) as sendcode,

       to_number(eit.COL15) as channel_bitmap,

       -1 as  sig_tx_mode,

       -1 as st_chg_trap_en,

       -1 as  lpbk_status,

       -1 as  channelize,

       -1 as  signal_mode,

       -1 as  trk_cond_en,

	eit.COL35 as circuit_identifier,

       -1 as reserved

FROM

       ne_info_table ne,

       eqpt_info_table eit,

       module_type_table mtt

WHERE

       ne.NEDBAccessId = eit.NEDBAccessId AND

       ne.NEModelType  = mtt.ModelType    AND

       eit.ModuleType  = mtt.ModuleType   AND

       mtt.ModuleType  > 20052            AND

       mtt.ModuleType  < 20058;

2.3.5 MGX8880_Inventory_View

CREATE OR REPLACE VIEW MGX8880_inventory_view AS

SELECT

       ne.NESysId,

       eit.PhysicalLoc as PhysicalLoc,

       mtt.ModuleName as ModuleName,

       eit.Col4 as ModuleDescription,

       eit.Col5 as FcSerialNum,

       eit.Col6 as FcHwRev,

       eit.Col7 as FcSwRev,

       to_number(eit.Col8)  as FcResetReason,

       to_number(eit.Col9) as ModuleState,

       eit.Col11 as BcName,

       eit.Col12 as BcSerialNum,

       eit.Col13 as BcHwRev,

       to_number(eit.Col14) as BcState,

       eit.Col16 as SecBcName,

       eit.Col17 as SecBcSerialNum,

       eit.Col18 as SecBcHwRev,

       to_number(eit.Col19) as SecBcState,

       to_number(eit.Col20) as SyncMode,

       to_number(eit.Col21) as LogicalSlot,

       to_number(eit.Col22) as SctId,

       to_number(eit.Col23) as SctVersion,

       -1 as UnitNum,

       -1 as GroupNum,

       ne.NEDBAccessId,

       eit.IFINDEX,

       mtt.ModuleType,

       eit.rowid as myrow,

       ne.NEAliasID as NEAliasID

FROM

       ne_info_table ne,

       eqpt_info_table eit,

       module_type_table mtt

WHERE

       ne.NEDBAccessId = eit.NEDBAccessId AND

       ne.NEModelType  = mtt.ModelType    AND

       eit.ModuleType  = mtt.ModuleType   AND

       mtt.ModuleType  > 20002            AND

       mtt.ModuleType  < 20030

UNION ALL

SELECT

       ne.NESysId,

       eit.PhysicalLoc as PhysicalLoc,

       mtt.ModuleName as ModuleName,

       '' as ModuleDescription,

       'N/A' as FcSerialNum,

       'N/A' as FcHwRev,

       'N/A' as FcSwRev,

       0  as FcResetReason,

       to_number(eit.Col6) as ModuleState,

       'N/A' as BcName,

       'N/A' as BcSerialNum,

       'N/A' as BcHwRev,

       0 as BcState,

       'N/A' as SecBcName,

       'N/A' as SecBcSerialNum,

       'N/A' as SecBcHwRev,

       0 as SecBcState,

       0 as SyncMode,

       0 as LogicalSlot,

       0 as SctId,

       0 as SctVersion,

       to_number(eit.Col4) as UnitNum,

       to_number(eit.Col5) as GroupNum,

       ne.NEDBAccessId,

       eit.IFINDEX,

       mtt.ModuleType,

       eit.rowid as myrow,

       ne.NEAliasID as NEAliasID

FROM

       ne_info_table ne,

       eqpt_info_table eit,

       module_type_table mtt

WHERE

       ne.NEDBAccessId = eit.NEDBAccessId AND

       ne.NEModelType  = mtt.ModelType    AND

       eit.ModuleType  = mtt.ModuleType   AND

       mtt.ModuleType  > 20029            AND

       mtt.ModuleType  < 20038            AND

       eit.Col2 is null

UNION ALL

SELECT

       ne.NESysId,

       eit.PhysicalLoc as PhysicalLoc,

       mtt.ModuleName as ModuleName,

       eit.Col3 as ModuleDescription,

       'N/A' as FcSerialNum,

       'N/A' as FcHwRev,

       'N/A' as FcSwRev,

       0  as FcResetReason,

       to_number(eit.Col7) as ModuleState,

       'N/A' as BcName,

       'N/A' as BcSerialNum,

       'N/A' as BcHwRev,

       0 as BcState,

       'N/A' as SecBcName,

       'N/A' as SecBcSerialNum,

       'N/A' as SecBcHwRev,

       0 as SecBcState,

       0 as SyncMode,

       0 as LogicalSlot,

       0 as SctId,

       0 as SctVersion,

       to_number(eit.Col9) as UnitNum,

       0 as GroupNum,

       ne.NEDBAccessId,

       eit.IFINDEX,

       mtt.ModuleType,

       eit.rowid as myrow,

       ne.NEAliasID as NEAliasID

FROM

       ne_info_table ne,

       eqpt_info_table eit,

       module_type_table mtt

WHERE

       ne.NEDBAccessId = eit.NEDBAccessId AND

       ne.NEModelType  = mtt.ModelType    AND

       eit.ModuleType  = mtt.ModuleType   AND

       mtt.ModuleType  = 20038            AND

       eit.Col2 is not null;

2.3.6 Node

CREATE OR REPLACE VIEW node AS

SELECT

       ne.NEDBAccessId as node_id,

       ne.NESubNetworkID as netw_id,

       mgxne.NeName as node_name,

       -1 as ipx_netw_id,

       -1 as ipx_node_id,

       ne.NEIPAddr as net_ip_address,

       mgxne.LanIpAddress as lan_ip_address,

       -1 as nw_ip_address,

       mgxne.MgxModelNum as model,

       'B' as submodel,

       -1 as alarm_state,

       -1 as filtered_alarm,

       -1 as gateway,

       1 as active,

       mgxne.Platform as platform,

       4 as subtype,

       ne.NEVersion as release,

       -1 as fs_inc_rate,

       -1 as fs_dec_rate,

       -1 as fs_fdec_rate,

       -1 as rst_timeout,

       ne.ConfigSyncStatus as sync_mode,

       getMgx88xxMgmtState(ne.isNEConnected) as mgmt_state,

       -1 as manager,

       -1 as protocol,

       mgxne.NodePrefix as node_prefix,

       mgxne.PnniNodeId as pnni_node_id,

       -1 as connroute_mode,

       -1 as reserved

FROM

       ne_info_table ne,

       mgx8880_ne_info_table mgxne,

       model_type_table mtt

WHERE

       ne.NEDBAccessId = mgxne.NEDBAccessId AND

       ne.NEModelType  = mtt.ModelType AND

       mtt.ModelType   = 34;

2.3.7 Node_Info

CREATE OR REPLACE VIEW node_info AS

SELECT

       ne.NEDBAccessId as node_id,

       mgxne.NeName as node_name,

       ne.NESubNetworkID as network_id,

       -1 as subnetwork_id,

       ne.NESnmpCommString as get_str,

       mgxne.NESetSnmpCommString as set_str,

       ne.NEUSERNAME as ftp_user_name,

       ne.NEUSERPASSWORD as ftp_user_passwd,

       'MGX MGW node' as descriptor,

       4 as sync_mode,

       ne.NEIPAddr as ipaddress,

       mgxne.MgxModelNum as model,

       'B' as submodel,

       ne.NEState as active,

	mgxne.ftp_enabled as ftp_enabled,

       mgxne.sftp_supported as sftp_supported,

       mgxne.snmp_version as snmp_version,

       mgxne.snmpv3_request_user as snmpv3_request_user,

       mgxne.snmpv3_trap_user as snmpv3_trap_user

FROM

       ne_info_table ne,

       mgx8880_ne_info_table mgxne,

       model_type_table mtt

WHERE

       ne.NEDBAccessId = mgxne.NEDBAccessId AND

       ne.NEModelType  = mtt.ModelType AND

       mtt.ModelType   = 34;

2.3.8 Peripheral

CREATE OR REPLACE VIEW peripheral AS

SELECT

       -1 as obj_id,

       eit.NEDBACCESSID as node_id,

       9999 as network_id,

       -1 as node_type,

       to_number(eit.COL1) as shelf,

       to_number(eit.COL3) as peri_type,

       to_number(eit.COL4) as unit_num,

       eit.COL11 as serial_num,

       eit.COL12 as hw_rev,

       to_number(eit.COL6) as status,

       -1 as severity,

       -1 as alarm_num,

       eit.ifindex as phy_index,

       to_number(eit.COL5) as group_num,

       to_number(eit.COL10) as reserved

FROM

       ne_info_table ne,

       eqpt_info_table eit,

       module_type_table mtt

WHERE

       ne.NEDBAccessId = eit.NEDBAccessId AND

       ne.NEModelType  = mtt.ModelType    AND

       eit.ModuleType  = mtt.ModuleType   AND

       mtt.ModuleType  > 20029            AND

       mtt.ModuleType  < 20038            AND

       eit.Col2 is null;

2.3.9 Sensor

CREATE OR REPLACE VIEW sensor AS

SELECT

       9999 as network_id,

       eit.NEDBACCESSID as node_id,

       to_number(eit.COL1) as shelf,

       to_number(eit.COL2) as slot,

       eit.ifindex as ent_phy_idx,

       eit.COL3 as description,

       to_number(eit.COL4) as type,

       to_number(eit.COL5) as scale,

       to_number(eit.COL6) as value,

       to_number(eit.COL7) as status,

       to_number(eit.COL8) as alarm_state,

       to_number(eit.COL9) as sensor_num,

       to_number(eit.COL10) as reserved

FROM

       ne_info_table ne,

       eqpt_info_table eit,

       module_type_table mtt

WHERE

       ne.NEDBAccessId = eit.NEDBAccessId AND

       ne.NEModelType  = mtt.ModelType    AND

       eit.ModuleType  = mtt.ModuleType   AND

       mtt.ModuleType  = 20038            AND

       eit.Col2 is not null;

2.3.10 Xpvc

create or replace view xpvc 
(xpvc_id,num_segs,status,conn_type,service_type,vp_conn_flag,l_node_id,l_slot,l_line,l_por
t,l_logical_port,l_subchnl_1,l_subchnl_2,r_node_id,r_slot,r_line,r_port,r_logical_port,r_s
ubchnl_1,r_subchnl_2,secondary_state,l_end_nni,r_end_nni,l1_parent_status,l2_parent_status
,l3_parent_status,r1_parent_status,r2_parent_status,r3_parent_status,l_endpoint_type,r_end
point_type) AS SELECT

    to_number(eit.COL1) as xpvc_id ,

    to_number(eit.COL1) as num_segs,

    to_number(eit.COL1) as status ,

    to_number(eit.COL1) as conn_type,

    to_number(eit.COL1) as service_type ,

    to_number(eit.COL1) as vp_conn_flag ,

    to_number(eit.COL1) as l_node_id ,

    to_number(eit.COL1) as l_slot ,

    to_number(eit.COL1) as l_line ,

    to_number(eit.COL1) as l_port ,

    to_number(eit.COL1) as l_logical_port ,

    to_number(eit.COL1) as l_subchnl_1 ,

    to_number(eit.COL1) as l_subchnl_2 ,

    to_number(eit.COL1) as r_node_id ,

    to_number(eit.COL1) as r_slot ,

    to_number(eit.COL1) as r_line ,

    to_number(eit.COL1) as r_port ,

    to_number(eit.COL1) as r_logical_port ,

    to_number(eit.COL1) as r_subchnl_1 ,

    to_number(eit.COL1) as r_subchnl_2 ,

    to_number(eit.COL1) as secondary_state ,

    to_number(eit.COL1) as l_end_nni ,

    to_number(eit.COL1) as r_end_nni ,

    to_number(eit.COL1) as l1_parent_status ,

    to_number(eit.COL1) as l2_parent_status ,

    to_number(eit.COL1) as l3_parent_status ,

    to_number(eit.COL1) as r1_parent_status ,

    to_number(eit.COL1) as r2_parent_status ,

    to_number(eit.COL1) as r3_parent_status ,

    to_number(eit.COL1) as l_endpoint_type ,

    to_number(eit.COL1) as r_endpoint_type

FROM

    eqpt_info_table eit

WHERE eit.NEDBACCESSID=10000;

2.3.11 Xpvc_Segment

create or replace view xpvc_segment 
(xpvc_id,segment_order,in_network,l_endpt_type,l_node_id,l_slot,l_line,l_port,l_logical_po
rt,l_subchnl_1,l_subchnl_2,l_parent_status,r_endpt_type,r_node_id,r_slot,r_line,r_port,r_l
ogical_port,r_subchnl_1,r_subchnl_2,r_parent_status,status,secondary_state,service_type,l_
end_nni,r_end_nni,vp_conn_flag,pnni_flag,p_xlmi_l_node_id,p_xlmi_l_slot,p_xlmi_l_port,p_xl
mi_r_node_id,p_xlmi_r_slot,p_xlmi_r_port,s_xlmi_l_node_id,s_xlmi_l_slot,s_xlmi_l_port,s_xl
mi_r_node_id,s_xlmi_r_slot,s_xlmi_r_port) AS SELECT

    to_number(eit.COL1) as xpvc_id ,

    to_number(eit.COL1) as segment_order ,

    to_number(eit.COL1) as in_network ,

    to_number(eit.COL1) as l_endpt_type ,

    to_number(eit.COL1) as l_node_id ,

    to_number(eit.COL1) as l_slot ,

    to_number(eit.COL1) as l_line ,

    to_number(eit.COL1) as l_port ,

    to_number(eit.COL1) as l_logical_port ,

    to_number(eit.COL1) as l_subchnl_1 ,

    to_number(eit.COL1) as l_subchnl_2 ,

    to_number(eit.COL1) as l_parent_status ,

    to_number(eit.COL1) as r_endpt_type ,

    to_number(eit.COL1) as r_node_id ,

    to_number(eit.COL1) as r_slot ,

    to_number(eit.COL1) as r_line ,

    to_number(eit.COL1) as r_port ,

    to_number(eit.COL1) as r_logical_port ,

    to_number(eit.COL1) as r_subchnl_1 ,

    to_number(eit.COL1) as r_subchnl_2 ,

    to_number(eit.COL1) as r_parent_status ,

    to_number(eit.COL1) as status ,

    to_number(eit.COL1) as secondary_state ,

    to_number(eit.COL1) as service_type ,

    to_number(eit.COL1) as l_end_nni ,

    to_number(eit.COL1) as r_end_nni ,

    to_number(eit.COL1) as vp_conn_flag ,

    to_number(eit.COL1) as pnni_flag ,

    to_number(eit.COL1) as p_xlmi_l_node_id ,

    to_number(eit.COL1) as p_xlmi_l_slot ,

    to_number(eit.COL1) as p_xlmi_l_port ,

    to_number(eit.COL1) as p_xlmi_r_node_id ,

    to_number(eit.COL1) as p_xlmi_r_slot ,

    to_number(eit.COL1) as p_xlmi_r_port ,

    to_number(eit.COL1) as s_xlmi_l_node_id ,

    to_number(eit.COL1) as s_xlmi_l_slot ,

    to_number(eit.COL1) as s_xlmi_l_port ,

    to_number(eit.COL1) as s_xlmi_r_node_id ,

    to_number(eit.COL1) as s_xlmi_r_slot ,

    to_number(eit.COL1) as s_xlmi_r_port

FROM

    eqpt_info_table eit

WHERE

    eit.NEDBACCESSID = 10000;

2.4 Cisco ONS 152xx Views

CREATE OR REPLACE VIEW ONS15200_inventory_view AS

SELECT

       ne.NESysId,

       eit.PhysicalLoc as PhysicalLoc,

       mtt.ModuleName as ModuleName,

       to_number(eit.Col1) as SERVICESTATE,

       nvl(eit.Col8, 'N/A') as ITUCHNO,

       to_number(eit.Col9) as DATARATE,

       to_number(eit.Col11) as APPLICATIONCODE,

       eit.Col12 as SERIALNO,

       eit.Col13 as NEID,

       eit.Col14 as PARTNO,

       nvl(eit.Col15, 'N/A') as NCBSWVERSION,

       nvl(eit.Col16, 'N/A') as NCBBOARDREVNO,

       eit.Col17 as CLEICODE,

       nvl(eit.Col26, 'N/A') as CONTAINERNAME,

       to_number(eit.Col44) as POWERSPLIT,

       nvl(eit.Col91, 'N/A') as NAME,

       nvl(eit.Col92, 'N/A') as PRIMARYNCBID,

       nvl(eit.Col93, 'N/A') as SECONDARYNCBID,

       ne.NEDBAccessId,

       eit.IFINDEX,

       mtt.ModuleType,

       to_number(eit.Col250) as CONFLICTINGCLIP,

       eit.rowid as myrow

FROM

       ne_info_table ne,

       eqpt_info_table eit,

       module_type_table mtt

WHERE

       ne.NEDBAccessId = eit.NEDBAccessId AND

       ne.NEModelType  = mtt.ModelType    AND

       eit.ModuleType  = mtt.ModuleType   AND

       mtt.ModuleType  > 7002             AND

       mtt.ModuleType  < 7300;

2.5 Cisco ONS 15216 Views

2.5.1 ONS15216_Active_User_View

create or replace view ONS15216_Active_User_view as

select

        a.nesysid as NEId,

        b.userid as UserId,

        b.nedbaccessid,

        b.rowid as myrow,

       a.nealiasid as ALIASID

from

        ons15216_active_user_table  b,

        ne_info_table a

where

        b.nedbaccessid = a.nedbaccessid;

2.5.2 ONS15216_Inventory_View

create or replace view ONS15216_INVENTORY_VIEW as

SELECT DISTINCT

    t1.NEDBACCESSID,

    t1.NEMODELINDEX,

    t1.NESYSID,

    t1.NEDESCRIPTION,

    t1.NECONFIGMODE,

    t1.NEVERSION,

    t2.WAVELENGTH,

    t2.PARTNUMBER,

    t2.CLEICODE,

    t2.SERIALNUMBER,

    t1.NECLLICODE,

    t1.GNEID,

    t1.NESUBNETWORKID,

    t2.MODULENUMBER,

    t2.rowid as MYROW,

    t1.NEALIASID as ALIASID

FROM

    NE_INFO_TABLE t1,

    ONS15216_NE_INVENTORY_TABLE t2

WHERE

    t1.NEDBACCESSID = t2.NEDBACCESSID;

2.5.3 ONS15216_User_View

create or replace view ONS15216_User_view as

select

        a.nesysid as NEId,

        b.userid as UserId,

        b.nedbaccessid,

        c.privilege as Privilege,

        b.lastlogintime as LastLoginTime,

	b.Timeout as Timeout,

        b.rowid as myrow,

        a.nealiasid as ALIASID

from

        ons15216_user_table  b,

        ne_info_table a,

        ons15216_privilege_table c

where

        b.nedbaccessid = a.nedbaccessid AND

        b.privilegeid = c.privilegeid;

2.6 Cisco ONS 153xx Views

2.6.1 ONS1530x_Dcc_PM_View

CREATE OR REPLACE VIEW ons1530x_dcc_pm_view AS

SELECT

       pl.NESysId,

       mt.ModuleName,

       pi.PhysicalLoc,

       pi.NeIfIndex,

       pi.TimeStamp,

       pi.IFINUCASTPKTS,

       pi.IFINDISCARDS,

       pi.IFINERRORS,

       pi.IFINOCTETS,

       pi.IFOUTOCTETS,

       pi.IFINNUCASTPKTS,

       pi.IFINUNKNOWNPROTOS,

       pi.IFOUTUCASTPKTS,

       pi.IFOUTNUCASTPKTS,

       pi.IFOUTDISCARDS,

       pi.IFOUTERRORS,

       pi.IFINMULTICASTPKTS,

       pi.IFINBROADCASTPKTS,

       pi.IFOUTMULTICASTPKTS,

       pi.IFOUTBROADCASTPKTS,

       pl.NEDBAccessId,

       mt.ModuleType,

       pi.is24H,

       pl.NEModelType,

       pi.OBJECTTYPE,

       pi.STROBJINSTANCE,

       pi.rowid as myrow,

       pl.NEAliasID

FROM

       ne_info_table pl,

       module_type_table mt,

       ONS1530x_IP_PM_TABLE pi

WHERE

       pl.NEDbAccessID = pi.NEDbAccessId AND

       pl.NEModelType  = mt.ModelType    AND

       mt.ModuleType   = pi.ModuleType AND

       pi.iftype = 2;

2.6.2 ONS1530x_HO_FE_PM_View

CREATE OR REPLACE VIEW ons1530x_ho_fe_pm_view AS

SELECT

       pl.NESysId,

       mt.ModuleName,

       pi.PhysicalLoc,

       pi.NeIfIndex,

       pi.TimeStamp,

       pi.FEBBE,

       pi.FEES,

       pi.FESES,

       pi.FEUAS,

       pl.NEDBAccessId,

       mt.ModuleType,

       pi.is24H,

       pl.NEModelType,

       pi.OBJECTTYPE,

       pi.STROBJINSTANCE,

       pi.rowid as myrow,

       pl.NEAliasID

FROM

       ne_info_table pl,

       module_type_table mt,

       ONS1530x_HO_PM_TABLE pi

WHERE

       pl.NEDbAccessID = pi.NEDbAccessId AND

       pl.NEModelType  = mt.ModelType    AND

       mt.ModuleType   = pi.ModuleType ;

2.6.3 ONS1530x_HO_PM_View

CREATE OR REPLACE VIEW ons1530x_ho_pm_view AS

SELECT

       pl.NESysId,

       mt.ModuleName,

       pi.PhysicalLoc,

       pi.NeIfIndex,

       pi.TimeStamp,

       pi.NEBBE,

       pi.NEES,

       pi.NESES,

       pi.NEUAS,

       pl.NEDBAccessId,

       mt.ModuleType,

       pi.is24H,

       pl.NEModelType,

       pi.OBJECTTYPE,

       pi.STROBJINSTANCE,

       pi.rowid as myrow,

       pl.NEAliasID

FROM

       ne_info_table pl,

       module_type_table mt,

       ONS1530x_HO_PM_TABLE pi

WHERE

       pl.NEDbAccessID = pi.NEDbAccessId AND

       pl.NEModelType  = mt.ModelType    AND

       mt.ModuleType   = pi.ModuleType ;

2.6.4 ONS1530x_Inventory_View

CREATE OR REPLACE VIEW ONS1530x_inventory_view AS

SELECT

       info.NESysId,

       nvl(mtt2.ModuleName,'EMPTY SLOT') as ModuleName,

       inv.PhysicalLoc as PhysicalLoc,

       inv.InstallState as InstallState,

       mtt1.ModuleName as ExpectedModule,

       nvl(inv.ServiceState,'N/A') as ServiceState,

       nvl(inv.SoftwareVersion,'N/A') as SoftwareVersion,

       nvl(inv.SerialNumber,'N/A') as SerialNumber,

       nvl(inv.HardwareVersion,'N/A') as HardwareVersion,

       info.NEDBAccessId,

       info.rowid as myrow,

       inv.Type as ModuleType,

       info.NEAliasID

FROM

       ne_info_table info,

       ONS1530x_ne_inventory_table inv,

       module_type_table mtt1,

       module_type_table mtt2

WHERE

       info.NEDBAccessID = inv.NEDBAccessID AND

       info.NEModelType  = mtt1.ModelType AND

       info.NEModelType  = mtt2.ModelType AND

       mtt1.moduleType   = inv.ExpectedModule AND

       mtt2.moduleType   = inv.ModuleName;

2.6.5 ONS1530x_Lan_PM_View

CREATE OR REPLACE VIEW ons1530x_lan_pm_view AS

SELECT

       pl.NESysId,

       mt.ModuleName,

       pi.PhysicalLoc,

       pi.NeIfIndex,

       pi.TimeStamp,

       pi.IFINUCASTPKTS,

       pi.IFINDISCARDS,

       pi.IFINERRORS,

       pi.IFINOCTETS,

       pi.IFOUTOCTETS,

       pi.IFINNUCASTPKTS,

       pi.IFINUNKNOWNPROTOS,

       pi.IFOUTUCASTPKTS,

       pi.IFOUTNUCASTPKTS,

       pi.IFOUTDISCARDS,

       pi.IFOUTERRORS,

       pi.IFINMULTICASTPKTS,

       pi.IFINBROADCASTPKTS,

       pi.IFOUTMULTICASTPKTS,

       pi.IFOUTBROADCASTPKTS,

       pl.NEDBAccessId,

       mt.ModuleType,

       pi.is24H,

       pl.NEModelType,

       pi.OBJECTTYPE,

       pi.STROBJINSTANCE,

       pi.rowid as myrow,

       pl.NEAliasID

FROM

       ne_info_table pl,

       module_type_table mt,

       ONS1530x_IP_PM_TABLE pi

WHERE

       pl.NEDbAccessID = pi.NEDbAccessId AND

       pl.NEModelType  = mt.ModelType    AND

       mt.ModuleType   = pi.ModuleType AND

       pi.iftype = 0;

2.6.6 ONS1530x_LO_FE_PM_View

CREATE OR REPLACE VIEW ons1530x_lo_fe_pm_view AS

SELECT

       pl.NESysId,

       mt.ModuleName,

       pi.PhysicalLoc,

       pi.NeIfIndex,

       pi.TimeStamp,

       pi.FEBBE,

       pi.FEES,

       pi.FESES,

       pi.FEUAS,

       pl.NEDBAccessId,

       mt.ModuleType,

       pi.is24H,

       pl.NEModelType,

       pi.OBJECTTYPE,

       pi.STROBJINSTANCE,

       pi.rowid as myrow,

       pl.NEAliasID

FROM

       ne_info_table pl,

       module_type_table mt,

       ONS1530x_LO_PM_TABLE pi

WHERE

       pl.NEDbAccessID = pi.NEDbAccessId AND

       pl.NEModelType  = mt.ModelType    AND

       mt.ModuleType   = pi.ModuleType ;

2.6.7 ONS1530x_LO_PM_View

CREATE OR REPLACE VIEW ons1530x_lo_pm_view AS

SELECT

       pl.NESysId,

       mt.ModuleName,

       pi.PhysicalLoc,

       pi.NeIfIndex,

       pi.TimeStamp,

       pi.NEBBE,

       pi.NEES,

       pi.NESES,

       pi.NEUAS,

       pl.NEDBAccessId,

       mt.ModuleType,

       pi.is24H,

       pl.NEModelType,

       pi.OBJECTTYPE,

       pi.STROBJINSTANCE,

       pi.rowid as myrow,

       pl.NEAliasID

FROM

       ne_info_table pl,

       module_type_table mt,

       ONS1530x_LO_PM_TABLE pi

WHERE

       pl.NEDbAccessID = pi.NEDbAccessId AND

       pl.NEModelType  = mt.ModelType    AND

       mt.ModuleType   = pi.ModuleType ;

2.6.8 ONS1530x_MS_FE_PM_View

CREATE OR REPLACE VIEW ons1530x_ms_fe_pm_view AS

SELECT

       pl.NESysId,

       mt.ModuleName,

       pi.PhysicalLoc,

       pi.NeIfIndex,

       pi.TimeStamp,

       pi.FEBBE,

       pi.FEES,

       pi.FESES,

       pi.FEUAS,

       pl.NEDBAccessId,

       mt.ModuleType,

       pi.is24H,

       pl.NEModelType,

       pi.OBJECTTYPE,

       pi.STROBJINSTANCE,

       pi.rowid as myrow,

       pl.NEAliasID

FROM

       ne_info_table pl,

Table Of Contents

Database Schema Views

2.1 Common PM Views

2.1.1 NE_Module_Level_Debug_View

2.1.2 SDH_HO_FarEnd_PM_View

2.1.3 SDH_HO_NearEnd_PM_View

2.1.4 SDH_MS_FarEnd_PM_View

2.1.5 SDH_MS_NearEnd_PM_View

2.1.6 SDH_RS_NearEnd_PM_View

2.1.7 SONET_ENET_PM_View

2.1.8 SONET_Line_FarEnd_PM_View

2.1.9 SONET_Line_NearEnd_PM_View

2.1.10 SONET_Physical_PM_View

2.1.11 SONET_POS_PM_View

2.1.12 SONET_Section_NearEnd_PM_View

2.1.13 SONET_STSPath_FarEnd_PM_View

2.1.14 SONET_STSPath_NearEnd_PM_View

2.2 CTM Base Views

2.2.1 Active_Alarm_View_With_Ack

2.2.2 Admin_Job_View

2.2.3 Alarm_Count_View (Deprecated)

2.2.4 Alarm_Log_View

2.2.5 Audit_Log_View

2.2.6 BLSR_Table_View

2.2.7 Children_View

2.2.8 Circuit_Ctpspan_View

2.2.9 Circuit_Ctp_Drop_View

2.2.10 Circuit_Ctp_Sec_View

2.2.11 Circuit_Ctp_Src_View

2.2.12 Circuit_Ctp_View

2.2.13 Circuit_Ctp_XC_View

2.2.14 Circuit_Node_GUI_View

2.2.15 Circuit_Node_GUI_View_CTM46

2.2.16 Circuit_Node_View

2.2.17 Circuit_Span_View

2.2.18 Circuit_Span_View_CTM46

2.2.19 Circuit_View

2.2.20 Circuit_VLAN_View

2.2.21 Ckt_Span_Dest_Mt_View

2.2.22 Ckt_Span_Src_Mt_View

2.2.23 Client_User_View

2.2.24 CTM_Unknown_User_View

2.2.25 Data_Card_View

2.2.26 Domain_NE_Inventory_View

2.2.27 DSP_Circuit_View

2.2.28 DSP_Eqpt_View

2.2.29 GWCORBA_PM_Mapping_View

2.2.30 INTF_Sub_Intf_View

2.2.31 Inventory_Table_View

2.2.32 IOS_Active_Cards

2.2.33 IOS_Users_View

2.2.34 Link_Utilization_View

2.2.35 Mcard_L2topology_Map_View

2.2.36 MLBCC_View

2.2.37 MLL_Inv_View

2.2.38 NE_Alarm_View

2.2.39 NE_Circuit_NodeID_View

2.2.40 NE_Circuit_View

2.2.41 NE_Circuit_Vlan_View

2.2.42 NE_Info_NP_View

2.2.43 NE_Inventory_View

2.2.44 NE_SNMPV3_FILTERS_VIEW

2.2.45 NE_SNMPV3_GROUPS_VIEW

2.2.46 NE_SNMPV3_LOCAL_USER_VIEW

2.2.47 NE_SNMPV3_PROXY_FWD_VIEW

2.2.48 NE_SNMPV3_PROXY_TRAP_FWD_VIEW

2.2.49 NE_SNMPV3_REMOTE_USER_VIEW

2.2.50 NE_SNMPV3_TRAP_DEST_VIEW

2.2.51 NE_SNMPV3_VIEWS_VIEW

2.2.52 NE_Swimage_View

2.2.53 NGNE_Info_View

2.2.54 OSS_Alarm_Fltr_AidDeny_View

2.2.55 OSS_Alarm_Fltr_TidDeny_VIEW

2.2.56 OSS_Corba_User_View

2.2.57 OSS_User_View

2.2.58 POS_Ports_View

2.2.59 QoSTemplate_General_View

2.2.60 QoS_View

2.2.61 Roll_View

2.2.62 Self_Monitoring_View