We are in the Telecom business and need someone who is familiar with the industry that has or can develop an application that will allow us to compare call detail records between our system and the vendors system and determine where discrepancies exist.
When a call is completed, the CDR rated and stored into an MS SQL 2008 database. When we receive a bill, if the total number minutes in our system does not match the bill, or if the amount is wrong, then the vendor send us their CDRs in a flat csv file format. We must import this file into our system in order to compare their records with ours. The following must happen in order to do this:
1. Their fields must be mapped to ours before import. The app you develop must allow us to pick which fields match our fields. Some companies use a start and stop time, other do not. Some enter the rate information, others don't. The system must be flexible enough to allow us to do the mapping and then save this as a template for future use.
2. Their CDR times must be syncronized with our times. Every server uses a different time zone and not every one will use ntp. As a result, there will be an time offset in order to line their records with ours.
3. A threshold must be set for discrepancies. For example, if we set it to 2%, any CDR with a discrepancy of less than 2% will be ignored. Typically, the number of minutes billed between systems will not match exactly.
4. One the CDRs are lined up by time, the fields are mapped and the data is imported into our SQL databse, an analysis must be done. This will create the following report:
a. Total minutes we show
b. Total minutes they show
c. Total number of extra/missing records
d. Total minutes over/underbilled by the vendor
e. Total dollar amount over/underbilled by the vendor
Once the report is generated, a csv file will be prepared for download which will contain the information necessary to show the vendor what CDRs contain discrepancies. Within the text file, there will be their CDR and ours on the same line to identify the discrepancies. These will be the disputed CDRs. Disputed CDRs will consist of the following:
a. Extra/missing records
d. Minutes over/underbilled
e. Inaccurate amounts over/underbilled
If you do not have any experience in the telecom field or have never done such a project, I recommend you do not bid as explaining the entire business and process flow will take up too much of both mine and your time.
Additionally, when data is imported from the CSVs into our system, often times date/time and number formats must be transformed to match ours. We use the MS SQL smalldatetime for date/time fields and we use seconds for the time billed. Some companies use different date/time formats and may use minutes in MM:SS or MM.SS formats. Proper data transformation is critical.