Download This File                   Download All Documents                        Go to Home Page

Chapter 5

   

 

The CDR’s

 

The CDR has the following fields:

 

Name            Type                  Description

Call_ID             long                Primary Key, unique number for each call

Engine               long                Identifies which box (it could be several)

Account             char(30)         Account to which the call will be charged

ANI                   char(30)         Number of the calling party**

DialTime           Datetime       Time at which the call came in.

AnswerTime      long               Miliseconds from seizure to Answer Supervision

CallLength        long                Miliseconds from AnswerSupervision to Call Drop

InboundCh        long                Inbound Channel (span x 100 + Time Slot number)

OutboundCh     long                Outbound Channels (span x 100 + Time Slot number)

DialResult        long                Result code (see Result Codes list below).

DialedStr_i       char(30)         String dialed received at call setup.

DialedStr_o      char(30)         String dialed by the switch into the outbound channel

RouteIdx          long                ID of the destination.

InGroupID        long               Client ID

OutGroupID     long               Outbound trunk group

Sell_Cost          float               How much the Account was charged.

Buy_Cost          float               How much did the call cost*

 

*Unlike Sell_Cost that, in the case of prepaid accounts, requires to be calculated in real time, Buy_Cost is only needed for billing purposes and can be calculated at a later time at a Server or reporting computer. This reduces the switch CPU usage by OmniBox without any serious sacrifice.

**ANI: the value to be stored in this field is:

    It not necessarily the ANI sent with the outbound call (protocol allowing) which is determined by the stored procedure proc_GetANItobeSent that will be called from the OmniBox just before making the call.

 

CREATE PROCEDURE proc_GetANItobeSent 
@ChIn int, @ChOut int, @RouteIdx int, @AccNum Varchar(30), @ANI Varchar(30)

AS

/*EXAMPLE 1: ANI starts with a 4 then has up to 4 random digits and ends with the slot number*/
--SELECT '4' + Cast(cast(RAND(@ChIn)*9999 As int) As Char(4))+Cast(@ChOut%100 As Char(2))

/*EXAMPLE 2: Return same value as it had*/

SELECT @ANI

 

/*EXAMPLE 3 Return '0000000000'*/

--SELECT '0000000000'

  

The result codes:

 

Result for rejected calls

 

Code Description
0 CALL_DROP_BEFORE_SETUP
1 NO_CALL_ID
2 UNKNOWN_DESTINATION
3 NO_OUTB_CH_AVAILABLE
4 TOO_FEW_DIGITS
5 TOO_MANY_DIGITS
6 BLOCKED_NUMBER
7 ROUTE_IDX_NOT_FOUND
8 NO_RESOURCE_AVAILABLE
9 INCALL_SETUP_ERROR

Results for non connected calls
 
Code Description
50 BUSY_DETECTED
51 FAST_BUSY
52 NO_ANSWER
53 NO_RINGBACK
54 OPERATOR_INTERCEPT
55 HUNG_UP_WHILE_CONNECTING
56 NO_DIALTONE
57 MAKE_CALL_ERROR
58 MAKE_CALL_EXCEPTION
59 CALL_ANALYSIS_ERROR
60 HUNGUP_WHILE_RINGING
61 TRUNCATED
62 DEST_CALL_DROP

Results for connected calls
 
Code Description
100 HAS_DETECTED
101 FAX_DETECTED
102 RECORDING_DETECTED
103 VOICE_DETECTED
105 CONNECTED_UPON_MAX_RING

 

Results for forced disconnections
 
