Project Detail

Description

This is a very simple project and I need it finished very very fast. I have an Access database ready with tables already designed and data populated. You will simply be formatting the data using queries and creating a CSV output file.

Objective
This is an application that will be used in the telecommunication industry. Your objective will be to take various sets of pricing/vendor data and transform the information into a format that can be outputted as a CSV file.

Description
When a customer sends us a call over the internet, a switch decides which vendor to send to call to based on price. The switch will attempt the cheapest vendor first, if the call does not answer, the switch will try the next cheapest and the next until the call is completed. The switch can attempt up to 8 vendors before it gives up. Each vendor provides us a list of routes with a rate for each route. The switch does a digit match based on the first 7 digits of the number being called. For example, if the switch receives a call to 12148761404 it will look for a match in the system which would be 1214876. It then sends the call to the cheapest vendor to be completed. This system of sending calls to the cheapest vendor is called Least Cost Routing or LCR. In order for the switch to know how to route the calls, it requires that an LCR file be imported. Because there are anywhere from 50,000 to 150,000 routes per vendor, this task cannot be done by hand. An MS Access database has been prepared with all the rate and vendor data populated. You will only need to write an application within Access to query all the rate tables and create an LCR table.

Access Table/Data structure
1. Rates Tables. This is a table that contains all the routes and rates of one vendor. The fields are:
a. 1NPANXX. This is a unique number that the LCR output will use. This is the same as the digitmatch field in the LCR_Output table.
b. Desc. This is the alias field in the LCR_Output table.
c. Rate. The rate will be used to determine who the cheapest vendor is per route.
d. Min. This is ignored and will be used for future functionality.
e. Incr. This is ignored and will be used for future functionality.
2. TIDNames. This table determines which vendor rates will be used in the LCR and what the corresponding table relationship will be. It is possible that some vendors will not be used at any given time. The fields are:
a. TIDName. This is ignored and will be used for future functionality.
b. TID. The TID will be used in the LCR output.
c. RateTable. This tells the system which rate table to use for each vendor.
d. LCR. This states whether the vendor will be used in the LCR output or not.
3. LCR_Output. This is the table where the formatted LCR output will go to. All the data in the table now is sample information. The data in the table is:
a. #table (0 by default)
b. alias (use desc)
c. digitmatch (use 1NPANXX). This is a unique value.
d. extension (1 by default)
e. gid (0 by default)
f. policy (0 by default)
g. tid1, tid2, tid3, tid4, tid5, tid6, tid7, tid8. The order of the tids is the most important function of this application. It is here that using the rate tables provided, the program determines the order of the vendors in from the cheapest (tid1) all the way to the most expensive one (tid8) relative to the unique digitmatch.
h. con1, con2, con3, con4, con5, con6, con7, con8 (1 by default)
i. percentage1, percentage2, percentage3, percentage4, percentage5, percentage6, percentage7, percentage8 (0 by default)

Simply said, the only fields that must be formatted in order are digitmatch and tids. Once the LCR_Output table has been populated, the data must then be exported to a CSV file in the exact same format as the LCR_Output table.

Here is an example of how the system would function:

RATE INPUT

Rates-VendorA (TID 100)
1200201 USA .002
1200203 USA .006
1200204 USA .009

Rates-VendorB (TID 200)
1200201 USA .005
1200203 USA .002
1200204 USA .006

Rates-VendorC (TID 300)
1200201 USA .004
1200203 USA .003
1200204 USA .005

Rates-VendorD (TID 400)
1200201 USA .003
1200203 USA .008
1200204 USA .004

LCR OUTPUT (Default data has been ignored. Only processed data is shown)

digitmatch alias tid1 tid2 tid3 tid4
1200201 USA 100 400 300 200
1200203 USA 200 300 100 400
1200204 USA 300 300 200 100


Additional files submitted:
Project Spec.doc