Upload Rates

This page is accessed via Sourcing > Rate Maintenance > Upload Rates.

All rate offerings, rate records, rate record costs, and accessorial costs are inactive and read only upon upload. The Active check box is not selected which prevents these newly created/edited rates and costs from being used for shipment planning. For rate offerings and rate records, the Read Only check box is selected. You cannot edit these records via the OTM user interface.

You must approve and finalize the rates before they can be used in shipment planning. Once the approved rates are finalized, the Active check box is selected and the Read Only check box is deselected. Now the rates can be used in planning and edited via the rate offering and rate record managers.

Notes:

• If you update an accessorial cost that is used with any other special service or accessorial, then a new accessorial cost is created with the values included in the spreadsheet. The existing accessorial cost is not update.

• For the rate version ID, you must enter the GID (<domain_name>.<ID>) in the spreadsheet upon upload.
• Any columns in the downloaded spreadsheet that contain <domain_name>. require a domain name upon upload.
• If you are updating a rate and remove a value from a cell, such as circuity cost, which leaves the cell blank, the existing circuity cost is removed from the rate.

Prerequisites

Prior to uploading the rates spreadsheet, you edit the spreadsheet to include your new or edited rates. Review Downloaded Rates and Downloaded Rate Costs, Accessorials, and Special Services for notes on how rates are downloaded and what to expect upon upload.

Uploading Rates

1. Make changes to the downloaded spreadsheet so that it contains your new or updated rates.
2. Go to Sourcing > Rate Maintenance > Upload Rates.
3. Click Select File and browse to and select a spreadsheet to upload.
4. In the Rate Load Group field, enter a "free-form text identifier" to group the rates uploaded against a rate load definition. Use the Rate Load Group field to group rates by mode, carrier, geography, year, etc.
5. Click Upload.
   
   Once you click Upload, Oracle Transportation Management runs some validations. If any of these validations fail, you see a failure message on the page. If the upload is successful, you see a success message.
6. Click Done on the results page to return to the Upload page.
7. If upload is successful, you are ready go to the Approval Summary page and view the upload history.

Note: If you are uploading against an existing rate load group and forgot the exact spelling, you can check the spelling using the Approval Summary page.

Upload Validations

When you click Upload, Oracle Transportation Management validates the spreadsheet. If any of the following validations fail, you see an error in the upload rates validation page and the spreadsheet will not upload. The validations check:

1. upload file size is equal to or less than the 50MB,
2. uploaded file type is .xlsx,
3. uploaded file passes virus checking, provided virus checking is enabled,
4. uploaded spreadsheet has hidden RBIs sheet which is required and should never be removed,
5. rate load definition ID is correct,
6. rate load definition ID and rate load group ID are a valid combination,
7. uploaded spreadsheet has the correct number of tabs compared to the rate load definition, and
8. number of columns in each tab of the spreadsheet are the same as the number of columns configured for each tab in the rate load definition.

Note: If the involved parties on the rate load definition do not have a communication method of EMAIL and/or if the email address is not valid, you see a warning message that the involved parties will not be notified; however, the upload completes.

Data Validation

If the upload validations pass, then the data provided in each tab of the spreadsheet is validated against the database. If any columns fail the validation then that particular rate is not processed. Any rate that is not process is added to the failed rates spreadsheet.

For Rate Offering, Rate Record, and Child Tables

For all columns, first these checks are performed depending on whether they apply or not. Once these checks are passed, the checks for child tables run.

• Mandatory data: Data must exist for all mandatory database columns. Use the rate offering and rate record user interfaces as a guide to determine mandatory columns.
• Data type: The data type of the data in the spreadsheet must match the data type of the column in the database.
• Column length: The length of the value provided must fall within the allowed length in the database.
• Unit of measure (UOM): If the column is a unit of measure, the correct unit of measure must be indicated.
• Currency: If the column is a currency, then the correct currency unit of measure must be entered.

Columns in Child Tables

