V1_4

How can I import tariffs and e-rates?

The rate imports application allows importing buying and selling tariffs as well as e-rates. The tariffs created can be viewed and changed after import in tariffs.

Users needs a specific permission to import rates.

Start: Master Data > Finance > Rate imports

The import function enables importing different kinds of tariffs.
Tariffs for any cost types can be imported.
The import only specifies the layout. 

The following options are available: 

  • Ocean Freight
  • Courier Tariffs
  • e-rates (CSV)

Ocean Freight

  1. Select the file for import.
  2. Format examples:

  Example: LCL

Column name

Optional/
Mandatory

Description

type

Mandatory

Tariff type "buying", "selling" or "internal"

chargeType

Mandatory

Charge type code

branch

Optional

Branch partner code

module

Optional

Module, e.g. "AE", "AI", "SE", "SI"

conveyanceType

Optional

  • "Sea" the default
  • "Air"
  • "Rail"
  • "Road"
  • "Barge"

LCL basis

Optional

Calculation basis for the LCL tariff

  • "w/m" (default)
  • "volume"
  • "chargeable weight"
  • "gross weight"

customer

Optional

Customer partner or group code

supplier

Optional

Supplier partner or group code

shipper

Optional

Shipper partner or group code

consignee

Optional

Consignee partner or group code

agent

Optional

Agent partner or group code

departure

Optional

UN/Locode, country code or region code for tariff departure

destination

Optional

UN/Locode, country code or region code for tariff destination

product

Optional

Product code. For buying tariffs a buying product. Otherwise a selling product.

container depot

Optional

Container depot partner or group code

zip zone Optional ZIP zone code

currency

Mandatory

3-digit currency code, e.g. USD (ISO 4217)

LCL

Mandatory

Rate per M/W

LCL min

Optional

Minimum for LCL rate

LCL max

Optional

Maximum amount

LCL base

Optional

Base amount

LCL units

Optional

Units for the default rate breakpoint

validityStart

Optional

Validity start of tariff

validityEnd

Optional

Validity end of tariff

remarks

Optional

Tariff remarks

rate sheet presentation

Optional

Tarif rate sheet presentation

 

 Example: FCL

Column name

Optional/
Mandatory

Description

type

Mandatory

Tariff type "buying", "selling" or "internal"

departure

Optional

UN/Locode, country code or region code for tariff departure

destination

Optional

UN/Locode, country code or region code for tariff destination

container depot Optional Container depot partner or group code
zip zone Optional ZIP zone code

supplier

Optional

Supplier partner or group code

product

Optional

Product code. For buying tariffs a buying product. Otherwise a selling product.

customer

Optional

Customer partner or group code

currency

Mandatory

3-digit currency code, e.g. USD (ISO 4217)

chargeType

Mandatory

Charge type code

20DC

One column is Mandatory

Example for a container type code. The own container type codes have to be used.

40G0

Optional

Example for a container type code. The own container type codes have to be used.

rate Optional Rate for the non container-type tariff

shipper

Optional

Shipper partner or group code

consignee

Optional

Consignee partner or group code

agent

Optional

Agent partner or group code

validityStart

Optional

Validity start of tariff

validityEnd

Optional

Validity end of tariff

conveyanceType

Optional

  • "Sea" (default)
  • "Air"
  • "Rail"
  • "Road"
  • "Barge"

remarks

Optional

Tariff remarks

rate sheet presentation

Optional

Tariff rate sheet presentation

branch

Optional

Branch partner code

basis

Optional

The calculation basis

  • "W/M"
  • "Volume"
  • "Chargeable weight"
  • "Gross Weight"
  • "Number of containers"
  • "TEU"

base

Optional

Base amount

min

Optional

Mininum amount

max

Optional

Maximum amount


 Example: FCL/LCL

Column name

Optional/Mandatory

Description
type Mandatory Tariff type "buying", "selling" or "internal"
chargeType Mandatory Charge type code
branch Optional Branch partner code
module Optional Module, e.g. "AE", "AI", "SE", "SI"
conveyanceType Optional "Sea" the default
"Air"
"Rail"
"Road"
"Barge"
LCL basis Optional Calculation basis for the LCL tariff

"w/m" (default)
"volume"
"chargeable weight"
"gross weight"
customer Optional Customer partner or group code
supplier Optional Supplier partner or group code
shipper Optional Shipper partner or group code
consignee Optional Consignee partner or group code
agent Optional Agent partner or group code
product Optional Product code. For buying tariffs a buying product. Otherwise a selling product.
departure Optional UN/LOCODE, country code or region code for tariff departure
destination Optional UN/LOCODE, country code or region code for tariff destination
container depot Optional UN/LOCODE or region code
zip zone Optional ZIP zone code
currency Mandatory 3-digit currency code, e.g. USD (ISO 4217)
LCL base Optional Base amount
LCL min Optional Minimum for LCL rate
LCL max Optional Maximum amount
LCL Mandatory Rate per M/W
LCL units Optional Units for the default rate breakpoint
20DC One column is Mandatory Example for a container type code. The own container type codes have to be used.
40G0 Optional

