1. MSS Integration Cartridge Guide
  2. About Collected Data

4 About Collected Data

This chapter provides information about how the Oracle Communications Network Integrity MSS Integration cartridge treats collected data.

About Collected Data

The reference integration between Oracle Communications Network Integrity and Oracle Communications MetaSolv Solution (MSS) uses the MSS extract process that uses fast-refreshable, read-only materialized views to store the MSS inventory data, which is imported by the MSS Integration cartridge for discrepancy detection/resolution.

About the MSS Extract Process

The MSS extract process includes the following:

  • MSS Equipment Extract Process: The MSS equipment extract process extracts the relevant equipment information from MSS and stores it into read-only materialized views in the MSS database, which is imported by the MSS Integration cartridge for discrepancy resolution.

    See "MSS Equipment Extract Process" for more information.

  • MSS Circuit Extract Process: The MSS circuit extract process extracts the relevant circuit information from MSS and stores it into read-only materialized views in the MSS database, which is imported by the MSS Integration cartridge for discrepancy resolution.

    See "MSS Circuit Extract Process" for more information.

The MSS extract process enables you to do the following:

  • Retrieve information about:

    • Equipment

    • Equipment custom attributes

    • Port address custom attributes

    • Circuits

    • Template-based connections

    • Service Trails for circuits and connections

    • Connection custom attributes, including allocation parameters, such as VLAN ID and VPI/VCI

  • Store the retrieved equipment and circuit information into read-only materialized views within the EXTRACT schema in the MSS database, which is imported by the MSS Integration cartridge to do the following:

    • Compare the imported MSS data with either the discovered equipment data or assimilated circuit data and report any differences

    • Resolve discrepancies on equipment and circuits by using MSS APIs to correct entities, associations, and attributes in MSS

    Note:

    A materialized view is a complete or partial copy (replica) of one or more target (master) tables.

Advantages

The MSS extract process has the following advantages:

  • Improves the end-to-end performance and reliability of the integrated solution.

  • Includes custom attributes (connections, equipment, port addresses, and allocation parameters) and service trails of virtual connections. The system integrator can use these attributes to extend the SDH reference integration to support other technologies and meet specific business requirements.

  • Enables the system integrator to extend the MSS extract process without:

    • Defining new tables and/or columns within the MSS EXTRACT schema

    • Writing any Procedural Language (PL)/Structured Query Language (SQL) logic to update the tables

  • Enables the system integrator to:

    • Update the definition of an existing materialized view to retrieve the required data

    • Define a new materialized view to store the retrieved data

  • Leverages the capability of the Oracle database and its materialized view logs to keep the retrieved data in sync with the ASAP schema, instead of relying on complex user-written PL/SQL logic to update the retrieved data.

Limitations

The MSS extract process has the following limitations:

  • Equipment and Port Address Custom Attributes Resolution: The MSS extract process supports the extraction of equipment and port address custom attributes; however, there is no MSS API support to upload this data to MSS. Therefore, you must manually resolve such discrepancies in MSS.

  • End-to-end Reconciliation of SONET/SDH circuits modeled within the traditional SONET/SDH Network Design Module: The MSS extract process supports the extraction of the channelized connectivity that constitutes a synchronous optical networking/synchronous digital hierarchy (SONET/SDH) network built within the traditional SONET/SDH Network Design module; however, there is no API support to create or update the existing SONET/SDH network assignments and their related SONET blocks on the circuit's design layout report (DLR) in MSS. Therefore, Network Integrity must use custom logic to resolve such circuit discrepancies in MSS.

Setting Up the MSS Extract Process

Before you run the equipment/circuit extract process, you must set up the MSS extract process.

Setting up the MSS extract process involves the following steps:

  • Creating a new EXTRACT schema in the MSS database.

  • Creating new materialized views within the EXTRACT schema, which stores information about equipment and circuits.

  • Granting appropriate privileges to the ASAP and EXTRACT user to define and use the new materialized views.

To set up the MSS extract process:

  1. Download the MSS Integration cartridge ZIP file from the Oracle software delivery Web site:

    https://edelivery.oracle.com

    The MSS Integration cartridge ZIP file has the following structure:

    • MSS_Cartridge

    • Optical_Model

  2. Connect to the MSS database as the ASAP user through sqlplus at the command prompt.

  3. Navigate to the MSS_Cartridge/scripts folder and run the mss_ni_ext_using_mviews_mstr.sql file with database administrator privileges.

    The mss_ni_ext_using_mviews_mstr.sql file is the master file for the refactored MSS extract process using materialized views.

    When you run the mss_ni_ext_using_mviews_mstr.sql master file, the following scripts are run:

    • extr_schema.sql: Creates a new EXTRACT schema in the MSS database if the EXTRACT schema does not already exist.

    • extr_log.sql: Creates materialized view logs within the ASAP schema for the appropriate master tables that are used by the materialized views within the EXTRACT schema. The materialized view logs keep track of the changes to the data in the master tables and can be used to perform a fast refresh (incremental) for all materialized views without requiring a complete refresh every time the data in the master tables is modified.

      Note:

      A materialized view log is a table associated with the master table of a materialized view.

    • extr_grants.sql: Grants the following privileges to the ASAP user to define and use the new materialized views:

      • GRANT CREATE ANY MATERIALIZED VIEW TO ASAP;

      • GRANT CREATE TABLE TO EXTRACT;

      • GRANT GLOBAL QUERY REWRITE TO EXTRACT;

      • GRANT SELECT ON ASAP.TABLE_NAME TO EXTRACT;

      • GRANT SELECT ON MLOG$_TABLE_NAME TO EXTRACT;

        where:

        TABLE_NAME is the name of the master ASAP table from which the data is extracted. For example, ASAP.EQUIPMENT, ASAP.EQUIPMENT_SPEC, ASAP.CIRCUIT, ASAP.CIRCUIT_XREF, and so on.

    • extr_jklm.sql: Adds the JKLM function to the EXTRACT schema. The JKLM function calculates JKLM values.

    • extr_PKG_VIEW_PARAMETERS.sql: Creates the EXTRACT.PKG_VIEW_PARAMETERS package that you can use to GET/SET equipment ID and circuit design ID to retrieve data from the V_MP_HIER and V_PA_HIER hierarchy views:

      • pkg_view_parameters.set_equip_id(e_id in number). For example, pkg_view_parameters.set_equip_id(45332).

      • pkg_view_parameters.set_ckt_id(c_id in number). For example, pkg_view_parameters.set_ckt_id(1015332).

      You must set the equipment ID and circuit design ID in the EXTRACT.PKG_VIEW_PARAMETERS package before using V_MP_HIER and V_PA_HIER hierarchy views on the same transaction.

    • extr_mviews.sql: Creates materialized views under the EXTRACT schema.

      See the following for more information:

    • extr_views.sql: Creates normal views under the EXTRACT schema.

      See the following for more information:

    • extr_index.sql: Creates indexes on materialized views.

