Automatic Tariff Import

From Kolmisoft Wiki
Revision as of 13:13, 30 March 2020 by Ervinas (talk | contribs)
Jump to navigationJump to search

Introduction

An important task is the timely management of Provider Tariff Plans. While business practices differ, commonly, each Provider will deliver an initial Spreadsheet file detailing their prices to the range of Destination Codes that they are providing. This initial Spreadsheet will then be followed by update notification spreadsheets. These will be received at regular agreed-upon intervals identifying amendments to the previous prices. The format and content of these notifications is not standardised and differs from one Provider to another. This information is vital from a commercial and financial management point of view. It is important therefore that the rating information received from a Provider is uploaded regularly in order that the System can correctly calculate Provider rating costs for calls. It is also important to analyse Tariff/Rating Plan updates received from partners in order to ensure that any errors in the information are noticed early and resolved before causing Billing disputes.

To solve this necessity Tariff Import system should be used. Let's start from defining major terms used in this process.

Template

The Provider Import Template is the primary object that requires configuring so that the Provider Rating Import can occur. The Provider Import Template contains the settings that the System uses to convert the information from the Spreadsheet into the System.

Rate Import Rules

The Rate Import Rules feature enhances the analysis of the Providers rates by identifying rates that do not meet a specific criterion (for example a rate increase with less than 7 days’ notice) and then potentially either alerting the Administrator or rejecting the rate. One (or more) sets of Rate Import Rules can be defined on the System and assigned to many Tariff Import Rules.

Tariff Import Rules

Tariff Import Rules assign Rate Import Rules, Notifications and Template to the received Email/Attachment in order to properly convert received Tariff and upload rates to the System.

Notifications

Notifications will generate an email to the desired recipient when configured for various events. Such as:

  • Tariff is received
  • Tariff is analysed and waiting for confirmation before Import
  • Tariff is imported or rejected
  • and others

Import Job

The Import Job is an auditing object that is created by the import and provides information about what occurred during execution. The Import Job means that, when necessary, it is possible to go back and see what happened on previous imports. For that reason there is a Tariff Jobs table in the Tariff Jobs List window which can be reached by going to Menu -> Billing -> Tariff Import -> Jobs.



M2TIImage56.png

M2TIImage20.png

Tariff Jobs table columns:

  • Id: Tariff job identification number.
  • Created at: Date when job was created.
  • Status: The state of import process. Can be:
    • Assigned - import process was started.
    • Converting - rates are being converted.
    • Failed Conversion - there was an error in rates conversion.
    • Converted - rates were successfully converted.
    • Importing into Temporary Table - rates are being uploaded from attachment to the system.
    • Failed to Import into Temporary Table - there was an error in rates uploading process.
    • Imported into Temporary Table - rates have been loaded to the system.
    • Analyzing - rates are being analysed.
    • Failed Analysis - there was an error in analysis.
    • Analyzed - analysis process has been completed.
    • Rejected - rates did not match requirements and were rejected.
    • Waiting for confirmation - import is stopped until it will be confirmed by admin.
    • Cancelled - import was cancelled by admin.
    • Imported - rates were successfully imported.
  • Import Rules - name of the Tariff Import Rule used in import and the link to its edit view.
  • Rate Import Rules - name of the Rate Import Rules used in import and the link to its edit view.
  • Tariff - Tariff name which is being changed in import and the link to its edit view.
  • Attachment - name of the attachment that was used in import.
  • Import Type - type of import. Can be:
    • Update - rates will be added or updated.
    • Full - rates will be replaced.
  • Auto:
    • ‘Yes’ means that tariff import will be done without any request for confirmation.
    • ‘-’ means that confirmation will be needed after the analysis.
  • Email:
    • ‘Yes’ email about Tariff Job creation has been sent successfully.
    • ‘-’ email has not been sent.
  • Rate Changes - number of how many rates were changed during analysis.
  • Rejected Rates - number of how many rates were rejected during analysis.
  • Analysis (only in analysed jobs) - link to analysis details.
  • Delete - button to delete one job.

Note: yellow colored Tariff Jobs rows show that they are waiting for confirmation.

Automatic Deletion

Old Tariff Jobs are periodically deleted by the system. The period of deletion can be found and changed in Settings -> Various -> Delete Tariff Jobs older than. Default value is 30 days. It means that Tariff Jobs which were created before 30 days or more will be deleted.



M2TIImage9.png

Analysis

Detailed Analysis can be reached from the Tariff Jobs table by pressing on ANALYSIS link.

