Kolmisoft Wiki - User contributions [en]

ATI - Deutsche Telekom example

2025-10-24T08:52:02Z

Valerij: /* Step-by-step example for Deutsche Telekom Tariff */

== Step-by-step example for Deutsche Telekom Tariff ==

1. Review Deutsche Telekom XLSX tariff structure

 
[[File:DT_RS1.png]]
 

 
[[File:DT_RS2.png]]
 

 
[[File:DT_RS3.png]]
 

We can see that the XLSX document consists of three Worksheets: Worksheet 1 (DTGC Hubbing Rates), Worksheet 2 (Origin Rates) and Worksheet 3 (Origin Dialcodes).

2. Go to BILLING > Tariff Import > Import Rules > Templates to create a new Template

 
[[File:M2TIImage85.png]]
 

3. Configure Template for Deutsche Telekom XLSX structure

 
[[File:ATI-DeutscheTelekom1.png]]
 

As you can see, there are two tabs on this page: Primary Data and Secondary Data.
Please do not confuse them with Worksheets; they are not related.

For Primary data, we will use Worksheet 1 (DTGC Hubbing Rates)

 
[[File:CT_DT1.png]]
 

Destination/Zone Column A, Prefix/Code Column B, Effective Date Column D

 
[[File:CT_DT2.png]]
 

Rate Column C , Minimal Time second value from 0/1/1 and Increment third value from 0/1/1 in the Column E

 
[[File:CT_DT3.png]]
 

Secondary data will be imported from worksheet 2 (Origin Rates) and worksheet 3 (Origin Dialcodes).

The structure is a bit complicated because the rate of destination is based on the country of origin.