Refreshing Materialized Views

Because the MSS data is updated constantly, you must refresh the materialized views at regular intervals to ensure that the materialized views always contain the latest data.

You can refresh the materialized views in the following ways:

  • Through the Network Integrity GUI, do the following:

    • When running the MSS Import scan, select the Run MSS Extract check box to refresh the materialized views.

    Note:

    The scope of the materialized views to be refreshed is governed by the option you select from the Scope list of the Import Scan. For example, if you select Equipment Only from the Scope list, only those materialized views that store information about MSS equipment are refreshed. If you select Equipment and STM Links Only from the Scope list, only those materialized views that store information about MSS equipment/synchronous transport module (STM) links are refreshed. If you select Equipment, STM Links, and Circuits from the Scope list, all the materialized views that store information about MSS equipment/STM links/circuits are refreshed.

  • Manually call the following procedure:

    DBMS_MVIEW.REFRESH('MV_NAME','argument');

    where:

    • MV_NAME is the name of the materialized view

    • argument is one of the following:

      • ?: Performs a fast refresh, and if fast refresh is not successful, performs a complete refresh.

      • F: Performs a fast refresh, and if fast refresh is not successful, the materialized view is not refreshed.

      • C: Performs a complete refresh.

        Note:

        Oracle recommends that you use the ? argument to refresh the materialized views.

  • Using Oracle Scheduler (DBMS_SCHEDULER), you can schedule jobs to run at a specified time or interval.

MSS Equipment Extract Process

The MSS equipment extract process extracts the relevant equipment information from MSS and stores it into fast-refreshable, read-only materialized views within the EXTRACT schema in the MSS database, which is imported by the MSS Integration cartridge to compare the imported MSS data with discovered network data and resolve discrepancies on equipment in MSS.

MSS Equipment Extract Process Materialized Views

The MSS equipment extract process retrieves equipment information and stores it in the following MSS materialized views:

  • EXTRACT.MV_EQUIPMENT: Stores the attributes and defining information of an equipment instance. See Table 4-1 for more information.

  • EXTRACT.MV_MOUNTING_POSITION: Stores the slot hierarchy and installed equipment within an equipment instance. See Table 4-2 for more information.

  • EXTRACT.MV_EQUIPMENT_SPEC: Stores the attributes and defining information of an equipment specification. See Table 4-3 for more information.

  • EXTRACT.MV_EQUIPMENT_SPEC_MPOS: Stores information about the number of mounting positions each equipment specification contains. See Table 4-4 for more information.

  • EXTRACT.MV_PORT_ADDRESS: Stores the port address hierarchy and assigned circuits for an equipment instance. See Table 4-5 for more information.

  • EXTRACT.MV_NETWORK_NODE: Stores the attributes and defining information of a network element. See Table 4-6 for more information.

  • EXTRACT.MV_NS_COMPONENT: Stores the attributes and defining information of a network component. See Table 4-7 for more information.

  • EXTRACT.MV_NETWORK_LOCATION: Stores the attributes and defining information of a network location. See Table 4-8 for more information.

  • EXTRACT.MV_EQUIPMENT_CA: Stores the configurable parameters tied to an equipment instance which is stored within Custom Attributes. See Table 4-9 for more information.

  • EXTRACT.MV_PORT_ADDRESS_CA: Stores the configurable parameters tied to a port address which is stored within Custom Attributes. See Table 4-10 for more information.

  • EXTRACT.MV_NS_COMP_EQUIP: Stores the attributes and defining information of a network component tied to an equipment instance. See Table 4-11 for more information.

The following tables describe the contents of the MSS materialized views in which the MSS equipment extract process stores the inventory data.

Table 4-1 describes the contents of the EXTRACT.MV_EQUIPMENT materialized view.

Table 4-1 EXTRACT.MV_EQUIPMENT Materialized View

Column Name Data Type Description

EQUIPMENT_ID

NUMBER(9)

The unique table key.

EQUIPMENT_NAME

VARCHAR2(15)

The name of the equipment.

AVAILABILITY_STATUS

CHAR(1)

Indicates the current state of this item.

Valid values are:

  • I = Installed

  • S = Spare

  • U = Under Construction

LOCATION_ID

NUMBER(9)

A unique identifier visible only to the system. Used to store and retrieve information about the location.

LOCATION_ID_2

NUMBER(9)

The location ID that represents the 11-byte CLLI location.

NETWORK_NODE_ID

NUMBER(9)

Used to uniquely identify a network node.

TIMING_SOURCE

VARCHAR2(15)

Identifies the origination of the timing signal for this equipment.

Valid values are:

  • External

  • Loop/Line

  • Internal

