1. Data Management Guide
  2. Importing LTL Rates

Importing LTL Rates

This section describes how to specify LTL rates and gives sample CSV files for several scenarios.

The following tables must be loaded (in order):

  • RATE_OFFERING (setup manually on Oracle Transportation Management web pages)
  • X_LANE (see the Importing X_LANE Data for Rates section.)
  • RATE_GEO
  • ACCESSORIAL_CODE
  • ACCESSORIAL_COST
  • RATE_GEO_ACCESSORIAL (*)
  • RATE_GEO_COST_GROUP
  • RATE_GEO_COST
  • RATE_UNIT_BREAK_PROFILE
  • RATE_UNIT_BREAK
  • RATE_GEO_COST_UNIT_BREAK
    Note: (*) RATE_GEO_ ACCESSORIAL must come after RATE_GEO, but is not required before the remaining tables.

Assumptions:

  • You have loaded the rate offering table using Oracle Transportation Management web pages
  • You have loaded the X_Lane table (see the Importing X_LANE Data for Rates section.)

Simplified ERD for LTL Rates

Simplified Entity Relationship Diagram (ERD) for LTL Rate Data

Simplified Entity Relationship Diagram (ERD) for LTL Rate Data

Table Notes:

  • RATE_GEO Table

    Allow_uncosted_line_items in Y/N (defaults to “N”)

  • RATE_GEO_ACCESSORIAL
    • Left_Operand1 – Basis options define what variable you want to base your conditional charge on.
    • Oper1_gid – The operand you compare with.
    • Low_value1 – Depending on the operand you use, you might need only the low_value1 or additionally the high_value1.
  • RATE_GEO_COST_GROUP Table

    Use_deficit_calculations in Y/N (defaults to “N”)

  • RATE_GEO_COST Table
    • charge_unit_uom_code - unit of measure (e.g. “LB” for pounds, or “MI” for miles)
    • charge_unit_count - hundredweight, etc.
    • charge_action – add (A), setmin (M), setmax (X), multiply/discount (D)
    • charge_break_comparator -identifies data element used to access the break

Scenario 1: Based on Simple Unit Breaks

This first scenario assumes that rates are defined as simple unit breaks.

  1. Import RATE_GEO table.
    RATE_GEO
RATE_GEO_GID,RATE_GEO_XID,RATE_OFFERING_GID,MIN_COST,MIN_COST_GID,MIN_COST_BASE,X_LANE_GID,DOMAIN_NAME
"MYDOMAIN.194-064","194-064","MYDOMAIN.YELLOW",1.0,"USD",1.0,"MYDOMAIN.194-064","MYDOMAIN"
"MYDOMAIN.194-065","194-065","MYDOMAIN.YELLOW",1.0,"USD",1.0,"MYDOMAIN.194-065","MYDOMAIN"
  2. Import RATE_GEO_COST_GROUP table.
    RATE_GEO_COST_GROUP
RATE_GEO_COST_GROUP_GID,RATE_GEO_COST_GROUP_XID,RATE_GEO_GID,RATE_GEO_COST_GROUP_SEQ,GROUP_NAME,DOMAIN_NAME
"MYDOMAIN.194-064","194-064","MYDOMAIN.194-064",1,"MY_GROUP_NAME","MYDOMAIN"
"MYDOMAIN.194-065","194-065","MYDOMAIN.194-065",1,"MY_GROUP_NAME","MYDOMAIN"
  3. Import RATE_GEO_COST table.
    RATE_GEO_COST
RATE_GEO_COST_SEQ,RATE_GEO_COST_GROUP_GID,CHARGE_UNIT_UOM_CODE,CHARGE_UNIT_COUNT,CHARGE_BREAK_COMPARATOR,DOMAIN_NAME
1,"MYDOMAIN.194-064","LB",100,"SHIPMENT.WEIGHT","MYDOMAIN"
1,"MYDOMAIN.194-065","LB",100,"SHIPMENT.WEIGHT","MYDOMAIN"
  4. Import RATE_UNIT_BREAK_PROFILE table.
    RATE_UNIT_BREAK_PROFILE
RATE_UNIT_BREAK_PROFILE_GID,RATE_UNIT_BREAK_PROFILE_XID,DATA_TYPE,LOOKUP_TYPE,UOM_TYPE,DOMAIN_NAME
EXEC SQL ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDDHH24MISS'
MYDOMAIN.LT 1000,LT 1000,U,M,WEIGHT,MYDOMAIN
  5. Import RATE_UNIT_BREAK table.
    RATE_UNIT_BREAK
RATE_UNIT_BREAK_GID,RATE_UNIT_BREAK_XID,RATE_UNIT_BREAK_PROFILE_GID,RATE_UNIT_BREAK_MAX,DOMAIN_NAME
EXEC SQL ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDDHH24MISS'
MYDOMAIN.1000,0-1000,MYDOMAIN.LT 1000,1000 LB,MYDOMAIN
MYDOMAIN.1000-3000,1000-3000,MYDOMAIN.LT 1000,3000 LB,MYDOMAIN
  6. Import RATE_GEO_COST_UNIT_BREAK table.
    RATE_GEO_COST_UNIT_BREAK
RATE_GEO_COST_GROUP_GID,RATE_GEO_COST_SEQ,RATE_UNIT_BREAK_GID,CHARGE_AMOUNT,CHARGE_AMOUNT_GID,CHARGE_AMOUNT_BASE,DOMAIN_NAME
EXEC SQL ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDDHH24MISS'
MYDOMAIN.194-064,1,MYDOMAIN.1000,48.53,USD,48.53,MYDOMAIN
MYDOMAIN.194-064,1,MYDOMAIN.1000-3000,37.56,USD,37.56,MYDOMAIN

Scenario 2: Based on Cost per Pound, Surcharge, and Discount

This scenario assumes that:

  • Freight cost is $0.07 per lb
  • Fuel Surcharge is 3% of Total Cost (Accessorial)
  • Discount is 65% of Total Cost
  • There is a $50 allowance for loading
  • The minimum charge is based on 10,000 lb
  • Total Cost = (weight * 0.07 – 50.00) * (65% Discount) * (Accessorial Surcharge of 3%)
  • Min Cost = (10,000 * 0.07 – 50.00) * (1 - 0.65) * (1.03) = 234.325
  1. Import RATE_GEO table.
    RATE_GEO
RATE_GEO_GID,RATE_GEO_XID,RATE_OFFERING_GID,MIN_COST,MIN_COST_GID,MIN_COST_BASE,X_LANE_GID,DOMAIN_NAME
"MYDOMAIN.194-064-2","194-064-2","MYDOMAIN.YELLOW",234.325,"USD",234.325,"MYDOMAIN.194-064","MYDOMAIN"
  2. Import ACCESSORIAL_COST table.
    ACCESSORIAL_COST
ACCESSORIAL_COST_GID,ACCESSORIAL_COST_XID,CHARGE_MULTIPLIER,CHARGE_MULTIPLIER_SCALAR,CHARGE_ACTION,CHARGE_TYPE,USE_DEFAULTS,CHARGE_MULTIPLIER_OPTION,USES_UNIT_BREAKS,DOMAIN_NAME,IS_FILED_AS_TARIFF
EXEC SQL ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDDHH24MISS'
MYDOMAIN.FS,FS,SHIPMENT.COSTS.AMOUNT,1.03,A,B,N,A,N,MYDOMAIN,N
  3. Import ACCESSORIAL_CODE table.
    ACCESSORIAL_CODE
ACCESSORIAL_CODE_GID,ACCESSORIAL_CODE_XID,ACCESSORIAL_DESC,APPLY_GLOBALLY,DOMAIN_NAME,IS_FLOW_THRU,IS_VAT_EXEMPT
EXEC SQL ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDDHH24MISS'
MYDOMAIN.FUEL_SURCHARGE,FUEL_SURCHARGE,FUEL SURCHARGE,Y,MYDOMAIN,N,N
  4. Import RATE_GEO_ACCESSORIAL table.
    RATE_GEO_ACCESSORIAL
ACCESSORIAL_COST_GID,RATE_GEO_GID,ACCESSORIAL_CODE_GID,DOMAIN_NAME
EXEC SQL ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDDHH24MISS'
MYDOMAIN.FS,MYDOMAIN.194-064-2,MYDOMAIN.FUEL_SURCHARGE,MYDOMAIN
  5. Import RATE_GEO_COST_GROUP table.
    RATE_GEO_COST_GROUP
RATE_GEO_COST_GROUP_GID,RATE_GEO_COST_GROUP_XID,RATE_GEO_GID,RATE_GEO_COST_GROUP_SEQ,GROUP_NAME,DOMAIN_NAME
MYDOMAIN.194-064-2,194-064-2,MYDOMAIN.194-064-2,1,MY_GROUP_NAME_2,MYDOMAIN
  6. Import RATE_GEO_COST table.
    RATE_GEO_COST
RATE_GEO_COST_SEQ,RATE_GEO_COST_GROUP_GID,CHARGE_AMOUNT,CHARGE_CURRENCY_GID,CHARGE_AMOUNT_BASE,CHARGE_UNIT_UOM_CODE,CHARGE_UNIT_COUNT,CHARGE_MULTIPLIER,CHARGE_MULTIPLIER_SCALAR,CHARGE_ACTION,DOMAIN_NAME
1,"MYDOMAIN.194-064-2",0.07,"USD",0.07,"LB",1,"SHIPMENT.WEIGHT",,"A","MYDOMAIN"
2,"MYDOMAIN.194-064-2",-50.0,"USD",-50.0,,1,,,"A","MYDOMAIN"
3,"MYDOMAIN.194-064-2",,,,,1,,0.35,"D","MYDOMAIN"
    Note: An alternative to using the data specified for the RATE_GEO_ACCESSORIAL table above would be to add another Sequence to this table with the following (representing a 3% surcharge of the total value):
    4,”MYDOMAIN.194-064-2”,,,,,1,,1.03,”D”,”MYDOMAIN”

Scenario 3: Based on Cost per Pound, Conditional Surcharge, Global Surcharge, and Discount

