Rate management manual

THE MANUAL ON THE RATE MANAGEMENT IS CURRENTLY BEING WRITTEN AND MAY NOT BE COMPLETE OR 100% UP TO DATE YET. PLEASE EXCUSE OUR DUST.

This is the module you should start your work with. Here your processed rate sheet files show up (either when imported manually via Import rate file or when extracted from emails). The Inbox table contains combinations like Email from -> File -> Rate sheet or just File -> Rate sheet if you import a file manually. You can easily expand them by means of the plus sign on the left of the object name. The system supports importing of .csv and .xlsx files, so the end level of processed objects is Rate sheet.

If the system detects an enabled Parsing config for received email, it is shown in the Partner field and automatically used during the parsing process. Otherwise the default import parameters are applied.

The Processing status field displays one of the following statuses of the import:

• OK - email or rate file was processed properly.
• Ignored by config - file or sheet is ignored by Parsing config.
• No attachments - the system was not able to find any attachment to the email.
• No extension - file has no extension.
• Wrong file type - file type not supported for processing.
• Cant open archive - can not open an archive file.
• Empty file - file is empty.
• Conversion error - cant convert from .xls/.xlsx/.html/.xml, etc. to .csv.
• No rate table - file has no rate table.
• Insert error - cant insert parsed rates to the DB.
• Parsing errors - you can expand the row by the plus sign and check what exact Parsing errors have been detected in the file. Clicking on the row with errors opens the Match columns window with the parsed rates, where you may edit and re-parse them.

The View email body button allows you to check a received email body. The Re-parse sheet button opens a new window, similar to the one in Import rate file, where you can choose a parsing config, define the required date format and determine whether absence of certain date columns should raise errors or not. One additional option is available here: Use batch config for re-parsing - enabling the flag allows you to use the existing config for this batch for re-parsing (from a Parsing config table), no auto-parsing. Ignored if Partner is chosen.

This option lets you import a rate file manually. Click the Import rate file button in the Inbox module and select the file to be parsed. You can also do it by drag-n-drop a file right to the screen. The System will open a window with the following parameters:

• Partner (parsing config) - select the partner in the pop-up window. The System will take the partner's options (as defined in the Parsing config table) and fill out the rest of the parameters in the window with them.
• Date format - choose how the System will process dates. Undefined means that the System will try to determine date format on its own. Day/Month (25/02) and Month/Day (02/25) tell the System exactly how it should process months and days.
• Timezone (UTC if blank) - timezone of the dates in a rate file.
• Error if no Active from - activate the checkbox if the System should generate an error if it is unable to find a date when the rates comes into effect.
• Error if no Active till - activate the checkbox if the System should generate an error if it is unable to find a date when the rate expires.
• Error if area name mismatch - activate the checkbox if the System should generate an error when the area name in the rate does not match the master area name in 5gVision for that rate code (see Country codes).
• Treat duplicate codes - how to treat duplicate codes within the same Area if they occur in imported rates. Duplicate codes in different Areas can not be corrected this way. Please contact your vendor to get a correct rate sheet.
• Treat MCC/MNC codes - if imported codes are MCC/MNC codes for SMS termination - it is recommended to pad MNC with zeros to 3 digits.
• Round Excel rates - raw values of rates in Excel files may differ from what is shown in each cell when a file is open in an Excel application. This option allows to load the rate values as they are shown in Excel cells, not the actual raw values from the file.

When done, click OK to start the import process. The System will create an import batch (or several batches, if there were several spreadsheets in the file)

The system assigns a unique Import batch to every imported rate sheet and creates a separate row in the Parsing log table for it. The table lists the imported batches and their parameters - parsing time, number of rows, partner, type of column match, etc. Every row stores a group of parsing options which can be clustered to the so-called Batch config (date format, timezone, treat duplicate codes, errors if no date from or date till, etc). If a partner is not defined during the rates import, the System processes the rate sheet using default values of them. Otherwise, if a partner is chosen during manual Import rate file or the System automatically detects a partner based on the email data, the corresponding values from the Parsing config table is used. You always can change Batch config options using Re-parse options in the Match columns window.