On the top right corner there are buttons:



M2TIImage101.png

  • Confirm: results of analysis are confirmed and tariff import will continue to proceed.



M2TIImage78.png

  • Canceled: tariff import will be canceled.



M2TIImage69.png

  • Refine Results: allows to filter table by selected parameters.

Parameters:

Rate Increase, Oldest Effective date, Max Decrease, Duplicate Rate, Date Decrease, Maximum Effective Date, Max Rate, Min Times, New Rate, Max Increase, Zero Rate, Increments - can be filtered by values: All (default), None, Alert, Rejected.
Non Importable Reasons - can be filtered by values All (default), None, Any, Could not determine direction code, Rate price invalid, Effective from invalid.



M2TIImage94.png

Tariff Job Analysis table:



M2TIImage31.png

Table columns:

Tooltip: If the mouse is on the table, additional import rule information will be presented.



M2TIImage32.png

Row colors:

  • If the row is red colored, the rate is not importable. Non Importable Reason can be found by moving mouse over the rate.
  • If the row is yellow colored, the rate analysis was successful but there were some warnings. In the tooltip the word ‘alert’ will be next to the parameter which caused the alert.
  • If the row is white colored, the rate analysis was successful.

Analysis execution order: If analysis is being executed on several jobs which includes the same tariff, these jobs will be resolved consistently and the oldest job will have the highest priority. The next analysis of the job will be started only after the previous one is completed.

Workflow schema

The workflow can be visualized using the following schema:



M2TIImage15.png

Understanding and Working with Provider Import Templates

A Template is created when a Tariff/Rating Plan first needs importing, and can then be reused every time an update is required. Templates can also be amended at a later point if the format of the original file is altered. Import Templates are highly flexible and support many Rating Plan formats; they therefore include many options that may not need configuring for the more common simpler Tariffs/Rating Plans.

The Templates work by mapping the contents of an Excel sheet, and its format, to the information required in the Database. By configuring this information the data is then uploaded into the System and converted into the correct format.

This is illustrated in the diagram below:



M2TIImage64.png

Tip Before You Begin: Import Templates are specific to individual Tariffs/Rating Plans, so when creating a new Import Template for the first time it is important to have easy access to the Tariff/Rating Plan involved. A good tip for working with this - if you happen to have two monitors on your computer - is to display the Tariff/Rating Plan for which the Template is being constructed on one monitor, and System GUI on the other. Alternatively, if only one monitor is available, open the Tariff/Rating Plan in Excel and use Alt+Tab to switch directly between the applications.

The next Section is going to describe each of the settings available for configuring an Import Template.

The screenshot below shows the Template Settings in the default basic view:



M2TIImage113.png

In this view we can see the basic summary of the Template highlighting each of the sections that may be required to import the Tariff/Rating Plan.

Tip: Remember that if the Tariff/Rating Plan being imported does not include information relating to a particular section of the Template, then that section can simply be left blank. Many Import Templates will be very simple configuring only four or five settings from the available options.

Name

Name of the Template can be set to any, but for better management suggested to be the name of Provider.

Rate Sheet

The Rate Sheet field requires to be set to the corresponding Worksheet in Excel that the Rating Plan information is held on.
This means that Rating Plans that contain multiple sheets can still be imported.
Example:
In provided Tariff/Rating Plan below - Destinations, Prefixes and Rates are stored in the second (“Numberplan”) Worksheet.
M2TIImage73.png
Accordingly, Template’s Rate Sheet value must be set to 2.
M2TIImage97.png



Header Rows

Header Rows setting requires to be set to inform the System of the rows at the top of the Tariff/Rating Plan that are to be ignored when importing meaningful data.
If Header Rows are set incorrectly, there is a possibility that Tariff/Rating Plan will not be imported at all or with errors because of wrongly processed data.
Example 1:
Provided Tariff/Rating Plan below meaningful data starts from Row 1.
M2TIImage44.png
This means there are no rows to skip/ignore for the Header Rows setting.
Accordingly, Template’s Header Rows value must be set to 0.
M2TIImage124.png



Example 2:
Provided Tariff/Rating Plan below meaningful data starts from Row 23.
M2TIImage40.png
This means there are 22 rows to skip/ignore for the Header Rows setting.
Accordingly, Template’s Header Rows value must be set to 22.
M2TIImage114.png



Prefix/Code

