Data Model Changes

The following structural changes were made to the database:

Action

Added Keywords (string) for all actions. Starting with this release, all keyword parameters will be implemented using this field in a format of keyword1=value1,keyword2=value2, …

Product Type

The following have been implemented in the database and are reserved for future use in the Load Planning algorithm.

  • Added Cluster By Invoice (bool)
  • Added Cluster By Product Type (bool)
  • Added Cluster By Family (bool)
  • Added Cluster By Item (bool)
  • Added Cluster By Aisle (bool)

View

  • Renamed Audit Packer Builds to Audit Route Commits.
  • Added Is Collection Drill Down (bool), which replaces the feature previously implemented with the Collections keyword.

Omnitracs Wmc

  • Added Mode (choice) for selecting the mode in which data should be sent to a unit. Replaces the feature previously implemented as a keyword.
  • Added Audit Route Commits (bool) which was previously implemented as a keyword.

Paragon Itf

  • Added Minimum Layover Length (time span), replacing the similarly named keyword parameter.
  • Added Location ID Filter (bool), replacing the similarly named keyword parameter.

PowerVue Itf

  • Added Maximum Route Gap (time span)
  • Added Minimum Route Overlap (time span)
  • The above parameters are used in the standard sense with the Common Interface.

Packer

Added Scattered Product Cost (decimal number) which is reserved for future use by the Load Planning algorithm

Roadnet Itf

  • Added Ignore Driver Assignments (bool) which replaces the similarly named keyword parameter.
  • Added Display Session Selector (bool), which replaces the similarly named keyword parameter.

Roadnet Anywhere Itf

  • Added City Source and State Source (choice) for selecting where to obtain the city and state from the RNA data model.
  • Added Cube Source, Weight Source and Quantity Source (choice) for selecting where to obtain the size metrics from the RNA data model.
  • Added Include Route Statuses and Include Route States (string set) to permit filtering the routes to be loaded to be only those with specified statuses (e.g., Built, Active) and/or states (e.g., Operational, Modelling).
  • Added Include Published Only (bool) to limit the routes loaded to include only those marked as published.

Telogis

  • Added Route ID Filter (string) to allow entry of a regular expression to extract a route ID from the string loaded by the corresponding template, replaces feature previously implemented as a keyword.
  • Added Location ID Filter (string) to allow entry of a regular expression to extract a location ID from the string loaded by the corresponding template, replaces feature previously implemented as a keyword.

XRS Itf

  • Added Daily Detail Lookback Days (number) to define the number of days the interface should query when accessing the daily detail web service.
  • Deleted Daily Detail Last Processing Time (was no longer used).

Stored Procedures

Two system SQL Server stored procedures (StpGetDriverActivity and StpGetTractorActivity) were modified to reduce the amount of data returned to the data loading stage of the Common Interface.

Non-Structural Data Model Changes

  • A new trailer event named Hook Trailer was added, and each of Hook Trailer, Drop Trailer and Trailer Announce now have 3 vector cells for Trailer ID, Product ID and Category ID.
  • The definition of Route Event Delay Duration (in Delay events) was changed from an integer (number of minutes) to a TimeSpan. This will only have an adverse effect on two obsolete interfaces that used the former format (XataNet and Mobius).
  • The Route Event Source (choice) value for PowerVue was renamed to PowerVue Activities. A separate value PowerVue HOS was added for hours-of-service transactions only.

Batching No-Slot Orders

In the Load Planning algorithm, when batching (creating containers) orders that have been bundled and sequenced by Order Pick Sequence, orders having no identified slot will be sorted by reverse density (heavy-to-light) sequence.

Roadnet Interface

Expanded the Roadnet Interface to load plan routes, stops and cargo. This capability is activated by using the Load Plan Routes option in the action. City/State and Quantity/Cube/Weight columns are populated based of source selection options that map the RNA data model to the Syntelic data model. Routes may also be filtered based on State, Status and Published condition, as described above in the data model changes.

Filter Processing Time-Outs

Certain interface actions (XRS, Paragon, Telogis) use regular expressions to filter data. The processing of these regular expressions was modified to catch exceptions that could be thrown when (1) invalid an invalid expression is encountered and (2) an endless search (exceeding 2 seconds to parse a single string) is encountered due to a poorly designed expression. In each case, a user-friendly message will be displayed.

Allocation of Actual Distance to Stops