The Parsing log table displays several statuses of each batch:

• Import status - identical to Processing status for Rate sheets in the Inbox module.
• Compare status - rates comparison status as in the Compare table is written after every comparison, however, the comparison table is saved (accessible via clicking the status) only after moving compared results to Final check table or Main rate table.
• Upload status - status of uploading to an external billing.

You may do initial editing of the imported rates with the help of the Match columns window and, if there are no errors, send the batch to the next processing step - Import edit by clicking the Move to Import edit button, or directly to the Main RateCust / RateVend table by clicking the Move to Main button (see Move to Main from Parsing log / Import edit / Compare).

Here you can also View email body and check File top+bottom rows.

If you don't need to import the rates to the Main table, but only create a CSV file and get it, you can use the Build CSV file button available here.

To make manual post-processing adjustments to a rate file, click an Import status cell, double click a required import batch or select it and click Match columns. The opened Parsed rates window shows the way the System recognized the column content and matched it to the predefined columns of 5gVision. The olive-colored column headers are 5gVision predefined columns, while the green column headers are columns from the original file. It is possible to drag and drop the green headers to the desired position creating a new match, if you are not satisfied with the automatic processing results. It is also possible to combine two original columns into one by dragging one green column under another and selecting the separator for the merged columns in the Join by listbox.

The System also makes some minor corrections on the spot, like removing "-" sings from codes or adding time to dates without time. These changes are displayed in the "final" columns (like Code final). In case of errors occurred during processing, the opened window will be initially filtered to show errors only (see the full list of the errors in Parsing errors).

Unlike most of the errors, it is possible to fix the CountryNotFound , BadCountryCode, ?DateFrom, ?DateTill, NoDateFrom, NoDateTill, and DuplCode errors on the spot.

The CountryNotFound error occurs when the System was unable to find the country name in the reference dictionary. The BadCountryCode error occurs when the country prefix does not match the prefix defined in the 5gVision DB. As the System processes the file and tries to match the prefixes and names from the rate file to the reference prefixes and names kept in the DB, it is possible to stumble upon a country or code not present in the dictionary (e.g. due to spelling errors and such). You can either change the country records in 5gVision (if you are sure they are the wrong ones) or fix the original file. To repair the error, go to the Country codes module or invoke the pop-up menu and click Add country code. In the opened window you can specify the following parameters:

• Code - the country phone number prefix.
• Full Country Name - the full official name of the country.
• Country name to search for, one or two consecutive names - the name of the country to search for in processed rate files during the auto import. Once this name is found, the System will try to match it to the corresponding prefix using this table. The searched words must be unique across the table. If the first word of country name is shared among different countries (like "western"), the parameter should contain two words for the match to be unambiguous (e.g. "western sahara" and "western samoa").
• Dont try to match this code - activate the checkbox, if you want the System to ignore the specified code when matching codes to country names. This may be useful for satellite routes as they are numerous and have too many naming variants.

The ?DateFrom and ?DateTill errors may occur when the System is unable to determine the correct date format, as the date string can result in multiple valid dates. For instance, there is no way of telling whether the following format is Day/Month or Month/Day: 09/11/2014.

Another date-related errors are NoDateFrom and NoDateTill, which are signalled when the System expected the original file to contain dates but found none. To fix both kinds of errors, you need to click Re-parse options in the Match columns window. A new window will open, similar to the one in Import rate file, where you can define the required date format and determine whether absence of certain date columns should raise errors or not.

If you get DuplCode, you can also click Re-parse options and define how to treat duplicate codes.

After trying to correct these issues, you may click Re-parse rates in the Match columns window, and the system will try to process the rate file using new country, date definitions and duplicate codes settings. Please mention that the buttons Re-parse rates in this module and the Re-parse sheet in the Inbox act differently. The first one tries to fix the issues by changing column order right in the DB, which is quicker. But this wont work in all situations, then we need to try doing Re-parse sheet - loading info anew from file to the DB.

Some errors may be fixed using Column actions functionality. Column actions for the current batch can be opened using the Column actions button, or if you click the Column actions button or the Edit Column actions to fix errors link in the Re-parse options window and click , the ColAction-batch window will appear, filtered to display column actions for this batch only (see Column actions).

All other errors require alterations of the original file for now, however you can disable the row with the error and import the file without it. To disable one or several rows, select them, invoke the pop-up menu and click Disable selected rows.

In order not to repeat the manual column match process again for the same Partner, it is possible to save the resulting column layout. Once all processing is complete, click Save config for Partner in the Match columns window. This will store the selected match for this current Partner, which can be viewed in the Parsing config table. This also copies batch column actions back to the partner column actions config (see Column actions). When the System gets another rate file from the same partner, it will try to parse it using the stored options. Please note that all the changes to batch matching process are valid for the batch only and do not affect the partner config unless saved to it. Once all the preparations are complete and the batch has no errors or all erroneous rows are disabled, you may proceed with the next step in batch processing and import the rates into the Import edit table (click Move to Import edit) or directly into the Main RateCust / RateVend table (click Move to Main, see Move to Main from Parsing log / Import edit / Compare).

If you loaded a push/target list on the previous step, you can make a push/target report right from the Parsed rates window using the Push/target report button (see more in Push/target).

During the rate processing in the Import edit table, you may fully customize the imported rates. Each record is editable. You may also add new ones from scratch using the standard green plus sign or delete them using the red cross.

At this step, it is also possible to export the rates in the CSV file (see Build CSV file).

When done, you may move the rates to the next step - start Compare by clicking the Compare Vendor or Compare Customer (see Initial Customer rate plans load), or make a shortcut and import them directly to the main RateCust / RateVend table using the Move to Main button (see Move to Main from Parsing log / Import edit / Compare).

If you loaded a push/target list on the previous step, you can make a push/target report right from the Import edit window using the Push/target report button (see more in Push/target).

The Compare process concerns mostly Vendor rates import. For Customer rates it is applicable during Initial Customer rate plans load.

When you click the Compare Vendor button in the Import edit module, the System will display a dialog, where you can specify the comparison options.

• Rate Plan to compare to - select a Vendor rate plan to compare the imported rates to.
• Rate sets to compare - choose from the following options:
• Imported to latest active or future in Main table - compare the imported rates to the rates, that are already active or will be active in the selected rate plan.
• Imported to previous import batches - compare the imported rates to batches entered in the Previous import batches field.
• Compared batches to previous import batches - compare batches entered in the Compared batches field to batches entered in the Previous import batches field.
• Compared batches - enter source batches used for comparison with the help of the Compared batches to previous import batches option. You may enter several batches delimiting them with commas.
• Previous import batches - enter destination batches used for comparison with the help of Imported to previous import batches and Compared batches to previous import batches options. You may enter several batches delimiting them with commas.
• Ignore disabled records - select the checkbox if you do not want disabled records to be used in comparison (valid for Imported to previous import batches and Compared batches to previous import batches options only).
• Codes are deleted if they are - choose the way the System recognizes records to be deleted:
• Not present in imported rate sheet or marked as deleted - the System deletes the record from the plan if there were no such codes in the compared records or it is marked as deleted.
• Marked as "deleted" in imported rate sheet - the System deletes the record only if it was marked with block.
• Not present in each country of a partial rate sheet, AUTO code find - the System automatically form a country codes list based on the partial rate sheet and use it for deciding what records to delete.
• Not present in each country of a partial rate sheet, MANUAL code set - the System uses country codes from the Codes of replaced countries field for deciding what records to delete.
• Codes of replaced countries - for the Not present in each country of a partial rate sheet, MANUAL code set mode, the list of country codes (eg. 33 for France) for all countries that are fully replaced in a partial rate sheen, comma separated.
• Countries to replace codes for - the list of country names (first word only) for which the codes will be replaced from the partial rate sheet, comma separated.
• Countries to skip replacement - the list of country names (first word only) for which the codes will NOT be replaced from the partial rate sheet, comma separated.
• Active from, overwrite RP template - if not set - the Date template from the Rate plan is used.
• Active from, date template - select a Data template from the Date templates table if you chose to overwrite RP template.
• Active from, Base date, GMT - enter a Base date in GMT to add X days to (a number of days is set in the Date template).
• Active from, specific date, GMT - fill out the Active from date with this specific date in GMT, replaces all other options.
• Area names comparison - choose the options for code area name comparison - Do not match, Exact match, Ignore cases, Ignore cases and symbols, Check only the first 10 letters.
• Compare billing increments - activate the checkbox if you want to compare billing increments as well.
• Warn of earlier Active from - warn if the Active from date in a processed file is earlier than the Active from of the current rate. The Date template should be set to "From file" in this case.

There is a need to explain additionally a partial rate sheet processing with full country replacements. For the most cases the option AUTO code find is proper and enough because most countries have unique prefixes. However, with some countries we may face situations when automating which codes should be chosen for comparison from the Main table is not possible, so you have to use the additional fields Countries to replace codes for and Countries to skip replacement along with AUTO code find or choose MANUAL code set together with the Codes of replaced countries list.

For example, prefixes 1 (USA, Canada, Jamaica, etc) or 7 (Russia, Kazakhstan) represent several countries. If we have a partial rate sheet for Jamaica which has a prefix 1876, the System is not be able to detect an exact list of top prefixes for comparison, and you will get a warning: In this case you have 2 possible ways to fix the issue:

• Use the MANUAL code set option and enter explicitly all imported top (country) codes to the Codes of replaced countries field (1876 in our case).
• Use the AUTO code find option and add all countries that should be excluded (USA, Canada, etc) to the Countries to skip replacement.

If some of the top codes from the amendment are not present in the Codes of replaced countries field, the system will warn you and stop the comparison: If you have a partial rate sheet that contains codes for a lot of countries, and the System can't automatically determine which codes to delete, it is better to split the sheet into 2 separate ones: one with normally detected counties for automatic loading and one with countries like Jamaica.

When the filling is done, click OK. The System will make the comparison and display the results in the Compare table. The table shows the old and new values for each rate parameter and summarizes the outcome in the Comparison result column. The possible results are:

• Added - a new code is added to the rate plan.
• Deleted - the code is deleted from the rate plan.
• Collapsed - a longer code is deleted and rates for a shorter code take precedence.
• Expanded - a new longer code is added and its rate takes precedence over the rate for the shorter one.
• UP - the rate is increased.
• DOWN - the rate is decreased.
• Increment - the billing increment is changed (displayed only if Compare billing increments was active in the comparison options).
• DescrChange - the area description is changed (displayed only if Area names comparison was active in the comparison options).

The Group by strip lets you choose between 2 types of comparison results displaying: grouping rows by Code or Area.

Right from the Compare table you can run the comparison anew using the Compare Vendor or Compare Customer buttons. If you need to empty the table, please use the Clear table button.

After you review the results, you can either move the rates to the Final check table (using the Move to Final check button) or import them directly to the Main RateVend / RateCust table using the Move to Main button (see Move to Main from Parsing log / Import edit / Compare).

When you choose to move the rates being processed from the Compare table to the Final check table, a new window will appear where you may choose the move options. The available options are:

• Records to move to the Final check/Main table - select which records to move - All imported records or Only changed records. All imported records also include records that did not differ from those in the rate plan and thus that were not displayed in the Compare table.
• Active from, overwrite RP template - if not set - the Date template from the Rate plan is used.
• Active from, template for "All other records" - the Active from date will be set according to the All other records rule in the Date template for all records with no changes, or if we don't know which records were changed (if comparison was not done yet).
• Active from, Base date, GMT - enter the Base date in GMT to add X days to (a number of days is set in a Date template).
• Active from, specific date, GMT - fill out the Active from date with this specific date in GMT, replaces all other options.

When done, click OK. The rates will be imported from the Compare table to the Final check table, and you will be forwarded to the last one automatically.

The Final check table allows you to review and adjust the rates once again before committing them to the main RateCust / RateVend table. All rate records are editable and the table functionality is basically the same as the Import edit table.

At this step, it is also possible to export the rates in the CSV file (see Build CSV file).

