System and method for determining discrepancies in a communications system

[0001] The present invention is directed to systems and methods for identifying cost, network element and billing record discrepancies in a communication system. More particularly, the present invention is directed to systems and methods for examining data of a communications system to identify charges incurred unnecessarily or in error by relating invoice, inventory, usage, and billing data. In doing so, the system identifies overcharges and undercharges and where carriers lose capital revenue.

[0002] Revenue leakage is a persistent problem for telecommunications carriers. Revenue leakage may be defined as the loss of revenues or profits due to systemic inefficiencies related to failures to correctly match rates or costs with services actually provided, to accurately measure the quantity of services provided, and to properly characterize the nature of the traffic exchanged over the network. Errors in order-entry, inaccurate traffic measurement and disconnects are just a few examples of how revenue leakage causes carriers to lose millions of dollars each month. Because modern telecommunications networks typically have been patched together over time using different communications protocols and operating systems, such problems are not easily identified, and specific errors can persist indefinitely.

[0003] Revenue leakage has a significant impact on carriers' profitability. In 2000, worldwide telecom service revenues were nearly $875 billion. Some have estimated that revenue leakage is equal to between 2% and 11% of total carrier revenues. Thus, lost revenues industry-wide may range from $17.5 billion on the low end to as high as $96 billion. Revenue leakage is likely to become more serious as the volume and complexity of products, services and network traffic continues to increase, particularly in large enterprise retail and wholesale market segments.

[0004] Currently, telecommunications carriers address revenue leakage by performing annual or one-time system and process audits performed generally by large accounting and consulting firms. Typical practices include switch-to-bill verifications, interconnect billing audits, inventory and provisioning audits, and traffic and usage analysis. Additionally, there are a number of software vendors who address selected aspects of the revenue assurance market. For example, companies such as Vibrant, TEOCO, and BroadMargin have deployed invoice automation and analysis tools in the CLEC marketplace. Test call generators such as BoardRoom focus on the integrity of the switch- to-bill process, and inventory reconciliation companies such as CoManage and T-Soft have developed tools to address discrepancies in the inventory databases.

[0005] These methods fall short of providing an on-going comprehensive solution because they are one-time in nature and focused on single pieces of a much larger problem. What is missing is a all-inclusive solution: one that uses existing OSS (Operational Support System) and BSS (Business Support System) platforms and enables them to be related so that discrepancies can not only be identified and quantified, but also more easily resolved. In short, the solution would need to be easily integrated into existing legacy systems and able to interact with all the relevant invoice, usage, inventory and retail billing systems that are spread across the networks of carriers and their trading partners.

[0006] The present invention addresses the above issues and presents a novel system for determining revenue leakage in a communications system. The system according to the present invention may handle voluminous records of different data types and stores and analyzes data from disparate sources. By accurately capturing invoice data, the present invention may compare network inventory and usage data against invoice data. The information may be retrieved from a database and displayed in reports that are displayed in a Web browser, for example, on the client machine. The reports, accordingly, derived from electronic and paper (manually entered) invoices, may provide a comprehensive view of all electronic invoice activity.

[0007] Components of the present invention may effectively extract, condition, consolidate, and report on four expanding sources of data: cost data, revenue data, data from usage records, and network inventory data. To that end, the present invention may use data sources in the operational environment of a telecommunication carrier: network element cost data contained in thousands of detailed and cryptic monthly carrier invoices found in different locations, mediums, and formats; revenue data in the form of retail and wholesale customer monthly billing and collection data; usage records copied and extracted from the mediation and/or switch usage measurement platforms of a customer; and network inventory data found in multiple provisioning and facility assignment systems.

[0008] Invoice, inventory, usage and billing data may be collected, parsed, and stored. Next, transformation from ambiguous, meaningless data to succinct business and financial information may occur by relating these disparate elements. By leveraging reference information, the data may be further enriched (e.g., Industry Network Assignments, Rate/Tariff, Contract Terms, Network Inventory abstracts, and the like). Business rules may be applied to answer custom-designed questions, and analytic processing routines may be applied to solve complex business problems and to ensure the quality of the billing activities of the carrier.

[0009] Accordingly, in a first aspect of the present invention, a system for determining discrepancies in a communication system includes an invoice management module and a validation module. This embodiment may also include a reporting module, a discrepancy analytical module, an inter-carrier traffic analysis module,

[0010] In another aspect of the present invention, a system for determining discrepancies in a communications system includes a first module for maintaining persistent data for the system, a second module for processing invoice data, a third module having an application for allowing a user via a user interface to view data and a client module for accessing the application and user interface to view data. The modules may be a computer system, server system and/or workstation.

[0011] In yet another aspect of the present invention, a method for determining discrepancies in a communications network includes retrieving invoice data, parsing the invoice data into a plurality of first records, verifying the invoice data in the first records and loading the first records into a first database.

[0012] In the above aspect, parsing may include reading a specification file associated with the type of data of the invoice records and dividing the invoice records into a plurality of formatted discrete fields. Parsing may also include any one or more of the following: classifying each invoice record into an invoice record type, generating at least one token for each invoice record, determining a database table to assign each invoice record, extracting a first field required for the database table from each invoice record, writing the first field to an output file, and loading the output file into a primary database.

[0013] In another aspect of the present invention, a method for determining discrepancies in a communications system includes retrieving communication data from at least one data source, parsing the communication data, analyzing the parsed data and reporting a result of the analysis. Parsing may include one or more of the following: breaking the communication data down into a plurality of corresponding records and usage files which describe a structure and form of the communication data, enriching the communication data with reference data.

[0014] The above method aspect may also include storing the plurality of records on a relational database.

[0015] In yet another aspect of the present invention, a system for determining discrepancies of a communication system includes at least one data source, at least one data parser and/or data adaptor, at least one data loader and/or data abstractor, at least one database abstraction, at least one data store, an invoice management module, a revenue and cost management module, an intercarrier traffic management module, a secondary user-interface table and a user interface.

[0016] In still yet another aspect of the present invention, a system for determining discrepancies in a communications network includes retrieving means for retrieving invoice data, parsing means for parsing the invoice data into a plurality of first records, verifying means for verifying the invoice data in the first records and loading means for loading the first records into a first database.

[0017] Still other aspects of the present invention include computer readable media having computer instructions provided thereon for enabling a computer system to perform one or more of the method aspects set out above (either alone or in combination), and also to application program aspects for enabling a computer system to perform any one or more of the method aspects set out above (either alone or in combination).

[0018] Accordingly, the above objects, advantages, features and aspects of the present invention will become clearer with references to the figures (a brief description of which is set out below) and the following detailed description of the preferred embodiments.

[0019] FIG. 1 illustrates an overview of the system according to an embodiment of the present invention.

[0020] FIG. 2 illustrates a systems flow and technology overview of the system according to an embodiment of the present invention.

[0021] FIG. 3 illustrates a logical architecture of a parser and loader processes for an embodiment according to the present invention.

[0022] FIG. 4 illustrates the ICTA process for an embodiment according to the present invention.

[0023] FIGS. 5-8 are example screenshots of output generated by the system according to some of the embodiments of the present invention.

[0024] The following are definitions which may be associated with the present invention that are generally used in the detailed description of the preferred embodiments.

[0025] Access Charges: Fees paid by subscribers and long-distance carriers for their use of the local exchange in placing long distance calls.

[0026] Aging: The status of the invoice as it moves from one status to another (for example, from a new invoice to a disputed invoice).

[0027] AMA: Automatic Message Accounting.

[0028] BAN: Billing Account Number.

[0029] BDT: Billing Data Tape.

[0030] BTN: Billing Telephone Number.

[0031] CABS: Carrier Access Billing System.

[0032] CDR: Call Detail Record. A service feature in which call data on a specific telephone extension or group of subscribers are collected and recorded for cost-of-service accounting purposes.

[0033] Call Record: Recorded data pertaining to a single call.

[0034] Carrier: See its synonym, common carrier.

[0035] Central Office: The site that contains the local telephone company's equipment that routes calls to and from customers. This site also contains equipment that connects customers to long distance services and Internet Service Providers.

[0036] Checksum: Checksums, the sum of data items, are stored or transmitted with data and are intended to detect data integrity problems.

[0037] Call Record: Recorded data pertaining to a single call.

[0038] CLEC: Competitive Local Exchange Carrier. A CLEC is a wireline-based local exchange (switched and non-switched) carrier serving in a geographical area that is already served by an incumbent local exchange carrier.

[0039] Common Carrier: A telecommunications company providing communications transmission services. Its synonym is carrier.

[0040] CLEC: Competitive Local Exchange Carrier

[0041] Convergence: The merging of different technologies such as telephony, computers, and cable.

[0042] CPN: Calling Party Number or the originating number as defined in the Revenue Assurance system.

[0043] Dedicated Line: A communications circuit or channel provided for the exclusive use of a particular subscriber. Dedicated lines are often used to move large amounts of data between computers.

[0044] Discrepancy: An inconsistency that is detected between an invoice charge and an expected element value.

[0045] Discrepancy Tracking: A sub-component of Invoice Tracking, Discrepancy Tracking tracks and displays system-identified discrepancies for an invoice from open to closed states.

[0046] Dispute: Refers to payments being withheld and action taken to challenge the validity of one or several charge elements on an invoice.

[0047] Dispute Substantiation: Documentation of dispute including all original source data (for example, CABS source data would include the Pack Sequence number, the Record Sequence number, and the line number).

[0048] Dispute Tracking: Monitor, report and update the status, progress, and assignment of a specific dispute from initiation to resolution. Allows a manager to measure primary operational metrics (for example, aging, time from initiation to resolution, and assignments.)

[0049] DSL: Digital Subscriber Line. Provides instant Internet and network access at speeds up to 50 times faster than a 28.8 Kbps modem on a standard analog phone line. Because DSL sends data and voice over the same line, one can talk on the phone while connected to the internet.

[0050] DUF: Daily Usage File.

[0051] End Office: End Office (EO) is a switching system that establishes line-to-line, line-to-trunk, and trunk-to-line connections, and provides dial tone to customers.

[0052] IEC or IXC: Interexchange Carrier. Synonymous in common usage with long distance carrier.

[0053] ILEC: Independent Local Exchange Carrier.

[0054] Inbound: Refers to calls entering the network of the Revenue Assurance customer.