1. Stop-off charges: You must include data in both the amount and currency columns. If only one of them has data, the rate is invalid.
2. Remarks: Both the remark qualifier and remark text columns must contain data. If only one of them has data, the rate is invalid.
3. Involved parties: All three of the columns [Involved Party Qualifier ID (GID), Involved Party Contact ID (GID), and Communication Method ID (GID)] must contain data. If any one of them does not contain data, it is an invalid involved party.
4. Discounts: For each SMC3 discount, all of the checks in the first list are run. If any of them fails, the SMC3 discount is invalid.
5. NMFC class range: The Low, High, NMFC class columns must all contain data. If any are missed, the NMFC class range is invalid.
6. Reference numbers: Both the Reference Number Qualifier and Reference Number columns must contain data. The reference number is invalid, if only one of them is entered.
7. Cost columns: the following checks are completed for all cost columns, such as, accessorial cost, special service accessorial cost, and rate record cost:
   1. If the cost basis type is I or N, validate the charge unit.
   2. If the cost basis type is a unit of measure, validate both charge unit and charge unit UOM.
   3. If the cost basis type is a discount, validate the charge multiplier scalar column.
8. Also, for all cost columns validate that the charges for minimum and maximum cost are currency columns.

For the Cost Parameters Sheet

The following validations run for entries in the Conditions, Options, and Miscellaneous sections of all costs:

1. Validate each condition as follows:
   1. Rate basis item: You must specify a valid rate basis item. If rate basis item is not valid or is null, then the condition is invalid.
   2. Conditional operator: You must specify a valid conditional operator. If operator is not valid or is null, then the condition is invalid.
      1. If the operator is any value other than IS NULL and IS NOT NULL then validate the Low Value column as detailed in the section For Rate Offering, Rate Record, and Child Tables above.
      2. If the operator is BETWEEN, DEFICIT, IS WORK PERIOD and IS NON-WORK PERIOD then validate the High Value column as detailed in the section For Rate Offering, Rate Record, and Child Tables above.
   3. If more than one condition is specified AND/OR/SELECTED must be specified. If the value entered does not match any value in the drop-down list, then the condition is invalid.
   4. Repeat a-c above for all conditions.
2. For all other fields in the Conditions, Options, and Miscellaneous sections of the rate cost that are specified in the Cost Parameters sheet, run validations as listed in the sub-section For Rate Offering, Rate Record, and Child Tables above .
3. For costs in rate offering and rate record structure sheets validate the following:
   1. The cost parameter ID provided for the cost is available in the Cost Parameters sheet. If not, then the cost is not valid.
   2. If the cost parameter ID specified is available in the Cost Parameter sheet but refers to a cost parameter row which has one or more validation errors, then the cost is not valid.

 

Approval Summary

To upload rates, a rate load group/rate load definition combination (approval summary) must have a status of NEW. A rate load group can only be used against one rate load definition at a time.

Does Specified Rate Load Definition Exist?	Does Specified Rate Load Group Exist?	Approval Summary Status	Success or Failure of New Upload
Yes	Yes	NEW	Success
Yes	No	NEW	Failure. There is another rate load group available for this rate load definition.
Yes	Yes	IN PROCESS	Success
Yes	No	IN PROCESS	Failure. There is another rate load group available for this rate load definition.
Yes	Yes	FINAL	Failure. This approval summary is finalized.
Yes	No	FINAL	Success
No	No	NA	Failure. The rate load definition does not exist.
No	Yes	NA	Failure. The rate load definition does not exist.

 

Examples

The approval summary status is set as follows, <Rate Load Definition ID> <Rate Load Group ID> Status.

• If the status is RLD1 RLG1 NEW, then you can upload spreadsheets which use RLD1. However, you cannot upload a spreadsheet against RLG1 which uses a different rate load definition, such as RLD2.
• If the status is RLD1 RLG1 FINAL, then you can upload a spreadsheet against RLG1 which uses a different rate load definition such as RLD2.

Note: A rate load group ID is created using the domain you are logged into. For example, if you are logged into Oracle Transportation Management as RATEMGMT64.ADMIN and enter a new rate load group ID of RLG01 upon upload, the system creates a rate load group with the ID of RATEMGMT64.RLG01. As a result, you cannot log into a different domain and upload a spreadsheet using this rate load group even if appropriate domain grants are provided.