Prefix/Code requires to be set to match the Tariff/Rating Plan Prefix column.
On some occasions there may be two columns (like Country Code, City Code) which the System is also able to handle if Template settings are correct.
Sometimes provided multiple Prefixes are displayed in singular cell format like “71-76”, “55,551,552,55257” or “370, 371, 372-376, 3772”, such formats are parsed automatically and does not require additional configuration.
Example 1:
Provided Tariff/Rating Plan below has only one Prefix column which is B.
M2TIImage68.png
Accordingly, Template’s Prefix/Code value must be set to B.
M2TIImage42.png



Example 2:
Provided Tariff/Rating Plan below has two Prefix columns which are C and D.
M2TIImage67.png
Accordingly, Template’s Prefix/Code values must be set to C and D in exact order.
M2TIImage28.png



Destination/Zone

The Zone drop-down list box identifies the Column in the Excel sheet that contains the Zone name.
If there is only one Column containing this information then only one of the dropdown boxes need to be completed. However if the Zone is in two Columns then both of the drop-down boxes can be completed to identify this. In this scenario the blank fields either side of the second box can be used to format the Zone name.
Example: A Rate Plan includes the following information:
M2TIImage59.png
The Zone import is configured with the Columns A and B and the separator fields with a space “Column B”, as shown:
M2TIImage5.png



This means that the Zone name will be displayed as: Brazil Mobile
Example 1:
Provided Tariff/Rating Plan below has only one Destination/Zone column which is A.
M2TIImage7.png
Accordingly, Template’s Destination/Zone value must be set to A.
M2TIImage21.png



Example 2:
Provided Tariff/Rating Plan below has two Destination/Zone columns which are A and B.
M2TIImage34.png
Accordingly, Template’s Destination/Zone values must be set to A and B in exact order.
M2TIImage5.png



Connection Fee

While uncommon in wholesale, some Rating Plans being imported may still include a fixed Connection Charge - a one off charge applied when the call is connected.
If required, the Connection Charge drop-down list box can be used to identify the Column containing such a charge in the Rate Plan being imported.

Effective Date

The Effective Date options determine in which Column(s) the Date and Time information for the imported rates can be found.
Multiple options are provided to support different Spreadsheet formats.
Some Tariff/Rating Plan Providers split Effective Date data between two Worksheets, whereas in first Worksheet there are Destinations with Effective Date and in second Worksheet - Destination, Prefix, Rate data. Configuration of such cases is provided in Example 3.
Example 1:
Provided Tariff/Rating Plan below has an Effective Date column which is F.
M2TIImage29.png
Accordingly, Template’s Effective Date value can be set to F.
M2TIImage10.png



Example 2:
Provided Tariff/Rating Plan below has an Effective Date column which is F and Effective Time which is G.
M2TIImage17.png
Accordingly, Template’s Effective Date values can be set to F and G.
M2TIImage43.png



Example 3:
Provided Tariff/Rating Plan below in the first Worksheet shows Destination (Column A) and Effective Date (Column D).
In the second Worksheet, there are Destination (Column A), Prefix (Column B) and Rate/Price (Column C).
M2TIImage112.png
First dropdown configures from which Worksheet takes Effective Date data (which in this example’s case is Worksheet 1 (Rates)).
Second dropdown configures Effective Date column from secondary Worksheet.
Third dropdown configures by which secondary Worksheet column do the mapping with primary Worksheet (which in this example’s case is Destination from Worksheet 1 (Rates) Column A).
Fourth dropdown configures by which primary Worksheet column do the mapping with secondary Worksheet (which in this example’s case is Destination from Worksheet 2 (Numberplan) Column A).
Accordingly, Template’s Effective Date values can be set to 1, D, A = A.
M2TIImage100.png



Effective Date Format

After selecting the Column, the Format field is used to specify the format the information is held in the Rate Plan.
Some suggested formats already exist in dropdown, which can be selected or used as an example to create a custom one if it is not present in dropdown.
Example:
Provided Tariff/Rating Plan below has an Effective Date column which is F.
M2TIImage29.png
Accordingly, Template’s Effective Date Format value can be selected from dropdown with suggested formats.
M2TIImage50.png



Rate

Rate requires to be set to match the Tariff/Rating Plan Rate/Price column.
Example:
Provided Tariff/Rating Plan below has a Price column which is E.
M2TIImage60.png
Accordingly, Template’s Rate value must be set to E.
M2TIImage51.png



Minimal Time

