Downloaded Rates

The downloaded spreadsheet contains the following sheets:

• Rate Offering Structure: The Rate Offering Structure sheet contains headings and sub-headings related to the rate offering template as defined in the rate load definition.
• Rate Record Structure #: The Rate Record Structure sheets contain headings and sub-headings related to each rate record template as included in the rate load definition. Depending on the number of template rate records, there can be up to five Rate Record Structure # tabs.
• Cost Parameters sheet which lists all rate costs, accessorial costs, and cost conditions for the rate offering and rate record templates and all downloaded rates. Some columns are applicable only to rate costs and some of the columns are applicable only to accessorial costs. For each row in the rate record structure and cost parameters worksheets, you see a Cost Parameter ID column.
• A hidden sheet, RBIs, lists all possible rate basis items.

The downloaded spreadsheet contains the attributes and associated objects, such as stop-off charges, accessorials, special services, remarks, reference numbers, etc., as defined on the following:

• Headings and sub-headings come from the rate load definition which is based on the template rate offering and template rate records.
• Data in rows 6 and higher is pulled from the rate records and associated rate offering that you selected on the multiple structure rate download page.

Note: If you downloaded the spreadsheet using Download without Rates, then the spreadsheet only contains headings and sub-headings based on the rate load definition. No data appears in rows 6 and higher.

Important Information

If the rate load definition contains a different number of associated objects than the downloaded rate, then only the associated objects from the rate load definition are downloaded.

• Example 1, if the rate load definition has two accessorials of LOAD and FUEL SURCHARGE, but you download a spreadsheet for an existing rate offering with three accessorials, LOAD, FUEL SURCHARGE, and UNLOAD, only the LOAD and FUEL SURCHARGE accessorials are included in the downloaded spreadsheet.
• Example 2, if the rate load definition has three accessorials of LOAD, FUEL SURCHARGE, and UNLOAD, but you download a spreadsheet for an existing rate offering with two accessorials, LOAD and FUEL SURCHARGE, only the LOAD and FUEL SURCHARGE accessorials include values in the downloaded spreadsheet.

Note: Column headings and sub-headings in the downloaded spreadsheet always match what is defined on the rate load definition. If you download a rate record and associated rate offering that have different options selected than the template rate offering, you only see the columns as specified in rate load definition.

Note: For all columns, with the exception of version ID and lane ID, an ID must already exist in Oracle Transportation Management and the domain to which you are uploading rates must have access to that ID. For example, if uploading LTL rates, the rate offering ID specified in the Base Rate Offering ID column, must already exist.

Note: Do not move columns, insert new columns (except at the end), reorder columns, or change formatting. You can add new columns at the end only.

Date Columns

In date columns, the date format specified in the OTM user preference of the user downloading the spreadsheet is used. The user preferred date format is displayed as a comment in the header. To verify the date format used, see the hidden RBIs tab of the downloaded spreadsheet. If no date format is specified in your user preferences, OTM uses the default of YYYY-MM-DD.

By default, the date cells only include the date in the user preferred format. Time is not included.

Note: If you edit an existing date or enter a date in a new row, the default Microsoft Excel format may be applied. However, if you enter a date in the format specified in the cell comment in the header, the date will be valid.

Upload Notes

• You can upload both the user preferred date format (text) and the Microsoft Excel default date format (numeric date).
• Not all Microsoft Excel date formats (numeric date) are valid for upload.
• If you select a date format that is not available in your selected locale, the upload fails. For instance you if have Excel set to use a specific locale and you select a date format of DD-MM-YYYY from a different locale, the upload fails.
• If you use a date format which is not specified in your user preference and is not a standard Excel data format, the upload fails.

Rate Offering Structure Tab

The data included in rows 6 and higher are pulled from the rate offerings associated to the rate records specified on the Download Rates page.

Rate Header

The Rate Header section includes the fields on the rate offering that were included in the rate load definition.

Version

You can either upload rates with an existing rate version or create a new version via the uploaded spreadsheet.

Note: You cannot use the spreadsheet to edit an existing rate version.

If you enter new values in the following rows, a new version is created upon upload:

• Version
• Rate Version Name
• Effective Date and Expiration Date: Required date format is mm/dd/yy. However, the "/" may be replaced by "-" depending on the data format selected in the operating system on which the spreadsheet is opened.

Note: The important format is the cell format (right-click on the cell and select Format Cells...). Ignore the format in the formula bar. The format in the formula bar is incorrect, but will not cause an upload issue.

Other Sections

The rate offering structure tab may also include the following sections:

• Transaction code
• Stop-off charges
• SMC3 discounts
• NMFC class ranges
• Accessorials
• Special services
• Involved parties
• Remarks

Upload

Rate Record Structure Tabs

This tab contains the rate records specified on the Download Rates page.

Note: You cannot update active rate costs! Any rate records or accessorial costs which have the Active check box selected are not updated. If you upload changes to an existing, active rate record or accessorial cost, Oracle Transportation Management creates a new inactive rate record, or accessorial cost.

However, you can add new rate records to an existing, active rate offering.

Attributes

The Attributes section may contain the fields of the rate record as included in the rate load definition.

Reference Numbers

The spreadsheet may contain references numbers as included in the rate load definition.

Upload

If you are inserting a rate record, OTM creates all of the reference numbers with values entered in the spreadsheet. If you are updating an existing rate record, OTM deletes all the existing reference numbers and creates new reference numbers based on the values in the spreadsheet.

Lane Info

The spreadsheet may contain geographical hierarchy information as included in the rate load definition.

This section includes the following columns regardless of the geographical hierarchy selected on the rate record:

• Lane ID
• For both source and destination the following are included:
• • Location ID
  • City
  • Province Code
  • Postal Code
  • Country Code
  • Zone 1 through Zone 4
  • Geo Hierarchy
  • Region ID
  • Rail SPLC
  • Rate Station

Upload

You can either upload rates with an existing rate lane ID or create a new lane via the uploaded spreadsheet. If you specify a lane in the spreadsheet, Oracle Transportation Management first checks to see if the lane exists.

• If the lane exists, it cannot be modified using the spreadsheet. For an existing lane ID, Oracle Transportation Management discards any changes made in the spreadsheet.
• If the lane does not exist, Oracle Transportation Management creates a new lane based on the values in the fields listed above.

Postal Codes

When you create a new row in a downloaded spreadsheet, cells, such as postal codes, which may require a leading 0 will not work. You need to change the cell formatting from general to text so that the leading 0 is not automatically removed by Excel. To change the formatting, copy the format from the cell above.

Other Sections

The rate record structure tabs may also include the following sections:

• Transaction code
• Stop-off charges
• SMC3 discounts
• NMFC class ranges
• Accessorials
• Special services
• Remarks
• Rate costs

Transaction Code

The transaction code is required for uploading rates.

Download

The transaction code column defaults to UI (Update and Insert). A transaction code column is included in any rate offering structure and the rate record structure tab.

Upload

The transaction code must be set on a row by row basis on all rate offering and rate record structure tabs.

Depending on the transaction code, Oracle Transportation Management inserts, updates, or deletes the rate offerings and rate records. The transaction codes specific to rate maintenance are:

• I: Same as standard transaction code.
• IN: Oracle Transportation Management creates a new record and the ID of the rate offering or rate records is generated using business number generation or BNG. Used only for Rate Maintenance.
• U: Updates an existing record. If the rate offering or rate record is existing and active, the records are not updated and an error is written to the failed rates spreadsheet.
• UI: Stands for Update and Insert. For rate maintenance, UI works as follows:

Object	Does object exist?	Is object active?	Action taken by transaction code UI
Rate Offering	No		Create a new rate offering based on the ID provided in the spreadsheet. If no ID is provided, rate maintenance uses BNG to generate ID.
Rate Offering	Yes	No	Update existing rate offering based on the values provided in the spreadsheet.
Rate Offering	Yes	Yes	Fail record without processing.
Rate Record	No		Create a new rate record based on the ID provided in the spreadsheet. If no ID is provided, rate maintenance uses BNG to generate ID.
Rate Record	Yes	No	Update existing rate record based on the values provided in the spreadsheet.
Rate Record	Yes	Yes	Fail record without processing.

 