Example for a container type code. The own container type codes have to be used.

validityStart Optional Validity start of tariff
validityEnd Optional Validity end of tariff
remarks Optional Tariff remarks
rate sheet presentation Optional Tariff rate sheet presentation

 

  • W/M (weight or measure) = 1m3 = 1.000kg = 1 x w/m. LCL assumes chargeable weight is calculated at 1000 kg per volume factor.
  • The columns of the file do not need to be in the order described. As long the the description of the columns matches, Scope can import the tariffs.
    Additional containertypes can be added in new columns. FCL and LCL tariffs can be imported in a single file or in different files.

Please find examples for the files here: 

LCL
FCL
FCL/LCL
TEU


Courier Tariffs

Enables importing of selling or buying tariffs for any charge type in XLS or CSV format.

Selling tariff example: Selling tariff csv
Buying tariff example: Buying tariff csv
Courier tariffs can be imported with multiple tables in Excel files - example: Combined xlsx

The format is as follows

Column name

Optional / Mandatory

Description

type

Mandatory

Type of tariff. Possible values: "selling", "buying", "internal"

chargeType

Mandatory

Charge type code as maintained in Scope

currency

Mandatory

3-digit currency code, e.g. USD (ISO 4217)

product

Optional

Product code. For buying tariffs a buying product. Otherwise a selling product.

module

Optional

Module, e.g. "AE", "AI", "SE", "SI"

branch

Optional

Branch code

basis

Optional

The calculation basis

  • "base amount only"
  • "gross weight"
  • "chargeable weight" (default)
  • "volume"
  • "loading meter"
  • "number of pieces"
  • "number of pieces (dgr)"

volume factor

Optional

Volume factor

base Amount

Optional/Mandatory

A base amount (mandatory for basis: Base amount only)

min

Optional

Minimum amount

max

Optional

Maximum amount

departure

Optional

UN/LOCODE, country code or region code for tariff Departure

destination

Optional

UN/LOCODE, country code or region code for tariff Destination

supplier

Optional

Supplier partner or group code

customer

Optional

Customer partner or partner group code

shipper

Optional

Shipper partner or partner group code

consignee

Optional

Consignee partner or partner group code

agent

Optional

Agent partner or partner group code

zip Zone

Optional

ZIP zone code from Zip zone schema

round

Optional

Default is no. When entering yes, the rates of the breakpoint are rounded according to the setting of the currency.

BP FIX x

Mandatory/Optional

Describes a breakpoint with the following properties:

  • from Breakpoint: x kg
  • Method: FIX

BP STEP x y

Mandatory/Optional

Describes a breakpoint with the following properties:

  • From Breakpoint: y kg
    • The import substracts 0.001 from the y value given in the column header of the Excel sheet.
    • "BP STEP 1 100" for a chargeable weight tariff means that the step breakpoint is applied starting with 100 kg
    • "BP STEP 1 100.001" for a chargeable weight tariff means that the step breakpoint is applied starting with 100.001 kg
  • Method: STEP
  • Option accumulating: not checked
  • Unit: x

BP STEP x ACC y

Mandatory/Optional

Describes a breakpoint with the following properties:

  • From Breakpoint: y kg
    • The import substracts 0.001 from the y value given in the column header of the Excel sheet.
    • "BP STEP 1 100" for a chargeable weight tariff means that the step breakpoint is applied starting with 100 kg
    • "BP STEP 1 100.001" for a chargeable weight tariff means that the step breakpoint is applied starting with 100.001 kg
  • Method: STEP
  • Option accumulating: checked
  • Unit: x

BP PROP x y

Mandatory/Optional

Describes a breakpoint with the following properties:

  • from Breakpoint: y kg
  • Method: PROP
  • Unit: x

valid From

Optional

Valid from date

valid To

Optional

Valid to date

DGR level

Optional

DGR level of the shipment

  • "no dgr"
  • "dgr/pax"
  • "cao"
  • "limited or exempted quantity"

weekdays

Optional

Days on which the tariff is applicable like "135" for Monday, Wednesday and Friday

remarks

Optional

Tariff remarks

rate sheet presentation

Optional

Tariff rate sheet presentation

When the base is base amount only, the line must not contain a BP-element. Otherwise, one of the BP-elements is mandatory.

e-rates (CSV)

Scope uses system charge code Airfreight to import rates as buying rates.

This option enables importing of e-rates. Scope allows the import of the old version as well as the new version of the official e-rates sheet, which have been defined by the airlines.

The corresponding file needs to be saved as format ".csv" in order to be imported into Scope correctly.

The first nine lines of the import file are ignored (they usually contain the header data of the e-rates file). When manually creating a file for upload, enter nine empty lines.  

 

Deletion

 

all imported rates that have expired more than 6 month will be deleted on the first Sunday after this date automatically.