Rate import

This handbook demonstrates how to upload rates from a file into a tariff. We assume that the tariff has already been created by the admin.

• Prepare a .csv, .xls, or .xlsx rate file for upload. The file should include mandatory columns with rate codes and prices. See the rate file example below:
  The column with rate code descriptions (e.g., indication of whether the rate code is for mobile or landline destinations) is not mandatory. However, including it is helpful if the file contains new rate codes that need to be added to the system. The descriptions make it easier to identify and manage these new rate codes later on. If you want the “Rate code description” parameter to be automatically filled in during the upload process, create a “Rate code description” column in the file and enter descriptive information before uploading it.
• If your network connection is somewhat unstable, we recommend adjusting the load balancing among the web servers to ensure seamless rate upload. To do this, log in to the Configuration server web interface, open Web Cluster > HAProxyBA group, and select the consistent method in the hash_type option.

1. Start the rate import
2. Review file parsing
3. Review data mapping (match data columns with rate parameters)
4. Review rate information
5. Add new rate codes
6. Review rates summary
7. How to…

1. Navigate to Service catalog > Tariffs and open the needed tariff.
2. Open the Rates panel,click Rate upload on the toolbar,and select a .csv, .xls, or .xlsx file prepared earlier to upload.
3. Leave the following settings as is:
   • Add rates from the file to the existing rates – this option is only used when the vendor’s file contains only rates with price changes, not the full list. If the file includes a rate for a prefix that already exists in the tariff, the new price will override the old one. Rates not currently in the tariff will be added. If a rate exists in the tariff but is not included in the file, it will remain unchanged.
   • Replace all of the existing rates with rates from the file – this option is used when your vendor sends a file with all rates every time, regardless of whether prices or other rate parameters for rate codes have changed or not. If the file includes a rate for a prefix that already exists in the tariff, the new price will override the old one. If a rate code exists in the tariff but is not included in the file, PortaBilling will discontinue that rate code in the tariff.

     You can choose when the replacement should take effect by selecting Now or entering a specific date in the To date field. PortaBilling will upload all new rates and automatically mark any rates not listed in the uploaded file as discontinued.

   • Rates with an 'effective from' date in the past should be uploaded as effective immediately – if the vendor file arrives late and a rate’s “effective from” date has already passed, PortaBilling does not allow you to upload it with that past date. This prevents data inconsistency: earlier xDRs have already been rated using the previous rate and stored in the database. (See the xDR re-rating section for ways to correct such cases). To skip rates whose “effective from” date is in the past, clear this checkbox.
   • Skip rates with the same data – selecting this option allows you to avoid duplicate rates. Sometimes vendors include the same rate codes with the same data (e.g., multiple entries with rate code 44 and price $0.2) several times in the rate update file by mistake. When this option is selected, PortaBilling skips such duplicates and creates only one rate record. If you don’t select this option, the system will not uploadany of the identical entries.
   • Template – selecting a specific template defines the structure of the file to be uploaded. A template is required for the data to be imported correctly. This helps avoid errors and makes uploads faster and more consistent, especially when working with files that follow the same structure.

     If you upload the rates for the first time, the “New template” option is selected automatically. A rate upload template will be created based on the settings you’ve saved at each step of the rate upload process (e.g., during the Review file parsing step, and so on). If you want to set a specific rate upload template as the default for further uploads, define a template in the tariff ( General configuration > Upload/Download configuration > Web upload template). This template will then be selected automatically during the Rate upload step.

   • Time zone – PortaBilling interprets the “Effective from” date and time in the rate file using the time zone you select. By default, it uses the administrator’s time zone. Select the correct time zone to ensure that rates become active at the intended local time. If the time zone is incorrect, especially when the rate change is scheduled for a specific date and time (“effective from” date), rates may start earlier or later than expected, which can result in wrong call costs.
4. Click Next step.

PortaBilling automatically detects the file structure (e.g., available columns).

1. Review the parsing results and make adjustments if needed:
   • Data sheet – specifies which sheet from the uploaded file will be used for processing.
   • Delimiter for multiple rate codes – defines the character, e.g., comma (,), used to separate multiple rate codes within a single cell.
   • Data starts at row – indicates the row where the actual data begins.
   • Column names at row – indicates the row with the column headers (e.g., Rate code, Description, etc.).
2. Click Next step.