Code Description
1100 HAS_HUNG_TRUNK_TONE
1101 FAX_HUNG_TRUNK_TONE
1102 RECORDING_HUNG_TRUNK_TONE
1103 VOICE_HUNG_TRUNK_TONE
1105 MAX_RING_HUNG_TRUNK_TONE
1150 BUSY_DETECTED_AT_OUT_BY_HTRUNK
1151 FBUSY_DETECTED_AT_OUT_BY_HTRUNK
1152 NOANS_AT_OUT_BY_HTRUNK
1153 DEAD_AIR_AT_OUT_BY_HTRUNK
1154 OP_INT_AT_OUT_BY_HTRUNK
1250 BUSY_DETECTED_AT_IN_BY_HTRUNK
1251 FBUSY_DETECTED_AT_IN_BY_HTRUNK
1252 NOANS_AT_IN_BY_HTRUNK
1253 DEAD_AIR_AT_IN_BY_HTRUNK
1254 OP_INT_AT_IN_BY_HTRUNK
1600 HAS_INCONSISTENT
1601 FAX_INCONSISTENT
1602 REC_INCONSISTENT
1603 VOI_INCONSISTENT
2000 TOO_LONG
2100 HAS_TOO_LONG
2101 FAX_TOO_LONG
2102 RECORDING_TOO_LONG
2103 VOICE_TOO_LONG
2105 MAX_RING_TOO_LONG
3000 BLOCKED_INBOUND
3100 HAS_BLOCKED_IN
3101 FAX_BLOCKED_IN
3102 RECORDING_BLOCKED_IN
3103 VOICE_BLOCKED_IN
3105 MAX_RING_BLK_INBND
4000 BLOCKED_OUTBOUND
4100 HAS_BLOCKED_OUT
4101 FAX_BLOCKED_OUT
4102 RECORDING_BLOCKED_OUT
4103 VOICE_BLOCKED_OUT
4105 MAX_RING_OUTBND

 

Example:

Call_ID

Engine

Account

ANI

DialTime

Answer Time

Call Length

Inbound Ch

Outbound Ch

Dial Result

DialedStr_i

DialedStr_o

10064

16

1000000001

 

7/14/00 2:08:07 PM

24745

0

1107

2101

53

0115913234567

3234567

10065

16

1000000001

 

7/14/00 2:08:07 PM

16253

0

808

1902

50

0115936351289

0115913234567

10066

16

1000000001

 

7/14/00 2:08:07 PM

28250

126354

1409

1601

102

18761715689

1715689

10067

16

1000000001

 

7/14/00 2:08:07 PM

28240

612896

110

2202

103

18767074567

17074567

 

RouteIdx

InGroupID

OutGroupID

Sell_Cost

Buy_Cost

160

2

1

0

0

151

6

8

0

0

170

3

2

0.88

0.53

170

5

6

5.20

3.65

 

Partial Logging

 

When this feature is enabled in the 16th record of the sys_Parameter table, the call is partially logged into table Part_Log when the outbound call is CONNECTED. Of course, information related to call length is not yet available and even the result code may be subject to modification, When the call is finished, the missing information is supplemented, the temporary information modified if necessary and the call  is logged into the Log_CDR table. If for some reason the switch crashes or power goes off. the information is kept and then recovered when the OmniBox is loaded again. In the case of real-time billing, the corresponding accounts are charged and re-activated (InUse flag set to false).


Replication Scheme

The Centralized Dual Server  setup may only be used when the link between the OmniBox and the server computer running the OmniBilling system is 100% reliable. This kind of reliability is only attainable if the OmniBilling and the OmniBox run on the same computer or on two different computer but in the same LAN. 

 

If the available link is only through a WAN (and sometimes even within a LAN) the link may not be 100%. If the link goes down, OmniBox, tries to reconnect but it may eventually use an emergency exit as a last try to reestablish the link, something very near to a crash, . For these cases, replication is the way to go. 

 

Microsoft SQL, standard editions (no desktop), provides sophisticated replication features that, in principle, can keep the necessary data sync among the OmniBilling and OmniBox, but:

 

The information is generated by data entry and is relatively infrequent if compared to that generated by traffic at the OmniBox, CDR's , account balance updates and ANI  black list entries. Microsoft Replication drawback accentuated especially for traffic generated data. The Direct Replication Scheme was developed to provide a better solution to this problem.

 

In the reverse direction, data entry at the OmniBilling database, rates, accounts or new destinations can be replicated manually or even using standard Microsoft Replication but now the Reverse Replication Scheme (see below) is also available. 

 

If using Microsoft Replication, the OmniBilling being the Publisher while the OmniBox would be the Subscriber. The Articles for the Publication should be:

 

The Direct Replication Scheme

 

When a call is finished, the data for SQL statement are saved to the table Log_PendingRepl, these may be CDR's and/or Account updates. Also when a call is received the same is done to update  the ANIBlackList table. These transactions are::

 

EXEC proc_CDR.<All parameters>

UPDATE Balances Set Last_Update = `<Current Date>`, First_Use = `<Date Value>`, Balance = '<New Balance' Where cl_id = `<Acc<`

UPDATE ANIBlackList Set AttemptCount = AttemptCount + 1 Where @ANI = '<ANI>'

 

