Importing Data using Simple Templates

Note: This menu option only appears for DBA.ADMIN users.

This page is accessed via Operational Planning > Planning Data Import/Export > Import Planning Data.

Even if you are unsure of all of the data required to create certain business objects in OTM, you can still import them using CSV-based simple template. Supported business objects are:

Calendar
Equipment Group
Location
Order Release (ship unit-based only)
X lane
Rate offering (only TL mode)
Rate record

To download the simple templates, see the Knowledge Document 796594.1 on My Oracle Support.

You can create and import several simple templates at once. For example, you can upload all of the templates equipment group.csv, locations.csv, calendar. csv and orders.csv in a single .zip file.

Importing

Select a Transaction Type of either II or IU. See above for a description of each.
Select the Template-based Import check box.
Click Choose File to specify an Input Zip File. This can be a .zip file that includes at least one of the simple templates which you edited with your specific data. To download the simple templates, see the Knowledge Document 796594.1 on My Oracle Support.
Click Upload and the page updates.
Select a Template-based Import Option:
- Import to Database: Imports the data specified in the simple templates to the OTM database and also creates any required related data.
- Export to Table-based Zip: Allows you to import a simple template and export it to a table-based .zip file without importing the simple template into the OTM database. The table-based zip file will contain all of the columns and related-tables (in their own csv files) that can be imported for an OTM business object. If you want to import an OTM business object but are not sure of all of the possible fields that you can specify during import, you can use this option.
- Import to Database and Export to Table-based Zip: Imports the data specified in the simple templates to the OTM database and also creates any required related data. Also, creates a table-based zip file that includes all of the columns and related-tables (in their own csv files) that can be imported for the OTM business object.
Specify a Domain. This Domain is used both as the domain into which data is imported and as the domain name in all exported csv files.
To run the action in the background, select Yes for the Run in Background.

If you selected Yes for Run in Background, select a contact in the Contact ID field.
Click Submit.

You see a Results screen.

Equipment Group Template

The template to download is simple template.zip which contains the equipment group.csv template file.

To create a simple equipment group you can use the Equipment_Group.csv template which supports the following attributes:

Equipment Group ID (mandatory)
Equipment Group Name
Effective Weight (mandatory): If no UOM is entered, then domain default UOM is used. Or, you can enter "value UOM". For example, "100 LB".
Effective Volume (mandatory): If no UOM is entered, then domain default UOM is used. Or, you can enter "value UOM". For example, "10 CUMTR".
Height: If no UOM is entered, then domain default UOM is used. Or, you can enter "value UOM". For example, "10 IN".
Width: If no UOM is entered, then domain default UOM is used. Or, you can enter "value UOM". For example, "2 FT".
Length: If no UOM is entered, then domain default UOM is used. Or, you can enter "value UOM". For example, "10 FT".

Calendar Template

The template to download is simple template.zip which contains the calendars.csv template file. All calendars will have a Start Date of Monday, January 3, 2000, 00:00:00 and Days In Cycle of 7.

To create a simple calendar involves inserting data for both the calendar and the activities. The fields are:

Calendar ID (mandatory)
Activity ID (mandatory if specifying activities) used to specify Activity Type. This can be any PUBLIC record present in ACTIVITY table in OTM.
Calendar Definition (mandatory if specifying Activity ID) used to define the times for a certain activity type on a calendar

Calendar Definition Details

The calendar definition requires a specific format of <Days>_<HH:MM-HH:MM>. Time must be in the format of: <HH:MM> where HH uses the 24 hour format. AM and PM are not recognized. The following characters must be used to represent days in <Days>:

M for Monday
T for Tuesday
W for Wednesday
R for Thursday
F for Friday
S for Saturday
U for Sunday

Example

Let's look at an example calendar definition.

MWF_08:00-12:00 TRS_13:00-17:00 U_10:30-15:30

If this calendar defines when a PICKUP can be performed:

8 a.m. - 12 p.m. on Mondays, Wednesdays, and Fridays
1 p.m. - 5 p.m. on Tuesdays, Thursdays, and Saturdays
10:30 a.m. - 3:30 p.m. on Sundays

Note: You must adhere to the above format when specifying calendar definitions or you will see an error.

Location Template

The template to download is simple template.zip which contains the locations.csv template file.

To create a simple location involves a location and a location role. Optionally, you can add the columns from the calendar template to the location template. See the calendar template section above.