VERSION_OF_HARDWARE_INSTALLED

VARCHAR2(20)

The version of the hardware equipment to be installed.

SERIAL_NBR

VARCHAR2(35)

The unique identification for a piece of equipment. Entered/modified as an attribute residing on a circuit.

EQUIPMENT_SPEC_ID

NUMBER(9)

An identifier visible only to the system. Used for storing and retrieving information about an equipment specification.

SOFTWARE_RELEASE_IDENTIFIER

VARCHAR2(10)

The current software release for an operating system. For example, a Northern Telecom DNX-100 DACS may be at NSR-5 software release.

Table 4-2 describes the contents of the EXTRACT.MV_MOUNTING_POSITION materialized view.

Table 4-2 EXTRACT.MV_MOUNTING_POSITION Materialized View

Column Name Data Type Description

EQUIPMENT_ID

NUMBER(9)

A unique identifier visible only to the system. Used to store and retrieve information about the equipment.

EQUIPMENT_ID_2

NUMBER(9)

Describes the current piece of equipment that is installed in equipment_id.

MOUNTING_POSITION_NUMBER

VARCHAR2(8)

Identifies the exact location of an assignable item (equipment or termination) within relay rack or multi-position equipment.

GROUP_IDENTIFIER

VARCHAR2(12)

Allows you to associate mounting positions and port addresses with complement information (for example, DIGROUP A) for a piece of equipment (for example, D4 channel bank).

MOUNTPOS_SEQ

NUMBER(5)

System generated number to uniquely identify and sequence mounting positions for an equipment specification or a piece of installed equipment.

SLOT_NODE_ADDR

VARCHAR2(30)

Used to build the node address for a given port address when the software address depends on mounting information.

Table 4-3 describes the contents of the EXTRACT.MV_EQUIPMENT_SPEC materialized view.

Table 4-3 EXTRACT.MV_EQUIPMENT_SPEC Materialized View

Column Name Data Type Description

EQUIPMENT_SPEC_ID

NUMBER(9)

An identifier visible only to the system. Used for storing and retrieving information about an equipment specification.

EQUIPMENT_ACRONYM

VARCHAR2(10)

Used on the connection layout record (CLR) or design layout report (DLR). It is an acronym for a material item. For example, FXS is the acronym for a Foreign Exchange Channel Unit on the subscriber's end.

VENDOR_PART_NUMBER

VARCHAR2(25)

The part number for this unit of equipment as assigned by the manufacturer. For example, 263DB2, 1011, 4420D, and so on.

VENDOR_NAME

VARCHAR2(20)

The manufacturer of this unit of equipment.

EQUIPSPEC_TYPE

VARCHAR2(50)

Identifies the equipment type within an equipment category. For a category of SHELF, the type can be CHANNEL BANK or MUX. You can define the types within a category.

OCCUPIES_MOUNTING_POSITIONS

NUMBER(4)

The number of spaces or slots required in a parent piece of equipment (bay/rack/shelf) to mount this hardware.

Table 4-4 describes the contents of the EXTRACT.MV_EQUIPMENT_SPEC_MPOS materialized view.

Table 4-4 EXTRACT.MV_EQUIPMENT_SPEC_MPOS Materialized View

Column Name Data Type Description

EQUIPMENT_SPEC_ID

NUMBER(9)

An identifier visible only to the system. Used for storing and retrieving information about an equipment specification.

NBR_OF_MOUNT_POS

NUMBER

Stores the number of mounting positions an equipment specification contains, on which other equipment can be installed.

Table 4-5 describes the contents of the EXTRACT.MV_PORT_ADDRESS materialized view.

Table 4-5 EXTRACT.MV_PORT_ADDRESS Materialized View

Column Name Data Type Description

EQUIPMENT_ID

NUMBER(9)

A unique identifier used to store and retrieve information about equipment.

PORTADDR_SEQ

NUMBER(9)

System-generated number to uniquely identify and sequence port addresses for an equipment specification or a piece of installed equipment.

NODE_ADDRESS

VARCHAR2(30)

Identifies the specific port/channel addressing designation for a port address. The physical or logical address (software address) associated with this port. It may be derived from the node address of the equipment specification.

The node addresses are of the following types:

  • The node addresses that remain constant irrespective of the location of the installed device.

  • The node addresses that are dependent on the hierarchy of the equipment on which they are installed. For example, a DCM card in a DCM shelf has an address that is dependent on the bay-shelf combination on which it is installed.

  • The node addresses that are entirely dependent on the slot in which they are installed. For example, a low-speed card installed on a DDM2000 shelf inherits the node address of its ancestor, A-1-1.

RATE_CODE

VARCHAR2(10)

Identifies the bit rate associated with a circuit, facility, or equipment. For example, DS0, DS1, DS3, N/A, and so on.

CIRCUIT_DESIGN_ID

NUMBER(9)

A unique identifier used for storing and retrieving information about a single circuit.

PORT_ADDR_STATUS

CHAR(1)

Describes the current status of the circuit position.

Valid values are:

  • 1 = Unassigned

  • 2 = Pending installation work order

  • 3 = In service

  • 4 = Pending removal work order

  • 5 = Trouble

  • 6 = Reserved

  • 7 = Reserved capacity

PORTADDR_TYPE

CHAR(1)

Indicates whether the port address (or enabled port address) is physical or virtual. Physical ports are those that have actual wired connections and include their enabled (software) ports. Virtual ports are those that have no actual physical appearance or connection and are entirely in the software of the equipment.

Valid values are:

  • P = Physical

  • V = Virtual

The existing rows in the TBS database at the time of implementation default to P.

CIRCUIT_POSITION_NUMBER_CP

NUMBER(9)