[0055] Individual Charge Threshold: The amount by which the system recognizes an individual charge as a discrepancy.

[0056] Internet Traffic Analysis: Identification of dial-up data access numbers and measurement of network interconnection traffic volumes to these suspected Internet Service Providers.

[0057] Interconnection Carrier Traffic Report: Monthly reporting of carrier Minutes of Use (MOU) and peg count call levels that are exchanged between the CLEC and identified facility-based interconnecting networks. Balance ratios are calculated for reciprocal compensation bill validation.

[0058] Interexchange Carrier: See IXC.

[0059] IntraLATA Calling: Calls originating and terminating in the same service area (LATA).

[0060] Intrastate InterLATA Calling: Refers to calls originating in one service area (LATA) and terminating in another service area (LATA) but these calls are in the same state, for example, Austin to Dallas or Austin to Houston.

[0061] Invoice Browser: Read-only navigation, query and reporting on secondary tables.

[0062] Invoice Explorer (formerly invoice browser): Enables an end user of present system to look at everything in the user interface, even hidden details. See original invoice in its original structure as stored in the primary tables in the database. Not rolled up by USOC.

[0063] Invoice Tracking: Process of invoice to monitor, report, and update the status and assignment from receipt to closure.

[0064] ISDN: Integrated Services Digital Network. A standardized design for simultaneous voice, data and video signals over a pair of twisted wires, the most common type of customer line in the telephone network.

[0065] IXC: Interexchange Carrier. Synonymous in common usage with long distance carrier.

[0066] Jurisdictional Measurements: Measurement of toll and local interconnection traffic levels and calculation of actual Percent Interstate Usage (PIU), Percent Intrastate Toll, and Percent Local Usage (PLU) factors.

[0067] LATA: Local Access Transport Area. Defines that area, in a state served by a Bell telephone company, in which, under current federal Telecommunications Act rules, the company can provide service. Each service area may include one or more area codes or share a common area code.

[0068] LCC: Line Class Code. The Line Class Code is used to establish the routing service. A customized routing plan is negotiated as part of the Network Design Request (NDR) process that takes place between an ILEC and a CLEC.

[0069] LD: Long Distance.

[0070] LEC: A Local Exchange Carrier provides local exchange services including the Bell Companies and all independent telcos.

[0071] LERG: Local Exchange Routing Guide. The Local Exchange Routing Guide provides a listing of routing data obtained from the Routing Database System (RDBS) into which data are entered by Local Service Providers (LSPs) and/or their agents. The LERG Routing Guide reflects information contained in the database as of the run date of the LERG production cycle. With few exceptions, this is the last working day of each month. The LERG Routing Guide reflects the current state of active network data and also reflects future activity within the North American Numbering Plan (NANP).

[0072] Line-level Usage Validation: Line-level usage validation is functionality that categorizes, summarizes, and compares usage from two sources for a billing period by individual telephone numbers. The comparison produces discrepancies, indexed by Working Telephone Number (WTN), billing period, and other key fields, which are reported in Peg Count and MOU differences.

[0073] LLR: Line Loss Report.

[0074] Local Exchange Routing Guide: See LERG.

[0075] MOU: Minutes of Use. The aggregated total of minutes for a set of calls.

[0076] NPA: Numbering Plan Area.

[0077] Number Portability: Number portability is the term used to describe the capability of individuals, businesses, and organizations to retain their existing telephone number(s)and the same quality of servicewhen switching to a new local service provider.

[0078] OCC: Other Charges and Credits.

[0079] OCN: Operating Company Number. A telephone industry code used to identify a telephone company.

[0080] Originating: In the Revenue Assurance system, originating and terminating refers to the endpoints of a call. That is, originating refers to the caller making the call and terminating refers to the person receiving the call.

[0081] Outbound: Refers to calls leaving the network of the Revenue Assurance customer.

[0082] Peg Count: The number of Call Detail Records.

[0083] Percent Interstate Usage: PIU.

[0084] Rate Center: A Rate Center is technically the approximate midpoint of what is usually called a Rate Exchange Area, although the term Rate Center also has been used synonymously with the geographic area itself.

[0085] RBOC: Regional Bell Operating Company. In general, this refers to one of seven corporations created to provide local exchange service as part of AT&T's 1984 divestiture (Ameritech, Bell Atlantic, BellSouth, Nynex, Pacific Bell, Southwestern Bell, US WEST).

[0086] RBS: Retail Billing System.

[0087] RDBS: Routing Data Base System.

[0088] Resale: The ability of an entity that has not constructed a network of its own to offer to end users services located on a network built by another.

[0089] Site Map: A site map or site index is a visual model of a Web site's content. The site map allows the users to navigate through the site to find the information they seek.

[0090] Terminating: In the Revenue Assurance system, originating and terminating refers to the endpoints of a call. That is, originating refers to the caller making the call and terminating refers to the person receiving the call.

[0091] Time-of-Day Report: This reporting module identifies and reports the day-evening-night-weekend usage volumes for originating and terminating traffic.

[0092] Trending and Usage Patterns: Reports on statistics and time series measurements pinpoint interconnection traffic patterns and show changes in traffic distributions.

[0093] Unbundling: Disaggregated features, functions, and capabilities of the local network. Disaggregation is intended by regulators to maximize competitive entry.

[0094] UNE: Unbundled Network Element.

[0095] UNE-P: Unbundled Network Element-Platform.

[0096] Usage: Measurement of service utilization. Comes in the form of CDRs (AMA, BAF, EMI, SS7). In the Revenue Assurance system, reported usage is provided by a third-party source. Invoiced usage can be rated and billed on the invoice. Invoiced usage can be aggregated.

[0097] USOC: Universal Service Ordering Code.

[0098] Web Browser: Client software, communicating with a Web server, navigates a web of interconnected documents on the World Wide Web.

[0099] Web Server: A powerful computer that is connected to the Internet or an intranet that stores files and displays them to persons accessing them via hypertext transfer protocol (http).

[0100] WTN: Working Telephone Number.

[0101] Revenue Assurance Systems/Methods

[0102] FIG. 1 illustrates an embodiment of the present invention where monthly payables CABs data, which may include data associated with IXC, CLEC or other invoiced communication billing data (e.g., wireless), may be loaded using an invoice loader. Monthly receivable CABs data may also be loaded via the invoice loader. Usage data may be loaded using usage loader as well. FIG. 2 illustrates a Logical System Architecture Overview according to another embodiment of the present invention.

[0103] Using references data (abstraction) of the operational support system (OSS) and billing support system (BSS), the present invention may use one or more modules to load, analyze and report revenue discrepancies and validation of invoices, rates, and usage.

[0104] For the system to be easily integrated into a customer's existing operational environments, a Command Center feature for overall control of all the systems functions and processes may be preferably included. The Command Center allows system administrators to configure various data sources and log files, establish security policies, view file-processing status, and establish error-handling policies. All subsystems communicate with the Command Center to provide real-time system monitoring.

[0105] One embodiment of the system according to the present invention may automate the following processes and tasks:

[0106] Data Retrieval. The product reads network, billing and usage data from a variety of sources, including monthly invoices (CABS data), call records and usage files, and customer billing information derived from retail billing and accounting systems.

[0107] Preferably, an embodiment of the present invention provides a system which supports an unlimited number of external systems and has the capability to load several sources of data elements simultaneously by distributing processing across multiple application servers. The present system is preferably compatible with existing legacy systems and requires generally no external changes to those systems. The system generally uses only relevant information from complex OSS and BSS platforms and may asynchronously receive invoices, usage and reference data. The system may be capable of acquiring data from various sources including CD-ROM, FTP, NDM and manual input. In situations not covered by these scenarios, data retrieval can be accomplished through a custom reader.

[0108] The present invention automates the invoice administration process by reading, parsing, loading and processing CABS and/or BDT invoices, miscellaneous machine-readable electronic invoices, and existing customer invoice data stores. Also provided may be a means to manually load paper invoices. In addition, the system may read, process, bin and load a customer's usage measurement system and reference data sources.

[0109] Data Parsing, Standardization and Storage. Parsing modules may break original data streams down into corresponding records which describe the structure and form of the source data. As the parsers restructure the data into records, the data may be enriched using the client's reference data (e.g., tariff and rate information). Data enrichment generally reduces the number of reference look-ups that the system may need to perform when producing reports. Accordingly, data enrichment increases the overall system's efficiency and throughput.

[0110] Once the data has been retrieved and parsed, the data may be transformed into an abstracted/standardized format and stored on a relational database by loaders. Such relational databases may be, for example, Oracle 8.x and MS SQL Server 7.0 databases. In one embodiment of the present invention, the system matches records from the system database to those in the clients' source systems. By matching such records and identifying places where they disagree, the system identifies resource and billing discrepancies. The results of these comparisons are stored (e.g., back in the system database) for historical and reporting purposes.

[0111] Data Analysis and Reporting. Generally, analysis occurs where the software applies customer determined business rules to link/correlate data objects. The specific processing logic for linking unrelated data is performed through the use of database triggers, stored procedures or through program routines within the data loaders. Examples of data links the system performs include linking cost objects to their corresponding revenue objects. Business rules may also be applied to ensure that cost and revenue objects are computed correctly. Business objects may also be linked to corresponding cost or margin objects (for example).

[0112] Reporting. Preferably, once all the data has been processed, it is abstracted into a set of database views that simplifies reporting of information. These views also serve to help retain the unique and proprietary data schema that is core to the underlying system. Customers may view data through Java Server Pages (JSPs) that may be modified or extended, depending on customer requirements. Additionally, since a user interface is preferably based on internet-web technology, it is easily deployed to any workstation with an installed web browser.

[0113] The present system allows users to design and publish their own reports in both Web-friendly and Excel formats (for example). Because the reporting module uses a commercial, relational database as its source, the reporting package may be replaced with one of the clients' choosing.

[0114] Product Modules

[0115] Invoice Management Module. This module preferably may provide a streamlined, electronic, scalable view to manage and report business and financial measurements of incoming and outgoing invoice activity. The module's flexible framework enables automated loading of any electronic invoice, independent of type, and for non-electronic invoices, manual loading of invoice data is provided.

[0116] Summary bill views may present high-level information on invoices by carrier, state, billing account number (BAN), year and month. The present system also preferably enables the user to view every individual charge contained in a stored invoice, and the logical invoice structure as originally submitted by the carrier. Selection and display criteria allow users to preferably quickly access any invoice information. In that regard, users are able to view and analyze invoice data trends.

