Project Description:
PURPOSE
Xconnect needs a fully automated system for receiving and processing Terminator cost sheets based on set business principle, convert the accepted rates into a standard format that is e-mailed, dumped to an FTP server and/or accessible by Xconnect by ODBC Link.
REQUIREMENTS
All Terminators send Xconnect e-mails that has Cost Sheets with varying frequency (daily/weekly/monthly/quarterly/yearly) and with varying formats to XC Rates (). These e-mails contain attachments, typically in .XLS, but also .XLSX, .CSV and other formats, with information about a Rate or Code change for a specific destination and/or prefix, and can often also indicate effective dates, billing increments, service and currency type.
Based on customized templates for each vendor, the proposed system, should check the supplier and service (if multiple services are offered), the effective start date and whether it's an Update or Expire all and process and change the information contained in the attachment within a few minutes. This information is from one of more of the subsequent:
a. Email Subject Line
b. From address
c. Attachment file name
d. Body of email
New or adjustments to existing templates could be made accessible to XC Rates through a GUI/Web based interface.
The conversion process should then check the data for missing or errors information, and if needed alert XC Rates with a needed action to manually intervene, e.g. to add a new template or adjust the existing template. If the process is productively completed, then the results should be validated against the existing rates and business rules to make sure cost increases and code changes are notified with 7 day notice. Some manual adjustment or temporary lifting of restrictions could be possible through a GUI/Web based interface, e.g. to allow an immediate increase if already approved.
The Rejected Destination Dial/Rate Code changes should be notified to XC Rates immediately, who will communicate this to the Terminator. Optionally, the system could directly respond to the Terminator with details of the rejected Destination Dial/Rate Code changes, or acknowledge receipt and successful processing of the Rates.
The Approved Rates could be communicated to XC Rates in an agreed format by the subsequent methods:
1. E-mail to XC Rates
2. Delivery to FTP Server
3. XConnect to access rates via ODBC Link
Finally, the system could log the converted sheets for an everyday Update Report sent to XC Rates by e-mail.
XConnect use Microsoft SQL Server 2012 with each data stored in these databases. The solution should support the ability to pull data direct from the converted format using Native SQL connectivity, ODBC or SSIS (SQL Server Integration Services using ETL).
Skills required are Data Processing, Excel, SQL, Database Administration, User Interface / IA