Notice the use above of the accent instead of the apostrophe, this is because apostrophes have a functional meaning within SQL statements, this way you avoid the SQL interpreter from misinterpreting.  

 

With this scheme in place, why not use it to synchronize the trunk group information with Report Auxiliary Objects in the OmniBilling database? So, triggers for tables dia_InCh and dia_OutCh have been developed to also append INSERT INTO GroupInfo, UPDATE GroupInfo and DELETE FROM GroupInfo statements in the Log_PendingRepl table.

   

Log_PendingRepl

ID

SQL

115

{CALL proc_CDR(10134,0,`1000000000`,``,`07/13/00 11:08:59`,31185,117088,201,218,102,`01159321234567`,`01159321234567`,102,1,2,0.02,0.49)}

116

{CALL proc_CDR(10142,0,`1000000000`,``,`07/13/00 11:11:18`,16924,0,212,222,55,`18761234567`,`18761234567`,170,1,5,0.02,0.01)}

117

{CALL proc_CDR(10143,0,`1000000000`,``,`07/19/00 13:35:21`,42852,49982,201,213,102,`01159321234567#`,`01159321234567#`,102,1,2,0.03,0.23)}

118

{CALL proc_CDR(10144,0,`1000000000`,``,`07/19/00 13:56:13`,61428,81417,201,213,102,`01159321234567`,`01159321234567`,102,1,2,0.04,0.37)}

119

{CALL proc_CDR(10145,0,`1000000000`,``,`07/19/00 13:58:42`,34249,99734,201,214,102,`01159321234567`,`01159321234567`,102,1,2,0.02,0.42)}

 

An application called SrcRepl.exe runs on the same computer as the OMNIBOX. It reads from the Log_PendingRepl table and sends the SQL in a UDP packet with a sum check to the SQLReplic.exe running on the Server Computer. The ReplicSQL commits the transaction on the SQL Server and, if successful, returns an acknowledgement UDP packet (ACK) to SrcRepl.exe. Only after receiving the ACK is that it will delete the record from the Log_PendingRepl table. SrcRepl will re-send the ACK packet to the OmniMonitor and it will blink the Replic indicator green.

 

If for any reason SQLReplic can’t perform the transaction, then it sends a not acknowledge packet (NAK) to SrcRepl. When a NAK is received by SrcRepl, it will wait 5 seconds and re-send the same SQL but will send a NAK instead to the OmniMonitor that, this time, will blink the indicator in red. The same SQL will be re-resent until the transaction is successful. After 20 replication failures in a row, ReplSrc will send and alarm to the Watchdog (Alarm 12).

 

The SQLReplic window shows the last transaction performed and the result message below. When there is a replication failure, you can check on this window to figure out what is going wrong

 

 

Possible reasons for transactions to fail:

·         Different record with same Call_ID (if it were the same, ACK will be sent back)

·         No disc space for the SQL Server

·         No space for Transaction Log (Dump transaction in TempDB)

·         The table locked for some query being modifying records.

·         Sum check fails.

 

SQLReplic makes an ODBC connection to the billing database, the default DNS is ReplSrc, but a different one may be specified in the S/ command line switch. This connection may require a user name and password, maybe a different port number than the default 1099 or if the computer has several network adapters you may need to specify which IP is it that you need to work with. You specify these parameters using the SQLReplic command line switches. Syntax below:

 

SQLReplic.exe N/[port Number]  U/[User name] P/[Password] I/{Ip address] S/[ODBC Source name]

 

There are also entries to the OmniBox Database that are needed for reports at the OmniBilling server, these are trunk names, its reference ID's and data like LegACost, provider ID's and default accounts. Though for manual entries the Microsoft Replication is good enough, since this scheme is already set up we might as well use it. Triggers procedures for the dia_InCh and dia_OutCh tables are provided that generate records in Log_PendingRepl that will do the necessary updates at the OmniBilling database.  

 

The ReplSrc.exe Setup

 

The ReplSrc parameters are not read from the command line as the SQLReplic, but from the sys_parameter table. ReplSrc parameters are the ones belonging to set 100, 

 
Set_ID ParamID ParamNumValue ParamStringValue Description
100 1   10.10.10.122 Local IP address for replication
100 4 1128   WD UDP port number *
100 6 1125 0 UDPRelay UDP port number**
100 7 0   Delete without Verifying check sum ***
100 8   100.100.100.125 UDPRelay IP**
100 14 1099 100.100.100.125 SQLReplic port and IP

 