Will be created [[M4_Rates#CLI_Groups|CLI Groups]] ,

The name of the groups is Worksheet 2 Column C, which is mapped with column A in Worksheet 3

and a list of the prefixes belonging to the Cli Groups is in Column A of Worksheet 3

 
[[File:CT_DT4.png]]
 

Explanation:

Rate sheet 2, Headers rows 14 on worksheet 2, Prefix/Code Column B, Destination/Zone Column A

CLI Group Column C and mapped with Worksheet 3, Headers rows 14 Columns A and B.

Effective Date Column E

Rate Column D , Minimal Time and Increment Column F

 
[[File:CT_DT5.png]]
 

Everything is ready and you can press button to save this template

 
[[File:M2TIImage109.png]]
 <be>

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

 
[[File:M2TIImage91.png]]
 

5. Configure Rate Import Rules according to your specifications

 
[[File:CT DT6.png]]
 

In this example we changed these values:

'''Max Increase''': 50%. If the rate increase is higher than 50%, such a rate will be automatically rejected.

'''Max Decrease''': 50%. If the rate increase is higher than 50%, such a rate will be automatically rejected.

'''Max Rate''': 1.99. If the rate is higher than 1.99, such a rate will be automatically rejected.

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

 
[[File:M2TIImage84.png]]
 

7. Configure Tariff Import Rules according to your specifications

 
[[File:CT DT7.png]]
 

The minimum that you should change from default:

'''Rate Import Rules''': set to Import Rules created in step 5.

'''Target Tariff''': set to the Tariff you want to import rates into.

'''Import Template''': set to Template created and configured in step 3.

'''Email/Attachment Validation''': set to valid date for your Provider. The data in the screenshot is only an example and does not represent real values for Deutsche Telekom Provider.

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

 
[[File:CT DT8.png]]
 

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

 
[[File:CT DT9.png]]
 

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

10. Click on the ANALYSIS link to view a detailed analysis

 
[[File:CT DT10.png]]
 

By default, the Tariff Job Analysis page shows only rejected Rates. There are rejected rates, because Max rate in the Rate import Rule is configured to 1.99

 
[[File:CT DT11.png]]
 

in this case, so we can press the CONFIRM button to import all not rejected rates or decide to import them all by reconfiguring the rate import templates and repeat the process.
We decide to import all non-rejected rates and press CONFIRM

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

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

 
[[File:CT DT12.png]]
 

We can see that 217681 (217883 total - 202 rejected) rates have been imported to Tariff '''Deutsche Telekom'''.

ATI - Deutsche Telekom example

2025-10-24T08:50:54Z

Valerij: /* Step-by-step example for Deutsche Telekom Tariff */

== Step-by-step example for Deutsche Telekom Tariff ==

1. Review Deutsche Telekom XLSX tariff structure

 
[[File:DT_RS1.png]]
 

 
[[File:DT_RS2.png]]
 

 
[[File:DT_RS3.png]]
 

We can see that the XLSX document consists of three Worksheets: Worksheet 1 (DTGC Hubbing Rates), Worksheet 2 (Origin Rates) and Worksheet 3 (Origin Dialcodes).

2. Go to BILLING > Tariff Import > Import Rules > Templates to create a new Template

 
[[File:M2TIImage85.png]]
 

3. Configure Template for Deutsche Telekom XLSX structure

 
[[File:ATI-DeutscheTelekom1.png]]
 

As you can see, there are two tabs on this page: Primary Data and Secondary Data.
Please do not confuse them with Worksheets; they are not related.

For Primary data, we will use Worksheet 1 (DTGC Hubbing Rates)

 
[[File:CT_DT1.png]]
 

Destination/Zone Column A, Prefix/Code Column B, Effective Date Column D

 
[[File:CT_DT2.png]]
 

Rate Column C , Minimal Time second value from 0/1/1 and Increment third value from 0/1/1 in the Column E

 
[[File:CT_DT3.png]]
 

Secondary data will be imported from worksheet 2 (Origin Rates) and worksheet 3 (Origin Dialcodes).

The structure is a bit complicated because the rate of destination is based on the country of origin.

Will be created [[M4_Rates#CLI_Groups|CLI Groups]] ,

The name of the groups is Worksheet 2 Column C, which is mapped with column A in Worksheet 3

and a list of the prefixes belonging to the Cli Groups is in Column A of Worksheet 3

 
[[File:CT_DT4.png]]
 

Explanation:

Rate sheet 2, Headers rows 14 on worksheet 2, Prefix/Code Column B, Destination/Zone Column A

CLI Group Column C and mapped with Worksheet 3, Headers rows 14 Columns A and B.

Effective Date Column E

Rate Column D , Minimal Time and Increment Column F

 
[[File:CT_DT5.png]]
 

Everything is ready and you can press button to save this template

 
[[File:M2TIImage109.png]]
 <be>

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

 
[[File:M2TIImage91.png]]
 

5. Configure Rate Import Rules according to your specifications

 
[[File:CT DT6.png]]
 

In this example we changed these values:

'''Max Increase''': 50%. If the rate increase is higher than 50%, such a rate will be automatically rejected.

'''Max Decrease''': 50%. If the rate increase is higher than 50%, such a rate will be automatically rejected.

'''Max Rate''': 1.99. If the rate is higher than 1.99, such a rate will be automatically rejected.

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

 
[[File:M2TIImage84.png]]
 

7. Configure Tariff Import Rules according to your specifications

 
[[File:CT DT7.png]]
 

The minimum that you should change from default:

'''Rate Import Rules''': set to Import Rules created in step 5.

'''Target Tariff''': set to the Tariff you want to import rates into.

'''Import Template''': set to Template created and configured in step 3.

'''Email/Attachment Validation''': set to valid date for your Provider. The 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 the Tariff Import Rules have been correctly entered in the previous step, the Import Job will be created automatically.

 
[[File:CT DT8.png]]
 

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

 
[[File:CT DT9.png]]
 

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

10. Click on the ANALYSIS link to view a detailed analysis

 
[[File:CT DT10.png]]
 

By default, the Tariff Job Analysis page shows only rejected Rates. There are rejected rates, because Max rate in the Rate import Rule is configured to 1.99

 
[[File:CT DT11.png]]
 

in this case, so we can press the CONFIRM button to import all not rejected rates or decide to import them all by reconfiguring the rate import templates and repeat the process.
We decide to import all non-rejected rates and press CONFIRM

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

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

 
[[File:CT DT12.png]]
 

We can see that 217681 (217883 total - 202 rejected) rates have been imported to Tariff '''Deutsche Telekom'''.

Automatic Tariff Import

2025-10-24T07:18:49Z

Valerij: /* Detailed Import Examples */

<big><big>NOTICE: this is an add-on. Its functionality is identical in M4/MOR softswitches. M4 screenshots are used in this guide. It looks similar in MOR.</big></big>

 
<html>
<div align="center">
<table cellspacing="0" cellpadding="0">
<tr>
<td>
<iframe width="640" height="360" src="https://www.youtube.com/embed/ZjQxI5vx9bI" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
</td>
<td>
   
</td>
<td>
<iframe width="640" height="360" src="https://www.youtube.com/embed/GGuuj7sLHm8" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
</td>
</tr>
</table>
</div>
</html>
 

= Introduction =

An important task is the timely management of Provider Tariff Plans. While business practices differ somewhat, usually, 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 be followed by updated notification spreadsheets. These will be received at regular agreed-upon intervals, identifying amendments to the previous prices. The format and content of these notifications are not standardized and consequently differ from one Provider to another. This information is vital, from a commercial and financial management point of view. It is therefore important 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 analyze Tariff/Rating Plan updates received from partners, in order to ensure that any errors are noticed early and resolved before causing Billing disputes.

Doing all this by hand is a long and tedious process. It is possible to automate it with a human-in-between approach that requires confirming the final import step, to avoid potential misconfiguration problems.

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

 
= Workflow schema =

The workflow can be visualized using the following schema:

 
[[File:M2TIImage15.png]]
 

As can be seen, there are two components to this process:

# The configuration component, which is done once for each tariff file received from the provider
# The actual working component, where an automated process takes care of the tariff import, based on the settings configured in the first component

 

=Initial (Manual) Setup for Automation=

This sequence of steps is necessary to configure the automatic import of the concrete tariff format received from any provider. Once configured, it can be used many times thereafter, until the provider decides to change the format.

==Rate file received from Provider examined and analyzed==

As every Provider sends different format files, their structure should be analyzed and necessary fields marked for Template creation.

XLSX file can be compressed to RAR or ZIP archive.

==XLSX Transformation Template created==

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

==Rate Import Rules created==

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 fewer than seven days' notice) and then either alerting the Administrator or rejecting the rate.

==Tariff Import Rules created==

Tariff Import Rules assign each received Email/Attachment to the correct Provider/Tariff with appropriate Rate Import Rules, Notifications, and a Template to properly convert received Tariffs and upload rates to the System.

==Email Notifications configured==

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

* Tariff is received
* The tariff is analyzed and waiting for confirmation before the import
* The tariff is imported or rejected
* Others

 
=Normal (Automatic) Workflow=

Based on preconfigured settings in the first step, the Automatic Tariff Import could be carried out by using all the defined settings.

==Rate file received from Provider via email==

One mailbox, usually named rates@companyname.com, is monitored by the system and all received emails are analyzed.

==Email/Attachments analyzed by Tariff Import Rules==

When an email with a tariff attachment comes in, it is checked for address, email subject, attachment name, and other parameters to properly map the received Tariff file with the following procedures.

==Rate Plan converted and analyzed==

Based on the Tariff Import Rules, the Tariff file is converted to a standardized format (a process called Normalization) and then analyzed. During analysis, the Rate Import Rules are applied and necessary actions are taken based on the Rules.

==Notification for Review sent==

If there are no major problems, the responsible person receives an email saying that the Tariff is converted with such and such notices or warnings, and waits for approval. It is possible to skip this step with 100% automation, but it is usually not recommended, due to possible errors that human intervention could help to avoid.

==Import Denied==

The responsible person can deny the tariff import, based on the information received. The analysis could identify errors that can't be ignored or some other issues.

==Import Accepted==

If there are no major issues, the responsible person accepts the tariff import and the system is notified to continue the import.

==Tariff Imported==

The actual tariff import takes place, and old rates are replaced by new ones. All of the preparation work culminates in this step.

 
= How it is implemented in the system =

 
[[File:M2TIImage56.png]]
 
[[File:M2TIImage20.png]]
 

Tariff Jobs table columns:
* Id: Tariff job identification number.
* Created at: Date when the job was created.
* Status: The state of the import process. Can be:
** Assigned - import process was started.
** Converting - rates are being converted.
** Failed Conversion - there was an error in rate conversion.
** Converted - rates were successfully converted.
** Importing into Temporary Table - rates are being uploaded from the attachment to the system.
** Failed to Import into Temporary Table - there was an error in the rates uploading process.
** Imported into Temporary Table - rates have been loaded into the system.
** Analyzing - rates are being analyzed.
** Failed Analysis - there was an error in the analysis. By lowering over Failed Analysis you can get more details.
***rate_with_multiple_ratedetails_found refers to Rate Details (where you can set rate for peak/off-peak hours) being used on one or more rates on target tariff. Such rates are not supported by a tariff import.
** 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 canceled by admin.
** Imported - rates were successfully imported.
* Import Rules - the name of the Tariff Import Rule used in import and the link to its edit view.
* Rate Import Rules - the 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 - the name of the attachment that was used in import.
* Import Type - the 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 analyzed jobs) - link to analysis details.
* Delete - button to delete one job.

Note: yellow-colored Tariff Jobs rows show that they are waiting for confirmation.
 
Retry Failed Conversion

If the conversion failed, click the button to retry.
 
[[File:Retry failedconversion.png]]
 

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. The default value is 30 days. It means that Tariff Jobs which were created before 30 days or more will be deleted.

 
[[File:M2TIImage9.png]]
 

= Analysis =

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

On the top right corner there are buttons:

 
[[File:M2TIImage101.png]]
 
* To CSV: export analysis to CSV file.
 

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

 
[[File:M2TIImage78.png]]
 

* Canceled: tariff import will be canceled.

 
[[File:M2TIImage69.png]]
 

* Schedule: Schedule Import from. Rates will be imported by the selected time if there are no Jobs waiting for the same tariff.

 
[[File:M2TIschedule.png]]
 

* Refine Results: allows filtering 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.

 
[[File:M2TIImage94.png]]
 

Tariff Job Analysis table:

 
[[File:M2TIImage31.png]]
 

Table columns:
* Prefix: prefix on which the rate will be applied.
* Destination: destination name.
* Rate: rate value.
* Connection fee: [[How the Connection fee works]]
* Increment: [[What is increment]]
* Minimal time: [[What is increment]]
* Effective from: [[Rate Effective From]]
* Blocked: Rate blocked Yes/No
* Deleted: Rate deleted Yes/No

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

 
[[File:M2TIImage32.png]]
 

Row colors:
* If the row is red-colored, the rate is not importable. Non-Importable Reason can be found by moving the 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 the analysis is being executed on several jobs which include 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.

= 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:

 
[[File: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:

 
[[File:M2TIImage113.png]]
 
[[File:M2TIImage113_sec.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
: The name of the Template can be set to any, but for better management suggested to be the name of Provider.

CSV Column Separator
: Column Separator must be chosen from the imported CSV file.

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.
: [[File:M2TIImage73.png]]
: Accordingly, Template’s Rate Sheet value must be set to 2.
: [[File: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 the 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.
: [[File: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.
: [[File:M2TIImage124.png]]
 

: Example 2:
: Provided Tariff/Rating Plan below meaningful data starts from Row 23.
: [[File: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.
: [[File: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 do not require additional configuration.

: Example 1:
: Provided Tariff/Rating Plan below has only one Prefix column which is B.
: [[File:M2TIImage68.png]]
: Accordingly, Template’s Prefix/Code value must be set to B.
: [[File:M2TIImage42.png]]
 

: Example 2:
: Provided Tariff/Rating Plan below has two Prefix columns which are C and D.
: [[File:M2TIImage67.png]]
: Accordingly, Template’s Prefix/Code values must be set to C and D in exact order.
: [[File: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 needs 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 on either side of the second box can be used to format the Zone name.
: Example: A Rate Plan includes the following information:
: [[File:M2TIImage59.png]]
: The Zone import is configured with Columns A and B and the separator fields with space “Column B”, as shown:
: [[File: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.
: [[File:M2TIImage7.png]]
: Accordingly, Template’s Destination/Zone value must be set to A.
: [[File:M2TIImage21.png]]
 

: Example 2:
: Provided Tariff/Rating Plan below has two Destination/Zone columns which are A and B.
: [[File:M2TIImage34.png]]
: Accordingly, Template’s Destination/Zone values must be set to A and B in exact order.
: [[File:M2TIImage5.png]]
 
:CLI Group identifies the Column in the Excel sheet that contains the CLI Groups.
:Example of a CLI Group:
:[[File:Cli Group auto import example.png]]

If the CLI Group with mapping is selected (the second radio button), then five settings must be set (all are required).

* First dropdown, CLI Group column (A, B, C, ...).
* Second dropdown rate sheet number (1, 2, 3, ...) in which the mapping is located.
* Third input field, where you set how many header lines to skip in the mapping sheet.
* Fourth dropdown CLI Group mapping name column (A, B, C, ...).
* Fifth dropdown CLI Group mapping prefix column (A, B, C, ...).
 

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 the first Worksheet there are Destinations with the Effective Date, and in the second Worksheet - Destination, Prefix, and 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.
: [[File:M2TIImage29.png]]
: Accordingly, Template’s Effective Date value can be set to F.
: [[File:M2TIImage10.png]]
 

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

: Example 3:
: Provided Tariff/Rating Plan below in the first Worksheet shows the Destination (Column A) and Effective Date (Column D).
: In the second Worksheet, there is Destination (Column A), Prefix (Column B), and Rate/Price (Column C).
: [[File:M2TIImage112.png]]
: The first dropdown configures from which Worksheet takes Effective Date data (which in this example’s case is Worksheet 1 (Rates)).
: The second dropdown configures the Effective Date column from the secondary Worksheet.
: The third dropdown configures by which the secondary Worksheet column does the mapping with the primary Worksheet (which in this example’s case is Destination from Worksheet 1 (Rates) Column A).
: The fourth dropdown configures by which the primary Worksheet column does the mapping with the 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.
: [[File:M2TIImage100.png]]
 

Effective Date Format
: After selecting the Column, the Format field is used to specify the format of the information held in the Rate Plan.
: Some suggested formats already exist in the dropdown, which can be selected or used as an example to create a custom one if it is not present in the dropdown.

: Example:
: Provided Tariff/Rating Plan below has an Effective Date column which is F.
: [[File:M2TIImage29.png]]
: Accordingly, Template’s Effective Date Format value can be selected from the dropdown with suggested formats.
: [[File: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.
: [[File:M2TIImage60.png]]
: Accordingly, Template’s Rate value must be set to E.
: [[File:M2TIImage51.png]]
 

Minimal Time
: Minimal Time can be set to match the Tariff/Rating Plan Minimal Time column.

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

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

Increment
: Increment can be set to match the Tariff/Rating Plan Increment column.

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

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

Deleted and Blocked rates.
: Deleted and Blocked rates can be chosen in the same way in Deleted Rates and Blocked Rates columns.
: [[File:Blocked and deleted rates.png]]

If Providers send a Rates Sheet with the price "-1", which means, that this Destination is blocked, the system will block a route to particular Destinations.
 

===Exceptions===

: Exceptions allow additional data manipulation when specific rules are defined separately in the provided Tariff/Rating Plan. Exceptions are set in Templates (BILLING > Tariff Import > Import Rules > Templates > EDIT). The Mask field allows defining which Destinations/Zones should have exceptional values set. % can be used as a wild card to match part of a text in Destination/Zone. Mask looks for Destination/Zone values as they are within the tariff sheet file. See examples below:

: Example 1:
: [[File:M2TIImage41.png]]
: [[File:M2TIImage6.png]]
 

: Example 2:
: [[File:M2TIImage16.png]]
: [[File:M2TIImage19.png]]

Exceptions fields are '''case-INsensitive'''. This means that, if the file contains MEXICO, you can create a template as meXIco, and the system will still find and replace it.
Do not put % in front of the text, it already acts like there would be a wild card by default.
 

= 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 the 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.

 
[[File:M2TIImage70.png]]
 

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

 
[[File: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 set action will be taken.
: Value can be set to any integer between 0 and 30 which represents the 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 set will be taken.
: Value can be set to any integer between 0 and 30 which represents the 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 set action will be taken.
: Value can be set to any integer between 0 and 30 which represents the 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 set action will be taken.
: Value can be set to any integer between 0 and 365 which represents the 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 set action will be taken.
: Value can be set to any integer between 0 and 365 which represents the day(s).

Max Increase
: If updating the existing Rate, whose Rate (Price) increased more by percentages than set in Rule, then set 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 the existing Rate, whose Rate (Price) decreased more by percentages than set in Rule, then set 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 set 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 the existing or creating a new Rate, whose Rate (Price) is zero, then a set 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 the set Rule, then set 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 a comma (,).
:: Must not have any spaces.
:: Must not have leading zeros, unless it is only a zero.
:: Limited to max 10 values.
: 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 the set Rule, then set action will be taken.
: If updating existing or creating a new Rate, whose new Min Time value does not match any from the set Rule, then set 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 a comma (,).
:: Must not have any spaces.
:: Must not have leading zeros, unless it is only a zero.
:: Limited to max 10 values.
: Examples:
:: 0,100,60,7200
:: 0,1,60
:: 1,60
:: 1

= Tariff Import Rules =

 
[[File: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 you 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:

* '''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. The target tariff in M4 can be Buy or Sell tariff. The target tariff in M4 can be any Wholesale tariff (Provider and Users).
* '''Import Type''' - you can choose between 2 options: Add/Update and Replace All Rates
** '''Add/Update''' - new rates will be added from the file and existing ones will be updated
** '''Replace All Rates''' - existing rates will be deleted and new ones imported from the file
* '''Delete rates if not present''' - delete Rates for Prefixes that are not present in the imported file (available only with Import Type: Add/Update)
* '''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's Subject
** '''Email Body''' - Use Effective Date from Email's Body ([[Effective Date from Email's Body Example|Example]])
** '''File Name''' - Use Effective Date from File (Attachment) Name
* '''Prefix''' - the text after which the Effective Date is specified.
* '''Date Format''' - date format in which Effective Date is specified. See available formats [[Automatic_Tariff_Import#Date_Format | here]].
* '''Time Zone''' - used to convert Effective Date file timezone to system timezone.
* '''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 a 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

To edit the above parameters on Import Rules, which were previously used, you have to delete Processes related to those rules.

 
== 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. The first four are for email and the fifth is for the attachment.

 
[[File: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. An example is shown in the image below
 
[[File:M2TIImage110.png]]
 

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

 

== Notifications ==

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

 
[[File:M2TIImage106.png]]
 

Follow the steps below to create and configure a notification Template.
# Create an Email Template. Manual on how to create Email Templates can be found here: [[M4 Emails]]. Additional Email variables: tariff_job_id, tariff_job_analysis_url, tariff_job_tariff_id, tariff_job_tariff_name.
# Select created Email Template from the Email Template dropdown. The subject field will fill in automatically. [[File:M2TIImage112.png]]
# Enter Email Address(es) in the Recipients field. Multiple Email Addresses must be separated by ;. [[File:M2TIImage116.png]]
# Repeat the same process for desired Import Triggers. [[File:M2TIImage36.png]]

 
== Date Format ==

Data Format should be written in symbolic form, constructed from various elements. Could look like this %d-%m-%Y and this covers such date as 01-02-2021 (day-month-year). There are several ways how to describe it:

'''YEAR'''
* %Y - year (2021)
* %y - last two digits of the year (00..99)

'''MONTH'''
* %m - month (01..12)
* %b - locale's abbreviated month name (e.g., Jan)
* %B - locale's full month name (e.g., January)

'''DAY'''
* %d - day of the month (e.g., 01)

 

= Tariff Link Attachment Rules =

Sometimes providers send emails with the URL to download the rates list instead of the rates file.

Tariff Link Attachment Rules are for attachment extraction from the link only.

 
[[File:M2TariffLinkAttachmentRules.png]]
 

Here you can list, edit, or create new rules.

To change rules priority just drag the rule up or down.

 
[[File:M2TariffLinkAttachmentRulesList.png]]
 

Rule structure:

 
[[File:M2TariffLinkAttachmentRuleStructure.png]]
 

* '''Name''' - Rules name.
* '''String Start''' - String Start defines where to start to search for attachment links in email text or a web page.
'''Value:''' search word or phrase. Leave empty to start a search from the beginning.
Example (String Start - '''can be downloaded'''):
Hello,
Attachments '''can be downloaded''' from here: http://www.example.com
Thank you for using our services
* '''String End''' - String End defines where to stop to search for attachment links in email text or a web page.
'''Value:''' search word or phrase. Leave it empty to search till the end.
Example (String End - '''Thank you'''):
Hello,
Attachments can be downloaded from here: http://www.example.com
'''Thank you''' for using our services
* '''Link Pattern''' - Link pattern is used to search for particular links.
'''Value:''' link or its fragment.
Examples:
https://www.example.com
https://www.example.com/example
example.com/example

= 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 Provider's 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:

 
[[File: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 email attachments had been assigned to at least one of the 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

 
[[File:M2TIImage55.png]]
 

# Email column - Sender name, email address, and subject
# Email details button - toggles email details
# Attachments list
# Attachments file type list
# Import jobs associated with attachments
# Import jobs button - toggles import jobs(if there are more than one tariff import job)
# Attachment mapping status
# Processing note
# Inbox view tab
# Completed view tab
# Junk view tab
# Select all emails checkbox
# Select a single email checkbox
# Delete Selected emails
# 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.
* The Last Section represents three function buttons.

 
[[File:M2TIImage47.png]]
 

== Filter Emails ==

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

 
[[File:M2TIImage37.png]]
 

There are four fields:
* Period - the period between emails were received.
* From - email or sender name.
* Subject - email subject.
* Message - email message.

 
[[File:M2TIImage71.png]]
 

Press Refine button to see the results.

 
[[File:M2TIImage120.png]]
 

Press clear to clear fields.

 
[[File:M2TIImage107.png]]
 

== Delete Emails ==

To delete emails, select emails by clicking their checkboxes.

 
[[File:M2TIImage38.png]]
 

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

 
[[File:M2TIImage96.png]]
 

After that, selected emails will be in a 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.

 
[[File:M2TIImage88.png]]
 

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

 
[[File:M2TIImage72.png]]
 

== Manually assign Tariff Import Settings ==

It is possible to manually Assign Tariff Import Settings.
# Toggle Email Details
# Select Tariff Import Rules for specific attachment
# Press Assign Import settings

 
[[File:M2TIImage57.png]]
 

== Retry Rules mapping ==

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

 
[[File:M2TIImage33.png]]
 

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

== Attachment mapping status ==

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

 
[[File: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 its format compared to Tariff Import Rules settings. Numbers after the 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.

 
[[File: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 the Jobs list caption.

 
[[File:M2TIImage87.png]]
 

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

 
[[File:M2TIImage62.png]]
 

= Detailed Import Examples =
* [[ATI - Voicetrading example]]
* [[ATI - Tata example]]
* [[ATI - Speedflow example]]
* [[ATI - Deutsche Telekom example]]

 

= Template Examples =
* [[ATI - IDT Express Template]]
* [[ATI - Voicetrading Template]]
* [[ATI - Voxbeam Template]]
* [[ATI - Tata Template]]
* [[ATI - Deutsche Telekom Template]]

 

= See also =
* [[M4 Tariffs]]
* [[Automatic Tariff Import - Email is not taken from the mailbox]]

ATI - Deutsche Telekom example

== Step-by-step example for VoiceTrading Tariff ==

1. Review VoiceTrading XLSX tariff structure

 
[[File:M2TIImage95.png]]
 

 
[[File:M2TIImage83.png]]
 

We can see that the 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

 
[[File:M2TIImage85.png]]
 

3. Configure Template for VoiceTrading XLSX structure

 
[[File:M2TIImage92.png]]
 

As you can see, there are two tabs on 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 the XLSX file, so data is placed in two regions on the same row(s). Let’s review the screenshot below:

 
[[File:M2TIImage66.png]]
 

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

Let's go step by step and configure a Template for '''Primary Data''':

 
[[File: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)

 
[[File:M2TIImage3.png]]
 

'''Prefix/Code - B'''. The 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.

 
[[File:M2TIImage115.png]]
 

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

 
[[File:M2TIImage27.png]]
 

 
[[File:M2TIImage48.png]]
 

 
[[File:M2TIImage8.png]]
 

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

 
[[File:M2TIImage86.png]]
 

Now we need to map Worksheet 1 (Rates) where the 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 in Worksheet 2 (Numberplan). So we select '''A''' on the 3rd and 4th Dropdown accordingly.

 
[[File:M2TIImage39.png]]
 

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

 
[[File: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:

 
[[File:M2TIImage24.png]]
 

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

 
[[File: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.

 
[[File:M2TIImage30.png]]
 

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

 
[[File:M2TIImage27.png]]
 

 
[[File:M2TIImage53.png]]
 

 
[[File:M2TIImage119.png]]
 

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

 
[[File:M2TIImage98.png]]
 

Now we need to map Worksheet 1 (Rates) where the 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 in Worksheet 2 (Numberplan). So we select '''A''' on the 3rd dropdown and '''E''' on the 4th dropdown accordingly.

 
[[File:M2TIImage109.png]]
 

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

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

 
[[File:M2TIImage91.png]]
 

5. Configure Rate Import Rules according to your specifications

 
[[File:M2TIImage2.png]]
 

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

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

 
[[File:M2TIImage84.png]]
 

7. Configure Tariff Import Rules according to your specifications

 
[[File:M2TIImage99.png]]
 

The minimum that you should change from default:
'''Rate Import Rules''': set to Import Rules created in step 5.
'''Target Tariff''': set to the Tariff you want to import rates into.
'''Import Template''': set to Template created and configured in step 3.
'''Email/Attachment Validation''': set to valid date for your Provider. The 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.

 
[[File:M2TIImage13.png]]
 

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

 
[[File: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 the ANALYSIS link to view a detailed analysis

 
[[File:M2TIImage74.png]]
 

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

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

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

 
[[File:M2TIImage75.png]]
 

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

 
== See also ==
* [[Automatic Tariff Import]]
* [[ATI - Voicetrading Template]]

File:DT RS3.png

2025-10-23T06:19:16Z

Valerij:

File:DT RS2.png

2025-10-23T06:19:05Z

Valerij:

File:DT RS1.png

2025-10-23T06:18:54Z

Valerij:

ATI - Deutsche Telekom example

2025-10-23T06:18:38Z

Valerij:

ATI - Deutsche Telekom example

2025-10-23T06:00:03Z

Valerij: Created page with "== Step-by-step example for Deutsche Telekom Tariff == 1. Review Deutsche Telekom XLSX tariff structure"

== Step-by-step example for Deutsche Telekom Tariff ==

1. Review Deutsche Telekom XLSX tariff structure

Automatic Tariff Import

2025-10-23T05:56:21Z

Valerij: /* Template Examples */

<big><big>NOTICE: this is an add-on. Its functionality is identical in M4/MOR softswitches. M4 screenshots are used in this guide. It looks similar in MOR.</big></big>

 
<html>
<div align="center">
<table cellspacing="0" cellpadding="0">
<tr>
<td>
<iframe width="640" height="360" src="https://www.youtube.com/embed/ZjQxI5vx9bI" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
</td>
<td>
   
</td>
<td>
<iframe width="640" height="360" src="https://www.youtube.com/embed/GGuuj7sLHm8" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
</td>
</tr>
</table>
</div>
</html>
 

= Introduction =

An important task is the timely management of Provider Tariff Plans. While business practices differ somewhat, usually, 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 be followed by updated notification spreadsheets. These will be received at regular agreed-upon intervals, identifying amendments to the previous prices. The format and content of these notifications are not standardized and consequently differ from one Provider to another. This information is vital, from a commercial and financial management point of view. It is therefore important 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 analyze Tariff/Rating Plan updates received from partners, in order to ensure that any errors are noticed early and resolved before causing Billing disputes.

Doing all this by hand is a long and tedious process. It is possible to automate it with a human-in-between approach that requires confirming the final import step, to avoid potential misconfiguration problems.

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

 
= Workflow schema =

The workflow can be visualized using the following schema:

 
[[File:M2TIImage15.png]]
 

As can be seen, there are two components to this process:

# The configuration component, which is done once for each tariff file received from the provider
# The actual working component, where an automated process takes care of the tariff import, based on the settings configured in the first component

 

=Initial (Manual) Setup for Automation=

This sequence of steps is necessary to configure the automatic import of the concrete tariff format received from any provider. Once configured, it can be used many times thereafter, until the provider decides to change the format.

==Rate file received from Provider examined and analyzed==

As every Provider sends different format files, their structure should be analyzed and necessary fields marked for Template creation.

XLSX file can be compressed to RAR or ZIP archive.

==XLSX Transformation Template created==

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

==Rate Import Rules created==

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 fewer than seven days' notice) and then either alerting the Administrator or rejecting the rate.

==Tariff Import Rules created==

Tariff Import Rules assign each received Email/Attachment to the correct Provider/Tariff with appropriate Rate Import Rules, Notifications, and a Template to properly convert received Tariffs and upload rates to the System.

==Email Notifications configured==

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

* Tariff is received
* The tariff is analyzed and waiting for confirmation before the import
* The tariff is imported or rejected
* Others

 
=Normal (Automatic) Workflow=

Based on preconfigured settings in the first step, the Automatic Tariff Import could be carried out by using all the defined settings.

==Rate file received from Provider via email==

One mailbox, usually named rates@companyname.com, is monitored by the system and all received emails are analyzed.

==Email/Attachments analyzed by Tariff Import Rules==

When an email with a tariff attachment comes in, it is checked for address, email subject, attachment name, and other parameters to properly map the received Tariff file with the following procedures.

==Rate Plan converted and analyzed==

Based on the Tariff Import Rules, the Tariff file is converted to a standardized format (a process called Normalization) and then analyzed. During analysis, the Rate Import Rules are applied and necessary actions are taken based on the Rules.

==Notification for Review sent==

If there are no major problems, the responsible person receives an email saying that the Tariff is converted with such and such notices or warnings, and waits for approval. It is possible to skip this step with 100% automation, but it is usually not recommended, due to possible errors that human intervention could help to avoid.

==Import Denied==

The responsible person can deny the tariff import, based on the information received. The analysis could identify errors that can't be ignored or some other issues.

==Import Accepted==

If there are no major issues, the responsible person accepts the tariff import and the system is notified to continue the import.

==Tariff Imported==

The actual tariff import takes place, and old rates are replaced by new ones. All of the preparation work culminates in this step.

 
= How it is implemented in the system =

 
[[File:M2TIImage56.png]]
 
[[File:M2TIImage20.png]]
 

Tariff Jobs table columns:
* Id: Tariff job identification number.
* Created at: Date when the job was created.
* Status: The state of the import process. Can be:
** Assigned - import process was started.
** Converting - rates are being converted.
** Failed Conversion - there was an error in rate conversion.
** Converted - rates were successfully converted.
** Importing into Temporary Table - rates are being uploaded from the attachment to the system.
** Failed to Import into Temporary Table - there was an error in the rates uploading process.
** Imported into Temporary Table - rates have been loaded into the system.
** Analyzing - rates are being analyzed.
** Failed Analysis - there was an error in the analysis. By lowering over Failed Analysis you can get more details.
***rate_with_multiple_ratedetails_found refers to Rate Details (where you can set rate for peak/off-peak hours) being used on one or more rates on target tariff. Such rates are not supported by a tariff import.
** 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 canceled by admin.
** Imported - rates were successfully imported.
* Import Rules - the name of the Tariff Import Rule used in import and the link to its edit view.
* Rate Import Rules - the 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 - the name of the attachment that was used in import.
* Import Type - the 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 analyzed jobs) - link to analysis details.
* Delete - button to delete one job.

Note: yellow-colored Tariff Jobs rows show that they are waiting for confirmation.
 
Retry Failed Conversion

If the conversion failed, click the button to retry.
 
[[File:Retry failedconversion.png]]
 

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. The default value is 30 days. It means that Tariff Jobs which were created before 30 days or more will be deleted.

 
[[File:M2TIImage9.png]]
 

= Analysis =

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

On the top right corner there are buttons:

 
[[File:M2TIImage101.png]]
 
* To CSV: export analysis to CSV file.
 

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

 
[[File:M2TIImage78.png]]
 

* Canceled: tariff import will be canceled.

 
[[File:M2TIImage69.png]]
 

* Schedule: Schedule Import from. Rates will be imported by the selected time if there are no Jobs waiting for the same tariff.

 
[[File:M2TIschedule.png]]
 

* Refine Results: allows filtering 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.

 
[[File:M2TIImage94.png]]
 

Tariff Job Analysis table:

 
[[File:M2TIImage31.png]]
 

Table columns:
* Prefix: prefix on which the rate will be applied.
* Destination: destination name.
* Rate: rate value.
* Connection fee: [[How the Connection fee works]]
* Increment: [[What is increment]]
* Minimal time: [[What is increment]]
* Effective from: [[Rate Effective From]]
* Blocked: Rate blocked Yes/No
* Deleted: Rate deleted Yes/No

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

 
[[File:M2TIImage32.png]]
 

Row colors:
* If the row is red-colored, the rate is not importable. Non-Importable Reason can be found by moving the 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 the analysis is being executed on several jobs which include 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.

= 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:

 
[[File: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:

 
[[File:M2TIImage113.png]]
 
[[File:M2TIImage113_sec.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
: The name of the Template can be set to any, but for better management suggested to be the name of Provider.

CSV Column Separator
: Column Separator must be chosen from the imported CSV file.

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.
: [[File:M2TIImage73.png]]
: Accordingly, Template’s Rate Sheet value must be set to 2.
: [[File: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 the 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.
: [[File: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.
: [[File:M2TIImage124.png]]
 

: Example 2:
: Provided Tariff/Rating Plan below meaningful data starts from Row 23.
: [[File: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.
: [[File: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 do not require additional configuration.

: Example 1:
: Provided Tariff/Rating Plan below has only one Prefix column which is B.
: [[File:M2TIImage68.png]]
: Accordingly, Template’s Prefix/Code value must be set to B.
: [[File:M2TIImage42.png]]
 

: Example 2:
: Provided Tariff/Rating Plan below has two Prefix columns which are C and D.
: [[File:M2TIImage67.png]]
: Accordingly, Template’s Prefix/Code values must be set to C and D in exact order.
: [[File: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 needs 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 on either side of the second box can be used to format the Zone name.
: Example: A Rate Plan includes the following information:
: [[File:M2TIImage59.png]]
: The Zone import is configured with Columns A and B and the separator fields with space “Column B”, as shown:
: [[File: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.
: [[File:M2TIImage7.png]]
: Accordingly, Template’s Destination/Zone value must be set to A.
: [[File:M2TIImage21.png]]
 

: Example 2:
: Provided Tariff/Rating Plan below has two Destination/Zone columns which are A and B.
: [[File:M2TIImage34.png]]
: Accordingly, Template’s Destination/Zone values must be set to A and B in exact order.
: [[File:M2TIImage5.png]]
 
:CLI Group identifies the Column in the Excel sheet that contains the CLI Groups.
:Example of a CLI Group:
:[[File:Cli Group auto import example.png]]

If the CLI Group with mapping is selected (the second radio button), then five settings must be set (all are required).

* First dropdown, CLI Group column (A, B, C, ...).
* Second dropdown rate sheet number (1, 2, 3, ...) in which the mapping is located.
* Third input field, where you set how many header lines to skip in the mapping sheet.
* Fourth dropdown CLI Group mapping name column (A, B, C, ...).
* Fifth dropdown CLI Group mapping prefix column (A, B, C, ...).
 

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 the first Worksheet there are Destinations with the Effective Date, and in the second Worksheet - Destination, Prefix, and 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.
: [[File:M2TIImage29.png]]
: Accordingly, Template’s Effective Date value can be set to F.
: [[File:M2TIImage10.png]]
 

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

: Example 3:
: Provided Tariff/Rating Plan below in the first Worksheet shows the Destination (Column A) and Effective Date (Column D).
: In the second Worksheet, there is Destination (Column A), Prefix (Column B), and Rate/Price (Column C).
: [[File:M2TIImage112.png]]
: The first dropdown configures from which Worksheet takes Effective Date data (which in this example’s case is Worksheet 1 (Rates)).
: The second dropdown configures the Effective Date column from the secondary Worksheet.
: The third dropdown configures by which the secondary Worksheet column does the mapping with the primary Worksheet (which in this example’s case is Destination from Worksheet 1 (Rates) Column A).
: The fourth dropdown configures by which the primary Worksheet column does the mapping with the 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.
: [[File:M2TIImage100.png]]
 

Effective Date Format
: After selecting the Column, the Format field is used to specify the format of the information held in the Rate Plan.
: Some suggested formats already exist in the dropdown, which can be selected or used as an example to create a custom one if it is not present in the dropdown.

: Example:
: Provided Tariff/Rating Plan below has an Effective Date column which is F.
: [[File:M2TIImage29.png]]
: Accordingly, Template’s Effective Date Format value can be selected from the dropdown with suggested formats.
: [[File: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.
: [[File:M2TIImage60.png]]
: Accordingly, Template’s Rate value must be set to E.
: [[File:M2TIImage51.png]]
 

Minimal Time
: Minimal Time can be set to match the Tariff/Rating Plan Minimal Time column.

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

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

Increment
: Increment can be set to match the Tariff/Rating Plan Increment column.

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

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

Deleted and Blocked rates.
: Deleted and Blocked rates can be chosen in the same way in Deleted Rates and Blocked Rates columns.
: [[File:Blocked and deleted rates.png]]

If Providers send a Rates Sheet with the price "-1", which means, that this Destination is blocked, the system will block a route to particular Destinations.
 

===Exceptions===

: Exceptions allow additional data manipulation when specific rules are defined separately in the provided Tariff/Rating Plan. Exceptions are set in Templates (BILLING > Tariff Import > Import Rules > Templates > EDIT). The Mask field allows defining which Destinations/Zones should have exceptional values set. % can be used as a wild card to match part of a text in Destination/Zone. Mask looks for Destination/Zone values as they are within the tariff sheet file. See examples below:

: Example 1:
: [[File:M2TIImage41.png]]
: [[File:M2TIImage6.png]]
 

: Example 2:
: [[File:M2TIImage16.png]]
: [[File:M2TIImage19.png]]

Exceptions fields are '''case-INsensitive'''. This means that, if the file contains MEXICO, you can create a template as meXIco, and the system will still find and replace it.
Do not put % in front of the text, it already acts like there would be a wild card by default.
 

= 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 the 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.

 
[[File:M2TIImage70.png]]
 

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

 
[[File: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 set action will be taken.
: Value can be set to any integer between 0 and 30 which represents the 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 set will be taken.
: Value can be set to any integer between 0 and 30 which represents the 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 set action will be taken.
: Value can be set to any integer between 0 and 30 which represents the 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 set action will be taken.
: Value can be set to any integer between 0 and 365 which represents the 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 set action will be taken.
: Value can be set to any integer between 0 and 365 which represents the day(s).

Max Increase
: If updating the existing Rate, whose Rate (Price) increased more by percentages than set in Rule, then set 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 the existing Rate, whose Rate (Price) decreased more by percentages than set in Rule, then set 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 set 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 the existing or creating a new Rate, whose Rate (Price) is zero, then a set 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 the set Rule, then set 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 a comma (,).
:: Must not have any spaces.
:: Must not have leading zeros, unless it is only a zero.
:: Limited to max 10 values.
: 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 the set Rule, then set action will be taken.
: If updating existing or creating a new Rate, whose new Min Time value does not match any from the set Rule, then set 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 a comma (,).
:: Must not have any spaces.
:: Must not have leading zeros, unless it is only a zero.
:: Limited to max 10 values.
: Examples:
:: 0,100,60,7200
:: 0,1,60
:: 1,60
:: 1

= Tariff Import Rules =

 
[[File: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 you 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:

* '''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. The target tariff in M4 can be Buy or Sell tariff. The target tariff in M4 can be any Wholesale tariff (Provider and Users).
* '''Import Type''' - you can choose between 2 options: Add/Update and Replace All Rates
** '''Add/Update''' - new rates will be added from the file and existing ones will be updated
** '''Replace All Rates''' - existing rates will be deleted and new ones imported from the file
* '''Delete rates if not present''' - delete Rates for Prefixes that are not present in the imported file (available only with Import Type: Add/Update)
* '''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's Subject
** '''Email Body''' - Use Effective Date from Email's Body ([[Effective Date from Email's Body Example|Example]])
** '''File Name''' - Use Effective Date from File (Attachment) Name
* '''Prefix''' - the text after which the Effective Date is specified.
* '''Date Format''' - date format in which Effective Date is specified. See available formats [[Automatic_Tariff_Import#Date_Format | here]].
* '''Time Zone''' - used to convert Effective Date file timezone to system timezone.
* '''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 a 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

To edit the above parameters on Import Rules, which were previously used, you have to delete Processes related to those rules.

 
== 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. The first four are for email and the fifth is for the attachment.

 
[[File: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. An example is shown in the image below
 
[[File:M2TIImage110.png]]
 

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

 

== Notifications ==

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

 
[[File:M2TIImage106.png]]
 

Follow the steps below to create and configure a notification Template.
# Create an Email Template. Manual on how to create Email Templates can be found here: [[M4 Emails]]. Additional Email variables: tariff_job_id, tariff_job_analysis_url, tariff_job_tariff_id, tariff_job_tariff_name.
# Select created Email Template from the Email Template dropdown. The subject field will fill in automatically. [[File:M2TIImage112.png]]
# Enter Email Address(es) in the Recipients field. Multiple Email Addresses must be separated by ;. [[File:M2TIImage116.png]]
# Repeat the same process for desired Import Triggers. [[File:M2TIImage36.png]]

 
== Date Format ==

Data Format should be written in symbolic form, constructed from various elements. Could look like this %d-%m-%Y and this covers such date as 01-02-2021 (day-month-year). There are several ways how to describe it:

'''YEAR'''
* %Y - year (2021)
* %y - last two digits of the year (00..99)

'''MONTH'''
* %m - month (01..12)
* %b - locale's abbreviated month name (e.g., Jan)
* %B - locale's full month name (e.g., January)

'''DAY'''
* %d - day of the month (e.g., 01)

 

= Tariff Link Attachment Rules =

Sometimes providers send emails with the URL to download the rates list instead of the rates file.

Tariff Link Attachment Rules are for attachment extraction from the link only.

 
[[File:M2TariffLinkAttachmentRules.png]]
 

Here you can list, edit, or create new rules.

To change rules priority just drag the rule up or down.

 
[[File:M2TariffLinkAttachmentRulesList.png]]
 

Rule structure:

 
[[File:M2TariffLinkAttachmentRuleStructure.png]]
 

* '''Name''' - Rules name.
* '''String Start''' - String Start defines where to start to search for attachment links in email text or a web page.
'''Value:''' search word or phrase. Leave empty to start a search from the beginning.
Example (String Start - '''can be downloaded'''):
Hello,
Attachments '''can be downloaded''' from here: http://www.example.com
Thank you for using our services
* '''String End''' - String End defines where to stop to search for attachment links in email text or a web page.
'''Value:''' search word or phrase. Leave it empty to search till the end.
Example (String End - '''Thank you'''):
Hello,
Attachments can be downloaded from here: http://www.example.com
'''Thank you''' for using our services
* '''Link Pattern''' - Link pattern is used to search for particular links.
'''Value:''' link or its fragment.
Examples:
https://www.example.com
https://www.example.com/example
example.com/example

= 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 Provider's 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:

 
[[File: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 email attachments had been assigned to at least one of the 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

 
[[File:M2TIImage55.png]]
 

# Email column - Sender name, email address, and subject
# Email details button - toggles email details
# Attachments list
# Attachments file type list
# Import jobs associated with attachments
# Import jobs button - toggles import jobs(if there are more than one tariff import job)
# Attachment mapping status
# Processing note
# Inbox view tab
# Completed view tab
# Junk view tab
# Select all emails checkbox
# Select a single email checkbox
# Delete Selected emails
# 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.
* The Last Section represents three function buttons.

 
[[File:M2TIImage47.png]]
 

== Filter Emails ==

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

 
[[File:M2TIImage37.png]]
 

There are four fields:
* Period - the period between emails were received.
* From - email or sender name.
* Subject - email subject.
* Message - email message.

 
[[File:M2TIImage71.png]]
 

Press Refine button to see the results.

 
[[File:M2TIImage120.png]]
 

Press clear to clear fields.

 
[[File:M2TIImage107.png]]
 

== Delete Emails ==

To delete emails, select emails by clicking their checkboxes.

 
[[File:M2TIImage38.png]]
 

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

 
[[File:M2TIImage96.png]]
 

After that, selected emails will be in a 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.

 
[[File:M2TIImage88.png]]
 

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

 
[[File:M2TIImage72.png]]
 

== Manually assign Tariff Import Settings ==

It is possible to manually Assign Tariff Import Settings.
# Toggle Email Details
# Select Tariff Import Rules for specific attachment
# Press Assign Import settings

 
[[File:M2TIImage57.png]]
 

== Retry Rules mapping ==

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

 
[[File:M2TIImage33.png]]
 

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

== Attachment mapping status ==

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

 
[[File: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 its format compared to Tariff Import Rules settings. Numbers after the 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.

 
[[File: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 the Jobs list caption.

 
[[File:M2TIImage87.png]]
 

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

 
[[File:M2TIImage62.png]]
 

= Detailed Import Examples =
* [[ATI - Voicetrading example]]
* [[ATI - Tata example]]
* [[ATI - Speedflow example]]

 
= Template Examples =
* [[ATI - IDT Express Template]]
* [[ATI - Voicetrading Template]]
* [[ATI - Voxbeam Template]]
* [[ATI - Tata Template]]
* [[ATI - Deutsche Telekom Template]]

 

= See also =
* [[M4 Tariffs]]
* [[Automatic Tariff Import - Email is not taken from the mailbox]]

Automatic Tariff Import

2025-10-23T05:55:54Z

Valerij: /* Template Examples */

<big><big>NOTICE: this is an add-on. Its functionality is identical in M4/MOR softswitches. M4 screenshots are used in this guide. It looks similar in MOR.</big></big>

 
<html>
<div align="center">
<table cellspacing="0" cellpadding="0">
<tr>
<td>
<iframe width="640" height="360" src="https://www.youtube.com/embed/ZjQxI5vx9bI" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
</td>
<td>
   
</td>
<td>
<iframe width="640" height="360" src="https://www.youtube.com/embed/GGuuj7sLHm8" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
</td>
</tr>
</table>
</div>
</html>
 

= Introduction =

An important task is the timely management of Provider Tariff Plans. While business practices differ somewhat, usually, 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 be followed by updated notification spreadsheets. These will be received at regular agreed-upon intervals, identifying amendments to the previous prices. The format and content of these notifications are not standardized and consequently differ from one Provider to another. This information is vital, from a commercial and financial management point of view. It is therefore important 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 analyze Tariff/Rating Plan updates received from partners, in order to ensure that any errors are noticed early and resolved before causing Billing disputes.

Doing all this by hand is a long and tedious process. It is possible to automate it with a human-in-between approach that requires confirming the final import step, to avoid potential misconfiguration problems.

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

 
= Workflow schema =

The workflow can be visualized using the following schema:

 
[[File:M2TIImage15.png]]
 

As can be seen, there are two components to this process:

# The configuration component, which is done once for each tariff file received from the provider
# The actual working component, where an automated process takes care of the tariff import, based on the settings configured in the first component

 

=Initial (Manual) Setup for Automation=

This sequence of steps is necessary to configure the automatic import of the concrete tariff format received from any provider. Once configured, it can be used many times thereafter, until the provider decides to change the format.

==Rate file received from Provider examined and analyzed==

As every Provider sends different format files, their structure should be analyzed and necessary fields marked for Template creation.

XLSX file can be compressed to RAR or ZIP archive.

==XLSX Transformation Template created==

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

==Rate Import Rules created==

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 fewer than seven days' notice) and then either alerting the Administrator or rejecting the rate.

==Tariff Import Rules created==

Tariff Import Rules assign each received Email/Attachment to the correct Provider/Tariff with appropriate Rate Import Rules, Notifications, and a Template to properly convert received Tariffs and upload rates to the System.

==Email Notifications configured==

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

* Tariff is received
* The tariff is analyzed and waiting for confirmation before the import
* The tariff is imported or rejected
* Others

 
=Normal (Automatic) Workflow=

Based on preconfigured settings in the first step, the Automatic Tariff Import could be carried out by using all the defined settings.

==Rate file received from Provider via email==

One mailbox, usually named rates@companyname.com, is monitored by the system and all received emails are analyzed.

==Email/Attachments analyzed by Tariff Import Rules==

When an email with a tariff attachment comes in, it is checked for address, email subject, attachment name, and other parameters to properly map the received Tariff file with the following procedures.

==Rate Plan converted and analyzed==

Based on the Tariff Import Rules, the Tariff file is converted to a standardized format (a process called Normalization) and then analyzed. During analysis, the Rate Import Rules are applied and necessary actions are taken based on the Rules.

==Notification for Review sent==

If there are no major problems, the responsible person receives an email saying that the Tariff is converted with such and such notices or warnings, and waits for approval. It is possible to skip this step with 100% automation, but it is usually not recommended, due to possible errors that human intervention could help to avoid.

==Import Denied==

The responsible person can deny the tariff import, based on the information received. The analysis could identify errors that can't be ignored or some other issues.

==Import Accepted==

If there are no major issues, the responsible person accepts the tariff import and the system is notified to continue the import.

==Tariff Imported==

The actual tariff import takes place, and old rates are replaced by new ones. All of the preparation work culminates in this step.

 
= How it is implemented in the system =

 
[[File:M2TIImage56.png]]
 
[[File:M2TIImage20.png]]
 

Tariff Jobs table columns:
* Id: Tariff job identification number.
* Created at: Date when the job was created.
* Status: The state of the import process. Can be:
** Assigned - import process was started.
** Converting - rates are being converted.
** Failed Conversion - there was an error in rate conversion.
** Converted - rates were successfully converted.
** Importing into Temporary Table - rates are being uploaded from the attachment to the system.
** Failed to Import into Temporary Table - there was an error in the rates uploading process.
** Imported into Temporary Table - rates have been loaded into the system.
** Analyzing - rates are being analyzed.
** Failed Analysis - there was an error in the analysis. By lowering over Failed Analysis you can get more details.
***rate_with_multiple_ratedetails_found refers to Rate Details (where you can set rate for peak/off-peak hours) being used on one or more rates on target tariff. Such rates are not supported by a tariff import.
** 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 canceled by admin.
** Imported - rates were successfully imported.
* Import Rules - the name of the Tariff Import Rule used in import and the link to its edit view.
* Rate Import Rules - the 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 - the name of the attachment that was used in import.
* Import Type - the 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 analyzed jobs) - link to analysis details.
* Delete - button to delete one job.

Note: yellow-colored Tariff Jobs rows show that they are waiting for confirmation.
 
Retry Failed Conversion

If the conversion failed, click the button to retry.
 
[[File:Retry failedconversion.png]]
 

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. The default value is 30 days. It means that Tariff Jobs which were created before 30 days or more will be deleted.

 
[[File:M2TIImage9.png]]
 

= Analysis =

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

On the top right corner there are buttons:

 
[[File:M2TIImage101.png]]
 
* To CSV: export analysis to CSV file.
 

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

 
[[File:M2TIImage78.png]]
 

* Canceled: tariff import will be canceled.

 
[[File:M2TIImage69.png]]
 

* Schedule: Schedule Import from. Rates will be imported by the selected time if there are no Jobs waiting for the same tariff.

 
[[File:M2TIschedule.png]]
 

* Refine Results: allows filtering 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.

 
[[File:M2TIImage94.png]]
 

Tariff Job Analysis table:

 
[[File:M2TIImage31.png]]
 

Table columns:
* Prefix: prefix on which the rate will be applied.
* Destination: destination name.
* Rate: rate value.
* Connection fee: [[How the Connection fee works]]
* Increment: [[What is increment]]
* Minimal time: [[What is increment]]
* Effective from: [[Rate Effective From]]
* Blocked: Rate blocked Yes/No
* Deleted: Rate deleted Yes/No

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

 
[[File:M2TIImage32.png]]
 

Row colors:
* If the row is red-colored, the rate is not importable. Non-Importable Reason can be found by moving the 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 the analysis is being executed on several jobs which include 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.

= 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:

 
[[File: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:

 
[[File:M2TIImage113.png]]
 
[[File:M2TIImage113_sec.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
: The name of the Template can be set to any, but for better management suggested to be the name of Provider.

CSV Column Separator
: Column Separator must be chosen from the imported CSV file.

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.
: [[File:M2TIImage73.png]]
: Accordingly, Template’s Rate Sheet value must be set to 2.
: [[File: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 the 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.
: [[File: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.
: [[File:M2TIImage124.png]]
 

: Example 2:
: Provided Tariff/Rating Plan below meaningful data starts from Row 23.
: [[File: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.
: [[File: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 do not require additional configuration.

: Example 1:
: Provided Tariff/Rating Plan below has only one Prefix column which is B.
: [[File:M2TIImage68.png]]
: Accordingly, Template’s Prefix/Code value must be set to B.
: [[File:M2TIImage42.png]]
 

: Example 2:
: Provided Tariff/Rating Plan below has two Prefix columns which are C and D.
: [[File:M2TIImage67.png]]
: Accordingly, Template’s Prefix/Code values must be set to C and D in exact order.
: [[File: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 needs 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 on either side of the second box can be used to format the Zone name.
: Example: A Rate Plan includes the following information:
: [[File:M2TIImage59.png]]
: The Zone import is configured with Columns A and B and the separator fields with space “Column B”, as shown:
: [[File: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.
: [[File:M2TIImage7.png]]
: Accordingly, Template’s Destination/Zone value must be set to A.
: [[File:M2TIImage21.png]]
 

: Example 2:
: Provided Tariff/Rating Plan below has two Destination/Zone columns which are A and B.
: [[File:M2TIImage34.png]]
: Accordingly, Template’s Destination/Zone values must be set to A and B in exact order.
: [[File:M2TIImage5.png]]
 
:CLI Group identifies the Column in the Excel sheet that contains the CLI Groups.
:Example of a CLI Group:
:[[File:Cli Group auto import example.png]]

If the CLI Group with mapping is selected (the second radio button), then five settings must be set (all are required).

* First dropdown, CLI Group column (A, B, C, ...).
* Second dropdown rate sheet number (1, 2, 3, ...) in which the mapping is located.
* Third input field, where you set how many header lines to skip in the mapping sheet.
* Fourth dropdown CLI Group mapping name column (A, B, C, ...).
* Fifth dropdown CLI Group mapping prefix column (A, B, C, ...).
 

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 the first Worksheet there are Destinations with the Effective Date, and in the second Worksheet - Destination, Prefix, and 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.
: [[File:M2TIImage29.png]]
: Accordingly, Template’s Effective Date value can be set to F.
: [[File:M2TIImage10.png]]
 

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

: Example 3:
: Provided Tariff/Rating Plan below in the first Worksheet shows the Destination (Column A) and Effective Date (Column D).
: In the second Worksheet, there is Destination (Column A), Prefix (Column B), and Rate/Price (Column C).
: [[File:M2TIImage112.png]]
: The first dropdown configures from which Worksheet takes Effective Date data (which in this example’s case is Worksheet 1 (Rates)).
: The second dropdown configures the Effective Date column from the secondary Worksheet.
: The third dropdown configures by which the secondary Worksheet column does the mapping with the primary Worksheet (which in this example’s case is Destination from Worksheet 1 (Rates) Column A).
: The fourth dropdown configures by which the primary Worksheet column does the mapping with the 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.
: [[File:M2TIImage100.png]]
 

Effective Date Format
: After selecting the Column, the Format field is used to specify the format of the information held in the Rate Plan.
: Some suggested formats already exist in the dropdown, which can be selected or used as an example to create a custom one if it is not present in the dropdown.

: Example:
: Provided Tariff/Rating Plan below has an Effective Date column which is F.
: [[File:M2TIImage29.png]]
: Accordingly, Template’s Effective Date Format value can be selected from the dropdown with suggested formats.
: [[File: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.
: [[File:M2TIImage60.png]]
: Accordingly, Template’s Rate value must be set to E.
: [[File:M2TIImage51.png]]
 

Minimal Time
: Minimal Time can be set to match the Tariff/Rating Plan Minimal Time column.

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

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

Increment
: Increment can be set to match the Tariff/Rating Plan Increment column.

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

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

Deleted and Blocked rates.
: Deleted and Blocked rates can be chosen in the same way in Deleted Rates and Blocked Rates columns.
: [[File:Blocked and deleted rates.png]]

If Providers send a Rates Sheet with the price "-1", which means, that this Destination is blocked, the system will block a route to particular Destinations.
 

===Exceptions===

: Exceptions allow additional data manipulation when specific rules are defined separately in the provided Tariff/Rating Plan. Exceptions are set in Templates (BILLING > Tariff Import > Import Rules > Templates > EDIT). The Mask field allows defining which Destinations/Zones should have exceptional values set. % can be used as a wild card to match part of a text in Destination/Zone. Mask looks for Destination/Zone values as they are within the tariff sheet file. See examples below:

: Example 1:
: [[File:M2TIImage41.png]]
: [[File:M2TIImage6.png]]
 

: Example 2:
: [[File:M2TIImage16.png]]
: [[File:M2TIImage19.png]]

Exceptions fields are '''case-INsensitive'''. This means that, if the file contains MEXICO, you can create a template as meXIco, and the system will still find and replace it.
Do not put % in front of the text, it already acts like there would be a wild card by default.
 

= 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 the 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.

 
[[File:M2TIImage70.png]]
 

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

 
[[File: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 set action will be taken.
: Value can be set to any integer between 0 and 30 which represents the 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 set will be taken.
: Value can be set to any integer between 0 and 30 which represents the 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 set action will be taken.
: Value can be set to any integer between 0 and 30 which represents the 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 set action will be taken.
: Value can be set to any integer between 0 and 365 which represents the 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 set action will be taken.
: Value can be set to any integer between 0 and 365 which represents the day(s).

Max Increase
: If updating the existing Rate, whose Rate (Price) increased more by percentages than set in Rule, then set 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 the existing Rate, whose Rate (Price) decreased more by percentages than set in Rule, then set 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 set 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 the existing or creating a new Rate, whose Rate (Price) is zero, then a set 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 the set Rule, then set 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 a comma (,).
:: Must not have any spaces.
:: Must not have leading zeros, unless it is only a zero.
:: Limited to max 10 values.
: 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 the set Rule, then set action will be taken.
: If updating existing or creating a new Rate, whose new Min Time value does not match any from the set Rule, then set 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 a comma (,).
:: Must not have any spaces.
:: Must not have leading zeros, unless it is only a zero.
:: Limited to max 10 values.
: Examples:
:: 0,100,60,7200
:: 0,1,60
:: 1,60
:: 1

= Tariff Import Rules =

 
[[File: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 you 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:

* '''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. The target tariff in M4 can be Buy or Sell tariff. The target tariff in M4 can be any Wholesale tariff (Provider and Users).
* '''Import Type''' - you can choose between 2 options: Add/Update and Replace All Rates
** '''Add/Update''' - new rates will be added from the file and existing ones will be updated
** '''Replace All Rates''' - existing rates will be deleted and new ones imported from the file
* '''Delete rates if not present''' - delete Rates for Prefixes that are not present in the imported file (available only with Import Type: Add/Update)
* '''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's Subject
** '''Email Body''' - Use Effective Date from Email's Body ([[Effective Date from Email's Body Example|Example]])
** '''File Name''' - Use Effective Date from File (Attachment) Name
* '''Prefix''' - the text after which the Effective Date is specified.
* '''Date Format''' - date format in which Effective Date is specified. See available formats [[Automatic_Tariff_Import#Date_Format | here]].
* '''Time Zone''' - used to convert Effective Date file timezone to system timezone.
* '''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 a 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

To edit the above parameters on Import Rules, which were previously used, you have to delete Processes related to those rules.

 
== 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. The first four are for email and the fifth is for the attachment.

 
[[File: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. An example is shown in the image below
 
[[File:M2TIImage110.png]]
 

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

 

== Notifications ==

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

 
[[File:M2TIImage106.png]]
 

Follow the steps below to create and configure a notification Template.
# Create an Email Template. Manual on how to create Email Templates can be found here: [[M4 Emails]]. Additional Email variables: tariff_job_id, tariff_job_analysis_url, tariff_job_tariff_id, tariff_job_tariff_name.
# Select created Email Template from the Email Template dropdown. The subject field will fill in automatically. [[File:M2TIImage112.png]]
# Enter Email Address(es) in the Recipients field. Multiple Email Addresses must be separated by ;. [[File:M2TIImage116.png]]
# Repeat the same process for desired Import Triggers. [[File:M2TIImage36.png]]

 
== Date Format ==

Data Format should be written in symbolic form, constructed from various elements. Could look like this %d-%m-%Y and this covers such date as 01-02-2021 (day-month-year). There are several ways how to describe it:

'''YEAR'''
* %Y - year (2021)
* %y - last two digits of the year (00..99)

'''MONTH'''
* %m - month (01..12)
* %b - locale's abbreviated month name (e.g., Jan)
* %B - locale's full month name (e.g., January)

'''DAY'''
* %d - day of the month (e.g., 01)

 

= Tariff Link Attachment Rules =

Sometimes providers send emails with the URL to download the rates list instead of the rates file.

Tariff Link Attachment Rules are for attachment extraction from the link only.

 
[[File:M2TariffLinkAttachmentRules.png]]
 

Here you can list, edit, or create new rules.

To change rules priority just drag the rule up or down.

 
[[File:M2TariffLinkAttachmentRulesList.png]]
 

Rule structure:

 
[[File:M2TariffLinkAttachmentRuleStructure.png]]
 

* '''Name''' - Rules name.
* '''String Start''' - String Start defines where to start to search for attachment links in email text or a web page.
'''Value:''' search word or phrase. Leave empty to start a search from the beginning.
Example (String Start - '''can be downloaded'''):
Hello,
Attachments '''can be downloaded''' from here: http://www.example.com
Thank you for using our services
* '''String End''' - String End defines where to stop to search for attachment links in email text or a web page.
'''Value:''' search word or phrase. Leave it empty to search till the end.
Example (String End - '''Thank you'''):
Hello,
Attachments can be downloaded from here: http://www.example.com
'''Thank you''' for using our services
* '''Link Pattern''' - Link pattern is used to search for particular links.
'''Value:''' link or its fragment.
Examples:
https://www.example.com
https://www.example.com/example
example.com/example

= 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 Provider's 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:

 
[[File: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 email attachments had been assigned to at least one of the 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

 
[[File:M2TIImage55.png]]
 

# Email column - Sender name, email address, and subject
# Email details button - toggles email details
# Attachments list
# Attachments file type list
# Import jobs associated with attachments
# Import jobs button - toggles import jobs(if there are more than one tariff import job)
# Attachment mapping status
# Processing note
# Inbox view tab
# Completed view tab
# Junk view tab
# Select all emails checkbox
# Select a single email checkbox
# Delete Selected emails
# 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.
* The Last Section represents three function buttons.

 
[[File:M2TIImage47.png]]
 

== Filter Emails ==

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

 
[[File:M2TIImage37.png]]
 

There are four fields:
* Period - the period between emails were received.
* From - email or sender name.
* Subject - email subject.
* Message - email message.

 
[[File:M2TIImage71.png]]
 

Press Refine button to see the results.

 
[[File:M2TIImage120.png]]
 

Press clear to clear fields.

 
[[File:M2TIImage107.png]]
 

== Delete Emails ==

To delete emails, select emails by clicking their checkboxes.

 
[[File:M2TIImage38.png]]
 

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

 
[[File:M2TIImage96.png]]
 

After that, selected emails will be in a 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.

 
[[File:M2TIImage88.png]]
 

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

 
[[File:M2TIImage72.png]]
 

== Manually assign Tariff Import Settings ==

It is possible to manually Assign Tariff Import Settings.
# Toggle Email Details
# Select Tariff Import Rules for specific attachment
# Press Assign Import settings

 
[[File:M2TIImage57.png]]
 

== Retry Rules mapping ==

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

 
[[File:M2TIImage33.png]]
 

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

== Attachment mapping status ==

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

 
[[File: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 its format compared to Tariff Import Rules settings. Numbers after the 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.

 
[[File: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 the Jobs list caption.

 
[[File:M2TIImage87.png]]
 

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

 
[[File:M2TIImage62.png]]
 

= Detailed Import Examples =
* [[ATI - Voicetrading example]]
* [[ATI - Tata example]]
* [[ATI - Speedflow example]]

 
= Template Examples =
* [[ATI - IDT Express Template]]
* [[ATI - Voicetrading Template]]
* [[ATI - Voxbeam Template]]
* [[ATI - Tata Template]]
* [[ATI - Deutsche_Telekom_Template]]

 

= See also =
* [[M4 Tariffs]]
* [[Automatic Tariff Import - Email is not taken from the mailbox]]

Automatic Tariff Import

2025-10-23T05:55:16Z

Valerij: /* Template Examples */

<big><big>NOTICE: this is an add-on. Its functionality is identical in M4/MOR softswitches. M4 screenshots are used in this guide. It looks similar in MOR.</big></big>

 
<html>
<div align="center">
<table cellspacing="0" cellpadding="0">
<tr>
<td>
<iframe width="640" height="360" src="https://www.youtube.com/embed/ZjQxI5vx9bI" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
</td>
<td>
   
</td>
<td>
<iframe width="640" height="360" src="https://www.youtube.com/embed/GGuuj7sLHm8" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
</td>
</tr>
</table>
</div>
</html>
 

= Introduction =

An important task is the timely management of Provider Tariff Plans. While business practices differ somewhat, usually, 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 be followed by updated notification spreadsheets. These will be received at regular agreed-upon intervals, identifying amendments to the previous prices. The format and content of these notifications are not standardized and consequently differ from one Provider to another. This information is vital, from a commercial and financial management point of view. It is therefore important 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 analyze Tariff/Rating Plan updates received from partners, in order to ensure that any errors are noticed early and resolved before causing Billing disputes.

Doing all this by hand is a long and tedious process. It is possible to automate it with a human-in-between approach that requires confirming the final import step, to avoid potential misconfiguration problems.

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

 
= Workflow schema =

The workflow can be visualized using the following schema:

 
[[File:M2TIImage15.png]]
 

As can be seen, there are two components to this process:

# The configuration component, which is done once for each tariff file received from the provider
# The actual working component, where an automated process takes care of the tariff import, based on the settings configured in the first component

 

=Initial (Manual) Setup for Automation=

This sequence of steps is necessary to configure the automatic import of the concrete tariff format received from any provider. Once configured, it can be used many times thereafter, until the provider decides to change the format.

==Rate file received from Provider examined and analyzed==

As every Provider sends different format files, their structure should be analyzed and necessary fields marked for Template creation.

XLSX file can be compressed to RAR or ZIP archive.

==XLSX Transformation Template created==

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

==Rate Import Rules created==

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 fewer than seven days' notice) and then either alerting the Administrator or rejecting the rate.

==Tariff Import Rules created==

Tariff Import Rules assign each received Email/Attachment to the correct Provider/Tariff with appropriate Rate Import Rules, Notifications, and a Template to properly convert received Tariffs and upload rates to the System.

==Email Notifications configured==

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

* Tariff is received
* The tariff is analyzed and waiting for confirmation before the import
* The tariff is imported or rejected
* Others

 
=Normal (Automatic) Workflow=

Based on preconfigured settings in the first step, the Automatic Tariff Import could be carried out by using all the defined settings.

==Rate file received from Provider via email==

One mailbox, usually named rates@companyname.com, is monitored by the system and all received emails are analyzed.

==Email/Attachments analyzed by Tariff Import Rules==

When an email with a tariff attachment comes in, it is checked for address, email subject, attachment name, and other parameters to properly map the received Tariff file with the following procedures.

==Rate Plan converted and analyzed==

Based on the Tariff Import Rules, the Tariff file is converted to a standardized format (a process called Normalization) and then analyzed. During analysis, the Rate Import Rules are applied and necessary actions are taken based on the Rules.

==Notification for Review sent==

If there are no major problems, the responsible person receives an email saying that the Tariff is converted with such and such notices or warnings, and waits for approval. It is possible to skip this step with 100% automation, but it is usually not recommended, due to possible errors that human intervention could help to avoid.

==Import Denied==

The responsible person can deny the tariff import, based on the information received. The analysis could identify errors that can't be ignored or some other issues.

==Import Accepted==

If there are no major issues, the responsible person accepts the tariff import and the system is notified to continue the import.

==Tariff Imported==

The actual tariff import takes place, and old rates are replaced by new ones. All of the preparation work culminates in this step.

 
= How it is implemented in the system =

 
[[File:M2TIImage56.png]]
 
[[File:M2TIImage20.png]]
 

Tariff Jobs table columns:
* Id: Tariff job identification number.
* Created at: Date when the job was created.
* Status: The state of the import process. Can be:
** Assigned - import process was started.
** Converting - rates are being converted.
** Failed Conversion - there was an error in rate conversion.
** Converted - rates were successfully converted.
** Importing into Temporary Table - rates are being uploaded from the attachment to the system.
** Failed to Import into Temporary Table - there was an error in the rates uploading process.
** Imported into Temporary Table - rates have been loaded into the system.
** Analyzing - rates are being analyzed.
** Failed Analysis - there was an error in the analysis. By lowering over Failed Analysis you can get more details.
***rate_with_multiple_ratedetails_found refers to Rate Details (where you can set rate for peak/off-peak hours) being used on one or more rates on target tariff. Such rates are not supported by a tariff import.
** 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 canceled by admin.
** Imported - rates were successfully imported.
* Import Rules - the name of the Tariff Import Rule used in import and the link to its edit view.
* Rate Import Rules - the 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 - the name of the attachment that was used in import.
* Import Type - the 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 analyzed jobs) - link to analysis details.
* Delete - button to delete one job.

Note: yellow-colored Tariff Jobs rows show that they are waiting for confirmation.
 
Retry Failed Conversion

If the conversion failed, click the button to retry.
 
[[File:Retry failedconversion.png]]
 

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. The default value is 30 days. It means that Tariff Jobs which were created before 30 days or more will be deleted.

 
[[File:M2TIImage9.png]]
 

= Analysis =

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

On the top right corner there are buttons:

 
[[File:M2TIImage101.png]]
 
* To CSV: export analysis to CSV file.
 

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

 
[[File:M2TIImage78.png]]
 

* Canceled: tariff import will be canceled.

 
[[File:M2TIImage69.png]]
 

* Schedule: Schedule Import from. Rates will be imported by the selected time if there are no Jobs waiting for the same tariff.

 
[[File:M2TIschedule.png]]
 

* Refine Results: allows filtering 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.

 
[[File:M2TIImage94.png]]
 

Tariff Job Analysis table:

 
[[File:M2TIImage31.png]]
 

Table columns:
* Prefix: prefix on which the rate will be applied.
* Destination: destination name.
* Rate: rate value.
* Connection fee: [[How the Connection fee works]]
* Increment: [[What is increment]]
* Minimal time: [[What is increment]]
* Effective from: [[Rate Effective From]]
* Blocked: Rate blocked Yes/No
* Deleted: Rate deleted Yes/No

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

 
[[File:M2TIImage32.png]]
 

Row colors:
* If the row is red-colored, the rate is not importable. Non-Importable Reason can be found by moving the 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 the analysis is being executed on several jobs which include 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.

= 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:

 
[[File: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:

 
[[File:M2TIImage113.png]]
 
[[File:M2TIImage113_sec.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
: The name of the Template can be set to any, but for better management suggested to be the name of Provider.

CSV Column Separator
: Column Separator must be chosen from the imported CSV file.

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.
: [[File:M2TIImage73.png]]
: Accordingly, Template’s Rate Sheet value must be set to 2.
: [[File: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 the 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.
: [[File: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.
: [[File:M2TIImage124.png]]
 

: Example 2:
: Provided Tariff/Rating Plan below meaningful data starts from Row 23.
: [[File: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.
: [[File: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 do not require additional configuration.

: Example 1:
: Provided Tariff/Rating Plan below has only one Prefix column which is B.
: [[File:M2TIImage68.png]]
: Accordingly, Template’s Prefix/Code value must be set to B.
: [[File:M2TIImage42.png]]
 

: Example 2:
: Provided Tariff/Rating Plan below has two Prefix columns which are C and D.
: [[File:M2TIImage67.png]]
: Accordingly, Template’s Prefix/Code values must be set to C and D in exact order.
: [[File: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 needs 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 on either side of the second box can be used to format the Zone name.
: Example: A Rate Plan includes the following information:
: [[File:M2TIImage59.png]]
: The Zone import is configured with Columns A and B and the separator fields with space “Column B”, as shown:
: [[File: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.
: [[File:M2TIImage7.png]]
: Accordingly, Template’s Destination/Zone value must be set to A.
: [[File:M2TIImage21.png]]
 

: Example 2:
: Provided Tariff/Rating Plan below has two Destination/Zone columns which are A and B.
: [[File:M2TIImage34.png]]
: Accordingly, Template’s Destination/Zone values must be set to A and B in exact order.
: [[File:M2TIImage5.png]]
 
:CLI Group identifies the Column in the Excel sheet that contains the CLI Groups.
:Example of a CLI Group:
:[[File:Cli Group auto import example.png]]

If the CLI Group with mapping is selected (the second radio button), then five settings must be set (all are required).

* First dropdown, CLI Group column (A, B, C, ...).
* Second dropdown rate sheet number (1, 2, 3, ...) in which the mapping is located.
* Third input field, where you set how many header lines to skip in the mapping sheet.
* Fourth dropdown CLI Group mapping name column (A, B, C, ...).
* Fifth dropdown CLI Group mapping prefix column (A, B, C, ...).
 

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 the first Worksheet there are Destinations with the Effective Date, and in the second Worksheet - Destination, Prefix, and 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.
: [[File:M2TIImage29.png]]
: Accordingly, Template’s Effective Date value can be set to F.
: [[File:M2TIImage10.png]]
 

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

: Example 3:
: Provided Tariff/Rating Plan below in the first Worksheet shows the Destination (Column A) and Effective Date (Column D).
: In the second Worksheet, there is Destination (Column A), Prefix (Column B), and Rate/Price (Column C).
: [[File:M2TIImage112.png]]
: The first dropdown configures from which Worksheet takes Effective Date data (which in this example’s case is Worksheet 1 (Rates)).
: The second dropdown configures the Effective Date column from the secondary Worksheet.
: The third dropdown configures by which the secondary Worksheet column does the mapping with the primary Worksheet (which in this example’s case is Destination from Worksheet 1 (Rates) Column A).
: The fourth dropdown configures by which the primary Worksheet column does the mapping with the 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.
: [[File:M2TIImage100.png]]
 

Effective Date Format
: After selecting the Column, the Format field is used to specify the format of the information held in the Rate Plan.
: Some suggested formats already exist in the dropdown, which can be selected or used as an example to create a custom one if it is not present in the dropdown.

: Example:
: Provided Tariff/Rating Plan below has an Effective Date column which is F.
: [[File:M2TIImage29.png]]
: Accordingly, Template’s Effective Date Format value can be selected from the dropdown with suggested formats.
: [[File: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.
: [[File:M2TIImage60.png]]
: Accordingly, Template’s Rate value must be set to E.
: [[File:M2TIImage51.png]]
 

Minimal Time
: Minimal Time can be set to match the Tariff/Rating Plan Minimal Time column.

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

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

Increment
: Increment can be set to match the Tariff/Rating Plan Increment column.

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

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

Deleted and Blocked rates.
: Deleted and Blocked rates can be chosen in the same way in Deleted Rates and Blocked Rates columns.
: [[File:Blocked and deleted rates.png]]

If Providers send a Rates Sheet with the price "-1", which means, that this Destination is blocked, the system will block a route to particular Destinations.
 

===Exceptions===

: Exceptions allow additional data manipulation when specific rules are defined separately in the provided Tariff/Rating Plan. Exceptions are set in Templates (BILLING > Tariff Import > Import Rules > Templates > EDIT). The Mask field allows defining which Destinations/Zones should have exceptional values set. % can be used as a wild card to match part of a text in Destination/Zone. Mask looks for Destination/Zone values as they are within the tariff sheet file. See examples below:

: Example 1:
: [[File:M2TIImage41.png]]
: [[File:M2TIImage6.png]]
 

: Example 2:
: [[File:M2TIImage16.png]]
: [[File:M2TIImage19.png]]

Exceptions fields are '''case-INsensitive'''. This means that, if the file contains MEXICO, you can create a template as meXIco, and the system will still find and replace it.
Do not put % in front of the text, it already acts like there would be a wild card by default.
 

= 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 the 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.

 
[[File:M2TIImage70.png]]
 

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

 
[[File: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 set action will be taken.
: Value can be set to any integer between 0 and 30 which represents the 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 set will be taken.
: Value can be set to any integer between 0 and 30 which represents the 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 set action will be taken.
: Value can be set to any integer between 0 and 30 which represents the 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 set action will be taken.
: Value can be set to any integer between 0 and 365 which represents the 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 set action will be taken.
: Value can be set to any integer between 0 and 365 which represents the day(s).

Max Increase
: If updating the existing Rate, whose Rate (Price) increased more by percentages than set in Rule, then set 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 the existing Rate, whose Rate (Price) decreased more by percentages than set in Rule, then set 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 set 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 the existing or creating a new Rate, whose Rate (Price) is zero, then a set 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 the set Rule, then set 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 a comma (,).
:: Must not have any spaces.
:: Must not have leading zeros, unless it is only a zero.
:: Limited to max 10 values.
: 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 the set Rule, then set action will be taken.
: If updating existing or creating a new Rate, whose new Min Time value does not match any from the set Rule, then set 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 a comma (,).
:: Must not have any spaces.
:: Must not have leading zeros, unless it is only a zero.
:: Limited to max 10 values.
: Examples:
:: 0,100,60,7200
:: 0,1,60
:: 1,60
:: 1

= Tariff Import Rules =

 
[[File: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 you 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:

* '''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. The target tariff in M4 can be Buy or Sell tariff. The target tariff in M4 can be any Wholesale tariff (Provider and Users).
* '''Import Type''' - you can choose between 2 options: Add/Update and Replace All Rates
** '''Add/Update''' - new rates will be added from the file and existing ones will be updated
** '''Replace All Rates''' - existing rates will be deleted and new ones imported from the file
* '''Delete rates if not present''' - delete Rates for Prefixes that are not present in the imported file (available only with Import Type: Add/Update)
* '''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's Subject
** '''Email Body''' - Use Effective Date from Email's Body ([[Effective Date from Email's Body Example|Example]])
** '''File Name''' - Use Effective Date from File (Attachment) Name
* '''Prefix''' - the text after which the Effective Date is specified.
* '''Date Format''' - date format in which Effective Date is specified. See available formats [[Automatic_Tariff_Import#Date_Format | here]].
* '''Time Zone''' - used to convert Effective Date file timezone to system timezone.
* '''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 a 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

To edit the above parameters on Import Rules, which were previously used, you have to delete Processes related to those rules.

 
== 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. The first four are for email and the fifth is for the attachment.

 
[[File: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. An example is shown in the image below
 
[[File:M2TIImage110.png]]
 

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

 

== Notifications ==

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

 
[[File:M2TIImage106.png]]
 

Follow the steps below to create and configure a notification Template.
# Create an Email Template. Manual on how to create Email Templates can be found here: [[M4 Emails]]. Additional Email variables: tariff_job_id, tariff_job_analysis_url, tariff_job_tariff_id, tariff_job_tariff_name.
# Select created Email Template from the Email Template dropdown. The subject field will fill in automatically. [[File:M2TIImage112.png]]
# Enter Email Address(es) in the Recipients field. Multiple Email Addresses must be separated by ;. [[File:M2TIImage116.png]]
# Repeat the same process for desired Import Triggers. [[File:M2TIImage36.png]]

 
== Date Format ==

Data Format should be written in symbolic form, constructed from various elements. Could look like this %d-%m-%Y and this covers such date as 01-02-2021 (day-month-year). There are several ways how to describe it:

'''YEAR'''
* %Y - year (2021)
* %y - last two digits of the year (00..99)

'''MONTH'''
* %m - month (01..12)
* %b - locale's abbreviated month name (e.g., Jan)
* %B - locale's full month name (e.g., January)

'''DAY'''
* %d - day of the month (e.g., 01)

 

= Tariff Link Attachment Rules =

Sometimes providers send emails with the URL to download the rates list instead of the rates file.

Tariff Link Attachment Rules are for attachment extraction from the link only.

 
[[File:M2TariffLinkAttachmentRules.png]]
 

Here you can list, edit, or create new rules.

To change rules priority just drag the rule up or down.

 
[[File:M2TariffLinkAttachmentRulesList.png]]
 

Rule structure:

 
[[File:M2TariffLinkAttachmentRuleStructure.png]]
 

* '''Name''' - Rules name.
* '''String Start''' - String Start defines where to start to search for attachment links in email text or a web page.
'''Value:''' search word or phrase. Leave empty to start a search from the beginning.
Example (String Start - '''can be downloaded'''):
Hello,
Attachments '''can be downloaded''' from here: http://www.example.com
Thank you for using our services
* '''String End''' - String End defines where to stop to search for attachment links in email text or a web page.
'''Value:''' search word or phrase. Leave it empty to search till the end.
Example (String End - '''Thank you'''):
Hello,
Attachments can be downloaded from here: http://www.example.com
'''Thank you''' for using our services
* '''Link Pattern''' - Link pattern is used to search for particular links.
'''Value:''' link or its fragment.
Examples:
https://www.example.com
https://www.example.com/example
example.com/example

= 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 Provider's 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:

 
[[File: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 email attachments had been assigned to at least one of the 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

 
[[File:M2TIImage55.png]]
 

# Email column - Sender name, email address, and subject
# Email details button - toggles email details
# Attachments list
# Attachments file type list
# Import jobs associated with attachments
# Import jobs button - toggles import jobs(if there are more than one tariff import job)
# Attachment mapping status
# Processing note
# Inbox view tab
# Completed view tab
# Junk view tab
# Select all emails checkbox
# Select a single email checkbox
# Delete Selected emails
# 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.
* The Last Section represents three function buttons.

 
[[File:M2TIImage47.png]]
 

== Filter Emails ==

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

 
[[File:M2TIImage37.png]]
 

There are four fields:
* Period - the period between emails were received.
* From - email or sender name.
* Subject - email subject.
* Message - email message.

 
[[File:M2TIImage71.png]]
 

Press Refine button to see the results.

 
[[File:M2TIImage120.png]]
 

Press clear to clear fields.

 
[[File:M2TIImage107.png]]
 

== Delete Emails ==

To delete emails, select emails by clicking their checkboxes.

 
[[File:M2TIImage38.png]]
 

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

 
[[File:M2TIImage96.png]]
 

After that, selected emails will be in a 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.

 
[[File:M2TIImage88.png]]
 

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

 
[[File:M2TIImage72.png]]
 

== Manually assign Tariff Import Settings ==

It is possible to manually Assign Tariff Import Settings.
# Toggle Email Details
# Select Tariff Import Rules for specific attachment
# Press Assign Import settings

 
[[File:M2TIImage57.png]]
 

== Retry Rules mapping ==

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

 
[[File:M2TIImage33.png]]
 

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

== Attachment mapping status ==

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

 
[[File: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 its format compared to Tariff Import Rules settings. Numbers after the 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.

 
[[File: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 the Jobs list caption.

 
[[File:M2TIImage87.png]]
 

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

 
[[File:M2TIImage62.png]]
 

= Detailed Import Examples =
* [[ATI - Voicetrading example]]
* [[ATI - Tata example]]
* [[ATI - Speedflow example]]

 
= Template Examples =
* [[ATI - IDT Express Template]]
* [[ATI - Voicetrading Template]]
* [[ATI - Voxbeam Template]]
* [[ATI - Tata Template]]
* [[ATI_-_Deutsche_Telekom_Template]]

 

= See also =
* [[M4 Tariffs]]
* [[Automatic Tariff Import - Email is not taken from the mailbox]]

File:ATI-DeutscheTelekom2.png

2025-10-23T05:53:53Z

Valerij:

2025-02-04T09:36:49Z

3CX PBX interconnection with MOR

2023-09-26T05:58:53Z

Valerij: /* Steps in the MOR */

=Important!!! Manual is not finished !!=

=To connect the multitenant 3CX PBX with MOR you should do these steps on the MOR:=
------------------------------------------------------------------------------------------
==Adding once==
1. Create the Tariff without rates
 
2. Create LCR without providers
 
3. Create an Interconnection User with Tariff and LCR from 1 and 2.
 
==Adding for each whole 3CX PBX server==
1. Create a device for the Interconnection user with the IP of the 3CX PBX server - type Trunk Yes with Ani
 
-------------------------------------------------------------------------------------------

=Adding a new tenant on the 3CX PBX=

==Steps in the MOR==

1. Add new user (name of the tenant)
 
2. Add to this user regular tariff and the LCR with providers assigned to have the possibility to make calls
 
3. Add a Virtual Device to this user
 
4. Add CLI to this Virtual Device (as on the screenshot )

[[File:3cx cli.jpg]]

==Steps in the 3CX PBX==

1. Add trunk (as on the screenshot)

[[File:3cx trunk config.jpg]]

File:3cx cli.jpg

2023-09-26T05:57:57Z

Valerij:

3CX PBX interconnection with MOR

2023-09-26T05:55:03Z

Valerij: /* Steps in the 3CX PBX */

=Important!!! Manual is not finished !!=

=To connect the multitenant 3CX PBX with MOR you should do these steps on the MOR:=
------------------------------------------------------------------------------------------
==Adding once==
1. Create the Tariff without rates
 
2. Create LCR without providers
 
3. Create an Interconnection User with Tariff and LCR from 1 and 2.
 
==Adding for each whole 3CX PBX server==
1. Create a device for the Interconnection user with the IP of the 3CX PBX server - type Trunk Yes with Ani
 
-------------------------------------------------------------------------------------------

=Adding a new tenant on the 3CX PBX=

==Steps in the MOR==

1. Add new user (name of the tenant)
 
2. Add to this user regular tariff and the LCR with providers assigned to have the possibility to make calls
 
3. Add a Virtual Device to this user
 
4. Add CLI to this Virtual Device (as on the screenshot )
==Steps in the 3CX PBX==

1. Add trunk (as on the screenshot)

[[File:3cx trunk config.jpg]]

3CX PBX interconnection with MOR

2023-09-26T05:53:18Z

Valerij: /* Steps in the 3CX PBX */

=Important!!! Manual is not finished !!=

=To connect the multitenant 3CX PBX with MOR you should do these steps on the MOR:=
------------------------------------------------------------------------------------------
==Adding once==
1. Create the Tariff without rates
 
2. Create LCR without providers
 
3. Create an Interconnection User with Tariff and LCR from 1 and 2.
 
==Adding for each whole 3CX PBX server==
1. Create a device for the Interconnection user with the IP of the 3CX PBX server - type Trunk Yes with Ani
 
-------------------------------------------------------------------------------------------

=Adding a new tenant on the 3CX PBX=

==Steps in the MOR==

1. Add new user (name of the tenant)
 
2. Add to this user regular tariff and the LCR with providers assigned to have the possibility to make calls
 
3. Add a Virtual Device to this user
 
4. Add CLI to this Virtual Device (as on the screenshot )
==Steps in the 3CX PBX==

1. Add trunk (as on the screenshot)
[[File:3cx trunk config.jpg|thumb]]

File:3cx trunk config.jpg

2023-09-26T05:52:01Z

Valerij: