Overview
The product rate override import tool allows users to import new, update existing, and delete product rate overrides against service subscriptions in bulk.
Business Rules
The following business rules apply.
General Rules:
- The product must be available on the product rate card.
- Users can specify a price or a markup, but not both.
- The service subscription ID must exist under the service provider account.
- Cancelled or Aborted service subscription cannot have a product rate override applied.
Start Date Rules:
- The start date must not be prior to an Active service subscription start date.
- An override without a start date is permitted if the subscription is Preactive. When the service is activated, the start date of the override will be set to match the start date of the service.
- Back-dating can go back as far as the start of the service start date.
End Date Rules:
- If there is already a product rate override for a subscription with the same minimum quantity it must be end-dated first before attempting to create a new one.
- An end date can be provided when updating an existing override if there is already a start date.
The end date can be set as far back as the service start date.
The end date can be set far in the future with no restrictions.
Overlapping Date Rules:
In almost all cases, overlapping dates for overrides are not permitted. For instance, if a product override rate ends on the 30th of September, another with a start date of the 29th of September cannot be added.
Product rate overrides for the same product and subscription - with the same minimum quantity - are allowed, as long as the start and end dates don't overlap.
- Multiple import files will only be accepted if the start and end dates are not overlapping with the start and end dates for existing product rate overrides.
You can import a product rate override if the product and subscription are already existing, as long as the start and dates are not overlapping with the existing start and end dates.
Validation will be performed to check that the start and end dates of the import file are not overlapping with the existing start and end dates.
Overlapping Date Rule Exception:
- The only use case where overlapping start and end dates are permitted, is a case where the MIN_QTY_FOR_PRICE is varied.
Use Cases
Below are a number of supported use cases along with instructions on how to fill out the import template to meet them.
Multiple Overrides with No Overlapping Dates
Importing multiple product rate overrides for the same subscription is possible provided that the product and subscription are already existing, and as long as the start and dates are not overlapping with the existing start and end dates.
To successfully reset the start date of an override, your import template columns and data should look like this.
Below is an example of this case that the importer supports. As we can see, the override is for the same subscription/product, but there is no overlap in start and end dates.
PROD_RATE_OVERRIDE_ID | SERVICE_SUBSCRIPTION_ID | PRODUCT_ID | MARKUP | PRICE | MIN_QTY_FOR_PRICE | TAX_ID | START_DATE | END_DATE | NOTE |
123456 | 987654 | 1.50 | 1 | 1 | 2025-01-01 | 2025-01-30 | override covers the period in January 2025 | ||
123456 | 987654 | 1.30 | 1 | 1 | 2025-02-01 | 2025-03-31 | override covers the period Feb and March 2025 | ||
123456 | 987654 | 1.20 | 1 | 1 | 2025-04-01 | override covers from April onwards |
Special Notes regarding override start and end dates
When an override for a product ends on a given day and a replacement override is set on the same day, the new override will apply to all new miscellaneous usage charges received from the carrier/service supplier.
Example
The first override for Product ID: 1959761 starts on the 1st of June, 2018 at 00:00:0000. It ends on the 1st of July, 2018 at 00:00:0000.
The new override also begins 1st of July, 2018 at 00:00:0000.
If the misc usage record also has a start date of 1st of July, 2018 at 00:00:0000, the new override rate will apply.
Multiple Overrides with Overlapping Dates but varying Quantities
When creating a set of new overrides, there is a single use case where you can have:
multiple product rate overrides
for the same service subscription and product combination
with overlapping start/end dates.
This scenario requires the MIN_QTY_FOR_PRICE column to have different values. In this case, the system must allow the overrides to be saved.
Below is an example of this case that the importer supports.
PROD_RATE_OVERRIDE_ID | SERVICE_SUBSCRIPTION_ID | PRODUCT_ID | MARKUP | PRICE | MIN_QTY_FOR_PRICE | TAX_ID | START_DATE | END_DATE | NOTE |
123456 | 987654 | 1.50 | 1 | 1 | 2025-01-01 | 2025-01-31 | override covers the price for 1-4 products over January, 2025 | ||
123456 | 987654 | 1.30 | 5 | 1 | 2025-01-01 | 2025-01-31 | override covers the price for 5-9 products over January, 2025 | ||
123456 | 987654 | 1.20 | 10 | 1 | 2025-01-01 | 2025-01-31 | override covers the price for 10+ products over January, 2025 |
Reset Start Date Of Override
Enable the ability to update the start date of an existing override, but only under the following conditions:
The service is preactive
the service is active and the override start date is in the future (the override is not yet in effect).
To successfully reset the start date of an override, your import template columns and data should look like this.
PROD_RATE_OVERRIDE_ID | SERVICE_SUBSCRIPTION_ID | PRODUCT_ID | MARKUP | PRICE | MIN_QTY_FOR_PRICE | TAX_ID | START_DATE | END_DATE | NOTE |
12345678 | 2025-01-30 |
Bulk Update to End Date Existing Overrides
Enable the ability to bulk update the end date of existing product rate overrides.
Once the change is made, there will only two use cases that allow users to update an existing product rate override.
Users can update the start date of the override if the start date is not today, or is in the past. i.e. it is not currently applying an override to a price.
Users can update the end date per the rules below.
To successfully bulk update the end date of existing overrides, your import template columns and data should look like this.
PROD_RATE_OVERRIDE_ID | SERVICE_SUBSCRIPTION_ID | PRODUCT_ID | MARKUP | PRICE | MIN_QTY_FOR_PRICE | TAX_ID | START_DATE | END_DATE | NOTE |
12345678 | 2024-09-19 |
Adding or Updating a Note
The user will be able to enter a note when performing a bulk import to add a new override and update an existing override.
Subsequent updates to the note will override any existing note.
A history of notes will not be kept.
To successfully add or update a note from an existing override, your import template columns and data should look like this.
PROD_RATE_OVERRIDE_ID | SERVICE_SUBSCRIPTION_ID | PRODUCT_ID | MARKUP | PRICE | MIN_QTY_FOR_PRICE | TAX_ID | START_DATE | END_DATE | NOTE |
12345678 | a note |
Update or Delete a Note
The user will be able to delete a note against an override.
The user must only use the text string %%DELETE%% with no leading or trailing spaces or other characters.
If anything other than the string as shown is used, the note will be added/replaced with the string used.
To successfully delete a note from an existing override, your import template columns and data should look like this.
PROD_RATE_OVERRIDE_ID | SERVICE_SUBSCRIPTION_ID | PRODUCT_ID | MARKUP | PRICE | MIN_QTY_FOR_PRICE | TAX_ID | START_DATE | END_DATE | NOTE |
12345678 | ##DELETE## |
Licencing
This feature is available to subscribers of the following:
- Emersion Core
- Optional Modules (Products)
- Upon Request (please contact your Account Manager)
Import Result Notification
This feature utilises the Bulk upload processing notification event (event type 963) to send a notification to the user containing the results of the import attempt.
- If the import is successful, the email notification will advise you when the import process is complete.
- If the import is not successful, the email notification will send details of the records that failed validation and why.
An event template must be created and the event must be enabled before the import tool can be used.
Preparing The Data for Import
To prepare your data for import, you will need to create a CSV import file or you can download the import file below. You will need to extract the csv file and save it.
A hard limit of 10000 rows is supported via this tool, including the header row.
If you need to import more than this, create multiple import files and process them one-by-one.
Importing The Data
To access the import tool:
Admin > Bulk Import > Product Rate Override
- Click the Browse button and select the csv file containing the data you want to import.
- If you want the bulk upload processing notification email to be sent to a different email address than the address defined in the event template, tick the Override? checkbox and enter an alternative email address. This will only apply for this upload. To change the email address for all future imports, you will need to change the recipient defined in the event template.
- Press Submit
- The system will validate the data and if no errors are found, the system will import the data (if there are any errors, the validation will fail). When the notification email is received, the process has been completed.
Validating the Import
Bulk Import Validation
The system will parse the data for invalid formatting and overlapping start/end dates only. It cannot check for other forms of data accuracy. It is strongly recommended that you check the imported data to ensure that the import was accurately imported. To do this, divide your import records into groups of 20. Check a single record in each group. If an error is found, check each previous record for the group until you find the one that was imported incorrectly.
If you find an problem, you cannot roll back the import. You have two options should this occur:
- Manually correct the data.
- Request Emersion fix the errors for you.
Any request to correct imported data must be submitted via a ticket. Emersion will provide an estimate of the work and a quote. The quote must be approved and paid in advance before any restorative work commences.
Data Definition
This section contains a list of the required fields and a definition of what the system considers as valid inputs.
To complete your data import file, you will need access to some reference data.
| Data field | Description | Mandatory? |
|---|---|---|
| PROD_RATE_OVERRIDE_ID | The product rate override ID. Go to Report > List > Service and download the report Product and Access Fee Rate Overrides (id: 1257) | Yes, if updating an existing override’s start/end date or Notes |
| SERVICE_SUBSCRIPTION_ID | The service subscription ID that the override is to apply to. | Yes when adding a new override. No for end dating existing override, or when doing a note update. |
| PRODUCT_ID | The product ID of the product in the system. | Yes when adding a new override. No for end dating existing override, or when doing a note update. |
| MARKUP | The % markup for the override. Not mandatory if price set. Negative markups are permitted. Integers only. Letters or other special characters not allowed. | Yes when adding a new override and if no PRICE. No for end dating existing override, or when doing a note update. |
| PRICE | The overriding price. Negative values are not supported. | Yes when adding a new override and if no MARKUP. No for end dating existing override, or when doing a note update. |
| MIN_QTY_FOR_PRICE | The minimum quantity for the price. This may be an integer or a decimal value, depending on what is permitted on the Product. A maximum of 10 digits is allowed. | Yes when adding a new override. No for end dating existing override, or when doing a note update. |
| TAX_ID | The ID of the Tax. Go to Report > List > Reference and download the report Tax Type List report (id: 874) | Yes when adding a new override and if the PRICE is set. No for end dating existing override, or when doing a note update. |
| START_DATE | Start Date of when the override will take effect. Date format is YYYY-MM-DD | Yes when adding a new override or when resetting the start date of an existing override (as per the rules in this document) No for end dating existing override, or when doing a note update. |
| END_DATE | End date of the rate override. Date format is YYYY-MM-DD | Yes for end dating existing override |
| NOTE | An optional note to describe the change. Entering ##DELETE## will delete any previous notes. | Yes when doing a note update. |
Viewing Imported Data
After importing data it is important to validate it was imported correctly. Choosing a random records and performing a visual check in Cumulus should reveal any mistakes relatively quickly that would be harder to see in an import sheet.
Access Control
There are no controls governing access to this import tool.