* Legacy from the old Watchdog, unless you are using the old Watchdog app, this parameter is irrelevant.

**This is needed for the acknowledge message sent to the OmniMonitor

***For trouble shooting by #include only, must be always set to 0. 

 

ReplSrc uses UDP port 1998. 

The Reverse Replication Scheme

 

The scheme is basically the same changing the source and the destination SQL servers. There is another difference, in the Direct Replication Scheme, it is the OmniBox.exe that does most of the population of table Log_PendingRepl while in the Reverse Population Scheme population is performed by triggers in the tables that must be synchronized, namely:

These triggers will append a record into Log_PendingRepl for every change to any record in any of the above tables, this holding even for multiple row transactions.

 

The Extended Replication Scheme

 

To attain full OmniBox<->OmniBilling synchronization, the Reverse and Direct replication schemes are not enough, that's why the Replication Scheme had to be Extended

 

First problem is that not all the information to be replicated is equally urgent, balance updates, account blocking, etc. should have the highest priority, changes to routes and trunk groups come next, while CDR's can wait. To implement this feature a the field "Priority" has been added to the Log_PendingRepl table, those records with the smallest value in the Priority field will be replicated first.

 

There is another issue, what if there is more than one OmniBox in the system or, though less likely, more than one OmniBilling? The SrcRepl.exe application V2.0 can now send UDP messages to more than one destination, each at its own pace. Records won't be deleted until sent to every register destination. The default destination Set_ID in sys_Parameters is 100, a second destination destination can be registered a by a second set of records with Set_ID = 101 and so on.

 

The main difficulty in the implementation of this feature y not that of "broadcasting" the replication transaction messages, but allowing each destination to have its own record set within the Log_PendingRepl. No longer can a record be simply erased upon receiving an acknowledgement, because this record may still be pending for another destination. To implement the feature, still another field was added to the Log_PendingRepl table, the pnp, that stands for Prime Number Product.

 

Each destination will have a prime number assigned, the first will have a 2, the second a 3, next 5, 7, 11, 13, 17 and so on, up to the 16th primary number, which is 53. For, say 3 destinations, the default for pnp field, upon record append, will be set to the product of the first 3 prime numbers in the above list or 2 x3 x 5 = 30. When the replicator for the second destination, the one described in in sys_Parameters with Set_ID = 101, receives an acknowledge from its SQLReplic, instead of deleting the record, it will set the pnp field to its current value devided into its prime number (which is 3). The result could be 10, 2 or 1 depending on the acknowledgement order. When this division renders a 1, it would mean that the transaction has been already replicated to all the registered destinations and so, only then, it can be erased. Each replicator will only "see" those records in which pnp is divisible by its prime number.

 

The replication acknowledge indicator has been modified after OmniMonitor V3.2.0 to show from which destination the acknowledge message (or not acknowledge) is coming from:

 

 

Log_PendingRepl for the Extended Replication Scheme

ID SQL pnp Priority
1000 UPDATE Balances Set InUse = 1 Where cl_id = `90921678` 30 1

 

Versions of the OmniBox greater than 4..5, support the Extended Replication Scheme, versions of the SrcRepl and SQLReplic, must also be greater than 2.0 .

 

Report Auxiliary Objects

Reporting will require information from some of the OmniBox tables that have been already discussed in Chapter 3 and 4. Information in the following OmniBox tables is required in the reporting process:

 

Dia_OutCh

Dia_InCh

IdxLookup

 

The tables below are mainly needed at billing time but will be queried by OmniBox in case of functions with PIN verification:

 

Balances

AuthorizationCodes

 

The LegB tables below are required only for billing, but in the case of prepaid accounts, billing must be made in realtime:

 

LegBCostLookup

LegBRateLookup

 

The following Objects are only for reporting purposes:

 

GroupInfo ( for centralized setups is a VIEW but for the Replicated Dual Server Setup is a TABLE)

QueryData  

These tables must be made accessible to the reporting system or its information synchronized, manually or automatically to local tables. In systems with more than one OmniBox, it gets somewhat more involved. Dia_OutCh and Dia_InCh won’t do because there is no field to identify which box a record belongs to. In this case, these names will correspond to views of the GroupInfo object joined to the QueryData.

 

 

GroupInfo

GroupID

Engine

IO

Description

DefaultAcc

LegACost

Provider

2

20

1

WTC