The subposition within a mounting position. This column applies only to plug-in cards that have multi-position capabilities. For such cards, this field identifies the multiple position number of a transmission facility circuit (TFC) or a channel number within a carrier system. This number may correspond to the mounting position of the equipment used to terminate the TFC. This column on this table is a foreign key describing the circuit position that this port address enables.

CIRCUIT_DESIGN_ID_CP

NUMBER(9)

An identifier visible only to the system; Used for storing and retrieving information about a single circuit. This column is a foreign key describing the circuit position that this port address enables.

NODE_ADDR_LEVELS

VARCHAR2(2)

Determines how many pieces of equipment (levels up from the circuit_attachable piece) are used to determine the node address.

ORIG_ASSIGNMENT_IND

CHAR(1)

Used to designate whether or not an equipment assignment is the original assignment in a cross-connect chain. This is mainly used in the reconcile process of the circuit design to identify where the original assignment was made.

Valid values are:

  • Y = Yes

  • N (default) = No

A_Z_OTHER_CD

CHAR(1)

Identifies the location of a piece of equipment residing on a circuit.

Valid values are:

  • A = A location

  • Z = Z location

  • O = On the circuit but not at the A or Z location

EQUIPMENT_ID_VE

NUMBER(9)

A unique identifier visible only to the system, used to store and retrieve information about equipment. This plus the portaddr_seq_ve column indicate that this port_address is virtual and enabled by the port address referenced.

PORTADDR_SEQ_VE

NUMBER(9)

System generated number to uniquely identify and sequence port addresses for an equipment spec or piece of installed equipment. This plus the equipment_id_ve column indicate that this port_address is virtual and enabled by the port address referenced.

GROUP_IDENTIFIER

VARCHAR2(12)

Allows the user to associate mounting positions and port addresses with complement information (for example, DIGROUP A) for a piece of equipment (for example, D4 channel bank).

ADDITIONAL_ASSIGNMENT_SEQ_NBR

NUMBER(2)

This assignment sequence is used to keep track of equipment assignments for multiple assignments of a circuit to the same network.

NETWORK_NODE_ID

NUMBER(9,0)

Artificial key used to uniquely identify a network node. Allows nodes to be defined outside the network.

Table 4-6 describes the contents of the EXTRACT.MV_NETWORK_NODE materialized view.

Table 4-6 EXTRACT.MV_NETWORK_NODE Materialized View

Column Name Data Type Description

NETWORK_NODE_ID

NUMBER(9)

Artificial key used to uniquely identify a network node. Allows nodes to be defined outside of a network.

TFC_NETWORK_ID

NUMBER(9)

A system-generated number used to uniquely identify a network node

LOCATION_ID

NUMBER(9)

A unique identifier for a specific location. This ID is visible only to the system and it is used to store and retrieve information about the location.

NODE_NAME

VARCHAR2(50)

An identifier for the network element.

NODE STATUS

CHAR(1)

Status of the network node (network location on a SONET ring).

Valid values are:

  • 1 = Pending

  • 2 = In Service

  • 3 = Pending Removal

NODE_SEQUENCE

NUMBER(9)

Designates the nodes in sequential order to identify the switching or signaling directions, such as clockwise or counterclockwise.

TARGET_IDENTIFIER

VARCHAR2(25)

An equipment's network element address for the network node for communications between network elements and between operating systems and network elements.

NETWORK_ELEMENT_CD

CHAR(1)

Identifies the scope of the network node or network element. The network element can be a system (for example, a number of shelf assemblies) as with a switch or Digital Cross-connect System (DCS) or it can be a single shelf with a SONET network node.

Values include:

  • S = System (for example, DCS)

  • N = SONET network node

Table 4-7 describes the contents of the MV_NS_COMPONENT materialized view.

Table 4-7 MV_NS_COMPONENT Materialized View

Column Name Data Type Description

NS_COMP_ID

NUMBER(9)

An Oracle sequence number that uniquely identifies entities of this type.

NST_COMP_TYPE

VARCHAR2(10)

A type of component that can be part of a network system. For example, local digital switch (LDS), central office terminal (COT), remote digital terminal (RDT), digital cross-connect system (DCS), and so on.

NS_COMP_ACRONYM

VARCHAR2(50)

A short name for a network system component. The default value comes from NST Component Type. Examples, of acronyms are host digital terminal (HDT) and remote services terminal (RST). The name of the NST Component Type is generic. This attribute allows the acronym represented by the NST Component Type to be tailored, as it can be different for different types of equipment. For example, one vendor may refer to an RDT as an RST.

NS_COMP_NM

VARCHAR2(50)

The name of the network system component. The default value for entities of this type comes from the NS_CONFIG_COMP_DEFAULT_NM. An example of this name is Remote Services Terminal.

LOCATION_ID

NUMBER(9)

A unique identifier for a specific location. This ID is visible only to the system and it is used to store and retrieve information about the location.

STATUS

CHAR(1)

Describes the current operational state.

Valid values are:

  • 1 = (Pending)

  • 2 = (Assigned)

  • 3 = (In Progress)

  • 4 = (CLR Issued)

  • 5 = (DLR Issued)

  • 6 = (In Service)

  • 7 = (Pending Disconnect)

  • 8 = (Disconnected)

  • 9 = (Problem)

  • A = (Cancelled)

NETWORK_NODE_ID

NUMBER(9,0)

Unique ID to identify a network node. Foreign key to NETWORK_NODE. Allows nodes to be defined outside of a network.

Table 4-8 describes the contents of the EXTRACT.MV_NETWORK_LOCATION materialized view.

Table 4-8 EXTRACT.MV_NETWORK_LOCATION Materialized View

Column Name Data Type Description

LOCATION_ID

NUMBER(9)

A unique identifier for a specific location. This ID is visible only to the system and it is used to store and retrieve information about the location.

LOCATION_NAME