Location supported attributes are:
- Location ID (mandatory)
- Location Name
- City
- Province Code
- Postal Code (mandatory)
- Country Code: If not provided, defaults to USA.
- Location Type: Used to specify the Location Role. This can be any PUBLIC location role. If not specified, then Ship From/Ship To is used.
- Latitude: If not specified in .csv, the system tries to find Latitude and Longitude of the location based on its Postal Code. If found, it uses both the latitude and longitude otherwise the fields are populated as null. If only latitude is specified in .csv, then the system first tries to find the latitude based on the Postal Code; if still not found, value is set to null.
- Longitude: If not specified in .csv, the system tries to find Latitude and Longitude of the location based on its Postal Code. If found, it uses both the latitude and longitude otherwise the fields are populated as null. If only longitude is specified in .csv, then the system first tries to find the longitude based on the Postal Code; if still not found, value is set to null.

Order Release Template

The template to download is simple template.zip which contains the order releases.csv template file and creates an order release and a ship unit.

Note: The Order Configuration is set to One to One. This cannot be changed.

Order release supported attributes are:

Order Release ID (mandatory)
Source Location ID (mandatory)
Source City
Source Province Code
Source Postal Code
Destination Location ID (mandatory)
Destination City
Destination Province Code
Destination Postal Code
Early Pickup Date, Early Delivery Date, Late Pickup Date, and Late Delivery Date: Format is YYYY-DD-MM 00:00:00 UTC. You can specify any PUBLIC time zone. If no time zone is specified (for example YYYY-DD-MM 00:00:00), then the time zone is set to UTC.

Ship unit supported attributes are:

Ship Unit Weight (mandatory): If no UOM is entered, then domain default UOM is used. Or, you can enter "value UOM". For example, "100 LB".
Ship Unit Volume (mandatory): If no UOM is entered, then domain default UOM is used. Or, you can enter "value UOM". For example, "1 CUMTR".
Ship Unit ID
Ship Unit Count
Ship Unit Length: If no UOM is entered, then domain default UOM is used. Or, you can enter "value UOM". For example, "10 FT".
Ship Unit Width: If no UOM is entered, then domain default UOM is used. Or, you can enter "value UOM". For example, "1 FT".
Ship Unit Height: If no UOM is entered, then domain default UOM is used. Or, you can enter "value UOM". For example, "10 IN".

Can a Template Refer to Existing OTM Objects?

You can specify some existing OTM objects in the templates. For example, you can specify the following if the objects already exist in the database.

Calendar without activities
Order release without ship units
Locations on order releases without any fields describing the locations
Locations on lanes, rate offerings, and rate records

When you upload the template, OTM will update the existing objects.

Lane Template

The template to download is simple template.zip which contains the lane.csv template file and the lane definition.

Lane definition supported attributes are:

Lane ID: If Lane ID is not specified, one will be generated using a sequence number generator for lanes. If Lane ID is not specified, a new lane will always be inserted into the OTM, because there is no way to identify if a lane already exists in OTM.
Source Geo Hierarchy is taken from the following. One of the following must be specified.
- Source Location ID: Source Location ID and Destination Location ID are references to the LOCATION table. If these locations do not exist in OTM, a new location will be created in OTM, with default country as USA.
- From City
- From State/Province
- From Zip3: if specified Country defaults to USA
- From Zip5
- From Country
Destination Geo Hierarchy is taken from the following. One of the following must be specified.
- Destination Location ID: Source Location ID and Destination Location ID are references to Location Table. If these locations do not exist in OTM, a new location will be created in OTM, with default country as "USA".
- To City
- To State/Province
- To Zip3: if specified Country defaults to USA
- To Zip5
- To Country

Geo Hierarchy Information

Source/Dest Geo Hierarchy Gid is figured out based on which input is specified in the csv.
If "From/To Country" = USA and "From/To Zip5" is populated, Source/Dest Geo Hierarchy Gid = USZIP5. If only "From/To Zip5" is specified without country, geo hierarchy Gid = "POSTAL_CODE".
If trying to specify a location outside of the US you must specify a Country Code.

Rate Offering Template

The template to download is simple template.zip which contains the rate offering.csv template file and rate offering and related service providers (carriers), stops, etc..

Note: Only TL rates are supported.

Rate offering supported attributes are:

Rate Offering ID:
- If not specified, a rate offering ID will be generated using a sequence number generator.
- If not specified, a new rate offering is always created, because there is no way to identify if a rate offering already exists in OTM.
Carrier Location ID (mandatory): Carrier Location ID specifies the name of the OTM service provider for this rate offering.
Exchange Rate ID: Not included in the simple template, but defaults to DEFAULT when the template is imported.
Rate Distance ID: must be a PUBLIC record. If not specified, the rate distance ID defaults to LOOKUP ELSE ESTIMATE.
Rate Service ID: If specified, must be present in the domain in which data is being inserted. If not specified defaults to TL-SIM which must be present in the domain into which data is being inserted.
Rate Version ID (mandatory): Must be present in the domain in which data is being inserted.
Transport Mode ID: must be a PUBLIC record. If not specified, the transport mode ID defaults to TL. TL is the only mode supported.
Rate Offering Type ID: must be a PUBLIC record. If not specified, the rate offering type ID defaults to TL.
Stops Included In Rate
Minimum Cost: If a cost is specified in the form 300, then the currency defaults to USD. If you specify the currency with a cost it must be in the form of cost space currency (300 CAD). The specified currency must already exist in OTM.

Rate offering stops supported attributes are listed below. The stops must be continuous and must start at 1. For example, 1-5 or 1-2. You cannot skip a stop.

Rate Offering ID
Low Stop: mandatory when specifying stops.
High Stop: mandatory when specifying stops.
Per Stop Cost: If a cost is specified in the form 300, then the currency defaults to USD. If you specify the currency with a cost it must be in the form of cost space currency (300 CAD). The specified currency must already exist in OTM.

Rate Record Template

The template to download is simple template.zip which contains the rate record.csv template file and rate offering, rate record, and related service providers (carriers), costs, etc..

Rate record supported attributes are:

Rate Record ID: This is the rate record ID.
- If Rate Record ID (rate record ID) is not specified, a rate record ID will be generated using a sequence number generator for rate records.
- If Rate Record ID (rate record ID) is not specified, a new rate record will always be inserted into the OTM, because there is no way to identify if a rate record already exists in OTM.
Lane ID: If Lane ID is not specified, one will be generated using a sequence number generator for lanes. If Lane ID is not specified, a new lane will always be inserted into the OTM, because there is no way to identify if a lane already exists in OTM.
Rate Offering ID: If not specified, a rate offering ID will be generated using a sequence number generator. If not specified, a new rate offering is always created, because there is no way to identify if a rate offering already exists in OTM.
Minimum Cost: If a cost is specified in the form 300, then the currency defaults to USD. If you specify the currency with a cost it must be in the form of cost space currency (300 CAD). The specified currency must already exist in OTM.
Stops Included In Rate

To create rate offering stops, use the rate offering simple template. Rate record stops supported attributes are listed below. The stops must be continuous and must start at 1. For example, 1-5 or 1-2. You cannot skip a stop.

Rate Record ID: This is the rate record ID.
Low Stop
High Stop
Per Stop Cost:If a cost is specified in the form 300, then the currency defaults to USD. If you specify the currency with a cost it must be in the form of cost space currency (300 CAD). The specified currency must already exist in OTM.

Rate record cost supported attributes are:

Rate Record ID
Equipment Group ID
Per (mandatory): The column for rate record cost which includes the rate basis item or RBI. This column is required. Listed below are the supported rate basis items. Enter the italicized text in the Per column to create a rate with the associated rate basis item (RBI):
- Shipment: for Shipment RBI
- Distance: for the Shipment Total Distance RBI
- Empty_Distance: for the Shipment Total Empty Distance Traveled RBI
- Loaded_Distance: for the Shipment Total Loaded Distance Traveled RBI
- Transit_Time: for the Shipment Total Transit Time RBI
- Weight: for the Shipment Total Weight RBI
- Volume: for the Shipment Total Volume RBI
Rate (mandatory): This is where you specify the rate record cost: This column specifies the charge per unit of measure of the rate basis item included in the Per column.
- Shipment is per 1 shipment
- Distance is per mile
- Empty_Distance is per mile
- Loaded_Distance is per mile
- Transit_Time is per minute
- Weight is per LB
- Volume is per CUFT

Using the Results Screen

The system validates the data for any missing primary keys, missing value for non-nullable columns, etc.

See Importing Planning Data for more details.

Downloading the Errors .zip file

To view the records with errors, click Download to download a .zip file. The ErrorDetails.txt file includes details of the errors. You can make changes to the .csv files included in the .zip file, re-zip the files, and retry the import.