[0117] Detailed views may provide drill down functionality to examine specific details of any invoice charges. Current views are provided for Payments, Adjustments, OCCs (NRC), Recurring, Usage, Late Payment, Taxes and Surcharges. Additionally, each area can be viewed, summarized and trended, and easily exported from the user interface to Excel (CSV) and PDF formats. Each report page may be book-marked by the end user for convenient and easy access to the most relevant reports.

[0118] One advantage of the Invoice Management module according to one embodiment of the present invention is it that it may provide a single repository and a common, comprehensive view of all electronic invoice activity (BDT, non-BDT electronic, other invoice data) for a customer billing operations team. By acquiring, conditioning and normalizing all the disparate invoice records into a single data store, the Invoice Management module provides a vital information resource for a carrier's cost management, revenue assurance, and network financial functions.

[0119] Revenue and Cost Management Module. The Revenue and Cost Management Module may organize and analyze invoice, usage, and network inventory data to detect revenue and cost opportunities. The module preferably may perform the following functions: analytics and rate validation. Specifically, with regard to analytics, the present module preferably provides customizable, organized and logical views of invoice activities as it relates to service levels and network usage. The module may also include the ability to analyze itemized charges on both incoming and outgoing invoices and to detect and display revenue/cost trends at a detailed or macro level. With regard to rate validation, the module compares invoiced rates with those stored in the product rate book to identify inconsistencies that may result in under- or over-charges for services actually delivered.

[0120] Inter-Carrier Compensation Traffic Analysis (ICTA) Module. This module may draw upon customer call records (CDR) or usage measurement systems to consolidate and generate traffic analysis reports that relate measured traffic data to corresponding invoice data. Specifically, this module may provide detailed access to network usage data that identifies key inter-connect data including parties involved, locations, and type of traffic being exchanged between the customer and its trading partners. The ICTA module also provides product management, regulatory and legal personnel with accurate network traffic data for use in the resolution of billing disputes.

[0121] In a preferred embodiment of the present invention, the web-based interface provides a practical view of traffic patterns and trends generated by one or more of the modules that can be applied to affect changes in terms for ratio and factor based billing found in carrier contracts.

[0122] Reports. The following table illustrates exemplary reporting categories that may be generated by the system according to the present invention. See FIGS. 6-9 for example screenshots of some web-based, for example, reporting.

Reports	Description
Interconnection	Monthly reporting of local and access Minutes of Use
Carrier Traffic	(MOU), peg count, and corresponding balance of
	traffic levels. As needed carrier-to-carrier ratio and
	volume distribution factors can be calculated and
	reported.
Transit and	Discrete identification of originating and terminating
Time-of-Day	transit traffic levels by carrier, switch, and location.
Measurements	Additionally, time-of-day traffic distributions can be
	figured and compared to terms of local
	interconnection agreements.
Jurisdictional	Measurement of toll and local interconnection traffic
Measurements	levels and calculation of actual Percent Interstate
	Usage (PIU), Percent Intrastate Toll, and Percent
	Local Usage (PLU) factors.
Internet Traffic	Identification of dial-up data access numbers and
Analysis	analysis of traffic volumes to these suspect ISPs.
Traffic	Identification and quantification of traffic types and
Characterization	users interconnecting with the carriers network.
	Examples include wireless, toll free, Directory
	Assistance, Operator Services, and chat line traffic.

[0123] Parser and Loader Processes (FIG. 3). The system and method according to the present invention parses and transforms electronic and manual invoices and loads them into a database so that the data can be easily accessed from a desktop workstation. Preferably, the information is made available for various analysts and managers in an easy-to-understand user format that corresponds to the paper and electronic invoices.

[0124] The system according to an embodiment of the present invention may handle voluminous records and different data types. In addition to serving as an Invoice Browser, the end user may drill down on the data to examine details and analyze Intercarrier Traffic data for trends. Key functionality may include reports that show charge discrepancies between data that is reported in the invoice versus what is measured on the network. The user can display trend information graphically in the UI as well.

[0125] The invoice parser according to the present invention may extract information from paper (manually entered) and electronic invoices, and transform the data into output files that are loaded into a database using a bulk loader (e.g., such as Oracle Bulk Loader or SQL Loader). The electronic invoices are typically delivered in to the parser in BDT format, however, the invoice parser may also accept Call Record Details (CDRs).

[0126] The output files in the database are may be made readable for the user interface, although several steps may generally be performed prior to the information from the invoices being extracted, parsed, loaded and displayed.

[0127] Acccordingly, in one embodiment of the present invention, a script file (loader.sh) drives the loader and parser processes. The invoices may be loaded and the system may determine the invoice type using lookup tables. To that end, the loader.sh script looks at the Loader Configuration file to organize the invoices and to identify which type of Reader Specification file to use.

[0128] The Reader Specification files may specify record positions and record titles for each type of CABS file. Preferably, the Lookup files are used to identify the file types that distinguish records by their formats, version numbers, vendors (e.g., by OCNs), and geographical regions. These Reader Specification files, which represent each record type, may be modified easily to accommodate future versions of CABS invoices.

[0129] The parser may use the map files to map data to database columns and indicate which fields should be pulled into the database. Preferably, there exists one map file for every table to be populated, and a set of map files for every type and version of invoices to be parsed. One set of map files may be available for each Reader Specification file.

[0130] The invoice parser may include preferably four logical components: a reader, a lexer, a grammer component and an emitter. The reader may read the BDT file line-by-line and may parse the raw BDT records into formatted data structures. The Lexar component may take the parsed BDT record and classify it by record type, and may also generate one or more tokens (identifiers). The Grammar component may take classified records and determine what database table, if any, to populate with the record. The Emitter component may take the record and the database table name, extract the fields from the record that are required by the table, and then may write the table record to the output files.

[0131] With the Grammer component, the database table determination may be based generally on the context of the records that come before and after it. The Grammar files may be read in using Bison, for example, which pulls the data into a structure that corresponds to the proper form. Bison is a general-purpose parser generator that converts a grammar description for an LALR context-free grammar into a C program to parse that grammar.

[0132] The output files, which now may have a one-to-one relationship with the original invoice records, may be loaded into the primary database using a bulk loader.

[0133] The primary database records may be aggregated and formatted using SINS Processing (see below) to create user interface secondary tables. In anticipation of new record types and versions, the present invention may include a Database Schema Generator, which may use control files to generate new DDL files for a new database schema.

[0134] Expression Utilities. Because source data is extremely diverse, expression utilities may be used to modify input files using one or more business rules before they are loaded into the database. At a minimum, input data may come from a billing system, a switch, or from usage processors. Because the input files from these sources may have column values that are tab delimitated, certain operations may be performed programmatically to produce more meaningful output files. These output files may be designed to contain data based on agreed-upon business rules (for example). The data from these output files may be extracted and presented in the reports after data are calculated from the primary and secondary UI tables.

[0135] Expression Utilities may be used to modify input files. The below listed table gives a description of example Expression Utilities which may be used in the present invention.

Utility	Description
Agg	Takes in a pre-sorted file and passes the lines to the
	expression output system indicating when groupings of the
	lines ends, thus, allowing the user to manipulate output
	aggregations.
CAT	Programmatically combines files together so the files are
	concatenated. This utility assumes that the files have the same
	structure. Might be useful if additional operations are
	necessary. These can be done against one long file.
Filter	Another term for transforming file data with a simple pass
	through operation. For example, the output file could have
	one column removed if instructed.
Join	Takes two input files and examine sets of presorted delimited
	data. One can find what is in one set of data but not in the
	second set of data. An inner join can be added to find all like
	components, for example. Using the Join utility is beneficial
	when performing lookups. The Join utility is more powerfully
	used if two data sources are compared.
Sort	The Sort utility sorts delimited or undelimited text files based
	on a selected set of columns.
Usage	Combines two components, CAT and Sort. This is useful
Proc	when examining data for a month or longer.
xRef	Similar to Join but it can work with different input files.
	Effective when used for more than two input data sources.
	Our system can accommodate up to 32 different sources. For
	example, the Revenue Assurance system can look at files 1
	and 2 and produce file 3 that has specifically requested output
	information.

[0136] An Xreference utility may provide a way to do joins between multiple (>2) input sources. The primary use of this may be to replace the functionality of the text utility comm. For each input row the expression specified by the -x or -X option may be evaluated and the result of the evaluation may then be used to compare the rows.

[0137] SINS Processing. SINS processing describes how customer data, such as invoices may be processed. For example, invoice data types like UNE, Inter-carrier, Switched Access, Resale, and UNE-P from selected Operating Company Numbers (OCNs), may be processed with the data separated into directories by data type. To process the electronic data, the following steps may be taken: parsing the data, verifying that the invoice information has been parsed correctly, loading the invoice data into the database and calculating the primary and secondary user interface tables.

[0138] SINS processing, which may be an expanded version of SQL, may integrate shell script capabilities with interactive database utilities. SINS processing, which may be used throughout the loader process, performs several key functions. For example, it may be used whenever shell scripts hit the database for information of any sort. It may maintains a real-time status of invoice processing. SINS processing also may enable SQL to be run from a shell script with called arguments. It may echoe a SINS command to a GPP (Generic Pre-processor). This command may be expanded and allow a SQL call with an argument.

[0139] SINS may also be used to verify if an invoice exists in the database and may return an error if duplicate invoices are found. SINS processing may also include the driver for aggregating and formatting records from the primary tables to the user interface secondary tables. It may create unique identifiers in the secondary tables for the combined records from the primary tables. SINS processing may also be used to unload the database tables.

[0140] SINS Programming. SINS may be groups of SQL commands that are created as macros.

[0141] An example of a macro definition may be:

	define MACRO_NAME(ARG1, ARG2, ARG3)
	MACRO_BODY

[0142] Although the syntax may be different, the behavior may be similar to standard C pre-processing. For example, when MACRO_NAME (foo, bar, 2) is used, it is replaced globally with MACRO_BODY.

[0143] Also, SINS may support including files similar to C programs. An include example statement may be:

[0144] include utils.sql

[0145] The include path may be searched for the file utils.sql in the include path (described below) and, if found, it may be included.