VARCHAR2(50)

The name for a location.

CLLI_CODE

VARCHAR2(20)

A location identification code that identifies specific locations or terminations. This code may be free-form and user-defined, or the Common Language Location Identification (CLLI) code administered by iconectiv.

NETLOC_TYPE_CD

CHAR(1)

Describes whether this network location represents an end user, terminal location or a CLLI. This column is used only with the new architecture location model where the network location table becomes an entity with sub-types (CLLI location, terminal location, and end user location).

Valid values are:

  • E = end user

  • C = CLLI

  • T = terminal location

  • O = Other

Table 4-9 describes the contents of the EXTRACT.MV_EQUIPMENT_CA materialized view.

Table 4-9 EXTRACT.MV_EQUIPMENT_CA Materialized View

Column Name Data Type Description

EQUIP_CA_VALUE_ID

NUMBER(9)

An Oracle sequence that uniquely identifies entities of this type.

EQUIPMENT_ID

NUMBER(9)

An Oracle sequence number that uniquely identifies a piece of equipment.

CA_VALUE

VARCHAR2(1500)

The value taken on by an attribute, such as 320 for a Local Cell ID.

CA_VALUE_LABEL

VARCHAR2(50)

The name of the attribute associated to a value, such as Local Cell ID whose value is 320.

CA_VALUE_UOM

VARCHAR2(32)

The unit in a system that is used to determine the dimensions, area, volume, weight, or such of the attribute's value.

CA_USAGE_ID

NUMBER(9)

An Oracle sequence number that uniquely identifies an entity of this type.

CA_USAGE_VV_ID

NUMBER(9)

An Oracle sequence that uniquely identifies the valid value for an attribute associated to a building block.

MS_BB_ID

NUMBER(9)

Foreign Key from MS_BUILDING_BLOCK. Identifies the table or key (building block) to which this CA_Usage applies.

CURRENT_ROW_IND

CHAR(1)

Indicates whether this row of custom attributed is one of the current in-service rows for the network component.

CA_ID

NUMBER(9)

Foreign Key from CA_CUSTOMIZED_ATTRIBUTE. Identifies the CA value.

Table 4-10 describes the contents of the EXTRACT.MV_PORT_ADDRESS_CA materialized view.

Table 4-10 EXTRACT.MV_PORT_ADDRESS_CA Materialized View

Column Name Data Type Description

PORT_ADDR_CA_VALUE_ID

NUMBER(10)

A system assigned unique identifier for port_addr_ca_value table. It is populated by an Oracle generated sequence and is hidden to the user. This information is used internally for tracking purposes

EQUIPMENT_ID

NUMBER(9)

An Oracle sequence number that uniquely identifies a piece of equipment.

MS_BB_ID

NUMBER(9)

Foreign Key from MS_BUILDING_BLOCK. Identifies the table or key (building block) to which this CA_Usage applies.

CURRENT_ROW_IND

CHAR(1)

Indicates whether this row of custom attributes is one of the current in-service rows for the network component.

PORTADDR_SEQ

NUMBER(10)

System generated number to uniquely identify and sequence port addresses for an equipment spec or piece of installed equipment.

CA_VALUE

VARCHAR2(1500)

The value taken on by an attribute, such as 320 for a Local Cell ID.

CA_VALUE_LABEL

VARCHAR2(50)

The name of the attribute associated to a value, such as Local Cell ID whose value is 320.

CA_VALUE_UOM

VARCHAR2(32)

The unit in a system that is used to determine the dimensions, area, volume, weight, or such of the attribute's value.

CA_USAGE_ID

NUMBER(9)

An Oracle sequence number that uniquely identifies an entity of this type.

CA_USAGE_VV_ID

NUMBER(9)

An Oracle sequence that uniquely identifies the valid value for an attribute associated to a building block.

CA_ID

NUMBER(9)

Foreign Key from CA_CUSTOMIZED_ATTRIBUTE. Identifies the CA value.

Table 4-11 describes the contents of the EXTRACT.MV_NS_COMP_EQUIP materialized view.

Table 4-11 EXTRACT.MV_NS_COMP_EQUIP Materialized View

Column Name Data Type Description

NS_COMP_ID

NUMBER(9)

An Oracle sequence number that uniquely identifies entities of this type.

NS_COMP_EQUIP_SEQ

NUMBER(3)

A number that together with NS_COMP_ID uniquely identifies entities of this type. This number starts with one for each value of NS_COMP_ID.

EQUIPMENT_ID

NUMBER(9)

An Oracle sequence number that uniquely identifies entities of this type.

MSS Equipment Extract Process Normal Views

You use normal views to:

  • Consolidate the data from multiple fast-refreshable materialized views

  • Simplify the presentation of the data

All the MSS extract normal views retrieve data from the EXTRACT schema.