• D: Deletes an existing record. If the rate offering or rate record is existing and active, the records are not deleted and an error is written to the failed rates spreadsheet. To delete a record, only the rate offering or rate record GID is required.
• NP: Same as standard transaction code.

BNG Rules

BNG rules are used when creating rate offering and rate record IDs.

Rate Offering

All new rate offering IDs are generated using the default BN Rule of RATE_OFFERING_XID.DEFAULT which includes the following:

• BN Rule ID: RATE_OFFERING_XID.DEFAULT
• • BN Type: RATE_OFFERING_XID
  • Context: RATE_OFFERING_CONTEXT
• Database Field: RATE_OFFERING.RATE_OFERING_XID
• Rule Definition: {r*:xml=SERVPROV:substring=0,8:id=1}-{r*:xml=MODE:substring=0,9:id=2}-{r*:xml=EFFECTIVE:substring=0,4:id=3}-{nnn:contexts=1,2,3:start=1}
• Rule Description: The first 8 characters of service provider ID - the first 9 characters of the transport mode – the first 4 digits from the effective date – a 3 digit sequence number
• Example: SPGREEN1-TL-2014-001

Rate Record

All new rate record IDs are generated using the default BN Rule of RATE_GEO_XID.DEFAULT which includes the following:

• BN Rule ID: RATE_GEO_XID.DEFAULT
• • BN Type: RATE_GEO_XID
  • Context: RATE_OFFERING_CONTEXT
• Database Field: RATE_GEO.RATE_GEO_XID
• Rule Definition: {r*:id=1:xml=RATEOFFERING}-{nnnnnn:contexts=1:start=1}
• Rule Description: The rate offering ID – 6 digit sequence number.
• Example: SPGREEN1-TL-2014-001-000001

Notes

• You cannot leave the rate offering ID cells on either the rate offering tab or the rate record tab blank. This will result in an error in the failed rates spreadsheet.
• The recommended way to enter rate offering and rate record IDs in the spreadsheet and to get the most benefit out of BNG is as follows:
• • In the rate offering structure tab, enter a rate offering ID without the domain name. For example, RO1.
  • In the rate record structure tabs, for each rate record enter the same rate offering ID of RO1. Then, leave the rate record cells blank.
  • Upon upload the following rate offerings and rate records are created:
  • • Rate offering of SPRED-LTL-2015-001 and any number of the template rate records with the following IDs: SPRED-LTL-2015-001-000001, SPRED-LTL-2015-001-000002, etc.
• For the transaction codes of I and UI, if you do not enter a GID and instead only enter an ID (for example, RATE1) for the rate offering and rate record cells, the IDs are generated using BNG. For example, if your spreadsheet contains one rate offering in the rate offering structure tab and two rate records in the rate record structure tab, the following are created upon upload:
• • A rate offering of SPGREEN1-TL-2014-001 is created based on the rate offering structure tab.
  • A second rate offering of SPGREEN1-TL-2014-002 is created based on rate record structure tab and is associated with the following rate records that were also specified on the rate record structure tab:
  • 1. SPGREEN1-TL-2014-002-000001
    2. SPGREEN1-TL-2014-002-000002

Stop-off Charges

The spreadsheet may contain the Stop-off charges as included in the rate load definition. Stop-off charges may appear in both the rate offering structure and the rate record structure tabs.

Download

• When downloading existing rate offerings, stop-off charges are downloaded only if the low stop and high stop information on the rate load definition exactly matches the low stop and high stop information on the rate offering you are downloading. For example, if both your rate load definition and the rate offering you are downloading contain charges defined for stops 1-3 and stops 4-6, then the spreadsheet contains the sub-headings of: Stop off charge 1-3 and Stop off charge 4-6.
• You see entries in the amount and currency cells for each stop-off charge group.
• If you download a rate offering that has different stop-off charges than the rate load definition, the downloaded spreadsheet only contains the stop-off charges as defined on the rate load definition. For example, if the template rate offering contains the stop-off charges of 1-3 and 4-6, but the downloaded rate includes stop-off charges of 1-6 and 7-10, then the spreadsheet only contains stop-off charge headings of Stop off charge 1-3 and stop-off charge 4-6, and there are no values listed in the amount and currency cells for the downloaded rate offering.