[0146] SINS files also have conditional inclusion, which is of the form:

[0147] if string1string2

[0148] STUFF

[0149] endif

[0150] This tests to see if some value (string1) may be equal to some other value (string2). Typically, one of these strings may be a macro.

[0151] All SINS may be contained in the directory tree contained by, for example, $SALMON_HOME/conf/SINS. A shell script fragment, performSINS.sh, executes SINS. This script may not be executed by itself. Rather, it may be used by both the loader.sh script and confess.sh script, internally.

[0152] When SINS are run, there may be two parts: the loading of all the macros and the expanding of the macros that are called. If all only macros are defined, no output will result and no SQL that gets sent to the interpreter. When SINS are executed, the performSINS requires a main file and a SINS command. The main file may be a root file that includes all other files. These files all preferably exist in the SINS directory, at the top. Typically, the main files may be a list of includes, to pull in the appropriate files (and SINS) as needed. The loader.sh script may use loaderMain.sql, and confess.sh may use confessMain.sql. All files in the main file may be included as well as all subsequent files may be as well.

[0153] After all files that have been included have been processed, all possible macros have preferably been defined. At this time, the requested SINS commands may be performed, by expanding the requested macro. Preferably, two macros may always expanded, mINIT and mCLEANUP. Any other macros that get requested by loader.sh or confess.sh may be placed between these two macros.

[0154] By inspecting the confess.sh main file (confessMain.sql), an include for loaderMain.sql may be produced. Upon inspection of this file, an include for utils.sql may be produced. This file includes general utility macros to assist with compatibility issues if more then one database type is to be supported by the system. It also may promote easy readability of some SINS.

[0155] When SINS is executed, one or more items may be given to performSINS to configure the SINS appropriately. First, the type of database may be specified. Also, as much as is appropriate, the format, version, vendor, and type of file being processed may be specified.

[0156] Two things may be done with this information. First, an include path may be dynamically generated. Macros may also defined to be possibly tested for conditional inclusion (as described above). Below is a list of exemplary defines, and a list of directories (in order) that may be reviewed for include files. Anything beginning with $ may be a variable that may be supplied by the program (loader.sh or confess.sh) that is performing.

[0157] Defines:

[0158] _DATABASE_the type of database

[0159] _FORMAT_the format of the data

[0160] _VERSION_the version of the data being processed

[0161] _VENDOR_the vendor of the data being processed

[0162] _TYPE_the type of the data being processed

[0163] SINS Naming Convention. In general, the following naming conventions may be used: all macros begin with a lowercase m; all arguments begin with a lowercase a; preferably, SINS begin with the command that they are performing, if they are doing a single command (mINSERT_TableName, or mUPDATE_TableName); and files are named after the only (or main) SINS that are in the file (if the main SINS is called mINSERT_TableName, the file would be InsertTableName.sql).

[0164] Processing modules using SINS. There may be three options for processing the invoice data. First, a record set may be run in immediate batch mode; the default using the loader.sh script. Second, the records for the daemon may be queued, which allows for the records to be processed in the background (a scheduler triggers job processing). Third, scripts to execute each task individually (for example, parsing and verifying) may be run using arguments with the loader.sh script.