Minimal Time can be set to match Tariff/Rating Plan Minimal Time column.
Example 1:
Provided Tariff/Rating Plan below has a Minimal Time column which is E.
M2TIImage46.png
Accordingly, Template’s Minimal Time value can be set to E.
M2TIImage52.png



Example 2:
Provided Tariff/Rating Plan below has a Minimal Time column which is D and in the first place of “x/y/z/” fields.
M2TIImage49.png
Accordingly, Template’s Minimal Time value can be set to D and field 1.
M2TIImage26.png



Increment

Increment can be set to match Tariff/Rating Plan Increment column.
Example 1:
Provided Tariff/Rating Plan below has an Increment column which is F.
M2TIImage63.png
Accordingly, Template’s Increment value can be set to F.
M2TIImage11.png



Example 2:
Provided Tariff/Rating Plan below has an Increment column which is D and in the second place of “x/y/z/” fields.
M2TIImage49.png
Accordingly, Template’s Increment value can be set to D and field 2.
M2TIImage103.png



Exceptions

Exceptions allow additional data manipulation, when specific rules are defined separately in the provided Tariff/Rating Plan.
Example 1:
M2TIImage41.png
M2TIImage6.png



Example 2:
M2TIImage16.png
M2TIImage19.png



Creating and Configuring Rate Import Rules

Rate Import Rules are relevant to the importing of Provider Rates, and specify actions to be taken if the rule is broken.

The actions available are:

  • None (ignore this rule)
  • Alert (notify the Administrator)
  • Reject Rate (automatically reject the rate which breaches this rule)

Example: The first Rate Import Rule is the rate increase rule. The variable for this rule is a number of days, 7 days by default. What this means is that when a Provider Tariff/Rating Plan is uploaded and a rate increase is detected with an Effective Date less than 7 days into the future the action that has been configured will be triggered. The rate could be automatically rejected, or an alert about this rating entry was raised.



M2TIImage70.png

Available Rate Import Rules with explanation to each one of them:



M2TIImage93.png

Rate Increase

If updating existing Rate, whose Rate (Price) increases and takes into effect earlier (by days compared to importing effective from) than set in Rule, then according action will be taken.
Value can be set to any integer between 0 and 30 which represents day(s).

Rate Decrease

If updating existing Rate, whose Rate (Price) decreased and takes into effect earlier (by days compared to importing effective from) than set in Rule, then according action will be taken.
Value can be set to any integer between 0 and 30 which represents day(s).

New Rate

If creating a new non-existing Rate, which takes into effect earlier (by days compared to importing effective from) than set in Rule, then according action will be taken.
Value can be set to any integer between 0 and 30 which represents day(s).

Oldest Effective Date

If updating existing or creating a new Rate, whose new Effective Date is earlier (by days) than set in Rule, then according action will be taken.
Value can be set to any integer between 0 and 365 which represents day(s).

Maximum Effective Date

If updating existing or creating a new Rate, whose new Effective Date is later (by days) than set in Rule, then according action will be taken.
Value can be set to any integer between 0 and 365 which represents day(s).

Max Increase

If updating existing Rate, whose Rate (Price) increased more by percentages than set in Rule, then according action will be taken.
Value can be set to any decimal between 0 and 500 which represents Rate’s (Price’s) percentages.

Max Decrease

If updating existing Rate, whose Rate (Price) decreased more by percentages than set in Rule, then according action will be taken.
Value can be set to any decimal between 0 and 100 which represents Rate’s (Price’s) percentages.

Max Rate

If updating existing or creating a new Rate, whose Rate (Price) is higher than set in Rule, then according action will be taken.
Value can be set to any decimal between 0 and 100 which represents Rate’s (Price’s) value.

Zero Rate

If updating existing or creating a new Rate, whose Rate (Price) is zero, then according action will be taken.

Min Times Not Equal

If updating existing or creating a new Rate, whose new Min Time value does not match any from set Rule, then according action will be taken.
Acceptable value format is: “x,y,z,w”
Where x, y, z and w must be integer values between 0 and 7200.
Must be separated by comma (,).
Must not have any spaces.
Must not have leading zeros, unless it is only a zero.
Examples:
0,100,60,7200
0,1,60
1,60
1

Increment Not Equal

If updating existing or creating a new Rate, whose new Increment value does not match any from set Rule, then according action will be taken.
If updating existing or creating a new Rate, whose new Min Time value does not match any from set Rule, then according action will be taken.
Acceptable value format is: “x,y,z,w”
Where x, y, z and w must be integer values between 0 and 7200.
Must be separated by comma (,).
Must not have any spaces.
Must not have leading zeros, unless it is only a zero.
Examples:
0,100,60,7200
0,1,60
1,60
1