12345687

0.0

0

3

20

0

Ecuador

 

 

25

QueryData

DataID

Description

Date1

Date2

Int1

Int2

Int3

Int4

1

PullRecs

9/18/01

9/20/01

-1

0

8

0

 

Int2 , for the PullRecs row means Engine while Int1 will specify the temporary table Log_CDR1 is to be truncated (<> 0) or not (0) before pulling records from the main CDR table. Int3 and Int4 are used to introduce filters on Domain and Ranges respectively when pulling records, This is useful when you need to invoice only one client for a week, if you filter, the pulling query will run faster for you don’t need to pull all the records.  

 

The dia_InCh view will show GroupID as DomainID, Description, DefaultAcc and LegACost Where IO = 1 and Engine = 20. The dia_OutCh view will show the GroupID as RangeID, the Description and the Provider for IO = 0 and Engine = 20.

 

The GroupInfo table must be kept synchronized with all the dia_InCh and dia_OutCh tables in all the OmniBoxes. To do this the mentioned tables have triggers that, if activated, will either directly update this table if in Centralized mode or insert a transaction record into the PendingRepl table that will automatically update GroupInfo in the Billing server through the #include proprietary replication technology..

 

 

Local to the reporting system you must find the following tables:

Log_CDR      As define above

Log_CDR1    Temporary table with the same structure as Log_CDR but with two additional   integer number fields, namely RateID and CostID, that will store the rates and costs rate record identifiers that were valid at the time of the call. This table is indexed in almost every field to speed up the queries in the reports.

 

Rep_Engines        Names each of the OmniBox’s in the system.

EngineID

Name

0

include_0

 

Rep_Companies:                  Client or provider company information

 

[Company ID]                       int

[Company Name]                 varchar(40)

Contact                                  varchar(50)

Position                                 varchar(50)

[Division or Department]    varchar(20)

Address                                 varchar(60)

City                                         varchar(15)

Region                                   varchar(15)

[Postal Code]                        varchar(10)

Country                                 varchar(15)

[Federal ID]                           varchar(10)

[Sale Tax Number]                varchar(15)

Phone                                     varchar(24)

Fax                                          varchar(24)

Type                                       varchar(4)              (‘C’ for Client ‘P’ for provider)