Upload

You can enter values of amount and currency for any stop-off charges available in the downloaded spreadsheet. You cannot add stop-off charges that were not originally specified in the rate load definition. For example, if the rate load definition contains the stop-off charge 1-3 and stop-off charge 4-6, you can only add amount and currency information for those stop-off charges. You cannot add additional stop-off charge groups such as Stop-off charge 8-9.

The low stop and high stop information is copied from the template rate offering upon upload.

SMC3 Discounts

The spreadsheet may contain Discounts (SMC3 discounts) as included in the rate load definition. SMC3 discounts may appear in the rate offering structure and rate record structure tabs.

Download

While downloading existing rates, if the rate offering being downloaded has SMC3 discounts, then the related columns are downloaded on the spreadsheet, otherwise the columns are left empty.

Columns included are:

• Min Discount
• L5C Discount
• M5C Discount
• M1M Discount
• M2M Discount
• M5M Discount
• M10M Discount
• M20M Discount
• M30M Discount
• M40M Discount

Upload

Upon upload, Oracle Transportation Management inserts, updates, or deletes SMC3 discounts for the uploaded rate offering based on the values provided on the spreadsheet.

NMFC Class Ranges

The spreadsheet may contain NMFC class ranges as included in the rate load definition. NMFC class ranges may appear in the rate offering structure and the rate record structure tabs.

Download

Columns included are:

• NMFC Low
• NMFC High
• Rate As NMFC Class

Upload

If you insert a rate offering, create all the NMFC class ranges for which there are values entered in the spreadsheet. If you are updating a rate offering, delete all the existing NMFC class ranges and create new NMFC class ranges based on the values available in the spreadsheet.

Accessorials

See Downloaded Rate Costs, Accessorials and Special Services.

Special Services

See Downloaded Rate Costs, Accessorials and Special Services.

Involved Parties

The spreadsheet may contain Involved Parties as included in the rate load definition. Involved parties may appear in the rate offering structure tab.

Download

Columns included for each involved party are grouped under the column headings Involved Parties and Involved Party #:

• Involved Party
• Communication Method
• Involved Party Qualifier ID

Note: These are the involved parties on the rate offering. These are not the involved parties that are notified during the rate upload and approval processes.

Upload

If you insert a rate offering, OTM creates all of the involved parties for which there are values entered in the spreadsheet. If you are updating a rate offering, OTM deletes all the existing involved parties and creates new involved parties based on the values available in the spreadsheet.

Remarks

The spreadsheet may contain Remarks as included in the rate load definition. Remarks may appear in the rate offering structure and the rate record structure tabs.

Download

Columns included for each remark are grouped under the column headings Remarks and Remark #:

• Remark Qualifier ID
• Remark Text

Upload

If you insert a rate offering or rate record, OTM creates all remarks for which there are values entered in the spreadsheet. If you are updating a rate offering or rate record, OTM deletes all the existing remarks and creates new remarks based on the values available in the spreadsheet.

Rate Costs

See Downloaded Rate Costs, Accessorials and Special Services.

Data not Included in Spreadsheet but Uploaded to new Rates

Rate Offering

Data entered on the following rate offering tabs are not available in the rate load definition and will not be included in the downloaded spreadsheet. However if data is populated in these fields in the template rate offering, then the values in the template rate offering and template rate records are added as part of the upload process.

• Rules and Terms
• Continuous Move Rule Data
• Comments

The following fields from the Continuous Move Rule Data tab on the rate offering are included in the Rate Header section of the rate load definition. If information is entered in any of these fields in the template rate offering, that data will be copied to the uploaded rate offering.

• Max Number of Shipments
• Use Same Equipment
• Percent of Empty Cost Applied to Previous Shipment