One of the key internal route metric calculation processes (SyncRoute) was modified to recognize zero-duration motion events (such as drive, odometer, and breadcrumb events) that fall on the end of a stop, and to push or assign those events to the next stop so that actual distances generated from those events’ odometer readings will be allocated more accurately to the correct stop. In addition, consecutive odometer and fuel meter readings typically store their associated distance travelled and fuel used on the preceding (first) event of the pair. Starting with his release, the data will be stored on the previous event and stop ONLY when the two readings are tied to the same stop; otherwise the data will be stored on the second event and stop. This is intended to more accurately associate distance and fuel to the correct stop. This changes were implemented specifically for our PowerVue interface, but will impact the storage and calculation of actual distance/fuel for routes of any source – and so should be examined prior to release.

Ignoring Existing Invalid Events in Common Interface

The Common Interface was modified to ignore certain existing events brought in from the Syntelic database when being merged and shaped with new events being loaded from a specific interface. The ignored or excluded events include events with a missing start or finish time, start of finish times that are excessively distant from today’s date, events not having a tractor nor a driver, and HOS events not having a driver. Some of these conditions were previously causing error conditions when executing an interface.

Interpreting Boolean Values

The default accepted values (unless otherwise specified using a format string) interpreted as representing true for a boolean column being converted from a text field was expanded to include any of the following: Y, yes, t, true, 1, and x (case insensitive); and valid false values are N, no, f, false, 0, and an empty string. These rules apply while importing, in the UI when entering a boolean in a text field, and when converting a string to a boolean in a calculation.

PowerVue Interface

The PowerVue interface was rewritten to match the current Cadec API and to match current customer requirements. The interface operates in the context of one division only, as specified by the Center ID parameter. The interface may now be executed as a drill-down from a route view to load data from the selected plan routes. The following describes the data elements loaded. Hours-of-Service. Hours-of-service transactions (on-duty, drive, off-duty, sleeper) are loaded for each driver attached to the actual portion of each selected planned route. The HOS events are loaded across a timespan that ranges from the earliest to the latest activity (non-HOS transaction), expanded on each end by an amount equal to the Maximum Route Gap parameter for the action. HOS events include:

  • Driver
  • Event Type (On-Duty, Drive, Off-Duty, Sleeper)
  • Activity Start
  • Activity Finish

Drivers. The option to load all drivers populates the following data element:

  • ID
  • First Name
  • Last Name

Locations. The option to load all locations includes the following columns:

  • ID
  • Name
  • Address Line 1
  • Address Line 2
  • City
  • State
  • Postal Code
  • Phone
  • Type (always set to “Delivery”)

Route Actuals Overview. The process that loads actual data for route plans is based on a drill-down from a selected set of one or more routes. Within each actual route, PowerVue provides a list of actual stops, within the stops ther may be a list of actual shift activities, and within those activities may be additional detail transactions based on the type of activity. Syntelic route events are constructed as follows: Additional Notes. There is no fuel meter in the PowerVue data, only incremental amounts of fuel used; however, the Syntelic data model stores a fuel meter. Thus, we transform the fuel used amounts in each route (by vehicle) to a fictitious fuel meter that starts at 1,000,000. All events are written with the Assigned Trip Date, Trip Set ID, and Route ID of the associated plan route. Route Start. Route Start events (if the route has any actual data posted) may include the following:

  • Activity Start/Finish (both set to the PowerVue posted route start time).
  • Tractor ID (first one specified for the stop)
  • Driver ID (first one specified for the route)
  • Latitude/Longitude
  • Odometer and Fuel Meter (at the start of the stop)

Route Finish. Route Finish events (if actual route end time is specified) may include the same data elements as for the route start, except that both the Activity Start and Finish are set to the PowerVue posted time of the route end. Location Arrive. Location Arrive events are posted if the route’s stop has any actual data posted, and may include:

  • Activity Start/Finish (from the actual arrive time)
  • First specified route-level Tractor ID
  • First specified route-level Driver ID
  • Location ID (including in the Location VectorCell column)
  • Stop ID constructed as a 3-digit number representing PowerVue’s plan stop number)
  • Latitude/Longitude (at the start of the stop)
  • Odometer and Fuel Meter (at the start of the stop)

Location Depart. Location Depart events are posted if the route’s stop has a specified actual end time, and may include the same data as the Location Arrive, except the fields are as specified at the end of the actual stop. Shift Activity Overview. Each actual stop in PowerVue may include one or more Shift Activities. Each Shift Activity may generate one or more route events, and all typically may include the following:

  • Stop ID (from the actual stop)
  • Vehicle ID
  • Trailer ID
  • Location ID
  • Activity Start (from the Shift Activity)
  • Activity Finish (from the Shift Activity)
  • Odometer and Fuel (from the beginning of the Shift Activity)
  • Source ID is typically used by the Common Interface process. However, because this interface is not a CI process, we use the Source ID to store the raw PowerVue activity type string simply to document the source of the data.