[Parent Company ID]            int                           (Points to another company's record)

 

This is an open table, the OmniBilling structure and queries only uses [Company ID], [Company Name] and Type fields. The invoice report also references the contact and address related fields, but the user is free to add or remove fields as needed. Also a  hierarchical company structure can be made using the [Parent Company ID] field.

 

QueryData:    Is an auxiliary table that holds data to views and stored procedures involved in the reporting process.  For example data in DataID=1, will instruct stored procedure Rep_PullRecs to insert into Log_CDR1 record in Log_CDR from Date1 to Date2. If Int1 were < > 0, Log_CDR1 would be truncated before insertion, Int2 will hold the Engine ID for the records to be pulled.

 

DataID

Description

Date1

Date2

Int1

Int2

Int3

Int4

1

PullRecs

4/4/01

6/9/01

1

0

0

0

 

Basic Billing and Statistical Package

This Package has been programmed using Microsoft ACCESS but queries are run mostly from the Microsoft SQL Server using views and stored procedures.

 

The Log_CDR table is usually huge, it could hold millions of records corresponding to every call attempts ever made through the system. In a standard 4T in / 4T out system, 20000 attempts a day is a typical figure. Even a system like this will hit a million in 50 days. Indexes of big tables can become huge in size and also add overhead when inserting records, operation that must be done in real time by the switch or the replication system.  That is why Log_CDR is only indexed on Call_ID/Engine and DialTime. Only queries involving these fields are going to yield in reasonable times.

 

The above explains why the any report operation in this Package must be preceded by a Pull Records operation. This operation consists in copying record from  Log_CDR between two dates into the temporary table Log_CDR1.

 

 

 

Once the records are pulled, any of the reports may be previewed and then printed. Records can be pulled just copying the fields to Log_Call1 or with cost fields calculation (Buy and Sell costs will be calculated for each record using the valid rates to its DialTime). This last way is slower but you need it only for invoicing or to pay the providers, if you only need summaries or stats you may uncheck Calculate Moneys.

Reports

 

The Summary Report:

The $ummary Report

 

Invoice

The invoice number is automatically generated and unique for each date and company. The formula is: (Year(First Call) Mod 1000)*100000+(Week number)*1000+Company ID

  

 

Pay Provider

 

 

Result Codes


There is also an optional WEB reporting tool that can be accessed with a browser,  example

 

Dispute Reports

Is common practice among carriers and its clients or carriers and its providers, to send the CDR’s when a dispute generates concerning service charges. The CDR’s normally have more information than that required to settle a dispute, so actually the best is to supply only the information that’s needed to ease the review. To generate a dispute report you must first pull the records for the days in question and then hit the one two rightmost buttons in the Report Center form. Which one will depend upon the dispute being with client or a provider.  One of the two forms below will show:

 

 

The Dispute Table for a provider looks like this:

Company Name

Call_ID

Switch

DialTime

Call Length (ms)

Ch

DialedStr

Destination

Charge

ABC Satellite Telecom Inc.

24520

SW_0

4/16/01 11:53:28 AM

86604

213

01159347234567

Guayaquil

1.25

ABC Satellite Telecom Inc.

24521

SW_0

4/16/01 11:56:21 AM

133745

214

01159347234567

Guayaquil

5.07

 

And for a client, very similar:

Company Name

Call_ID

Switch

DialTime

Call Length (ms)

Ch

DialedStr

Destination

Charge

Best Carrier International Corp.

24520

SW_0

4/16/01 11:53:28 AM

86604

104

01159347234567

Guayaquil

1.45

Best Carrier International Corp.

24521

SW_0

4/16/01 11:56:21 AM

133745

105

01159347234567

Guayaquil

6.67

 

The differences being that the Company name is that of a client; the Ch in the case of a provider is an outbound channel while if a client, an inbound one; the charge also corresponds to the buy or sell rates respectively.

 

The dispute table can further be filtered by Destination, Ch, etc. to reduce the records to only those involved in the dispute. This table can be exported to any requested format using Microsoft ACCESS export features.

 

Data Entry Forms

The information on client and provider companies must be entered into the Rep_Companies table in order for it to properly show up in the Invoices and Pay Provider reports. The following is an entry form that will help accomplishing this task.

The full mailing address field need not be entered, it is automatically assembled by the form.

 

One of the most difficult parts of the accounting task in telephony traffic wholesale is that route rates change in a daily and sometimes even hourly basis. In order to produce an invoice CDR and rates must be combined to render the right charges. This package can do this for you, all you have to do is enter a record into the LegBRateLookup table every time a route changes its rate for some client type. The ActivationDate field must bear the time at which this rate is to become effective (or became effective). During the Pull Records operation the only the valid rates will affect the Sell_Cost calculation. There is an entry form for  the selling rates:

The most frequent use of this form is to change rates, remember that for this, you don’t edit records but you append new ones with a later activation date.

 

The form will list all the companies in that type in the Companies by Client Type subform and below all the route records ever defined for that client type. You can filter and sort this list at will in order to help your new entries. A combo-box will help you pick among the available routes in the IdxLookup table and the activation date will default to the current day at midnight.

 

LegBRate Setup

It is frequent that you need to create a new client type which leads to create a new set of  rate records for it. The task of entering them one by one must immediately make you wonder if there is a better way, well… there is. Hitting the button Open Utility for appending multiple records, will open the form bellow that comes to the rescue.

 

 

The idea is to cut & paste from the IdxLookup Subform in the left into the temporary one in the right. Enter the right values in the textboxes at the bottom of the window and hit create Leg B Rates, the records will be appended to the LegBRateLookup table. Once created you may edit the rates and Peak time field issues.

 

The creation of new client types usually involves entering rates from a given rate table in EXCEL, comma delimited text or any other typical format. To help automate this task, the Prepare Import Utility form is provided in this Billing Package. To use this tool you must first import the rates data into the New Rates table. On how to do that, read Microsoft ACCESS help.

 

To import rates into a newly created set of records in the LegBRateLookup table, there must be joining fields and the only possible are the Destination in IdxLookup and Country in New Rates. Usually, you may find a pretty good match, but there will always be a few exceptions. On the left top the Destination’s not matching any of the Country’s are listed. Left bottom you find the opposite. On the right IdxLookup as well as the New Rates tables are displayed for editing and/or appending to reduce the number of mismatches.

 

Once an acceptable match is attained, you may hit Do Import and the rates will be imported to all joined records. The remaining mismatch must be entered manually.

 

Also, a means is provided to update all the rates in New Rates incrementing them by a  percentage you may enter in a text box at the right center of the window.  The button Up Mark % will do the update.

 

There is a similar problem for the cost rates, providers can change rates as fast as you, so in order to calculate what your provider must be paid, the solution is very similar. The cost rates are stored in a very similar table called LegBCostLookup. To ease up the entry task there is a form:

 

 

The form will display all the route records ever defined to the selected provider. Combo boxes will drop down a list of all possible routes. The list of routes can be filtered and sorted at will to facilitate the copy and paste of entered information. The Do Import

 

Create multiple records opens the LegBRateSetup form, described above.

 

The New Provider Company button will open the Companies form shown above, with the company Type preset as “P” for client. The Prepare Import Utility button will bring the same Import Rate Utility form described above. The Do Import button will update the rates in all joined records.

 

The IdxLookup table is what associates dialed numbers to route indexes. The entry for to this table is invoke by hitting the IdxLookup EntryForm button in the Reports and Entry Form.

Manual entries to this table is the most labor intensive task involving dynamic routing. Not only there are hundreds of destinations with distinctive rates, but prefixes could be such that, even using wild cards, a single destination may require multiple records. To alleviate this burden, a table (SW_IdxLookupPrototype) with  IdxLookup prototype records to 459 destinations is provided with the OmniBilling system. Also a form is provided to further help in the task, it may be invoked by hitting the button Show Prototypes Form.

 

You may append all its records or just those corresponding to the supported destinations. The operation is straight forward.

 

Customer Service:

Any toll telephony service requires a minimal customer service where a client may call and change his PIN, check his balance or place a trouble ticket. The form below serves this purpose showing all it is to know about an account. To use it you must first pull the records for a time interval most likely to be disputed, only calls made during this period will show in the Account CDR’s subform.

Access to the Balances and AuthorizationCodes tables may need to be subject to security procedures since they store sensitive information. Microsoft Access provides means to secure such tables, consult its documentation on the subject. Also SQL security and custom triggers to log changes to these tables may be ordered from #include.

 

Calling Cards:

Calling Card accounts are just prepaid accounts. They must be set to client types below 100 so as to perform automatically the real time billing. This kind of retail will generate a lot of accounts, but the good news is that you don’t need to invoice them. OmniBilling provides tools to generates the account and PIN numbers for the calling cards. These tools are accessible through the buttons on the lower right corner the main form.

 The Generate and Fix buttons will show the two forms below respectively. The one the left is good for generating the accounts and the second for fixing potential mistakes in the generated accounts or changes that must be introduced after card generation, for instance, 1000 cards got printed with $10 instead of $15… What, throw them all away ? better is to fix the accounts to match the print  

 

 

 Calling Card Forms

 

All the accounts in the same batch will share the same values in the above shown fields: 

Default Batch number (Next Match) for a new batch will be the last batch generated+1, default card count in the batch is 10000 and 10 the PIN number default character count. PIN’s are actually characters, not numbers, this meaning that 0123456789 is not equal to 123456789. The account numbers will be generated as <batch>_<PIN>, for example the PIN above pertaining to batch 23 will have an account ID of 23_0123456789. The number generator in OmniBilling guarantees that it never generates the same number twice as long as old accounts are never erased even after the cards has been exhausted. Cards may be recharged for the same card tenant but a number may never be reused on a new card. 

The Export button will prompt you for the batch to export having the last one generated as default . Then it will prompt you for the txt file path to which the list of numbers will be exported, the default being:

  <path to OmniBilling.mdb>/Batch_<batch number>.txt

for example “C:\Work\OmniBox\Batch_23.txt”. The format of the file is one number per line in quotation marks, example:

 

“0123456789”

“4256874125”

“5874695123”

etc.

The Activate button will prompt you for the batch to export having the last one generated as default . If the batch is completely deactivated y will be activated, if it is already fully of partially activated you will be prompted for doing full activation, deactivation or to cancel the operation.

 

Security Features *

Balances and AuthorizationCodes tables store information very sensitive to a prepaid or even wholesale operation. Open access to these tables make them easy targets for fraudulent entries. Unless all the sales and customer service tasks are attended by reliable partners, access to these tables should be, not only password protected, but all the user activity should be logged in a table accessible only to an administrator. OmniBilling provides such a table ( OpLog ) and the necessary functionality to keep track of who, what and when.

 

OpLog Table example:

EvTime Event Operator
4/9/02 3:51:52 PM Authorization_ from 10000000000000 to 1000000000000; CustAdmin
4/11/02 10:53:34 AM UPDATED cl_id 1_0072372324 ClientType From 1 to 2 CustAdmin
4/11/02 11:52:16 AM DELETED cl_id = Test Account, Last_Update = Apr 11 2002 10:56AM, balance = 100, LanguageOffset = 0, WelcomeID = 5, ClientType = 100, LegAFactor = 0, InUse = 0, BalanceLimit = 0, CompanyID = 0, RoutingSet = 0, RealTimeBilling = 0, Batch = 0, First_Use = Apr 11 200, Expires = Apr 11 2003 11:52AM, Maintenance_Fee = 0 CustServ3
4/11/02 12:26:28 PM UPDATE Authorization from 10000000000000 to 10000000, cl_id from 1000000000 to 1000000000 CustServ1
4/11/02 12:38:47 PM DELETE Authorization = 8001000009, 8001000009 CustAdmin

These features involves triggers that are not activated by default because it requires setting up SQL server logins for the customer server operators and administrators.

 

*This option is not part of the basic Billing system, it must be explicitly ordered to #include.

Database Setup Options

The fact that OmniBox runs on two ODBC sources, namely OmniBoxSrc and the OmniBoxAccSrc, introduces the possibility of options on where tables accessed through OmniBoxAccSrc may reside and how they will be maintained. Currently there are three options:

  1. Centralized Single Server (Default)

  2. Centralized Dual Server

  3. Replicated Dual Server

Some History

In early versions of the OmniBox all the tables resided in the same database, we can call this the Legacy Setup. The need of removing the billing activity from the telephony application soon appeared and the solution given was to create a second set of routing and administrative tables in a remote SQL server at the billing site, the CDR's were for the first time replicated using an early but very similar replication scheme. The rest of the tables where synchronized manually or by using the SQL Server replication, which ended up being done manually.

The introduction of the IVR functions and the real-time billing made the replication more complex bringing about the Centralized Dual Server approach. This approach moved the Billing Server at remote billing site to the OmniBox LAN but, this time into a different computer. The two sets of tables became the same or "Centralized" at the Billing computer. The OmniBox read the routing and administrative directly tables from the Billing Server. Parameter 18 in the sys_Parameters table switches between Legacy and Centralized Setup options. 

The success of the Centralized Dual Server led to the modification of the original single database default setup (the Legacy Setup) into the Centralized Single Server, Simply, the Billing database (OmniBilling) was brought into the OmniBox Server

The development of the Reversed and Extended Replication Scheme allowed the Billing server to go back to the billing site with a replica of the OmniBilling database, becoming the Replicated Dual Server setup option.

Centralized Single Server

OmniBoxAccSrc tables reside in a different database, namely the OmniBilling, but in the same server as the OmniBoxScr ones. These tables are said to be centralized because the same "central" tables are used by the OmniBox and also by the billing and reports application. The opposite to  central would be if there were a different set of tables somehow synchronized.

This option is simple to setup, but reporting must be made on the same server, with the same CPU and same resources as the OmniBox,  When the number of record in the reporting queries become too big, the resources drained from the OmniBox can cause some telephony operations to timeout bringing down the app. To this resource draining, it must be added that of a remote control application like the LapLink, used  to access the OmniBox that seldom shares same location as the administration office.

Centralized Dual Server 

In order to avoid using the same CPU and memory resources for running telephony and reporting, the OmniBilling database can be pulled away to a different server running in another computer on the same LAN or one with a fast and reliable link to it. Since the tables are still centralized, they must be queried by the OmniBox in real-time, any failure in the link, can bring it down. Centralized tables, having the advantage of its inherent OmniBox-Reporting data synchronization, posses the problem of table locking during data editing or entry. Caution must be observed while doing mass data editing, entry or deleting because OmnibBox can not access the table during the transaction and it may crash by timeout.

How to change from Single to Dual Server: 

Replicated Dual Server

Contrary to the centralized concept, there will be two sets of  tables but in two different locations that will need just a soft link of small bandwidth between them. These sets of tables must be synchronized and for that, we must use the Replication Scheme. With this option, the following disadvantages disappear:

Also the following advantages appear:

How to Change from a Centralized Single or Dual to a Replicated Dual Server

 

 

 Download This File                   Download All Documents                        Go to Home Page