The following normal views enable Network Integrity to consolidate equipment-related data from the normal/materialized views:

  • EXTRACT.V_EQUIPMENT: Network Integrity uses this normal view to consolidate the required information from the following materialized views:

    • EXTRACT.MV_EQUIPMENT

    • EXTRACT.MV_EQUIPMENT_SPEC

    • EXTRACT.MV_NETWORK_LOCATION

    • EXTRACT.MV_NETWORK_NODE

    • EXTRACT.MV_NS_COMPONENT

    • EXTRACT.MV_NS_COMP_EQUIP

  • EXTRACT.V_EQUIPMENT_SPEC: Network Integrity uses this normal view to consolidate the required information from the following materialized views:

    • EXTRACT.MV_EQUIPMENT_SPEC

    • EXTRACT.MV_EQUIPMENT_SPEC_MPOS

  • EXTRACT.V_EQUIP_LEAF: Network Integrity uses this normal view to consolidate the required information from the following materialized views:

    • EXTRACT.MV_EQUIPMENT

    • EXTRACT.MV_PORT_ADDRESS

    • EXTRACT.MV_CIRCUIT

    • EXTRACT.MV_MOUNTING_POSITION

  • EXTRACT.V_NN_FOR_HIER: Network Integrity uses this normal view to consolidate the required information from the following materialized views:

    • EXTRACT.MV_EQUIPMENT

    • EXTRACT.MV_NETWORK_NODE

    • EXTRACT.MV_NS_COMPONENT

    • EXTRACT.MV_NS_COMP_EQUIP

  • EXTRACT.V_NETWORK_NODE: Network Integrity uses this normal view to consolidate the required information from the following materialized views:

    • EXTRACT.MV_NETWORK_NODE

    • EXTRACT.MV_NS_COMPONENT

    • EXTRACT.MV_NETWORK_LOCATION

  • EXTRACT.V_PA_HIER: Network Integrity uses this normal view to consolidate the required information from the following normal/materialized views:

    • EXTRACT.MV_EQUIPMENT

    • EXTRACT.V_EQUIPMENT_SPEC

    • EXTRACT.MV_MOUNTING_POSITION

    • EXTRACT.V_NN_FOR_HIER

    • EXTRACT.MV_NETWORK_LOCATION

    • EXTRACT.MV_PORT_ADDRESS

    • EXTRACT.MV_CIRCUIT

  • EXTRACT.V_MP_HIER: Network Integrity uses this normal view to consolidate the required information from the following normal/materialized views:

    • EXTRACT.MV_MOUNTING_POSITION

    • EXTRACT.V_EQUIPMENT_SPEC

    • EXTRACT.MV_EQUIPMENT

    • EXTRACT.MV_NETWORK_LOCATION

    • EXTRACT.V_NN_FOR_HIER

MSS Circuit Extract Process

The MSS circuit extract process extracts the relevant circuit information from MSS and stores it into fast-refreshable, read-only materialized views within the EXTRACT schema in the MSS database, which is imported by the MSS Integration cartridge to compare the imported MSS data with discovered network data and resolve discrepancies on circuits in MSS.

MSS Circuit Extract Process Materialized Views

The MSS circuit extract process retrieves circuit information and stores it in the following MSS materialized views:

  • EXTRACT.MV_CIRCUIT: Stores the attributes of a circuit. See Table 4-12 for more information.

  • EXTRACT.MV_CIRCUIT_POSITION: Stores the channelization and assignment information for circuits. See Table 4-13 for more information.

  • EXTRACT.MV_CIRCUIT_XREF: Stores information about the circuit cross-reference. See Table 4-14 for more information.

  • EXTRACT.MV_CIRCUIT_TRAIL: Stores the hop-by-hop path for all non-channelized connectivity, including the allocation parameters such as VLAN ID, VPI/VCI, and DLCI, stored within custom attributes. See Table 4-15 for more information.

  • EXTRACT.MV_CIRCUIT_CA: Stores all of the custom attributes of a template-based connection such as the Bit Rate, Broadband Service Category, and Capacity Allocation Thresholds. See Table 4-16 for more information.

  • EXTRACT.MV_TFC: Stores additional information about CLF-formatted circuits. See Table 4-17 for more information.

  • EXTRACT.MV_NETWORK_LOCATION: Stores the attributes and defining information of a network location. See Table 4-8 for more information.

The following tables describe the contents of the MSS materialized views in which the MSS circuit extract process stores the inventory data.

Table 4-12 describes the contents of the EXTRACT.MV_CIRCUIT materialized view.

Table 4-12 EXTRACT.MV_CIRCUIT Materialized View

Column Name Data Type Description

CIRCUIT_DESIGN_ID

NUMBER(9)

An identifier visible only to the system. Used for storing and retrieving information about a single circuit.

EXCHANGE_CARRIER_CIRCUIT_ID

VARCHAR2(53)

Commonly known as EC ID. This is the circuit number assigned by you or provided on the order by the OEC (Other Exchange Company). Oracle recommends that you use the iconectiv COMMON LANGUAGE CLF, CLS, CLT, and CLM formats for circuits; however, freeform formatted identifications are also stored here for "Other" facility, serial, and telephone-type formatted identifications. The other identifications are identified by "OTF," "OTS," and "OTT" as an ECCKT_Type.

ECCKT_TYPE

VARCHAR2(3)

The ECCKT type:

  • CLF (Common Language Facility)

  • CLM (Common Language Message)

  • CLT (Common Language Telephone)

  • CLS (Common Language Serial)

  • OTF (Free format of the CLF; unformatted facility)

  • OTS (Free format of the CLS; unformatted serial)

  • OTT (Free format of the CLT; unformatted telephone number, used for PSR dialtone products: line and trunk)

  • CLF, CLT, CLS, and CLM values indicate the iconectiv Common Language Circuit Identification format.

TYPE

CHAR(1)

The type of circuit.

Valid values are:

  • F = Facility (connects two terminating locations with a rate that is usually higher than DS0 and carries other circuits).

  • T = Trunk (connects two serving office switching systems. A serving office can be a C.O. or MTSO).

  • S = Special (a dedicated circuit connecting two end-user locations or an end-user location to a coded location).

  • P = Product (a circuit ID created at the back end in PSR for dial tone circuits or trunks).

  • C = Template-based connections.

STATUS

CHAR(1)

The status for the circuit.

Valid values are:

  • 1 = Pending

  • 3 = In Progress

  • 4 = Record Issued

  • 5 = DLR Issued

  • 6 = In Service

  • 7 = Pending Disconnect

  • 8 = Disconnected

  • 9 = Problem

  • A = Cancelled

RATE_CODE

VARCHAR2(10)

The rate code associated with the circuit. For example, DS0, DS1, DS3, N/A, and so on.