Login/Logout. Login and Logout events are created from Portal Shift Start and Portal Shift Finish activities for informational purposes but are not used in Syntelic processing. Clock-In and Clock-Out. Clock-In and Clock-Out events are generated from Sign-On and Sign-Off Shift activities for informational purposes but are not used in Syntelic processing. Breaks. The following three types of activities are translated into Break events with one of the following Break Descriptions:

  • Break
  • Lunch
  • Restroom

Inspections. The following types of activities are translated into Inspection events with one of the following Inspection Description values:

  • Pre Trip Inspection
  • Post Trip Inspection
  • DOT Inspection
  • Tire Check
  • Cargo Check
  • Hazmat Check
  • Refrigerated Unit Inspection
  • Security Inspection
  • Windows Lights Mirrors
  • Pre Trip Trailer
  • Brake Check
  • Truck Check

Drop-n-Hook. Drop, Hook and Drop and Hook activities each create a general Task event noting the activity type in the Task description. In addition, Drop Trailer, Hook Trailer and Trailer Announce events are generated from detail transactions are available within the Hook/Drop Shift Activity. From these transactions, we general post either a Drop Trailer or a Hook Trailer event; however, a Trailer Announce event is created when PowerVue posts an Unknown type in its drop-n-hook transaction is posted when the transaction type is noted as Unknown. Each event may include the following vector cells:

  • Hook Trailer ID / Drop Trailer ID / Trailer Announce ID (PowerVue Trailer ID)
  • Hook Product / Drop Product / Trailer Announce Product (PowerVue Product ID)
  • Hook Category / DropCategory / Trailer Announce Category (PowerVue Commodity ID)

Delays. Delay activities each create a general Task event noting the activity description in the Task Description (either “Road Delay” or “Location Delay”). For each activity, there may be one or more detailed transactions, each creating a Delay event with:

  • Activity Start/Finish as specified by the PowerVue start/end of the delay.
  • Delay Reason Code as specified by the transaction.
  • Delay Duration calculated as the difference between the start and end time.
  • Delay Type taken from the transaction’s reason code ID.
  • Delay Description is set to the same as the activity description.

Pickup and Delivery. Pickup and Delivery activities each create a general Task event noting the activity description in the Task Description (either “Pickup”, “Delivery” or “Pickup and Delivery”). Note that pickup-delivery transactions may have a transaction type of “UNKNOWN”, in which case the quantities are assumed to be delivery quantities and are posted that way. For each pickup and delivery activity, there may be one or more detailed transactions, each creating a Delay event with:

  • Pickup/Delivery Plan Quantity (from the transaction’s quantity)
  • Pickup/Delivery Actual Quantity (from the transaction’s net quantity)
  • Pickup/Delivery Unit of Measure (from transaction’s container units)
  • Pickup/Delivery Shipment ID (from transaction’s invoice)
  • Pickup/Delivery Cargo Type (from transaction’s commodity ID)
  • Pickup/Delivery Container Type (from transaction’s container ID)

Cleaning Activities. Cleaning activities are posted as simple Task events with one of the following Task Descriptions:

  • Truck Wask
  • Clean Trailer
  • Snow Brush
  • Vac and Rebed

Fueling Activities. Fueling activities are posted as simple Task events with one of the following Task Descriptions:

  • Fueling
  • Fuel Def
  • Fuel Alternative
  • Fueling CNG
  • Adding Additives
  • Fuel CNG Fast
  • Fuel CNG Slow

  Miscellaneous Activities. Miscellaneous activities are posted as simple Task events with one of the following Task Descriptions:

  • Off Duty
  • On Duty Other
  • Trailer Pickup Drop
  • Shift Start
  • Start of Day
  • Report to Truck
  • Report to Office
  • Warehouse Check In
  • All Additional Delivery
  • Backhaul
  • Load
  • Unload
  • Backhaul Load Unload
  • Unknown
  • Unknown Stop Drive
  • Scales
  • Scaling
  • Waiting Time
  • Misc Wait
  • Warehouse Work
  • Meeting
  • Paperwork
  • Other
  • Training
  • Unpaid
  • Pumpwork
  • Shop
  • Unassigned
  • Yardwork
  • Customs
  • California Dispatch
  • Pump Water from Fills
  • Pecticide Application
  • Drug and Alcohol Testing
  • DOT Physical
  • TCI
  • Help Other Driver
  • Non Company