When done, click Move to Main + Upload. The new window with the following options will open:

• Records to upload to ext. billing - select which records to upload to an external billing - Final check records, Full new A-Z , currently active, Full new A-Z , latest active or future or Full new A-Z , all active and future.
• Supersede future rates - disable all rates from the Main table that have same codes as the newly loaded rates and that will become active in future (always done in the current design). For example, today is October 5, a rate plan has a rate with prefix 1234 active from October 10 and you want to import a rate with the same prefix active from October 20. If you set the checkbox, the October 10 rate will be replaced with the October 20 rate. If you do not set the checkbox, the October 10 rate will last for 10 days and be then overridden with the October 20 rate (see Deleting and overriding rates).
• Create .csv files with new Vendor rates - select if you need to generate .csv files.
• Send emails with .csv to external billing - select if it is necessary to send emails with .csv to the external billing system.
• Upload new Vendor rates to external billing - select if it is necessary to upload new Vendor rates to your billing (if supported).

By clicking OK the rates will be imported to the Main RateCust / RateVend table into the previously chosen Rate plan.

If you are sure you do not need to go through all the processing steps, you may import the rates directly from the Parsing log, Import edit or Compare table using the Move to Main button. The floating windows in all the tables contain the similar set of options except the first one. In the Parsing log and Import edit tables you should select Rate plan to move to, while the Compare table has Records to move to the Final check/Main table, where you should choose which records to move - All imported records or Only changed records. All imported records also include records that did not differ from those in the rate plan and thus that were not displayed in the Compare table. The difference here is because when you are in the Compare table, you already selected a Rate plan on the previous step. The similar options are:

• Active from, overwrite RP template - if not set - the Date template from the Rate plan is used.
• Active from, template for "All other records" - the Active from date will be set according to the All other records rule in the Date template for all records with no changes, or if we don't know which records were changed (if comparison was not done yet).
• Active from, Base date, GMT - enter the Base date in GMT to add X days to (a number of days is set in a Date template).
• Active from, specific date, GMT - fill out the Active from date with this specific date in GMT, replaces all other options.
• Supersede future rates - disable all rates from the Main table that have same codes as the newly loaded rates and that will become active in future (always done in the current design).

When done, click OK. The rates will be imported to the Main RateCust / RateVend table into the chosen Rate plan.

The Upload log table stores history of all imports to the Vendor rates, CSV generations and uploads to external billing (if supported). Every imported batch has its own record in the upload log, where you can find a status and time of moving rates to the Main RateVend table. Besides, if you chose the corresponding options of the Move to Main + Upload window in the Final check table, you can see a status and time of the CSV file generation, sending email and file uploading to an external billing system.

The Upload file gen status field shows you how many imported codes were found in an external billing system and how many from them are new (only if supported by an external billing system). Depending on the amount and ratio of new and found codes the System fill up one of the following columns: New codes, New codes file, Found codes Found codes file. If there are only new or only found codes, all the above fields are left empty because the full list of the codes is already available in the uploaded CSV file. By default, the new codes are displayed in the table if a ratio of them to the total number of the imported codes is less than 80%. Otherwise, the found codes are shown. In order not to overload the table by a huge amount of codes in the New codes or Found codes columns, the System instead represents them as a link to a CSV file in the New codes file or Found codes file fields (by default the number of codes should be more than 100).

When you perform Move to Main + Upload in the Final check table, at first the System makes a snapshot of the actual rates and saves it to a CSV file (its link is available in the hidden by default CSV file snapshot column of this table). This approach allows you to restart uploading of a specific rates batch anytime by means of the Upload to External billing button in case of an error (for example, a break of connection to an external billing) using exactly the same state of rates which was during the initial import. The available options are similar to the Move to Main + Upload: create CSV files, send emails and upload files to an external billing system.

If you need to upload certain Vendor rates right from the Main RateVend table to an external billing system, you can use the Upload rates from Main button.

The View Comparison results button allows you to view comparison results for each upload in a separate Compare table if the comparison for this batch was done. If the upload was done from the Final check table, you can view its saved copy by clicking the View Final check button.