[0165] The loader.sh script automates the process of parsing, verifying, loading, and committing the invoice records (typically delivered on BDT) into the database. In a test environment, for example, invoices may be found on jigsaw-sun>BDT>*>* (for example, jigsaw>sun>BDT>Conversent>N>9106>V35. The N subdirectory represents billing data type, in this case, UNE files. The Q subdirectory (not shown here), represents Resale files. The 91* subdirectory indicates the region from which the data represents, for example, 9106 stands for, for example, Verizon New York, and V35 indicates the version of the invoice file.

[0166] Without any options, the script may parse, load, and SINS may process all files. The loader.sh script may include the following miscellaneous options set out below.

[0167] Before running the Loader.sh script, the following pre-requisites preferably are met. Bash may be installed as /bin/bash; Oracle may be installed correctly and sqlplus and sqlldr may be in the PATH; ORACLE_HOME may be properly set. Also, prior to executing this script, all database* properties found in Salmon.prop (in $SALMON_HOME/conf) are preferably edited so that the user name, password, and instance are set properly to point to the Oracle instance in which the tables and other database objects are created.

[0168] The program loader.sh is preferably run in $SALMON_HOME/bin/loader.sh, and the name of the file for loading may be provided. Output is parsed and intermediate files may be stored in $SALMON_HOME/output/*.

[0169] The program loader.sh in $SALMON_HOME/bin/loader.sh preferably is run and a name of the file to be loaded is preferably named. Output is then parsed and intermediate files may be stored in $SALMON_HOME/output/*. In addition, Log files are created in $SALMON_HOME/log/*. Below is a table summarizing example options for the loader.sh.

[0170] Loader.sh <Argument><file name>

[0171] Loader.sh Script

Options	Function
v	Turns on verbose logging.
p	Parses all specified invoice files but does not load the processed
	data into the database.
P	Sets a property.
I	Load the invoices from data that has been parsed. Invoice IDs
	are used to specify invoices.
s	Updates the secondary tables by running SINS. Invoice IDS
	are used to specify invoices.
j	Performs Java processing.
q	Queues all specified invoice files.
d	The script runs without exiting. It periodically looks for any
	queued invoices and processes them.
U	Unloads the specified invoice Ids.
b	Flags the invoice file as inbound (i) for cost or outbound (o)
	for revenue.
f	Forces the system to process an invoice file. With this
	argument, one can force the system to load the same
	BDT file twice. By default, the system does not allow
	loading the same file.
u	Processes usage files. Where appropriate use usage IDs or
	usage files.

[0172] A typical example of an output from a successful run of the loader.sh is set out below:

PREPARING ALL REQUESTED FILES
Calculating MD5 Sum... done
Source file.............. /home/data2/E/9104/v36/vbtcn02002.nva
File ID.................. 20
File MD5 Checksum........ 7203b41d6181246b9ddeee8b97bcc9a8
File identified as....... cabs (ver 36) from 9104 (E)
Preparation completed.... Successful
PROCESSING ALL REQUESTED FILES
Source file.............. /home/data2/E/9104/v36/vbtcn02002.nva
File ID.................. 20
File MD5 Checksum........ 7203b41d6181246b9ddeee8b97bcc9a8
File identified as....... cabs (ver 36) from 9104 (E)
Running parser...........
.....
total records: 5549
total time: 10.0 seconds
average rate: 557 records/sec
Parsing finished........ No Errors detected
Loading all tables.......
.............................
Loading completed........ No Errors detected
SINS Processing..........
SINS.....................Committed
Finished processing...... /home/data2/E/9104/v36/vbtcn02002.nva (id 20)
Total Time Taken.........

[0173] The Loader.sh script may use identifyFile.sh to determine the format, version, and carrier of the invoice file. It may set the appropriate properties so later reading properties may yield a correct result.

[0174] Parsing the invoice. After loading the configuration file, the parser program may be run on the file. The parser may reads the specification files from the specified directory and may map files from the map files directory to determine how to read the invoice. In addition, the parser may determine what directory to output the file in.

[0175] Loading the Invoice. The loader program may then use the bulk loader program and associated control files to load data into the database. After the data is loaded, various SQL queries may be run if the -s option is given.

[0176] SINS Implementer Generic Hooks (SIGNs) may be used to allow an implementer to add behavior to the SINS processing without changing existing SINS. The file may contain a plurality of macros (prefixed with mSIGH_) that are empty. These macros may be called in various stages of the SINS processing and allow a user to add behavior to the process without changing existing SINS. To use a SIGN, the corresponding SIGNs.sql file may be copied to a new director based on the include path (described above), and/or when the SIGNs are activated. The desired macros may then be populated as needed.

[0177] For even greater flexibility, the SIGNs.sql file may be copied to a top area and then add includes to various files that may be created. The files may then be created in different levels to allow more general or more specific overrides. If the necessary SIGNs do not exist, a new hook may be added with the same method that existing hooks use.

[0178] SIGHs may also be used to add columns of new data to existing tables, to populate completely new tables (add a whole new insert SINS), or to override existing macros to change behavior drastically.

[0179] Unsupported invoices (i.e., invoices which do not have a corresponding configuration file), will yield an error when the system attempts to load and parse them. However, unsupported invoices may be parsed by an existing configurations for the parser by using a generic grammar.

[0180] Many of the grammars may be specific to a respective billing company. The invoice type may be identified by the OCN of the billing company, but many of the billing companies may have multiple OCNs. Therefore, a company-specific grammar may be used for a specific unsupported invoice. For example, Verizon Northeast has OCNs 9102 and 9104. If there is a grammar for the CABS version 35, 9102, then most likely that grammar also will work for CABS version 35 9104 because the bills are produced by the same company.

[0181] Parsing an invoice. The parser may be invoked from a command line or shell script using loader.sh (Loader.sh <Argument><file name>). The options for the parser may be drafted in a standard <opt><opt arg>manner. The parser may include a set of options that may be required and a set that may be optional as illustrated below.

[0182] Example REQUIRED OPTIONS

b <bdt id>	BDT ID to assign to this file in the output.
f <bdt file>	BDT invoice file to be parsed.
G <grammar dir>	Directory where the grammar.y file is located.
L <lexer dir>	Directory where the lexer.def is located.
l <lookup dir>	Directory where the lookup files are located.
m <mappings dir>	Directory where the .map files are located.

[0183] -o<directory path> Directory to which output files are to be written. User invoking the parser must have write permission for the directory.

[0184] -s<reader spec dir> Directory where the .spec files are located for this particular version of BDT.

[0185] -v<version number> Version number of the BDT being parsed.

[0186] Example OPTIONAL OPTIONS

[0187] -c<checksum> Use the checksum specified. Do not calculate the checksum of the input file. Cannot be used with -e.

[0188] -d Generate debugging sequences in the output files.

[0189] -e Simply calculate the checksum of the input file and exit. No other options except -f are required when using -e. Cannot be used with -c.

[0190] -E<exceptionsfile> Specify the file to be used for logging exceptions.

[0191] -p<outputformat> Specifies the format of the output files. Also changes the suffixes of the output files. The current allowable values are:

	Loader	The default delimited format. (.dat)
	CSV	Comma separated. (.csv)
	HTML	HTML tables. (.html)

[0192] -n Verify that the BDT is valid grammatically, don't produce output files.

[0193] -r Enable unused element parsing. Normally the parser will not parse record fields that will never be outputted. This enables parsing these elements.

[0194] -t Verify that the BDT can be read. Do not check it grammatically. Do not produce output.

[0195] -V<log level>Specify the log level and log mask to use for logging. The format of <log level>is the following:

[0196] <INFOLOWMEDIUMHIGHFATAL>:<hex number>

[0197] A .spec file may specify the format of an individual BDT record type. Preferably, there may exist one spec file for every record type, and a set of spec files for every type and version of the BDT that is to be parsed. The file may include the record type id, the record type name, and a list of the name, offset, length and format of all the fields in a record of that type. The parser may use these files for reading and interpreting the raw BDT record data. The format for a spec file is as follows:

	.spec file BNF:
	Spec_File : Record_Name
	Record_Type_ID
	Field_Spec*
	Record_Name : <string>
	Record_Type_ID : <8-digit integer>
	Field_Spec : Field_Name
	Start_Byte
	End_Byte
	Field_Type
	Field_Name : name:<string>
	Start_Byte : start-byte:<integer>
	End_Byte : end-byte:<integer>
	Field_Type : type:<format>

[0198] .Spec file field descriptions:

Field
Name	Description
Record	An 8-digit integer identifying the record type. In the CABS case
Type	it is made up of the 6-digit CABS record id and the 2-digit
ID	CABS record id suffix.
Record	A string identifying the record terminated by a newline.
Name	Required but not currently used. In the CABS case it
	is the name from the CABS specification which is
	usually non-descriptive at best and just plain
	confusing at worst.
Field	The field specs tell where in the physical BDT record line the
Spec	field is (Start_Byte, End_Byte), what the field's name
	is (Field_Name), and what type of data it contains
	(Field_Type).
Field	A name for the field. The name is taken to be every character
Name	(including white space) after the colon until the new line.
Start	A 1-based index (i.e. the first byte in a record is byte 1) into the
Byte	record where the particular field starts.
End	A 1-based index into the record where the particular field ends
Byte	(the field data includes the byte at the End_Byte position).
Field	Specifies what type of data the field includes. The format string
Type	is comprised of a combination of the following specifiers.

[0199] Valid specifiers may be:

	X(<length>) Sequence of characters from the printable ASCII set
	of specified length.
	9(<length>) Sequence of numbers of specified length.
	SSign /.
	VDecimal point.
	Examples:
	X(100)String of length 100.

[0200] S9(9)V9(2) Signed floating point number with 9 numbers before the decimal and two after. 9(4) Four digit unsigned integer.

[0201] Format strings for date and times:

[0202] X(6) (CCYYMM) Date with the century, year, and month.

[0203] X(8) (CCYYMMDD) Date with the century, year, month, and day.

[0204] 9(4) (HHMM) Time with hour and minute.

[0205] Accordingly, below is an example partial spec file for the 10-60-90-00 CABS record type:

[0206] DETAIL OF CIRCUIT LISTING TOTAL 10609000

[0207] name:RECORD IDENTIFICATION

[0208] start-byte: 1

[0209] end-byte:6

[0210] type:X(6)

[0211] name:RECORD IDENTIFICATION SUFFIX

[0212] start-byte:7

[0213] end-byte:8

[0214] type:9(2)

[0215] name:SUFFIX RECORD IND

[0216] start-byte:9

[0217] end-byte:9

[0218] type:9(1)

[0219] name:ACCESS CUSTOMER NAME ABBREVIATION

[0220] start-byte: 10

[0221] end-byte: 14

[0222] type:X(5)

[0223] name:BILL DATE

[0224] start-byte: 15

[0225] end-byte:22

[0226] type:X(8) (CCYYMMDD)

[0227] name:BILLING NUMBER

[0228] start-byte:23

[0229] end-byte:32

[0230] type:X(10)

[0231] name:CUSTOMER CODE

[0232] start-byte:33

[0233] end-byte:35

[0234] type:X(3)

[0235] name:PACK SEQUENCE NUMBER

[0236] start-byte:36

[0237] end-byte:39

[0238] type:9(4)

[0239] name:RECORD SEQUENCE NUMBER

[0240] start-byte:40

[0241] end-byte:48

[0242] type:9(9)

[0243] name:RESERVED FOR EC USE

[0244] start-byte:49

[0245] end-byte:61

[0246] type:X(13)

[0247] name:TOTAL INTERSTATE CIRCUIT CHARGES

[0248] start-byte:62

[0249] end-byte:72

[0250] type:S9(9)V9(2)

[0251] Map Files. A map may specify which record fields from which BDT records are used to populate a particular database table. There may exist one map file for every table to be populated and there may exist a set of .map files for every type and version of BDT to be parsed. These files may not instruct the parser that a table should be populated with all occurrences of a particular BDT record, the files may merely specify what table members may be populated from which record fields (if the table is to be populated by a record).

[0252] The file name of the map file may specify which table for which it is a map. Therefore, the mappings for the C_UNEOCCItem table is in the C_UNEOCCItem.map file (for example).

[0253] .map File BNF:

[0254] Map_File:Mapping*

[0255] Mapping:<table member name>:Source

[0256] Source:Record_Field Foreign_Key Primary_Key

[0257] Record_Field:<record type id>:<recordfield name>

[0258] Foreign_Key:<foreign_table name>.<foreign_table field>

[0259] Primary_Key:Generated

[0260] Map file definitions:

[0261] Mapping: Specifies the table member name and from what source it should be populated.

[0262] Source: Source from which to populate a table member can either be a field from a BDT record, a foreign key to another table, or an auto generated primary key.

[0263] Record Field: Specifies which record type and which field from it to get data from. The <record type id> must be one of the 8-digit IDs from the spec files. The <record field name> must exactly match (including white space) a field name from the spec file of the record type.

[0264] Foreign Key: Specifies to populate the table member with the data in member <foreign_table_field> from foreign table <foreign_table_name>.

[0265] Primary Key: Specifies to auto generate this data from the primary key of the primary table.

[0266] Map file example:

[0267] BDTID:BDTInfo.BDTID

[0268] PackID:C_CSRPack.PackID

[0269] C_UNECircuitLocationContID: Generated

[0270] C_UNECircuitLocationID: C_UNECircuitLocation.C_UNECircuitLocationID

[0271] RecordSequenceNumber:40151500:RECORD SEQUENCE NUMBER

[0272] UsocOrFid:40151500:USOC OR FID

[0273] FidData:40151500:FID DATA

[0274] FidDataContinuationlnd:40151500:FID DATA CONTINUATION IND

[0275] Lexer files. The lexer.def file may be input to the parser and may define the mapping between records and grammar tokens. Because some records may be disambiguated beyond record type id, the lexer.def file may include a script that analyzes the contents of a record and determines the appropriate token number to return. The script may be run once for each record that is parsed.

[0276] Like the grammar, the lexer.def file may be generated for each BDT to be parsed depending on the format of the BDT, version, and OCN. The file may be generated by preprocessing the following files from the conf/lexer directory: lexerMain, lexer.def, casedefs.def, and cases.def. The loader.sh script may be responsible for running the preprocessor at runtime, but one may also run the genlexer.sh script to generate the grammar for debugging purposes.

[0277] On each run, the preprocessor may be given a set of include paths that depend on the type, version, and originating OCN of the BDT the grammar is for. The include paths in the order that they, for example, are searched are as follows:

[0278] <conf dir>/lexer/<bdt format>/<org. OCN>/<file version>

[0279] <conf dir>/lexer/<bdt format>/<org. OCN>

[0280] <conf dir>/lexer/<bdt format>/<file version>

[0281] <conf dir>/lexer/<bdt format>

[0282] <conf dir>/lexer/include

[0283] <conf dir>/lexer

[0284] This may allow the grammar to be changed for a specific BDT type simply by creating a file higher up in the include path. For example the lexerMain file, which is the only input file that is fed to the preprocessor, is simply:

[0285] include lexer.def

[0286] The preprocessor then may search the include paths for the file lexer.def to include. In general, the only lexer.def files that may be found are in the <conf dir>/lexer/<bdt format>directory. This enables AEBS, CABS, etc. to be supported using the same preprocessor. If necessary, the whole grammar for a particular format/version/OCN may be overrided by, for example, simply placing a lexer.def file into <conf dir>/grammar/<bdt format>/<org. OCN>/<file version>.

[0287] Grammar files. The grammar files may define a context-free grammar for interpreting BDT files. The BDT (CABS, SECAB and AEBS) files have a grammatical structure. The records are the words, and groupings of the words are the sentences. Because the CABS and AEBS specifications state that certain record types must follow others, a grammar may be expressed English (though less complicated). The grammar files may express this grammar, and also may specify what to do when a grammatical element is complete. For example, the 10-50-* CABS records describe the taxes on a bill. To describe the taxes, there may be a series of 10-50-05-00 (Tax Detail Records) that describe the individual tax charges, and after all the 10-50-05-00s there must follow a 10-50-90-00 (Tax Total) record.

[0288] The actual grammar input into the parser may be generated for every BDT file by a preprocessor step. The output of this step specifies a grammar in a YACC compatible format. The loader.sh script may be responsible for running the preprocessor at runtime, but one also can run the gengrammar.sh script to generate the grammar for debugging purposes. As mentioned previously, the grammar output may be BISON- and YACC-compatible. Therefore, either one may be used for debugging the grammars.

[0289] CDR Adapters and Processors. CDR Adapters and Processors may be used to convert and process raw Call Record Data (CDR). The conversion process may enable the Revenue Assurance system according to the present invention to examine line-level usage and to compare measured, billed, or reported usage against each other.

[0290] Measured usage may represent data from the equipment of customers, such as switches. Billed or invoiced usage may refer to usage as reported from the client billing system. A third data source may be reported usage, which may be reported by third-party carriers, such as the Interexchange carriers.

[0291] Line-level usage validation is functionality that may categorize, summarize, and/or compares usage from two sources for a billing period by individual telephone numbers. The comparison produces discrepancies, indexed by Working Telephone Number (WTN), billing period, and other key fields, which are reported in Peg Count and MOU differences.

[0292] Analyzed data may be client-specific. The system according to the present invention may handle, at a minimum the following data sources: measured usage, billed or invoiced usage, and reported usage. Measured usage, which comes from CDRs from the client switch or switches, may include toll, calling card, local, and directory assistance information (other types of information may also be included as necessary to meet a client's needs). Data formats may include, for example, AMA, EBAF, or SS7 CDR (not SS7 messages). Additional data formats may be added as necessary to support client needs.

[0293] Invoiced usage, which may be extracted from the client billing systems, may be available in numerous proprietary formats.

[0294] Reported usage may be from a third-party carrier such as an IXC, and may be generally in the form of a Daily Usage File (DUF), which may come in various data formats.

[0295] Line-level usage may be analyzed by systemically comparing usage data from one source against usage data from another source. Accordingly, by comparing the usage information from the two sources, the system may report discrepancies.

[0296] CDR Adapter. A CDR Adapter implements specific methods of CdrReader and may be responsible for taking in raw data and creating CallDetail objects that may be passed along to the CDR Processors. Supporting data and options required by a given CDR Processor are provided either via the CdrGlobals objects or from the Properties object. Each adapter may include a unique identifier associated with it, for example, AMA.

[0297] A CDR Processor may be responsible for taking in the CallDetail objects created by adapters and processing them in a manner appropriate for the processor. Supporting data and options needed by a given CDR Processor may be provided in one of the following ways: an array of string options (specified in a file with the given processor), via the CdrGlobals objects, an OutputFilenameSpec object (which controls the name of the output file for the processor), and from a second array of string options (specified along with the current input file which is generally used by the OutputFilenameSpec object), for example. Each Processor may have a unique identifier associated with it (for example, CdrProc_WtnOrigBillStats).

[0298] The output of a CDR Processor may include the WTN and pairs of Peg Count/MOU columns. For example:

[0299] WTN:stringIntralataPC:intIntralataMOU:numberInterLataPC:intInterLataMOU:numberInterStatePC:intInter StateMOU:numberInternatio

[0300] nalPC:intInternationalMOU:numberTollFreePC:intTollFreeMOU:numberOtherPC:intOtherMOU:numberTo llCallPC:intTollCallMOU:number

[0301] 50865386402880.70000000.00000000.00000000.00000000.00000000.00000000.000000

[0302] 508879610016.90000000.00000000.00000000.0000001010.0000001 1 10.0000001010.000000

[0303] 6172271286119.00000000.00000000.00000000.00000000.00000000.00000000.000000

[0304] 6172328814216.70000000.0000001010.00000000.00000000.00000000.00000000.000000

[0305] 617236448010.40000000.00000000.00000000.00000000.00000000.00000000.000000

[0306] Using scripts to process and store processed data. The GencricLineLevel program may process a CDR. This program may take the following arguments:

[0307] GenericLineLevel Program

	Argument	Description
	p	Processor list file name (required)
	r	CDR reader name (required)
	i	Input file list file name (required if -f not
		used)
	n	Base ID
	c	Properties file
	s	File Separator
	f	Input file information (required if -i not
		used)
	P	Property Information
	V	Enables verbose logging

[0308] The -p and -r arguments may always used, and either -i or -f may be provided. The file separator defaults to the if not specified and the Base ID defaults to 1. The -V argument enables verbose logging. The -f argument may be equivalent to providing one line from the input file list on the command line. The Properties file defaults to Properties.txt.

[0309] The CDR reader name may be a unique identifier associated with a CDR Adapter. Each line in the Processor list file may include the following: CDR Processor unique identifier, followed by a string used to create an OutputFilenameSpec object, followed by any other processor-specific information (such as location of reference data). The information may be separated by the separator specified via the -s option. An example of a line in a Processor list file is as follows:

[0310] CdrProc_WtnOrigAlgxStats./results.test/2_WtnOrigAlgx_%_.txt

[0311] The meaning of the string used to create the OutputFilenameSpec object may be as follows: % is replaced by the date of the call as provided by the CallDetail object; is replaced by the File ID number, which is calculated by adding the offset of the input file in the input file list and adding the Base ID to it; 1, etc., is replaced by the equivalent field provided in the input file list; and any other text is passed through untouched.

[0312] Unlike the lines in the Processor list file, the lines in the input file list file may include the same number of options. Accordingly, each line may include the following: input file name, followed by the date associated with the file (when it was created or received), followed by CDR format, and finally any other options. The information also may be separated by the separator specified via the -s option. An example of options in the input file list file:

[0313] /u04/CDR/ama/bos20020401165107-165246.ama20020401AMA

[0314] /u04/CDR/ama/bos20020401165247-165358.ama20020401AMA

[0315] After combining the information in the two examples above, examples of output files may be as follows:

[0316] ./results.test/AMA_WtnOrigAlgx200203311.txt or

[0317] ./results.test/AMA_WtnOrigAlgx200204011.txt.

[0318] Generally, most of the work involved in scripting the processing may include two items: tracking which CDR or DUFs have been processed and constructing appropriate input file list files. The remainder of the work has been done for the customer in the LineLevel Perl module.

[0319] A typical Perl script to handle this process is illustrated below (the script is generally more complex and may have a completely different structure).

/usr/local/bin/perl
use Properties($0, ../../../);
use lib $Properties::SalmonHome/lib/perl;
use LineLevel;
sub ExecuteLineLevel
my($input_file) shift;
my($id) shift;
{grave over ()}$LineLevel::LINE_LEVEL -p prop.txt -r Ama -i $input_file -n $id;
sub MakeInput
my($input_file) shift;
my($dir) /u04/CDR/ama/;
opendir(DIR,$dir);
my(temp) readdir(DIR);
closedir(DIR);
my($file);
foreach $file (temp)
next if ($file /.ama/);
push(files,$dir$file);
my($date);
files sort(files);
my($count) 0;
my(%map);
$dir qq $dir;
open(FILE,>$input_file) die Can't open $input_file
for writing: $n;
foreach $file (files)
$file /$dira-zA-z_(d)_/ && ($date $1);
$count;
$map $count $date;
print FILE $file$dateAMAn;
last if ($count > 4);
close(FILE);
return %map;
sub CombineFiles
my($map) shift;
my($dir) ./results.test;
opendir(DIR,$dir);
my(temp) readdir(DIR);
closedir(DIR);
my($file);
my(files);
foreach $file (temp)
next if ($file /{circumflex over ()}AMA.*.txt$/);
next if ($file /_dddddddd.txt/);
push(files,$dir/$file);
my(final) LineLevel::CreateIntermediateFile($map,files);
my(data);
foreach $file (final)
data LineLevel::ConvertFileNameToStorageData($file);
LineLevel::StoreFile($data3,$data2,$data0,$data1,
$file, ./library.text);
$map MakeInput(input.txt);
ExecuteLineLevel(input.txt, 1);
CombineFiles($map);

[0320] As the example demonstrates, the first few lines may be common to all LineLevel-related scripts, although the location of the Perl executable may vary. The MakeInput portion may need to move files around to signify pre- and post-processed states or it may need to keep an index of files that have already been processed.

[0321] As will be appreciated by one of ordinary skill in the art, the ExecuteLineLevel portion of this script, may be fairly standard. The $LineLevel::LINE_LEVEL may be executed with appropriate options.

[0322] The final step (CombineFiles), may vary similarly to MakeInput. The LineLevel::methods may generally be called, but the filtering of the files to be processed may be non-standard. LineLevel::CreateIntermediateFile will take a hash of input file dates and maps the order as passed to GenericLineLevel to a date, as well as an array of processed file names in the form of AMA_WtnRemAlgx200205011.txt.

[0323] This method converts the files to the src_type_calldate_filedate.txt format and merge all relevant files that would map to the same output (for example, AMA_WtnRemAlgx2002050120020501.txt). This may be accomplished by looking up the 1 in the hash of input file dates. This assumes that the source file may be from src_type_calldate_filedate_.txt (for example AMA_WtnRemAlgx200205011.txt).

[0324] At the end of process, the files may be stored in the library which may be associated with a directory (in the above example, ./library.text). Each file may be stored and retrieved by the following key fields: File Date, Call Date, Source, and Comparison Type. During retrieval, the date may be specified as follows: a date string is of the form dateBlock1, dateBlock2, and the like. A date block may be either a date or a date range. A date range may be of the form startDate-endDate where the year portions are the same. A date may be of the form 20020506 (YYYYMMDD).

[0325] The following discusses scripting the retrieval and the merging of two homogenous sets of data (from different sources) from the library, comparing these sets of data, and generating discrepancies. This part of the process may be very similar to scripting the processing of CDRs because both may be done in Perl, for example, and both may use some of the same functions.

[0326] Accordingly, a typical Perl script to handle this process may look as follows (although it will be more complex and could have a completely different structure):

/usr/local/bin/perl
use Properties($0, ../../../);
use lib $Properties::SalmonHome/lib/perl;
use LineLevel;
sub MakeComparisonSet
my($calldate) shift;
my($filedate) shift;
my($output) shift;
my(files)
LineLevel::GetFiles($filedate,$calldate,AMA, WtnOrigAlgx,
./library.text);
my($filter) sub return LineLevel::BlankFilter(_); ;
LineLevel::MergeFiles(1,$output,$output_temp, $filter, files);
MakeComparisonSet(20020401,20020401,Ama1.txt);
MakeComparisonSet(20020401,20020331,20020401,20020331,
Ama2.txt);
$excludes %empty;
$data1 LineLevel::parseFile(Ama1.txt,$excludes);
$data2 LineLevel::parseFile(Ama2.txt,$excludes);
my($ofile) Ama_out;
my($ofile1) $ofile_1.dat;
my($ofile2) $ofile_2.dat;
my($oboth) $ofile_both.dat;
for $i (1..7)
push(cols, $i);
LineLevel::makeDiffs($ofile1,$ofile2,$oboth,773,0.0,20020401,
BOS,$data1,
$data2,cols );
cat $ofile1 $ofile2 $oboth > UI_USAGEDISCREPANCY.dat;
rm $ofile1 $ofile2 $oboth;
LineLevel::LoadDiscrepancies(./);

[0327] In this process, the first part of the script may remain the same, in that the routine retrieves the relevant files based on the two date keys, source type and data type. Those files may then be merged into one file, which may then load into memory via LineLevel::parseFile, and three different discrepancy files may then be created via LineLevel::makeDiffs. The three files may be WTN entry in Source 1, but not in Source 2, in Source 2, but not in Source 1, and in Source 1 and Source 2, but may be with different values for PegCount and MOU. Finally, the three files may be combined into one and loaded into the database via LineLevel::LoadDiscrepancies.

[0328] Generally, two different sources may be compared; for example, AMA and SS7, instead of two sets from the same source as previously mentioned. In addition, when running the makeDiffs, the 773, 0.0, 20020401 and BOS arguments may be specified more dynamically as determined by the appropriate business logic.

[0329] The retrieval (through the MakeComparisonSet) and the discrepancy generation portion may be separated into separate scripts. Additionally, a non-blank filter may often replace the blank filter portion.

[0330] Generating Discrepancy Information. The Discrepancy Type may identify the two data sources being compared and the type of comparison being done. The first 8 bits may be used for the two data sources and the second 8 bits as the type. The Bucket Type may be relative to the Discrepancy type and may be generally just an enumeration of the categories within that Discrepancy type (for example, IntraLATA, Toll Free, etc.). A discrepancy consists of the following information (for example): WTN, Bill Period Date, Zone, Discrepancy Type, Bucket Type, PegCount1, MOU1, PegCount2, and MOU2.

[0331] For example:

[0332] 508310007820020701BOS25921341.4000001656.900000

[0333] 508310007820020701BOS2593184537.500000178519.00000

[0334] Inter-carrier traffic analysis (ICTA) processors (FIG. 4). The Revenue Assurance system according to the present invention may use ICTA Processors or banners to help compare the measured (MOU) data versus the data represented in an invoice. This may be referred to as Measured versus Invoiced. This comparison data may be used to show charge discrepancies.

[0335] The invoice data may be logically grouped. The data in the bins are sectionalized based on the direction of the traffic (inbound/outbound) and the jurisdiction (for example, local traffic) as reported in the invoices. The system uses SINS to parcel the data into the appropriate ITCA bins. For example, if the invoiced MOU represent totals for transit traffic, three bins may be used: interstate, intrastate, and local. The measured MOU for transit traffic may be binned the same exact way: interstate, intrastate, and local. The system may compare data in the corresponding bins, for example, invoiced transit interstate MOU versus measured transit interstate MOU and so on. Using comparison data, the system may create charge discrepancies. For outgoing invoices, no disputes or discrepancies may be created.

[0336] All ICTA processing may be done before the invoice is loaded into the database. The ICTA processors may use OCNs to identify the carrier using the LERG. On invoices, the Access Customer Name Abbreviation, which is not in the LERG, may identify the access customer of the invoiced usage. The administrator must provide the carrier code for the customer for outgoing invoices. This is done in the UI_BAN table.

[0337] Flexibility may be built into the system using the properties file. For example, the system may be instructed to ignore discrepancies if the financial impact is less than $ 1,000. The system may use two properties, which may be used in MOU Measured versus Invoiced functionality. These properties are in Salmon.prop. They may be set to these default values:

[0338] Discrepancy.MOUDifferenceRatio0.05

[0339] Discrepancy.EstimatedFinanciallmpact1000

[0340] A MOU-type discrepancy may be generated if the absolute value of the ratio between the MOU difference and the invoiced MOU is larger than the first property and if the Estimated Financial Impact of the MOU difference is larger than the second property.

[0341] The system also may also modify SINS to accommodate specific agreements between carriers. A SINS file may be created specifically for the carrier to reflect an uncommon agreement. For example, there could be a maximum limit on the MOU that a carrier can bill. This may be reflected when the invoice and measured data are compared. Therefore, a field called Correct Billed MOU may be created. This field reflects all the business agreements the carrier may have and it also may consider if there were any minutes billed already in previous invoices for a specific time period.

[0342] All processors may derive from the abstract class CdrProcessor:

	namespace Salmon
	class CdrProcessor
	public:
	virtual void process(const CallDetail* pCdr) 0;
	virtual void onBegin( )
	virtual void onEnd( )
	struct Stats
	int m_nCount;
	double m_fMou;
	Stats( )
	m_nCount 0;
	m_fMou 0.0;
	virtual Stats( )
	void update(const CallDetail* pCdr)
	m_nCount;
	m_fMou (pCdr->getHoldSeconds( ) / 60.0);
	void update(const Stats* pStats)
	m_nCount pStats->m_nCount;
	m_fMou pStats->m_fMou;
	;
	;
	// namespace Salmon

[0343] All CdrProcessor-derived classes may implement the process( ) method, which may be called by the ICTA engine for each Call Detail Record (CDR) in the file. The CallDetail interface may be detailed and may be found in the file CallDetail.h. Most CdrProcessor-derived classes may take a pointer to an IctaMainBins and/or an IctaTransitBins object in which running totals may be stored:

	namespace Salmon
	class IctaMainBins
	public:
	CdrProcessor::Stats* getStats(intnActualDate,
	intnPeriodDate,
	const char* szCarrier,
	const char* szState,
	intnLata,
	intnBin,
	intnLabel);
	...
	;
	class IctaTransitBins
	public:
	CdrProcessor::Stats* getStats(intnActualDate,
	intnPeriodDate,
	const char* szCarrier,
	const char* szState,
	intnLata,
	const char* szCarrier2,
	intnBin);
	...
	;
	// namespace Salmon

[0344] The dates may be passed as integers in YYYYMMDD format. The PeriodDate may be the date of the end of the billing period, which may be obtained from a BillCycleMap object (declared in BillCycle.h), which may often passed as a construction parameter to the CdrProcessor-derived class. The Bin and Label parameters are numeric identifiers for the stat type. An initial listing of these values can be found in BinList.h, but implementers may add new (non-conflicting) values as needed.

[0345] Processors are instantiated and installed in the main( ) function in main.cpp:

[0346] //instantiate main objects

[0347] IctaMainBins oMainBins;

[0348] IctaTransitBins oTransitBins;

[0349] BillCycleMap oBillCycles(BILL_CYCLE_MAP_FILE);

[0350] CdrProcEngine oEngine;

[0351] //instantiate processors

[0352] CdrDotCounter oCounter(4000, 25);

[0353] MyNewProcessor oMyNewProc(&oMainBins, &oBillCycles);

[0354] //install processors

[0355] oEngine.addProcessor(&oCounter);

[0356] oEngine.addProcessor(&oMyNewProc);

[0357] Post-Load Java processing may be performed. After an invoice or CDR file is loaded, Java classes are called to perform post-load processing. Rate and quantity validation may be performed using post load Java processing. Rate validation is performed on both recurring and usage charges. The design of rate validation allows for extension due to the expected difference in rate application, especially for recurring charges. Quantity validation is less complex than rate validation and is not directly extensible.

[0358] For recurring charges the Validator_Subtype may correspond to the charge type in the recurring charges details. When processing invoices, all charges of charge type Unit and Mileage may be validated by the class RateValidatorRecurringBasic. This same validator may be capable of handling both charge types. Another charge type of Not Applicable is a charge type found on some line items. The validator for these types of charges is RateValidatorRecurringUnassigned.

[0359] RateValidatorRecurringBasic may compare all charges against the rate book in order to create discrepancies where appropriate.

[0360] RateValidatorRecurringUnassigned may catch charge types that are not validated. It may generate discrepancies to indicate that there is no rate book entry. However, during discrepancy handling a rate book entry may be created for this entry to prevent another discrepancy from being generated.

[0361] The following explains how the class RateValidatorRecurringBasic performs validation.

[0362] An in memory rate book of the Unit and Mileage recurring entries are created from the rate book. This data may be stored in a hash map with the keys OCN/State/Jurisdiction/USOC/Charge Type.

[0363] Each line item of the invoice may be processed for recurring unit and mileage charges. Discrepancies are created as they are determined and exceed the individual charge threshold. Discrepancies are keyed differently than the rate book. In addition to the rate keys of OCN/State/Jurisdiction/USOC/Charge Type, Unit Rate/Mileage Rate/Undercharge are further keys for the discrepancy hash table.

[0364] Discrepancies may be reported for No Rate Element Found types. However, undercharges and overcharges may only be reported if the aggregate charge threshold is exceeded.

[0365] The RateValidatorRecurringBasic uses a class DbRecurringlnterface may access the invoice line items. This class is created to isolate changes if there were any changes to the UI tables.

[0366] To report discrepancies, the DbDiscrepancy class may be used. Without modification this class may be used to create discrepancies. To generate a discrepancy, the following steps are performed:

[0367] For each discrepancy call the set description method to initialize the discrepancy. The text parameter preferably includes the description of the discrepancy.

[0368] Then call SetNameValue for each name value pair of additional information desired to be set with the discrepancy UI. If there are hidden name value pairs that are being set for internal use but not for display set the hidden parameter to true. Call InsertDiscrepancy to insert the discrepancy record into the database.

[0369] Database schemes. The revenue assurance system according to the present system includes the following tables: primary, secondary, UI, reference, info, and sequence.

[0370] The loader uses inserts data into the primary tables. SINS processes use the primary table data to populate secondary tables. The UI tables hold information that supports the user interface (UI). The reference tables, which are populated at installation, provide lookup information. The info tables are used for auditing, logging information about loaded data. The sequence tables are created to ensure ID uniqueness.

[0371] After incoming data is parsed, it is loaded into primary tables. A new set of primary tables must be created for every type of incoming data. Exemplary sets of primary tables for the present invention include: CABS tables, manual entry tables, and ICTA tables, SECAB, AEBS, and EDI 811 tables.

[0372] The schema generator automatically generates CABS tables. The schema generator uses the spec files, and a table definition configuration file to generate version-specific ddl files. The table definition configuration file (cabstables.dat) is created using domain knowledge of CABS systems and CABS invoice files (BDT). The version-specific ddl files go through minor manual modifications to generate a version independent schema.

[0373] CABS tables are named using the prefix C_ for example. Often the same record type can land in multiple tables depending on the circumstances. The parser disambiguates these records depending on individual elements or position in the invoice data file.

[0374] Manual entry tables support the MS Access template for entering invoices manually. This is for invoices that are not in machine-readable format. The manual entry tables are named using the M_ prefix and have the following hierarchy:

	<M_ACCOUNT INFO>
	<M_ADJUSTMENT>
	<M_MRC>
	<M_MRCUSOC>
	<M_OCC>
	<M_OCCPHRASE>
	<M_OCCPHRASEUSOC>
	<M_TAXES>
	<M_USAGE>
	<M_USAGE_DETAIL>
	<M_USAGE_STATS>

[0375] ICTA tables are where binned CDR data ends up. For example, UI_ICTAMAINholds main Intercarrier Traffic Analysis rolled up data and UI_ICTATRANSITholds transit Inter-carrier Traffic Analysis rolled up data (two carriers, pier and other end).

[0376] Secondary tables. After data are loaded into the primary tables, additional processing is done to create data that can be accessed by the UI server without any real-time processing. This secondary data, which is generated by SINS, includes mostly rollups and summaries. There are three types of secondary tablestemporary tables, Network Element Discrepancy (NED) tables, and invoice viewer tables.

[0377] Temporary tables are created during SINS processing generally to simplify and accelerate generation of other secondary tables (NED, invoice viewer, etc.). The data in the temporary tables are deleted after a successful completion of a load.

[0378] Network Element Discrepancy Tables. The NED tables support the RBOC NED functionality, which identifies discrepancies between the network inventory and the invoices. The three NED tables are as follows:

[0379] N_RRNEDDEDUCEDINVENTORYThis table is updated by SINS when outgoing CABS bills are loaded. The system examines Monthly Recurring Charges and OCCs, updating dates of new circuits being established or disconnected.

[0380] N_RRNEDMISSINGINNICIRCUITSThis table, which is populated by the NED process, contains circuits that are being billed but are not represented in the network inventory.

[0381] N_RRNEDUNBILLEDCIRCUITSThis table, which is populated by the NED process, contains circuits that are in the network inventory but are not being billed.

[0382] The invoice viewer tables are created to support the viewing of invoices in a user interface. These tables are created by SINS processes, which select the most important data from the primary tables and summarize it. These tables combine all different types of invoice related primary tables (CABS, manual, AEBS, . . . ).

[0383] User Interface tables are support the administration of the user interface.

[0384] Cost Discrepancy Table. Invoice validators produce invoice cost discrepancies. These are functional modules that examine an invoice and generate discrepancies.

[0385] The table that stores discrepancies is as follows:

TABLE
UI_DISCREPANCY
Field	Type	Description
UI_DISCREPANCYID	NUMBER	This is unique number needed for UI
		manipulations. It is automatically
		assigned when a row is created by a
		stored procedure.
VALIDATOR_TYPE	VARCHAR2(10)	Records the type of the type of
		discrepancy. Values should be
		distinct for each validator and
		permanently associated with that type
		of validator.
VALIDATOR_SUBTYPE	VARCHAR2(50)	Records the sub-type of the
		discrepancy. This is context specific
		to a validator. For example, a
		recurring charge rate validator might
		indicate whether this is a mileage or
		unit rate discrepancy.
BILLID	VARCHAR2(50)	This is the invoice (bill) identifier
		associated with a value in
		UI_BILLSUMMARY.BILLID.
DESCRIPTION	VARCHAR2(1024)	Structured text that describes the
		discrepancy or carries hidden
		information about the discrepancy
		that is needed for further processing.
		See discussion below on how this is
		formatted.
STATUS	VARCHAR2(16)	Records the status of the discrepancy
		according to how the user (ICA) has
		decided to handle the discrepancy.
		The initial value is Open - other
		values include Dismissed, Disputed,
		Updated, etc.

[0386] The description field carries multiple sub-texts. A vertical bar () with optional white space around is used to delimitate each sub-text. Each sub-text is preferably one of three types:

[0387] Plain texta sub-text that is to be simply displayed to the user or copied directly into a dispute letter. These are identified by the first character being *.

[0388] Visible Name/Value Paira sub-text consisting of a name and a value, separated by . These will be shown to the user as such. Further, certain well-known pieces of information may be located and copied into programmed fields in a dispute or fed back into reference books. These are identified by the first character, which is .

[0389] Hidden Name/Value Paira sub-text consisting of a name and a value, separated by . These are identified by the first character being . Typically these pairs carry fielded information, which may be necessary in processing the dispute in special waysfor example, to update a rate book entry with the discovered information.

[0390] A number of other tables handle invoices and disputes, for example:

[0391] Table UI_BillWorkflow. This table keeps track of all the invoices, including their current states and the associated owners.

[0392] Table UI_BillWorkflowHistory. This table maintains the history of the invoice, with data on the current state and past states from receipt through closure. Data includes where the invoice went, who changed the state, etc. Each time the state of the invoice changes a new record is added.

[0393] Table UI_Dispute. This table contains all of the disputes as well as information that includes who owns them, their states, any related notes, and the discrepancies from which the disputes were created.

[0394] Table UI_DisputeHistory. This table has the history of the dispute, including where it went and the name of the person who changed the state. Each time the state of the dispute changes a new record is added.

[0395] Table UI_BillRouting. This table maps the BAN to the user ID with the BAN indicating who is to be sent the bill. The routing information also indicates when a bill is first processed.

[0396] Table UI_DisputeRouting. This table maps the BAN to the user ID with the BAN indicating who is to receive and handle the dispute. This table, which has the User/BAN pairing, states when a dispute was created.

[0397] Table UI_ManagerInfo. This table has information about a user, including his employee level and the name of his supervisor. The employee level is used to identify how much (specified in the UI_Threshold table) the user can approve for payment in dollar terms.

[0398] Table UI_Threshold. This table identifies a user, with a specified level (specified in the UI_ManagerInfo table), and the threshold that is used to determine the monetary amount he can approve for payment based on the level. A threshold also is recorded to indicate the dollar amount by which the user must receive approval from his manager before granting payment.

[0399] Reference tables provide lookup information. Exemplary reference tables are as follows:

[0400] R_LERG_NPANXXcontains NPA/NXX data extracted from LERG.

[0401] R_LERG_OCN_CATEGORYOCN category table needed to populate the R_LERG_OCN table.

[0402] R_LERG_OCNcontains OCN to Company Name, Virtual OCN, and Virtual Company Name mapping. Virtual OCN and Virtual Company Name are used to map subdivisions to corporate entities (Verizon New England is a company name, Verizon is its virtual company name).

[0403] R_PhraseCodeLookupcontains OCC Phrase Codes and the associated description.

[0404] UIBINSUBLOOKUPcontains labels for ICTA bins.

[0405] DB_VERSIONcontains the database build-date (for versioning purposes).

[0406] The info tables provide auditing information for invoices and traffic summaries. They provide status information, format, checksum, and other auditing information. For example, Invoiceinfo table for invoices and CDRFileInfo for traffic files are generally used.

[0407] A sequence table used to create compatibility between platforms when generating or incrementing the ID (it cannot be atomic with insertion of data).

[0408] There are three tables that are used for rate validation. SYS_RATEBOOK, a repository for rate book entries, SYS_RATEVALIDATOR describes the rate validator that is associated with a rate book entry, and SYS_RATEVALIDATORPARAMETER describes the generic parameters used in SYS_RATEBOOK. The SYS_QUANTITYBOOK table maintains the expected unit quantities of a USOC for a BAN. This table is used by quantity validation to determine discrepancies. Entries to this table can be added manually, by CSV import, or by discovery during discrepancy management.

[0409] UI Components

[0410] User Interface Components. User Interface Components include database objects, OCN lookups, renderer, html encoder, servlets, charts, parameters object, redirector, user object and dynamic menus are designed to be used with the Java Presentation Builder (JPB) tool.

[0411] The database object provides a simple wrapper around the standard JDB component database calls. In addition, the database object provides database connection pooling. The db.properties file contains which database to connect to. The database object looks up a particular key in the db.properties file.

[0412] The ICTA Binner class is derived from the Database class. It provides the same functionality as the Database class except that the ICTA Binner caches the results. This allows the ICTA Binner to perform many functions without constantly querying the database. Such functions may include, for example: cache of the sum of every column for fast and easy access; calculation the percentage of an item out of the sum of the column; calculate the product of two columns; calculate the ratio of two columns; and the ability to execute two SQL queries and combine the results into one result set

[0413] A bin is a logical grouping of data that contains SubID (which translates to text; e.g., carrier name), Peg Count, and MOU (Minutes Of Use). For example in Originating Traffic, Outbound Traffic Type is a bin that contains five sub-ids: IntraLATA, InterLATA Intrastate, InterLATA Interstate, Toll Free, Unknown. Bins are very generic and multiple bins can be store in a single database table. All of the ICTA reports use one or more bins per page.

[0414] To build ICTA reports, the appropriate bins are assembed. Each bin only differs by an id, and each report will contain one or more of the following objects:

[0415] ICTAInnerLoopDisplays a specific bin given a bin id (BinGroupID). A similar object is ICTAInnerLoopAndChart which adds a chart to the report.

[0416] ICTAGroupIt is the parent of the inner loop that allows multiple inner loops as children. It also contains the selection criteria, display criteria, and the outer loop.

[0417] ICTAOuterLoopLoops over multiple InnerLoops by Carrier, State, or LATA depending on the display criteria.

[0418] ICTAInnerLoopTransitSame as inner loop except that it access the ICTA transit table.

[0419] OCN Lookup is a singleton class that is used to retrieve the virtual company name given an OCN. This class provides a simple static get method for this purpose. This class is used in ICTAjpb. This class is loaded on first use and is stored in the servlets application space (ServletContext). This means that the object is cached in memory and all users will refer to the cached copy. A virtual company name is a name that is extracted from the actual company name. An example of a virtual company name is Verizon. An example of an actual company name is Verizon New Jersey.

[0420] The renderer is a class that knows how to render certain types of objects like Strings, Dates, and Numbers (int, float, etc). The renderer is called from JPB. For example, see the Format property of the DatabaseField object in standardlibraryjpb.

[0421] The render method takes in a JAVA object, such as String, Date, int, float, double and converts it to a formatted String depending on the given output type. Such output types may include currencyadds a dollar sign ($) and rounds to 2 decimal places (e.g., $8,112.64) and datetimedisplays month name, day year (e.g., example: Aug. 4, 2001)

[0422] Also, by default the Renderer class assumes output will be for a HTML page. Accordingly, this means that all string output will be HTML encoded. This also works well for PDF documents.

[0423] Having described the invention with reference to the presently preferred embodiments, it should be understood that numerous changes in creating and operating such systems and methods may be introduced without departing from the true spirit of the invention as defined in the appended claims.