Tariff Import Rules



M2TIImage118.png

The main Tariff Import Rules page shows all the Tariff Import specific rules in the system. Here you can add new rules by clicking the button at the upper-right corner. Also, you can Edit/Delete selected Rules with the relevant buttons. The first column ACTIVE? allows to activate or deactivate a specific Rule by clicking the YES/NO buttons. If you would like to change the order of the Rules, Drag&Drop functionality is available.

Follow the steps below to create Tariff Import Rules:

By clicking the ADD NEW TARIFF IMPORT RULES button, you are presented with the following settings screen:



M2TIImage65.png

  • Name - Name of the Rules
  • Rate Import Rules - Select the Rate Import Rules you would like to use
  • Target Tariff - The Tariff for which the changes will be applied
  • Import Type - you can choose between 2 options: Add/Update and Replace All Rates
    • Add/Update - new rates will be added from file and existing ones will be updated
    • Replace All Rates - existing rates will be deleted and new ones imported from file
  • Import Template - Template to be used
  • Effective Date - Effective Date value to be used with Rates
    • Template - Inline - Use Effective Date from File set by Template setting (see: Template)
    • Subject - Use Effective Date from Email Subject
    • File Name - Use Effective Date from File (Attachment) Name
  • Manual Review - Import automatically or wait for manual confirmation
  • Reject Import if errors found - Tariff Import will be rejected if at least one error will be found * after analyzing
  • Stop processing more Rules - Matching with Email Details and Active Tariff Import Rules will be in specific order (from Tariff Import Rules List, from top to bottom). Email assigning will be stopped on this Rule if this option is enabled
  • Default Effective From - Effective From value in Rates, when invalid value supplied from file
  • Default Connection Fee - Set Connection Fee for Rates if it is not present or invalid in Import File
  • Default Increment - Set Increment for Rates if it is not present or invalid in Import File
  • Default Minimal Time - Set Minimal Time for Rates if it is not present or invalid in Import File

Email/Attachment Validation

Email/Attachment Validation section is used for specifying which emails where will be used by Tariff Import Rules. There are five fields. First four are for email and fifth is for attachment.



M2TIImage104.png

  • Mail From - Email Address(es). Multiple emails can be separated by ;.
  • Sender Name - Senders name.
  • Mail subject - Emails subject.
  • Mail text - Emails text.
  • File Name - Attachments file name.

Wildcard % is allowed, which substitutes any character with any length. Example is shown in the image below

M2TIImage110.png

  1. Mail From has two values %@gmail.com and john@%.%.
    1. %@gmail.com specifies it is any gmail email address.
    2. john@%.% specifies it is john from any email client.
  2. Sender Name J% any value That starts with uppercase J
  3. Mail Subject %Rates Subject ends with word Rates
  4. Mail Text Hello Mail Text Starts with word Hello
  5. File Name %.csv filename ends with .csv

Notifications

Email Notifications are used to generate email notifications to an internal or external list of recipients.



M2TIImage106.png

Follow the steps below to create and configure a notification Template.

  1. Create Email Template. Manual how to create Email Templates can be found here: M2 Emails. Additional Email variables: tariff_job_id, tariff_job_analysis_url.
  2. Select created Email Template from Email Template dropdown. Subject field will fill in automatically.
    M2TIImage112.png
  3. Enter Email Address(es) in the Recipients field. Multiple Email Addresses must be separated by ;.
    M2TIImage116.png
  4. Repeat the same process for desired Import Triggers.
    M2TIImage36.png

Inbox

The Inbox is a type of email inbox available within the System that allows the Rating. Administrator to view and manage Provider Tariffs/Rate Notifications that have been sent to the System's Email Address.

Caution: The Inbox functions in a similar way to the email inbox of your desktop or web based email client, with emails sent to the platform’s email address being displayed and stored in the inbox. The inbox also includes additional functionality to support Providers Rate Import procedures and processes. However, it is important to understand that the Rate Inbox is designed to manage received emails and not to be a “full email client”. This means that it is not possible to Reply To or Forward emails that are received into the Rate Inbox.

Inbox Interface:



M2TIImage82.png

The Inbox consists of 3 views: Inbox, Completed and Junk.