SERVICE_TYPE_CATEGORY

VARCHAR(20)

A description of the service provided, such as special services (for IntraLATA and LATA Access), switched services, and facility services.

SERVICE_TYPE_CODE

VARCHAR2(10)

Identifies the service provided by a circuit:

  • For special services, the characters in positions 3 and 4 of the CLCI-SS format indicate the service that is provided.

  • For message services, the characters in positions 5 and 6 of the CLCI-MSG Trunk Group format indicate the traffic use.

  • For facility service, the characters in positions 6 to 11 of the CLFI format indicate the facility type.

NST_CON_TYPE

NUMBER(6)

A value that along with the category and name logically identifies a type of connection used to join two network system component types.

Valid values are:

  • 1 = Physical

  • 2 = Virtual

  • 3 = Group

NST_CON_CATEGORY_CD

NUMBER(6)

Indicates the category of connector spec type. This value is denormalized from the value within the NST_CON_TYPE table. Similar to circuit type. It further defines the type of link or connector.

Valid values are:

  • 1 = Facility (connects two terminating locations, with a rate that is usually higher than DS0, and carries other circuits).

  • 2 = Trunk (connects two serving office switching systems. A serving office can be a C.O. or MTSO).

  • 3 = Special (a dedicated circuit connecting two end user locations or an end user location to a coded location).

  • 4 = Product (a circuit ID created at the back end in the PSR for dial tone circuits or trunks).

  • 5 = Virtual circuits.

  • 6 = Bandwidth circuits.

  • 7 = Virtual connection that is not a PVC.

LOCATION_ID

NUMBER(9)

The A location ID of the circuit.

LOCATION_ID_2

NUMBER(9)

The Z location ID of the circuit.

Table 4-13 describes the contents of the EXTRACT.MV_CIRCUIT_POSITION materialized view.

Table 4-13 EXTRACT.MV_CIRCUIT_POSITION Materialized View

Column Name Data Type Description

CIRCUIT_DESIGN_ID

NUMBER(9)

The circuit design ID of the circuit.

CIRCUIT_POSITION_NUMBER

NUMBER(5)

The channel position of the circuit with respect to the parent circuit.

CIRCUIT_DESIGN_ID_3

NUMBER(9)

An identifier visible only to the system, used for storing and retrieving information about a single circuit. This circuit is assigned to the circuit represented by Circuit Design ID

ADDITIONAL_ASSIGNMENT_SEQ_NBR

NUMBER(2)

This assignment sequence is used to keep track of equipment assignments for multiple assignments of a circuit to the same network.

CIRCUIT_NODE_STATUS

CHAR(1)

Describes the current status of the circuit position.

Valid values are:

  • 1 = Unassigned

  • 2 = Pending installation work order

  • 3 = In Service

  • 4 = Pending removal work order

  • 5 = Trouble

STS_CHAN_NBR

NUMBER(3)

The synchronous transport number that is used to identify the actual designation for the virtual channel assignment. This is used in the concatenation process of network assignment identification. For example, 12-7-4, where 12 equals the STS assignment.

VTG_CHAN_NBR

NUMBER(1)

The virtual tributary group (VTG) number that is used to identify the actual designation for the virtual channel assignment. This is used in the concatenation process of network assignment identification. For example, 12-7-4, where 7 equals the VTG assignment.

VT_CHAN_NBR

NUMBER(1)

The virtual tributary (VT) number that is used to identify the actual designation for the virtual channel assignment. This is used in the concatenation process of network assignment identification. For example, 12-7-4, where 4 equals the VT assignment.

PROTECTED_PATH_TRI

CHAR(1)

Distinguishes the primary path from the protection path.

Valid values are:

  • Y = Identifies a protected path.

  • N = Identifies a primary (working) path.

  • Null = Identifies that the assignment is not part of a network path.

The attribute is set when creating a new network assignment block using the optical network provisioning assistant. It is used in mass reconcile and reconciliation from the circuit reconciliation window to keep the design lines in the proper order with the primary path displayed on top of the protection path in the Connection Design window.

Table 4-14 describes the contents of the EXTRACT.MV_CIRCUIT_XREF materialized view.

Table 4-14 EXTRACT.MV_CIRCUIT_XREF Materialized View

Column Name Data Type Description

CIRCUIT_DESIGN_ID

NUMBER(9)

An identifier visible only to the system. Used for storing and retrieving information about a single circuit.

CIRCUIT_XREF_SEQ

NUMBER(3)

Sequence number that starts over with every new relationship to circuit. (Not an Oracle sequence).

CIRCUIT_XREF_ECCKT

VARCHAR2(60)

ECCKT that needs to be cross-referenced to an ECCKT that was provisioned.

An ECCKT that another provider provisioned or an alias of one of the circuits.

LOCATION_ID

NUMBER(9)

The A location ID for the circuit.

STATUS

CHAR(1)

The status for the circuit.

Valid values are:

  • 1 = Pending

  • 3 = In Progress

  • 4 = Record Issued

  • 5 = DLR Issued

  • 6 = In Service

  • 7 = Pending Disconnect

  • 8 = Disconnected

  • 9 = Problem

  • A = Cancelled

Table 4-15 describes the contents of the EXTRACT.MV_CIRCUIT_TRAIL materialized view.

Table 4-15 EXTRACT.MV_CIRCUIT_TRAIL Materialized View

Column Name Data Type Description

CIRCUIT_DESIGN_ID_PARENT

NUMBER(9)

This is a foreign key from the CIRCUIT table. Represents the parent when connectors are associated with other connectors. For example, when a connection is allocated to a link, the link is the parent. Or, when multiple connectors are associated to one another to create a group, the parent is the group. For example, this occurs for inverse multiplexing.

CIRCUIT_DESIGN_ID_CHILD

NUMBER(9)

