FWRoute

The FWRoute table is used to link a specific schedule with a specific resource. All the Resource attributes are duplicated on each FWRoute record so that different settings can be used to build different routes within different schedules. A FWRoute record cannot exist without a schedule or resource record. In addition, there are other required table attributes and they are specified below.

Create: The route record can be created in several ways:

· The resource creation process (based on the preferred schedule key)

· The route copy process (user selection)

· The route move process (user selection)

· The route new process (user input)

Ü Note―Please see the status updates when a route is created.

Ü Note―The Route is not automatically updated when the related resource or schedule is updated.

Delete: Delete selected record(s) – see resource for more delete details.

Update: Update as needed.

Reset: This function can be used to rebuild the selected route based on the current resource and schedule records.

Attribute Name	Type	Field Size	Description	Required
RouteID	int	4	Unique internal DB Identifier of the Route record.	Required
ScheduleID	int	4	Unique internal DB Identifier of the Schedule record. Must exist on the FWSchedule table.	Required
ResourceID	int	4	Unique internal DB Identifier of the Resource record. Must exist on the FWResource table.	Required
ResourceKey	nvarchar	50	Business key to identify the Resource this Route refers to. Must exist on the FWResource table.	Required
ScheduleKey	nvarchar	50	Business key to identify the Schedule this Route refers to. Must exist on the FWSchedule table.	Required
ModifiedBy	int	4	See ModifiedBy.
ModifyDate	datetime	8	See ModifyDate.
CreatedBy	int	4	See CreatedBy.
CreatedDate	datetime	8	See CreatedDate.
RoutingParmameterKey	nvarchar	50	Business key to identify the Routing parameter this Route refers to. Must exist on the FWRoutingParameter table (default =Default).
Type	tinyint	1	This field is used to identify different order types that may require different processing. Valid Values: · 0 = Single day · 1 = Multi-day · 3 = Rechargeable · 4 = Service · 5 = …
Status	tinyint	1	Default to the state of the Schedule. Valid Values: · 0 = Open · 1 = Closed · 2 = Reservation Open · 3 = Reservation Closed · 4 = Planning Open · 5 = Planning Closed · 6 = Dispatch Open · 7 = Dispatch Released · 8 = Dispatch En Route · 9 = Dispatch Exception · 10 = Dispatch Closed	Required
ProjectedTerritory	nvarchar	50	Aterritory assigned to an order by Rmpi.
ProjectedViolation	int	4	System assigned - display only value. Please note this value will need to be rendered before it can be displayed.
ProjectedProfit	int	4	System assigned - display only value. Please note this is the output profit value (calculated by the optimizer) and not the input profit value.
ProjectedDistance	float	8	System assigned. Note― System Assigned. Same as the final stop ProjectedDistance.
ProjectedOnBoard(1-9)	float	8	System assigned. Note― This value is determined from the stop measure amounts.
ProjectedElapsedTime	int	4	System assigned - Elapsed time in the route and considers durations.
ProjectedSlackTime	int	4	System assigned - Estimated Slack Time on the Route.
ProjectedTimeDriven	int	4	System assigned - Estimated Driving Time.
ProjectedWorkTime	int	4	System assigned - Considers only duration fields in the Route.
LastReportedDate	datetime	8	This field will contain the Date and Time of the last reported status.
LastReportedLatitude	float	8	Only specified when different from any associated stops. This field contains the Last Reported Latitude for this route.
LastReportedLongitude	float	8	Only specified when different from any associated stops. This field contains the Last Reported Latitude for this route.
LastReportedStatus	int	4	Same as the stop status and contains the last Status reported to any of the Stops of this Route.
NumberOfStops	int	4	Total number of stops
ProjectedBreaks	tinyint	2	Projected Number of Breaks calculated by the optimization engine
ProjectedRests	int	4	Projected number of rests calculated by the optimization engine
ProjectedDepartedDate	datetime	8	Projected Departed Date
ProjectedArrivedDate	datetime	8	Projected Arrived Date
PlannedDepartedDate	datetime	8	Keeps the original value of the ProjectedDeparteddDate. Once the route enters into an execution state (first GPS or Status msg is received) this field will remain unchanged.
PlannedArrivedDate	datetime	8	Keeps the original value of the ProjectedArrivedDate. Once the route enters into an execution state (first GPS or Status msg is received) this field will remain unchanged.
ActualDepartedDate	datetime	8	Stores the Actual Departed Date reported by the status message
ActualArrivedDate	datetime	8	Stores the Actual Arrived Date reported by the status message
ProjectedBreakTime	int	4	Projected Break Time calculated by the optimization engine
ProjectedRestTime	int	4	Projected Rest Time
ProjectedWaitTime	int	4	Projected Wait Time.
LastReportedStopID	int	4	Database ID of the last reported Stop.
ModifyCount	int	4	Everytime a route record is updated ModifyCount is increased by 1. The Route Planner backend uses it for concurrency control among competing transactions.
PublishRoute	int	4	Field to indicate the publishing status of this Route. · 0 – Has not been published · 1 – Published · 2 – Changed after published
DriverID	int	4	Unique Identifier of the Driver record. Must exist on the FWDriver table.
ProjectedCompletedDate	datetime	8	Projected Completed Date
ProjectedBegunDate	datetime	8	Projected Begun Date.
MasterRoute	int	4	Indicates if this route is a Master Route. Valid values are: · 0 – Normal Route · 1 – Master Route	Required
AVLStatus	int	4	Field reserved for AVL generated status codes.
FreezeInd	bit	1	This fields indicates if the route is frozen. IF this field has a value of 1, then the route cannot be changed at all. It can only take status messages.
PlannedNumberOfStops	int	4	Keeps the original value of the Number of Stops. Once the route enters into an execution state (first GPS or Status msg is received) this field will remain unchanged.
PlannedTimeDriven	int	4	Keeps the original value of the ProjectedTimeDriven. Once the route enters into an execution state (first GPS or Status msg is received) this field will remain unchanged.
PlannedDistance	int	4	Keeps the original value of the ProjectedDistance. Once the route enters into an execution state (first GPS or Status msg is received) this field will remain unchanged.
StartOdometer	int	4	Start odometer reading. This field can be set by GPS or Status messages.
LastReportedOdometer	int	4	This field will be reported in the last gps or status message received.
PlannedElapsedTime	int	4	Keeps the original value of the ProjectedElapsedTime. Once the route enters into an execution state (first GPS or Status msg is received) this field will remain unchanged.
RemainingNoOfStops	int	4	This field will be updated based on the reporting of completed stops.
RemainingDropOffStops	int	4	This field will be updated based on the reporting of completed stops of type Drop off.
RemainingPUStops	int	4	This field will be updated based on the reporting of completed stops of type Pickup.
ActualTravelTime	int	4	Actual Travel Time calculated using GPS and Status messages
DeltaTravelTime	int	4	Route (actual minus planned) drive time in minutes. Note— Delta is calculated so that positive values indicate that the actual time exceeded the planned time, i.e. negative values are good.
PerfTravelTime	float	8	Route (planned divided by actual) drive time as a percent. Note— The percent is calculated so that a value greater than 100% indicates the actual time exceeded the planned time, i.e. values less than 100% are good.
ActualServiceDuration	int	4	Route current cumulative service time in minutes.
DeltaServiceDuration	int	4	Route (actual minus planned) service time in minutes.
PerfServiceDuration	float	8	Route (planned divided by actual) service time as a percent.
ActualDistance	int	4	Route current cumulative distance traveled miles or kilometers (km).
DeltaDistance	int	4	Route (actual minus planned) distance in miles or km.
PerfDistance	float	8	Route (planned divided by actual) distance as a percent.
ActualElapsedTime	int	4	Route current cumulative total time in minutes.
DeltaElapsedTime	int	4	Route (actual minus planned) total time in minutes.
PerfElapsedTime	float	8	Route (planned divided by actual) total time as a percent.
PlannedWorkTime	int	4	Keeps the original value of the ProjectedWorkTime Once the route enters into an execution state (first GPS or Status msg is received) this field will remain unchanged.
InTransitInd	bit	1	Indicate the vehicle is in transit mode (not at a customer site or depot)
EndOdometer	float	8	Odometer reading at the end of a route
ProjectedWorkUnits	float	8	Projected work units
StartDate	datetime	8	Start date of the route
EndDate	datetime	8	End date of the route
wGLNSyncInd	bit	1	Can be set to true or false depending on the response from wGLN. 0 – Failure. wGLN does not have the latest changes. 1 – wGLN is in sync with Route Planner.
wGLNResponse	nvarchar	512	This field holds the response from wGLN
PlannedProfCost	float	8	Planned profile cost
LastReportedTimeZone	float	8	The time zone of last reported loation
LastReportedDateUTC	datetime	8	This field will contain the Date and Time of the last reported status. (Coordinated Universal Time)
ActualArrivedDateUTC	datetime	8	Stores the Actual Arrived Date reported by the status message (Coordinated Universal Time)
ActualDepartedUTC	datetime	8	Stores the Actual Departed Date reported by the status message. (Coordinated Universal Time)
PlannedArrivedDateUTC	datetime	8	Keeps the original value of the ProjectedArrivedDate. Once the route enters into an execution state (first GPS or Status msg is received) this field will remain unchanged. (Coordinated Universal Time)
PlannedDepartedDateUTC	datetime	8	Keeps the original value of the ProjectedDepartedDate. Once the route enters into an execution state (first GPS or Status msg is received) this field will remain unchanged. (Coordinated Universal Time)
ProjectedArrivedDateUTC	datetime	8	Projected Arrived Date (Coordinated Universal Time)
ProjectedDepartedDateUTC	datetime	8	Projected Departed Date (Coordinated Universal Time)
StartDateUTC	datetime	8	Start date of the route (Coordinated Universal Time)
EndDateUTC	datetime	8	End date of the route (Coordinated Universal Time)
LockInd	bit	1	A Boolean value indicating whether a route was locked or not. When a route is locked, the preferred resource and the route position of the assigned orders are set to the current resource and their current stop number
OnBoardMeasure1-9	float	8	Sum of OnBoard Capacity for all routes.
LastPublishedDate	datetime	8	The date/time at which the route was last published.
ProfileCost	float	8	Total cost of this route using Profile Costs
IdleTime	float	8	Idle time at the route level is the sum of all stops’ idle time, including initial and final stations.
FirstIgnition/LastIgnition	float	8	Displays the time recorded between the First Ignition=true value reported after the Start of Route and the Last Ignition=false value reported before the End of Route status messages.
Projected3rdPartyDistance	float	8	Stores the distance calculated using a 3^rd party distance calculator (not rmpi)