Inbox - is the primary working inbox. It is this view that will show emails that have been received that have an associated task that requires carrying out. That task might be to review and commit an update, assign Tariff Import settings, or a number of other actions. Emails will remain visible in the Action Required view until the associated rate update is committed, or they are manually completed or deleted.

Completed - Fully processed emails are shown in the completed tab. Fully processed means that all of the emails attachments had been assigned to at least one of Tariff Import Rules and Tariff Job which is completed.

Junk - Emails that are deleted from Junk will be removed permanently, not displayed in the deleted view.

Tariff Inbox basics



M2TIImage55.png

  1. Email column - Sender name, email address and subject
  2. Email details button - toggles email details
  3. Attachments list
  4. Attachments file type list
  5. Import jobs associated with attachments
  6. Import jobs button - toggles import jobs(if there are more than one tariff import job)
  7. Attachment mapping status
  8. Processing note
  9. Inbox view tab
  10. Completed view tab
  11. Junk view tab
  12. Select all emails check box
  13. Select single email check box
  14. Delete Selected emails
  15. Refine Results - shows fields to filter emails

Email Details

Email details are toggled by pressing the arrow button in the Email column. There are four sections: Email Details, Message, Attachments and function buttons.

  • Email Details section shows detailed email information such as ID, Received, From, Sender, Subject, To, Reply to and CC.
  • Message section contains emails message.
  • Attachments section shows attachments associated with email.
  • Last Section represents three function buttons.



M2TIImage47.png

Filter Emails

Email filtering is done by pressing the Refine Results button in the top right corner of Tariff Inbox.



M2TIImage37.png

There are four fields:

  • Period - period between emails were received.
  • From - email or sender name.
  • Subject - emails subject.
  • Message - emails message.



M2TIImage71.png

Press refine button to see results.



M2TIImage120.png

Press clear to clear fields.



M2TIImage107.png

Delete Emails

To delete emails, select emails by clicking their checkboxes.



M2TIImage38.png

Then press Delete Selected button on top right side of Tariff Inbox



M2TIImage96.png

After that, selected emails will be in junk view. To delete permanently, repeat the process in Junk view.

To select all emails, click on the top checkbox near the Email column heading.



M2TIImage88.png

Also, email can be deleted by toggling Email Details and pressing Delete button



M2TIImage72.png

Manually assign Tariff Import Settings

It is possible to manually Assign Tariff Import Settings.

  1. Toggle Email Details
  2. Select Tariff Import Rules for specific attachment
  3. Press Assign Import settings



M2TIImage57.png

Retry Rules mapping

If email with multiple attachments has attachments with no import rules it is possible to retry rules mapping. It's simple, press Retry Rules mapping.



M2TIImage33.png

Note: It’s not possible to Retry Rules mapping if all email attachments will have at least one import rule assigned to it.

Attachment mapping status

Attachment mapping status(Status column in Emails list) indicates if Tariff Import Rules mapping was successful or not.



M2TIImage90.png

Import Rules Found - attachment was mapped successfully with at least one of the Tariff Import Rules. Import Rules Not Found - attachment was not mapped to any of the Tariff Import Rules or there were no Tariff Import Rules to map. Invalid Effective Date/Format for Tariff Import Rules - Invalid attachment date or it’s format compared to Tariff Import Rules settings. Numbers after status text represent Tariff Import Rules IDs.

Email Example

Email can have one or multiple attachments. Every attachment will be processed individually. Attachment can have one or multiple Import Jobs which are dependent on the number of mapped Tariff Import Rules.



M2TIImage61.png

If there are multiple Tariff Jobs they will be shown as a toggle list. To toggle the list, simply press the arrow button next to Jobs list caption.



M2TIImage87.png

To see Job details click on any Import Job. Page will be redirected to the Tariff Jobs list.



M2TIImage62.png

Examples

Rate upload from TATA

1. Analyze the tariff:

M2TIImage105.png

2. Create template for a tariff in BILLING → Tariff import → Import Rules → Templates

M2TIImage81.png

3. Select appropriate fields according to the tariff cells:

M2TIImage22.png

4. Create Rate Import Rules in BILLING → Tariff import → Import Rules → Rate Import Rules

M2TIImage54.png

5. Fill cells according your needs:

M2TIImage80.png

6. Create Tariff Import Rules in BILLING → Tariff import → Import Rules

M2TIImage14.png

7. Fill required cells:

M2TIImage111.png

8. Check the Inbox for a new email at Billing → Tariff Import → Inbox

9. Enter the mail, select Tariff Import Rules and press Assign Import Settings to continue the import:

M2TIImage18.png

10. Tariff job is created now, you can see it in BILLING → Tariff Import → Jobs

M2TIImage102.png

11. Job is now waiting for confirmation. You can view analysis before confirming:

M2TIImage45.png

12. Point to the line you want to analyze to see more information:

M2TIImage35.png

13. If you are satisfied, press confirm to finish the import procedure, otherwise cancel it.

M2TIImage1.png

14. If you pressed confirm, you will see that job was successfully confirmed:

M2TIImage77.png

15. Go back to Jobs page in BILLING → Tariff Import → Jobs to see status of the Job:

M2TIImage79.png

16. Tariff import is finished. As a target tariff in this example was “SUPPLIER PREMIUM”, you can verify rates checking page BILLING → Tariffs.

M2TIImage12.png

Speedflow Tariff Import configuration



M2TIImage117.png

1. Create import template. Go to BILLING > Tariff Import > Import Rules > Templates. Click "ADD NEW TEMPLATE" at the top-right corner of the page.

Set "Name" to "speedflow". It can be any name, but "speedflow" will represent for which Provider tariffs this template is used. Set "Rate Sheet" to "1", as this tariff files contains only one worksheet. Set "Header Rows" to "22", as this tariff have 4 rows before actual rates data begins. Set "Refix/Code" to column "A"; Set "Destination/Zone" to column "B"; Set "Effective Date" to column "E"; Set "Effective Date Format" to "%d.%m.%y"; Set "Rate" to column "C"; Set "Minimal Time" to column "E" and value "1"; Set "Increment" to column "E" and value "2";



M2TIImage76.png

Click "Create" to save changes.

2. Create Rate Import Rules Go to BILLING > Tariff Import > Import Rules > Rate Import Rules Click "ADD NEW RATE IMPORT RULES" at the top-right corner of the page.

This example shows how to create Rate Import Rule which will inform admin about new rates above 1.0 in price and will reject rates where price is equal to 0.

Set "Name" to "speedflow"; Set "Max Rate" to value "1.0" and action "Alert"; Set "Zero Rate" to action "Reject Rate".



M2TIImage23.png

Click "Create" to save changes.

3. Create Tariff Import Rules Go to BILLING > Tariff Import > Import Rules Click "ADD NEW TARIFF IMPORT RULES" at the top-right corner of the page.

Set fields like this: "Name":"speedflow"; "Rate Import Rules":"speedflow"; "Target Tariff":"speedflow (EUR)". This is tariff assigned to speedflow Termination Point; "Import Type":"Add/Update"; "Import Template":"speedflow"; "Effective Date":"Template - Inline" "Mail From":"rates@speedflow.com". This is not actual email from which speedflow sends rates. Set correct email address from which you get tariffs. "File Name":"%.xls"



M2TIImage58.png

Click "Create" to save changes

At this point the system is ready to receive email from speedflow.

5. Once email from speedflow is received you will see a new Tariff Job waiting for confirmation in Billing > Tariff Import > Jobs.



M2TIImage108.png

Click "ANALYSIS" to see rates to be imported. By default, the ANALYSIS page does not show new rates. Click "REFINE RESULTS", uncheck "Show Only Rejected" and click REFINE to see all received rates.

Click "CONFIRM" at the top-right corner of the page to proceed with import of rates.



M2TIImage89.png

In BILLING > Tariffs List, Tariff Import is completed and new Rates are now visible.



M2TIImage121.png

Step by step example for VoiceTrading Tariff

1. Review VoiceTrading XLSX tariff structure



M2TIImage95.png



M2TIImage83.png

We can see that XLSX document consists of two Worksheets: Worksheet 1 (Rates) and Worksheet 2 (Numberplan). 2. Go to BILLING > Tariff Import > Import Rules > Templates to create a new Template



M2TIImage85.png

3. Configure Template for VoiceTrading XLSX structure



M2TIImage92.png

As you can see, there are two tabs in this page: Primary Data and Secondary Data. Do not confuse them with Worksheets, they are not related. Some Providers do not want to exceed a certain limit of rows in XLSX file, so data is placed in two regions on the same row(s). Let’s review screenshot below:



M2TIImage66.png

Green square corresponds to Primary Data, while Blue square corresponds to Secondary Data. They both contain the same information: Destination name, Prefix and Rate.

Lets go step by step and configure a Template for Primary Data:



M2TIImage4.png