This is a foreign key from the CIRCUIT table. Represents the child when connectors are associated with other connectors. For example, when a connection is allocated to a link, the connection is the child. Or, when multiple connectors are associated to one another to create a group, the individual connectors are the children. For example, this occurs for inverse multiplexing.

CA_ID

NUMBER(9)

Foreign Key from CA_CUSTOMIZED_ATTRIBUTE. Identifies the CA value.

CA_VALUE

VARCHAR2(1500)

The value taken on by an attribute, such as 320 for a Local Cell ID.

CA_USAGE_ID

NUMBER(9)

An Oracle sequence number that uniquely identifies an entity of this type.

CA_VALUE_LABEL

VARCHAR2(50)

The name of the attribute associated to a value, such as Local Cell ID whose value is 320.

CA_USAGE_VV_ID

NUMBER(9)

An Oracle sequence that uniquely identifies the valid value for an attribute associated to a building block.

MS_BB_ID

NUMBER(9)

Foreign Key from MS_BUILDING_BLOCK. Identifies the table or key (building block) to which this CA_Usage applies.

NS_COMP_ID

NUMBER(9)

An Oracle sequence number that uniquely identifies entities of this type.

Table 4-16 describes the contents of the EXTRACT.MV_CIRCUIT_CA materialized view.

Table 4-16 EXTRACT.MV_CIRCUIT_CA Materialized View

Column Name Data Type Description

CONN_CA_VALUE_ID

NUMBER(9)

An Oracle sequence that uniquely identifies an attribute.

CIRCUIT_DESIGN_ID

NUMBER(9)

An identifier visible only to the system. Used for storing and retrieving information about a single circuit.

CA_VALUE_LABEL

VARCHAR2(50)

The name of the attribute associated to a value, such as Local Cell ID whose value is 320.

CA_VALUE

VARCHAR2(1500)

The value taken on by an attribute, such as 320 for a Local Cell ID.

CA_VALUE_UOM

VARCHAR2(32)

The unit in a system that is used to determine the dimensions, area, volume, weight, or such of the attribute's value.

CA_USAGE_ID

NUMBER(9)

An Oracle sequence number that uniquely identifies an entity of this type.

CA_USAGE_VV_ID

NUMBER(9)

An Oracle sequence that uniquely identifies the valid value for an attribute associated to a building block.

MS_BB_ID

NUMBER(9)

Foreign Key from MS_BUILDING_BLOCK. Identifies the table or key (building block) to which this CA_Usage applies.

CA_ID

NUMBER(9)

Foreign Key from CA_CUSTOMIZED_ATTRIBUTE. Identifies the CA value.

CURRENT_ROW_IND

CHAR(1)

Indicates whether this row of custom attributed is one of the current in-service rows for the network component.

Table 4-17 describes the contents of the EXTRACT.MV_TFC materialized view.

Table 4-17 EXTRACT.MV_TFC Materialized View

Column Name Data Type Description

CIRCUIT_DESIGN_ID

NUMBER(9)

An identifier visible only to the system. Used for storing and retrieving information about a circuit.

VIRTUAL_IND

CHAR(1)

Indicates whether the circuit is part of a virtual assignment or not.

Valid values are:

  • Y: Yes

  • N: No

TFC_NETWORK_ID

NUMBER(9)

The unique ID which identifies a network. A TFC Network maintains information concerning the various transmission facility circuit network topologies, such as point-to-point, linear Add/Drop, hubbing, and rings. These fiber networks are normally SONET-based; however, specific asynchronous facilities can be included. Other information pertaining to these networks are the Fiber Network Identification, assignment methods, protection schemes, and switching directions.

MSS Circuit Extract Process Normal Views

You use normal views to:

  • Consolidate the data from multiple fast-refreshable materialized views

  • Simplify the presentation of the data

All the MSS extract normal views retrieve data from the EXTRACT schema.

The following normal views enable Network Integrity to consolidate circuit-related data from the normal/materialized views:

  • EXTRACT.V_CIRCUIT: Network Integrity uses this normal view to consolidate the required information from the following materialized views:

    • EXTRACT.MV_CIRCUIT

    • EXTRACT.MV_NETWORK_LOCATION

  • EXTRACT.V_PA_HIER: Network Integrity uses this normal view to consolidate the required information from the following normal/materialized views:

    • EXTRACT.MV_CIRCUIT

    • EXTRACT.MV_MOUNTING_POSITION

    • EXTRACT.MV_EQUIPMENT

    • EXTRACT.V_EQUIPMENT_SPEC

    • EXTRACT.V_NN_FOR_HIER

    • EXTRACT.MV_NETWORK_LOCATION

    • EXTRACT.MV_PORT_ADDRESS

  • EXTRACT.V_CIRCUIT_POSITION: Network Integrity uses this normal view, which uses the JKLM stored function, to consolidate the information about JKLM values from the following materialized views:

    • EXTRACT.MV_CIRCUIT

    • EXTRACT.MV_CIRCUIT_POSITION

Extending the MSS Extract Process

This section provides information on extending the MSS extract process.

The system integrator can extend the Equipment/Circuit extract process by adding additional columns to the definition of an existing materialized view, create new materialized views, and create new normal views to retrieve data from new or existing materialized views. When retrieving data from a table that does not already have a materialized view log, you must first create the materialized view log in order to incrementally refresh the materialized view. This process does not require writing any PL/SQL logic.

When creating a new materialized view or extending an existing materialized view, Oracle recommends that you structure the materialized views to be incrementally (fast) refreshed.

The reference integration also provides normal views to consolidate and simplify the data from multiple materialized views. See the following sections for more information on the normal views:

You can find more information about materialized view concepts and architecture at the following Web site:

http://docs.oracle.com/cd/B28359_01/server.111/b28326/repmview.htm#i34980