This scenario assumes that:

  • Freight cost is $0.07 per lb
  • Unload fee is $10 if the weight > 20000lb (Accessorial)
  • Fuel Surcharge is 3% of Total Cost (Accessorial)
  • Discount is 65% of Total Cost
  • There is a $50 allowance for loading
  • The minimum charge is based on 10,000 lb

Summary

  • Total Cost = ((weight * 0.07 – 50.00) * (65% Discount) + (if weight>20000lb then Accessorial Surcharge of 10)) * (1.03)
  • Min Cost = (10,000 * 0.07 – 50.00) * (1 - 0.65) * (1.03) = 234.325
  1. Import RATE_GEO table.
    RATE_GEO
RATE_GEO_GID,RATE_GEO_XID,RATE_OFFERING_GID,MIN_COST,MIN_COST_GID,MIN_COST_BASE,X_LANE_GID,DOMAIN_NAME
MYDOMAIN.194-064-3,194-064-3,MYDOMAIN.YELLOW,234.325,USD,234.325,MYDOMAIN.194-064,MYDOMAIN
  2. Import ACCESSORIAL_COST table.
    ACCESSORIAL_COST
ACCESSORIAL_COST_GID,ACCESSORIAL_COST_XID,LEFT_OPERAND1,OPER1_GID,LOW_VALUE1,AND_OR1,LEFT_OPERAND2,OPER2_GID,LOW_VALUE2,CHARGE_MULTIPLIER,CHARGE_AMOUNT,CHARGE_AMOUNT_GID,CHARGE_AMOUNT_BASE,CHARGE_UNIT_COUNT,CHARGE_MULTIPLIER_SCALAR,CHARGE_ACTION,CHARGE_TYPE,USE_DEFAULTS,CHARGE_MULTIPLIER_OPTION,USES_UNIT_BREAKS,DOMAIN_NAME,ROUNDING_TYPE,ROUNDING_FIELDS_LEVEL,ROUNDING_APPLICATION,IS_FILED_AS_TARIFF
EXEC SQL ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDDHH24MISS'
MYDOMAIN.FS,FS,,,,,,,,SHIPMENT.COSTS.AMOUNT,,,,,1.03,A,B,N,A,N,MYDOMAIN,N,0,A,N
MYDOMAIN.FS-2,FS-2,SHIPMENT.STOPS.SHIPUNITS.ACTIVITY,EQ,D,S,SHIPMENT.STOPS.WEIGHT,GT,20000 LB,SHIPMENT,10,USD,10,1,,A,B,N,A,N,MYDOMAIN,,,,N
  3. Import ACCESSORIAL_CODE table.
    ACCESSORIAL_CODE
ACCESSORIAL_CODE_GID,ACCESSORIAL_CODE_XID,ACCESSORIAL_DESC,APPLY_GLOBALLY,DOMAIN_NAME,IS_FLOW_THRU,IS_VAT_EXEMPT
EXEC SQL ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDDHH24MISS'
MYDOMAIN.FUEL_SURCHARGE,FUEL_SURCHARGE,FUEL SURCHARGE,Y,MYDOMAIN,N,N
  4. Import RATE_GEO_ACCESSORIAL table.
    RATE_GEO_ACCESSORIAL
ACCESSORIAL_COST_GID,RATE_GEO_GID,ACCESSORIAL_CODE_GID,DOMAIN_NAME
EXEC SQL ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDDHH24MISS'
MYDOMAIN.FS-2,MYDOMAIN.194-064-3,MYDOMAIN.FUEL_SURCHARGE,MYDOMAIN
MYDOMAIN.FS,MYDOMAIN.194-064-3,MYDOMAIN.FUEL_SURCHARGE,MYDOMAIN
  5. Import RATE_GEO_COST_GROUP table.
    RATE_GEO_COST_GROUP
RATE_GEO_COST_GROUP_GID,RATE_GEO_COST_GROUP_XID,RATE_GEO_GID,RATE_GEO_COST_GROUP_SEQ,GROUP_NAME,DOMAIN_NAME
MYDOMAIN.194-064-3,194-064-3,MYDOMAIN.194-064-3,1,MY_GROUP_NAME_3,MYDOMAIN
  6. Import RATE_GEO_COST table.
    RATE_GEO_COST
RATE_GEO_COST_SEQ,RATE_GEO_COST_GROUP_GID,CHARGE_AMOUNT,CHARGE_CURRENCY_GID,CHARGE_AMOUNT_BASE,CHARGE_UNIT_UOM_CODE,CHARGE_UNIT_COUNT,CHARGE_MULTIPLIER,CHARGE_MULTIPLIER_SCALAR,CHARGE_ACTION,DOMAIN_NAME
1,MYDOMAIN.194-064-3,0.07,USD,0.07,LB,1,SHIPMENT.WEIGHT,,A,MYDOMAIN
2,MYDOMAIN.194-064-3,-50,USD,-50,,1,,,A,MYDOMAIN
3,MYDOMAIN.194-064-3,,,,,1,,65,D,MYDOMAIN