Rate Sheet - 2. VoiceTrading XLSX file has two sheets, so set to 2 Header Rows - 0. VoiceTrading XLSX file has no header rows (data starts at 1st row)



M2TIImage3.png

Prefix/Code - B. Prefix is in Cell B for Primary Data. Destination/Zone - A. Destination is in Cell A for Primary Data. Connection Fee - Empty. There is no Connection Fee cell in VoiceTrading XLSX.



M2TIImage115.png

To better understand Effective Date mapping, let’s review both Worksheets again:



M2TIImage27.png



M2TIImage48.png



M2TIImage8.png


Effective Date is on Worksheet 1 (Rates) and on Column D. So we select 1 and D on the first and second dropdown accordingly.



M2TIImage86.png

Now we need to map Worksheet 1 (Rates) where Effective Date is defined with Worksheet 2 (Numberplan), where Rates and Prefixes are defined. We can do this by Destination Column, which is A in Worksheet 1 (Rates) and also A on Worksheet 2 (Numberplan). So we select A on 3rd and 4th Dropdown accordingly.



M2TIImage39.png

Effective Date Format for VoiceTrading is in YYY.MM.DD, so we select accordingly.



M2TIImage125.png

Rate - C. Prefix is in Cell C for Primary Data. There are no Minimal Time and Increment cells in VoiceTrading XLSX.

Let’s configure Secondary Data now:



M2TIImage24.png

Rate Sheet, Header Rows and Effective date will be the same as on Primary data.



M2TIImage25.png

Prefix/Code - F. Prefix is in Cell F for Secondary Data. Destination/Zone - E. Destination is in Cell E for Secondary Data. Connection Fee - Empty. There is no Connection Fee Cell in VoiceTrading XLSX.



M2TIImage30.png

To better understand Effective Date mapping, let’s review both Worksheets again:



M2TIImage27.png



M2TIImage53.png



M2TIImage119.png

Effective Date is on Worksheet 1 (Rates) and on Column D. So we select 1 and D on the first and second dropdown accordingly.



M2TIImage98.png

Now we need to map Worksheet 1 (Rates) where Effective Date is defined with Worksheet 2 (Numberplan), where Rates and Prefixes are defined. We can do this by Destination Column, which is A in Worksheet 1 (Rates) and E on Worksheet 2 (Numberplan). So we select A on 3rd dropdown and E on 4th dropdown accordingly.



M2TIImage109.png

Once configuration is complete, press CREATE button to create a new Template.

4. Go to BILLING > Tariff Import > Import Rules > Rate Import Rules to create Rate Import Rules



M2TIImage91.png

5. Configure Rate Import Rules according to your specifications



M2TIImage2.png

In this example we changed these values: Max Increase: 50%. If rate increase is higher than 50%, such rate will be automatically rejected. Max Decrease: 50%. If rate increase is higher than 50%, such rate will be automatically rejected. Max Rate: 1.99. If rate is higher than 1.99, such rate will be automatically rejected.

6. Go to BILLING > Tariff Import > Import Rules to create Tariff Import Rules



M2TIImage84.png

7. Configure Tariff Import Rules according to your specifications



M2TIImage99.png

The minimum what you should change from default: Rate Import Rules: set to Import Rules created in step 5. Target Tariff: set to Tariff you want to import rates to. Import Template: set to Template created and configured in step 3. Email/Attachment Validation: set to valid date for your Provider. Data in the screenshot is only an example and does not represent real values for VoiceTrading Provider.

8. Once email(s) will be received, you will see them in BILLING > Tariff Import > Inbox If Tariff Import Rules have been correctly entered in the previous step, Import Job will be created automatically.



M2TIImage13.png

9. Click on the Import Job link. This will redirect you to the Tariff Jobs page.



M2TIImage123.png

Here we can see that Tariff Job is waiting for confirmation and that there are 1178 rates to be updated and 0 rejected rates.

10. Click on ANALYSIS link to view detailed analysis



M2TIImage74.png

By default, Tariff Job Analysis page shows only rejected Rates. There are no rejected rates in this case, so we can press CONFIRM button.

11. Go to BILLING > Tariff Import > Jobs to check Tariff Jobs status again. We can see that status is Imported.

12. Process is completed. Let’s go to Billing > Tariffs to confirm this



M2TIImage75.png

We can see that 1178 rates have been imported to Tariff Supplier VoiceTrading.

Retrieved from "https://wiki.kolmisoft.com/index.php?title=Automatic_Tariff_Import&oldid=25457"