Roadshow Connection/Integration Setup
1.
ODBC Setups
a.
A MAPDB ODBC must be setup to connect to the
Roadshow db or roadshow will not launch
b.
For the integrations, an ODBC must be setup to
connect directly to the BWPL database
c.
Login as sqladmin
d.
Use Default dataset
e.
Uses the BW_ROImport_Orders for all orders
f.
Uses the BW_ROImport_Customers for all customers
2.
Create Product Classes
a.
If you do not do this step, you will get the
error “Commodity Type does not exist” when you attempt to import your orders
b.
The commodity type is similar to an Item Class
that contains UofM, Weight and Volume defaults depending on the item class
c. We will need to create a
separate product class for every single item
d.
Open Roadshow
e.
Click View>Data Manager
f.
Click Products on the bottom bar of buttons
g.
Click Edit>New Record
h. Create an item with a default Commodity
Class and Commodity Type of “C”
3.
Create Items
a. Items
can be imported from the orders from the order item number field, however the
actual weights and volumes for the item have to be set by assigning the item to
a commodity code, then defining weight and volume modifiers for each item if it
does not conform to a commodity type
b. These
additional modifiers cannot be imported
4.
Setup Route Services
a.
Routes>Services>Route Set Services
b.
Select Route Set>Global Rename
c.
5.
Create Vehicle Type
a.
View>Data Manager>Vehicle Type
b.
Edit>New
6.
Create Vehicle
a.
View>Data Manager>Vehicle
b.
Edit>New
c.
Fill in route name and tracking number with
actual route it will be servicing
7.
Create Depot
a.
Tools>Network\Customers>Depot
b.
Network>New
c.
Click on map to set depot location
d.
Select depot
e.
Click Network>connect
f.
Connect the depot to the route by clicking on
the yellow customer site on the route
g.
Connecting the depot will enable the “process”
button
8.
Setup Imports
a.
Launch Roadshow enterprise
b.
Click on File, Import (this can be automated,
update this doc)
c.
In the drop down menu at the bottom, select the
type of import you want to setup
d.
Create a new connection
e.
Select odbc
f.
Select the odbc to connect to BWPL
g.
Enter the SQL statement to grab the data you
need
h.
i.
This must be done for Customers and Orders
j.
OrderHdrandLine Import
k.
|
SELECT
'E' as [import command]
,[CustomerID]
,[roorderid]
,[ReqShipDate]
,[QtyOrdered]
,[OrderType]
,[Comment]
,[BuildNumber]
,[DeliveryDate],[deliverydate] as
Date2,0000 as opentime, 2359 as closetime, Itemnumber, 1 as Multiday,'xxxxxx
' as ServiceDays
FROM [BWPL].[dbo].[BW_ROImport_Orders]
|
l.
m.
9.
Get customers to a valid location on the map if
the Geocode is wrong
a.
View>Network>Stops
b.
Double click on a stop
c.
Click “Clear Location” to remove current
location
d.
Move the map to the location you want
e.
Double click on the stop to open the stop
properties window for that stop
f.
Click on the map to set the location
g.
Click Apply to save the location
h.
10.
Setup Active time Windows
a.
Double click on Customer
b.
Customer Details>time windows
c.
Enter time windows>click Update
11.
Connect new Customers to Network
a.
View>Network
b.
Tools>Autoconnect
12.
Setup Load Splitting
a.
Tools>Routing Parameters
b.
BW>Order Splitting>Split Load Routing
c.
Setup options as required
d.
To set if loads are split based on volume or
weight, go to
i. Tools>Routing
Parameters>Route Limits>Capacity Limitation>Both
13.
Process Orders
a.
The routing date controls how orders are grouped
together for processing sets
b.
Use the priority field to control which orders
are loaded first
c.
Tools>Process Orders
d.
Click Process
e.
Select Depot - OG
f.
Select Parameter Set - BW
g.
Click service time formulas, select BW
h.
Setup customer types and different times each
customer takes to service
i.
Apply and save
j.
Set split options here, tick split load routing
k.
l.
14.
Auto-Route
a.
Go to View>Route Planner
b.
Go to tools>Auto-route
2.
a.
b.
Tools>Autoroute>Vehicle Manager>Add vehicles
to depot
i. Drag
vehicles from one route to another
c.
Select parameter set “BW”
d.
Click Route
e.
This will generate the route
f.
Click Show to get a route list
g.
Click View>Route Planner>Press f4 to see
the route
h.
Right click on red line>show path to show
actual path for driver
i.
Click save to save the route
j.
This can then be exported to a flat file, or
connect to the database directly via views to pull the data
15.
Assign Vehicles and Drivers
k.
Click 2nd icon to open route planner
l.
Click Routes>Services>Route Services
m.
Select Route
n.
Scroll to the right, enter vehicle type, vehicle
and driver
i. Or
click Assign Drivers and Assign vehicle
o.
Click dispatch
p.
It will renumber the route, and dispatch it
q.
Save and close RS
r.
Export the file, and it will populate all the
tables
16.
Export Format
a. YOU MUST RUN THE EXPORT
PROCEDURE IN ORDER FOR THE SQL TABLES TO UPDATE WITH THE CORRECT ROUTE DATA
b.
route: dispatch date
c.
route: name
d.
stop: sequence number in route
e.
order: order number
f.
order: order size
g.
order: volume
h.
We are now reading directly from the
BW_RO_OrderExport View.
i. Any
null route_indexes means that line items for that order was not routed
17.
Geocode Conversion
a.
BW is recording geocodes in the following format
i. Lat:
N39.637 Long W23.526
ii. These
can be converted to Degrees,Minutes,Seconds using the following formula
1.
N = 10 Degrees, W = -61 Degrees
2.
Minutes = digits before the decimal
3.
Seconds = 60 x digits after the decimal
iii. Conversion
example
1.
N = 10, W = 61
2.
39.637 = 39 Minutes, 23.526 = 23 Minutes
3.
.637 = 60x0.637 = 38.22 Seconds, .526 =
60x0.626=31.56 Seconds
4.
N39.637 = 10d 39’ 38.22” , W23.526 = -61d 23’
31.56”
iv. To
further convert to decimals
1.
Absolute values are used, and then converted to
East or West
2.
East = 1, West = -1
3.
(Degrees + Minutes / 60 + seconds / 3600) * (E
or W)
18.
Command Sets
a.
Command sets stop running if there is an error
i. We
need to process each routing class, and export each set to a separate truck
ii. We
need
1.
Process C, process H, process K
2.
Export all as one large file with all routes and
stops for each truck
19.
Questions for Roadshow
a.
What is the correct format to import the
customer’s geocodes?
i. Lat – 5 digit decimal – decimal
degrees
ii. Long – 5 digit decimal – decimal
degrees
b.
Item weights and volumes in extender?
i. Extender – build weights, L
inches, W inches, H inches
c.
How can I set it up so that if a route cannot
deliver to all customers within the service times, it creates another route?
i. This depends on how many
vehicles are setup, and the service times and drive time parameters
d.
If I have 2 depots. In a single route, It loads
from 1 in the morning, and the other in the afternoon, how do I configure this?
i. This would have to be done manually
as two processes. Each would begin at a diff depot.
e.
Can I set the default route numbering sequence
to 201 and have it increment automatically?
i. No, must have 4 digits minimum
f.
How do I default the order processing to use a
specific Depot(OG) and Parameter set (BW)?
i. Will only need 1 depot, do not setup others
g.
How do I default the parameter set used in the
Auto-Route(BW)?
i. *SESS* means the last parameter set you used, so if you used
BW, it will continue to use BW
h.
Are we automating any of the tasks? What is
being automated, and what is being done manually?
i. TBD
i.
How do I
automate
i. Imports
– yes, by name
ii. Order
processing – yes, by date
iii. Auto
Connect New customers to network - yes
iv. Auto
Routing – can be done, but will most likely remain manual
v. Exports
– yes, by name
vi. User guide has scripts
Import Source Script
SELECT
'E' as [import
command]
,[CustomerID]
,[DocumentNo]
,[ReqShipDate]
,[CommodityType]
,[QtyOrdered]
,[TotalShippingWeight]
,[TotalVolume]
,[OrderType]
,[Comment]
,[BuildNumber]
,[DeliveryDate],[deliverydate] as Date2,0000 as opentime, 2359 as
closetime, Itemnumber, 1 as Multiday,'xxxxxx ' as ServiceDays
FROM
[BWPL].[dbo].[BW_ROImport_Orders]
Process Orders Options
Aggregate orders: select to aggregate orders during
processing. Aggregating combines orders that share the same routing date,
class, set, vehicle stop, commodity class, and order type, and that have
compatible delivery time windows.
• Establish service time via formulas: when selected,
Route Planner RS processes the order set using the standard service time
formulas that have been defined on the Service Time Formulas tab; when
not selected, Route Planner RS assigns each order a service time based on
service times in the product database and customer/stop service times shown in
the Network/Customer module.
• List un-connected stops (for use with Standard Road
Networks only): Route Planner RS lists stops in the order set that have
temporary connections. During order processing, Route Planner RS creates
temporary network connections (either stop-to-stop or stop-to-underlying
network point) for any stops in the order set that were not connected in the
Network/Customer module. These temporary connections let the stops be routed.
• Remove 1-way streets: when selected, Route Planner
RS routes as though all streets are two-way; useful for routing sales people in
dense areas where most travel between stops is done by walking.
• Source/follow-up order naming convention: used to
identify pairs of orders that must be serviced on the same route in a
particular sequence. This is normally used to represent a pick-up at a stop
followed by a delivery at a second stop. It may also be used to represent two
visits to the same stop on the same route that must be done in a particular
sequence. When this option is selected, Route Planner RS looks for pairs of
orders whose order numbers use the follow-up/source order naming convention, in
which a source order and its follow-up order are given numbers that are
identical except for the last character (S for source, F for follow-up). When
it is not selected, Route Planner RS uses the source-order customer's
identification on the follow-up order to identify the corresponding source
order.
• Split load routing: Use this option when two
vehicles may be needed to service one aggregate order in cases where it would
improve loading efficiency or where an order is larger than a truckload. Route
Planner RS creates a ‘dummy’ duplicate aggregate order for each aggregate order
that allows the order to be split into two parts of different sizes where each
part may be serviced by a different route.
• Sum sales values during aggregation: determines
aggregate order sales value; default aggregate order value takes first sales
value imported among aggregate orders.
• Track unprocessed orders: select first time you
process an order set if it is likely more orders will be added to the database
after the order set is processed; allows you to find out later whether orders
have been added to the set. Use this option only if your orders come in
gradually. If you import all of your orders into Route Planner RS at the same
time, it is not necessary to track which have not been processed.
• Vehicle type/region restrictions (for use with standard
road networks only): prevents vehicles of certain types from being routed
into particular regions; you must first restrict vehicle types in the Data
Manager module; then restrict regions in the Network/Customer module. When this
option is selected, Route Planner RS does not route a vehicle that is of a
restricted type into a restricted region. If this option is not selected,
vehicles of all types are routed into all regions.
• Windows spanning start of day adjustment: adjusts
time windows for orders in set so deliveries can begin on either of two
consecutive days. When it is not selected, Route Planner RS checks the
customer/stop time windows on either side of the start-of-day time and routes
the order on the day that has the larger time window. When it is selected,
Route Planner RS
• Turns each stop's existing hard window into a soft window.
• Gives every order in the set a new hard window of 00:00 to
23:59.
• Uses the new soft window times to permit deliveries to
begin on either of two consecutive days.
• Deadline arrival time based on service time: Route
Planner RS can adjust customer/stop time windows in the processed order set to
ensure that orders arrive in enough time for service to be completed before the
stop closes. Under Deadline arrival time based on service time, apply
one of the following options to be applied to the entire processed order set:
• Hard windows only: Route Planner RS adjusts closing
times of all stops in the processed order set by subtracting service time from
customer/stop hard window end time. For example, if a stop closes at 11:00, but
takes 30 minutes to service, the delivery must arrive at 10:30 in order for
service to be completed. Route Planner RS adjusts the stop's close time
accordingly.
• Hard and soft windows: Route Planner RS adjusts
both soft window open times and hard window close times.
• None: Route Planner RS assumes that if a driver
arrives at stop before the time window ends, service can be completed; time
windows are not adjusted.
• Use historical data for: Route Planner RS can use
actual historical data stored in the feedback tables to calculate on-site
service times and stop-to-stop driving times when it is processing an order
set. (If there are no historical data available, Route Planner RS uses the
network and standard service time computations.)
• Road network type:
• Standard: uses the standard Route Planner RS
network.
• None: does not use a network when processing - fast
way to process orders with estimated travel times/distances.
• External: uses external network for paths, travel
time and distance calculations. External Network parameters on the Auto-Route
window are used to adjust travel speeds for different road classifications in
an external network. See the section External
Network Parameters for details.
• Accelerated Standard: uses the standard Route
Planner RS network, but does not load any vehicle stop links from the database.
Use in-memory auto-connect to create the vehicle stop-network connections.