1. If the file parsing process doesn’t automatically associate a column (e.g., Rate code description) with the corresponding rate parameter, click the dropdown list next to the misidentified column header and select the correct one.
   In PortaBilling, you configure the rate information using two billing parameters: the first interval ( First Interval) and subsequent intervals ( Next Interval). The first interval defines the minimum duration you charge for every call. The next interval defines the interval used to calculate the billed duration after the first interval. Most vendors provide only one billing interval in their files.
2. When importing such a file into a tariff, you would need to assign the First Interval and Next Interval data fields to that single column that contains the interval. Click the dropdown list next to the Interval column, and select the First Interval and Next Interval checkboxes.
3. If the mandatory Rate code or Price columns are not automatically assigned to the correct header names, PortaBilling will display a warning indicating which data field is missing or incorrectly mapped.
4. Click Next step.

After uploading the rate file and mapping the data fields, the system processes the data and presents it for review.

1. This page includes the following rate information:
   • Status – displays the comparison between the current rate in PortaBilling and the rate uploaded from the file so you can quickly understand how the price changes. A red arrow indicates a price increase (this destination becomes more expensive), and a green arrow indicates a price decrease (this destination becomes cheaper). The status column also highlights “new rates” in this tariff and “new rate codes” that will be added to the system.
   • Off-peak Period – shows the off-peak period defined for the rate via the tariff configuration (Tariff > General configuration). You can change it by selecting another off-peak period from the list already created in the system.
   • First Price and Next Price – you can see whether the price increases or decreases for updated rates. Also, you can double-click a specific cell in the table and enter a new price value. For example, if your vendor notifies you of a last-minute change, you can update a specific rate code directly in the table without re-uploading the entire file. You can edit active rates only.
     The duplicate rates (e.g., the rates that have the same parameters as already existing ones in the database) are highlighted as inactive and therefore appear gray.
2. You can manually assign these statuses to individual rates by selecting Yes in the corresponding column:
   • Payback Rate – this means that the customer is credited for using certain services, rather than being charged (e.g., when the service provider charges other service providers for termination of the calls to their network, they may want to encourage their customers to receive more calls by passing on a certain portion of the revenue).
   • Discontinued – this means that the rate is deactivated immediately. If the tariff already contains a rate for a specific rate code, and you upload it again, with the “Discontinued” status, PortaBilling will update the existing rate and deactivate it immediately.
   • Forbidden – this means that no calls are allowed for this particular rate code.
3. Click Next step.

This step is available if the uploaded rate file includes rate codes not yet defined in the system. You can review and adjust the list of these new rate codes.

1. Each new rate code must be associated with a country. Check and, if needed, manually assign countries to new rate codes.

   PortaBilling automatically assigns a country if the new rate code starts with an existing higher-level prefix already defined in the system. For example, if 33 is assigned to France in the tariff and there is a new rate code 33040 in the file, PortaBilling will automatically suggest France as the country.

2. Optionally, if you use code groups, you can assign a new rate code to the needed code groups:
   • Select a specific rate code(s) and click Assign groups.
   • Select the needed code group in the list and click Assign.
3. Click Next step.

The Rates summary page displays an overview of the rate records to be processed after upload, including the total number of rates, new rates, and new rate codes.

Also, you can fill in the fields in the Uploading options section:

• Save as template – specify a name to save the current import settings as a new template. By default, during the first upload, PortaBilling pre-fills the template name with the tariff name. You can reuse this template when uploading rates to this or other tariffs.
• Send additional notification to – specify an email address to receive a report about the uploaded rates.

Click Upload to finish the rate upload process. If the upload fails, the report will include an attachment. The attached file will contain all rates that failed to be uploaded.

The report will contain the following information:

• Total rates processed – the total number of rates in the uploaded file.
• Total rates created – the total number of rates created, including the newly created rates (the rates which were not present in the tariff before the import) and the overwritten ones.
• Old rates overridden – the total number of rates, which were updated (i.e., the previous rates were replaced by the new ones for some destination).
• Skipped (due to errors) – the number of rates that contained errors (e.g., a typo in the price column) and therefore were not added to the tariff.
• Skipped (duplicates) – the number of duplicate rates (identical to those already in the tariff) that PortaBilling skipped during the upload.
• Rates for destinations, previously absent in this tariff, created – the number of rates created for rate codes that were not in the tariff before the import.
• Destinations, previously absent in the system, created – the number of new rate codes created in the system (billing environment).