Greene King

TomTom WEBFLEET Planned Vs Actual Solution Design

CALIDUS ePOD

2nd February 2016 - 1.0
Reference: REQ 332569

Introduction

This document is the TomTom WEBFLEET Planned Vs Actual Solution Design.

Objective

The primary purpose of this document is to document the requirements gathered from Greene King from various sales meetings.

Scope and Limitations

Time Windows will be included as part of this project. However, the changes here are to support multiple time windows from the bespoke DiPS interface, and for use within the Planned Vs Actual report only - there is no mechanism of viewing or editing these through the ePOD Admin application. If this is required, this will generate additional cost. If the full CALIDUS ePOD solution is agreed, this time may be added part of the full solution defined in that project.
It is noted that multiple Time Windows are not supported in Aurora at this time. It is hoped that the changes specified within this document will be suitable to support this future requirement. However, if changes to Aurora require modifications to the application to support this, this will generate additional cost.
Jobs that are cancelled on TomTom WEBFLEET devices provide a different interface back into CALIDUS ePOD - this requires prototyping at functional specification stage and may result in increased cost. Reasons entered against a cancelled job are also required. It is expected, however, that the costs in this document will be sufficient to achieve the full interface.
The processing of jobs on the TomTom WEBFLEET devices is subject to configuration on the device and the fleet within WEBFLEET. Actual data gathered from TomTom WEBFLEET relies on the accurate use of the in-cab TomTom navigation unit, for example, the arrival button must be pressed on arrival to the destination. Incorrect configuration or use of this application will mean that the solution will not work as expected. For the solution to work as expected, this must include:
- Order Start.
- Order Arrival.
- Order Cancellation with entry of reason code.
- Order Completion.
The TomTom WEBFLEET application process above is as required for the stand-alone implementation of Planned Vs Actuals and TomTom WEBFLEET. When/if CALIDUS ePOD is implemented for the customer, this process is likely to be modified to simplify the process:
- Order Start.
- Order Completion used for Arrival.
- Order Cancellation with entry of reason code.
- The CALIDUS ePOD application would then handle Order Completion times
The Unique Load ID is assumed to be the Callover Sequence Number from DiPS, as this is the only depot-unique number available. Note that the Route Code is stored as part of this solution, but for reference and reporting only.
Planned Break jobs are part of this solution. DiPS can provide a planned driver break on the inbound file, and this will be in between of the execution or orders, not during the execution of the orders. This solution expects that breaks will be created as delivery jobs with specific default information. These will then act identically to delivery jobs within TomTom WEBFLEET. If additional development is required regarding processing of break jobs within TomTom WEBFLEET, or the use of these jobs if utilising the full CALIDUS ePOD solution, this will generate additional cost and will be identified in the full CALIDUS ePOD solution if required.
There is a requirement to display the Weight and Quantities on the TomTom WEBFLEET device, combining the information from DiPS them into the instructions field. This can be achieved, depending on whether the length of the data required exceeds the length in CALIDUS ePOD. This will need to be agreed and the DiPS fields from which this data is taken agreed during the mapping session. If this exceeds the maximum length, additional cost may be incurred.
The TomTom Logo in the top left and the GK Logo in the top right of the Planned Vs Actual Report will not be produced on the report. If this is required, and additional change will be required at additional cost.
It is not expected that CALIDUS Portal TTM will be in use with this system stand-alone, but that this will require the full CALIDUS ePOD solution to be implemented.
The implemented system will not be capable of being used with the CALIDUS ePOD mobile device application.

Client Requirements

Export of routes in drop order by DiPS
Import of routes into TomTom WEBFLEET
Export of planned vs. actual reporting
Operation of planned solution will be at individual depot level

Additional request from 09/10/2015:

To ensure the drivers are clicking arrive at the correct location can we use the lat/long information sent back from TomTom to display on a map where the driver was at that point in time? If budget does not allow, can we just store the lat/long so Greene king can manually determine the location.

Overview of Solution

Export of routes in drop order by DiPS.
Import of routes via C.S.V into CALIDUS ePOD.
Export of routes and jobs to TomTom WEBFLEET.
Import of completed jobs from TomTom WEBFLEET.
Export of planned vs. actual reporting in Excel format

Cross-functional Process Flow

Export Planned Routes From DiPS

Note: The details of the integration from DiPS are subject to an integration meeting, to be scheduled.

SCR-332569-1:

DiPS-EPOD Integration Meeting.

Initial Run

Orders will be exported from Aurora into DiPS for route planning. DiPS is a route optimisation tool which optimises the drops in to the optimum route. When planned within DiPS, the routed orders with load details will be exported back into Aurora, through an existing interface, consisting of only limited details. At this time, DiPS will also generate a new fully-populated CSV for import into CALIDUS ePOD.

The file will be generated from DiPS in mid-afternoon for the following day - they will then be finalised and sent through to TomTom.

The file will contain all fields that can be exported from DiPS, to minimise any additional work required for any future requirements.

The file will contain all routes for individual depot, or all routes for all depots. Routes must be assigned to a vehicle & driver. Routes must contain postcodes and/or valid Lat/Long co-ordinates.

An example full file export has been provided and is referenced in Appendix B.

This file will be generated to a local or network folder, accessible by CALIDUS ePOD - this can be through an FTP account or file sharing. It will be expected to be named uniquely, as a variant of DIPS2AXS.TXT. Note that the name must either be unique per export, or unique per depot, or created as a different name then renamed for CALIDUS ePOD to pick up, to prevent partial uploading of the file.

CALIDUS ePOD will be modified to automatically import the file through a process triggered to run every few minutes. This will be a bespoke process written specifically for this purpose.

SCR-332569-2:

DiPS-ePOD Interface for Load and Order Sequencing.

The process will process the file and create Loads and Jobs from the information provided.

The first depot job on a load will be marked as a loading job, and all subsequent depot jobs on a load will be marked as an unloading job. The Order Number (Job Code) for these jobs will be generated from a code "LOA" or "UNL" and the Load ID and a sequence.

Break jobs on a load will be generated as a normal job with customer 'BREAK'. They will be given a job address based on the BREAK customer, but with a Lat/Long set as the last job processed on the load. The instructions will be the break length. The Order Number (Job Code) for these jobs will be generated from a code "BRK", plus the Load ID and a sequence.

Note: DiPS can provide a planned driver break on the inbound file, in between of the execution of orders, not during the execution of the orders. This solution expects that breaks will be created as delivery jobs with specific default information. These will then act identically to delivery jobs within TomTom WEBFLEET.

No functionality is included here to make Break jobs visible within CALIDUS ePOD specifically as Break job types or to handle them differently to collections or deliveries on the mobile device. If the full CALIDUS ePOD solution is put in place, a further change will be undertaken there to handle break jobs on the Android device and within the general administration screens. This will be added as a change in the Greene King CALIDUS ePOD solution design document.

The Jobs and Load created will be stored in the normal tables within CALIDUS ePOD, with additional fields created for the new planned data.

The process will generate the following standard CALIDUS ePOD tables:

EPOD_LOAD - each unique load will be created, within the Site (Depot) owning the load.
EPOD_JOB - a unique job will be created for the order on the load specified.
EPOD_CUSTOMER - A customer record will be automatically created if one does not already exist, including the Lat/Long of the address.
EPOD_JOB_ADDRESS - if the customer record exists, but the address or information on it differs to the information on the job, an Address will be created specifically for the job.
EPOD_VEHICLE - A vehicle record will be automatically created for vehicle assigned to the load, based on the Vehicle Registration, if one does not already exist. Note: Vehicles created automatically in this way will not have a TomTom ID (Object ID) assigned to them, meaning that loads for vehicles automatically generated in this way will NOT be automatically sent to TomTom until this is completed.
EPOD_USER - a driver record will be automatically created based on the driver name received from DiPS. This driver ID will be generated from the driver name, and provided a generic password. Note: Although not part of this project, these automatically created drivers may then be used on CALIDUS ePOD devices to complete the jobs. This will be disabled until CALIDUS ePOD is implemented.

The following assumptions are made regarding the data that CALIDUS ePOD will use:

Load
- Site - the depot, from CALLS OWNING DEPOT
- Load ID - from CALLOVER SEQUENCE NO. Note: This load ID must be unique for every load ever created for a depot.
- Load Planned Start/End times - from the individual jobs on the load.
- Vehicle ID - generated from VEHICLE IDENT
- User ID - generated from DRIVER'S NAME OR CARRIER
- New fields will be required to store:
  - Route Code - TRIP LABEL
Job
- Job Code (the main reference) - ORDER OR SHIPMENT ID. Note: This reference must be unique for for the order on the load (it can appear as a collection and a delivery if required).
- Job Type - These will be identified as Delivery jobs only, regardless of whether they are collections or deliveries within DiPS. If functionality is added between Aurora and DiPS to identify collections to DiPS, this may generate additional development. As this would only affect the full CALIDUS ePOD solution (if implemented), this will be added as change to that project.
- Job Group (the main configuration of the execution of the jobs) - based on the job type, hard-coded. For Loading, Unloading and Break jobs, a different job group will be used to Collections and Deliveries.
- Job Instructions - a concatenation of the weights/quantities, or ORDER SPECIAL DELIVERY INST
- Planned Start Date/Time (Planned Arrival) - OPENING TIME 1
- Planned End Date/Time - CLOSING TIME 1
- Planned Distance - TRAVEL DISTANCE TO NEXT CALL from previous job on this load
- Customer Code - IDENT OF CALL OR DEPOT
- Customer Details, from:
  - Name - ADDRESS LINE 1
  - Address - ADDRESS LINE 2/3/4/5/POSTCODE
  - Telephone - TELEPHONE NUMBER
- Sequence on Job List - LINK SEQ NO
- Loading Type - For Depot jobs.
- New fields will be required to store:
  - Planned Travel Time (Minutes) - TRAVEL TIME TO NEXT CALL from the previous job
  - Planned Work Time (Minutes) - WORK TIME
  - ETA Date and Time - For TomTom WEBFLEET integration

Note: There is a requirement to display the Weight and Quantities on the TomTom WEBFLEET device, combining them into the instructions field. This can be achieved, depending on whether the length of the data required exceeds the length in CALIDUS ePOD. This will need to be agreed and the DiPS fields from which this data is taken agreed during the mapping session. If this exceeds the maximum length, additional cost may be incurred.

In order to support the functionality required within the Planned Vs Actual report regarding time windows, the application will be modified to store the time windows information from the DiPS interface.

SCR-332569-3:

Add Time Windows

These will be added as new table, at Job level, to account for potential multiple time windows. The data is assumed to be taken from OPENING/CLOSING TIME 2 at this time. There is no way of identifying or specifying multiple delivery windows or customer delivery windows in the way that Aurora and DiPS plan jobs at this time. A customer is expected to always have a delivery window configured against it, and that it may change (by configuration) for each day of the week if required. Although this change will be made capable of storing multiple time windows against a job or against a customer, the interface here will only create job windows, and only one of them per job. Should Aurora and DiPS (or the interface between them) be modified to support multiple windows, or there is a product requirement to add multiple customer windows, this will generate additional cost. It is not expected that this will be required at this time.

Note: The changes here are to support multiple time windows from the bespoke DiPS interface, and for use within the Planned Vs Actual report only - there is no mechanism of viewing or editing these through the ePOD Admin application. If this is required, this will generate additional cost. If the full CALIDUS ePOD solution is agreed, this time will be added part of the full solution defined in that project.

Update Run

Additional orders, cancellation of orders and changes to orders already received throughout the day will necessitate changes to the plan. Aurora and DiPS will follow the same process to send orders across to DiPS and route plan them. When exported from DiPS, the same import file will be created for all of the orders and loads.

When uploading files with orders and loads that have already been created, the process will update any existing Loads and Orders with the information from the new import file.

When all imports are completed for a load, the process will check if any orders already on the load were not included in the new upload. If orders are found, these will be deleted.

When all load imports are completed, the process will check if any loads already in the system for that date were not included in the new upload file. If loads are found, these will be deleted.

Exporting Data to TomTom WEBFLEET

Once the DiPS import is complete, the drivers will require the ability to execute the jobs through TomTom WEBFLEET. In order to achieve that, CALIDUS ePOD must generate the orders on to TomTom WEBFLEET. A process already exists to complete that, when loads are set to In Progress. The setting of loads to this status is normally achieved through the CALIDUS ePOD devices requesting a load. As CALIDUS ePOD will not be in use at this time, a process will be required to automatically set the status of the earliest loads for a vehicle to In Progress.

SCR-332569-4:

ePOD Process to automatically set Load In Progress.

This process will be a timed process, running every few minutes, after the DiPS Import has completed, but before any messages are sent to TomTom WEBFLEET. The process will be capable of being configured by Site (Depot). Any loads at status In Progress or Pending will be checked and the earliest load for that vehicle will be marked in progress. This will then automatically trigger a requirement to send this load to TomTom WEBFLEET. If there was already a load in progress for the vehicle, this load will be changed back to Pending and TomTom WEBFLEET will be informed to remove any jobs associated to this load from the worklist.

Notes:

Only vehicles configured with a TomTom External ID within CALIDUS ePOD will have orders sent to them.
Only jobs that are not yet complete will be sent to TomTom WEBFLEET.
Only Loads In Progress will be sent to TomTom WEBFLEET.

Jobs will be sent through as follows:

Order No - Site (Depot) and Job ID. Note: This is a unique ID for the job for TomTom purposes and is not the customer's reference.
Object - Vehicle External Reference
Date/Time - Planned Date/Time, plus a quarter-hour tolerance
Lat/Long - from the customer or the address
Order Type - Collection or Delivery
Job Instructions
Address line 1, Postcode and Country
Contact information

Some slight modifications to the way that data is sent to TomTom WEBFLEET for orders will be undertaken, as part of the product:

Job Instructions will include the order reference to be displayed.
The Planned Date and Time will be amended to be the Expected Arrival Date and Time (Planned Start Date/Time within CALIDUS ePOD).

SCR-332569-5:

ePOD-WEBFLEET Interface Modifications.

Completing Jobs on TomTom WEBFLEET

Once work has been sent to a TomTom vehicle, these orders are visible within TomTom WEBFLEET and the driver can log on to the TomTom WEBFLEET application and see the list of jobs.

The process of completing jobs on the WEBFLEET application depends on the fleet and/or device configuration, but is expected to be:

An orders list is displayed, showing all the orders to be completed.
Select the first job to view the instructions and information.
Start the Job through the button provided.
Navigate to the job using the TomTom routing provided.
Arrive the Job through the button provided.
Complete the Job through the button provided.

Capturing Completed Jobs Information

As each job is actioned by the drivers, information on those actions is stored within TomTom WEBFLEET. This information will be pulled back into CALIDUS ePOD and will update the jobs. A new process will be required to complete this. This will be a timed process, running at regular intervals.

SCR-332569-6:

WEBFLEET-ePOD Job Update Interface.

The process will create a queue to poll for:

Order Completion messages
Order Cancellation messages
Order Started messages
Order Arrived messages

All these messages should include Odometer and Lat/Long co-ordinates.

Each message will be processed and then acknowledged from the queue. The success or failure of processing of the message files received from TomTom will be audited.

It is intended that processing these messages will provide the following information:

Job Status (Completed/Cancelled)
Reason for cancellation if required
Start/Arrival/End Times
Start/Arrival/End Odometer readings

This information will then be used to update the jobs on CALIDUS ePOD.

Note: Jobs that are cancelled on TomTom WEBFLEET devices provide a different interface back into CALIDUS ePOD - this requires prototyping at functional specification stage and may result in increased cost. Reasons entered against a cancelled job are also required. It is expected, however, that the costs in this document will be sufficient to achieve the full interface.

TomTom WEBFLEET non-working time (unplanned breaks) is required to be supported by this project and therefore this interface - if the driver logs non-working time as part of the execution of an order, this will generate a break job in the database, with no planned times or distances. Additionally, this time will be taken away from any travel or working time on any jobs that have actual start, arrival or end times around that unplanned break.

SCR-332569-7:

Update non-working time as a new break

When all jobs on a load are updated to cancelled or completed through this interface, the load will be marked as complete.

An optional change has also been requested to indicate the position at which the driver clicks the Arrive button, so that Greene King can check the location.

SCR-332569-8:

Display location of TomTom Order Arrival.

In order to achieve this, this process will write User Audit records for each message processed from the TomTom files received, for the following information:

Order Started
Order Arrived
Order Completed

This information would then be accessible through the existing CALIDUS ePOD User Audit Admin screen.

Admin User Tracking Screen, showing prototyped TomTom messages

Planned Vs Actual Reporting

A facility will exist within CALIDUS ePOD to allow reporting of Planned Vs Actuals. This will be available from the Reports menu in the CALIDUS ePOD Admin system.

Admin Reports Screen, showing prototyped TomTom Planned Vs Actual Report

SCR-332569-9:

Planned Vs Actual Report.

The report is expected to be run daily.

The report will allow the following parameters to be entered:

Depot - This will default to the current Site. The drop-down list will allow the selection of other depots configured specifically for the report, or all depots for that customer.
Driver - an optional parameter, to select only loads completed by a particular driver. The parameter will allow selection of one specific driver from the depots selected, or all drivers from the depots selected (the default value).
Vehicle Reg - an optional parameter, to select only loads completed on a particular vehicle. The parameter will allow selection of one specific vehicle from the depots selected, or all vehicles from the depots selected (the default value).
Date range - a required parameter. This will select jobs with a Planned End date within this range. This will default to the current date for both parameters. A range of more than one month will not be allowed, although it will be allowed to select a range earlier.
Customer Account number - an optional parameter, to select jobs for a specific customer only. The parameter will allow selection of one specific customer from the depots selected, or all customers from the depots selected (the default value).

Admin Reports Screen, showing prototyped report parameters

Once selected, the user will run the report, and the system will generate a Microsoft Excel file with the results.

Notes:

Only completed or cancelled jobs will be selected.
The report will be named based on the parameters selected:
- PVA_{Depot}_{Driver}_{Vehicle}_{Customer}_{Date}.xls
- If a parameter is left as All Values, this will not appear in the name, with the exception of Depot and Date Range.

Depending on browser settings, this may either offer to save the report immediately, or open immediately in the browser. If opened in the browser, the report may be saved locally.

An example report has been provided and is referenced in Appendix B.

The report will have multiple tabs as defined in the sample report provided. An additional Parameters tab will be created as the first tab, showing the parameters entered.

Report Parameters

Sample Plan vs Actual Parameters Tab

The Parameters screen will show the report title and the selected parameters. Where a parameter is selected, the code and the description will be shown.

The page will be oriented in landscape.

General Tab Notes:

All pages will include the title bar, consisting of the tab title alone.
The TomTom Logo in the top left and the GK Logo in the top right will not be produced on the report. If this is required, and additional change will be required at additional cost.
All tables will be outlined with single borders
All titles and summary lines will be bold.
All titles will be fixed - no additional fields can be added and the text may not be changed through configuration of the report, although they may be changed after the report is produced.
Work time is defined as the time in between arriving at a location and departing a location. The working time consists of loading/unloading and paperwork
Travel time is defined as the time in between the start and arrive times stored against a job, either travelling on Collections/Deliveries or travel to/from the depot.
Route displayed on the reports will be the new Route field added to the load, if this is not blank, otherwise this will be the default Load ID.
Date displayed on the report will be the Planned End Date, when the delivery should be completed.
Data on the report will be primarily sorted by Planned End Date, Vehicle Reg and Route, then any additional data specific to that tab.

Plan vs Actual Summary

Sample Plan vs Actual Summary Tab

The report will show the variance between the planned distances, work time and travel time, per load (Route) and Vehicle.

Each of the monitored values will be calculated as follows:

Plan - the sum of the planned values against the jobs on the load.
Actual - the sum of the actual values against the jobs on the load.
Var - the planned summed value minus the actual summed value.
% Var - the summed variance divided by the summed planned value, expressed as a percentage with 1 decimal place.

A total line will be shown, totalling all the calculated monitored values and the total number of lines (routes).

The page will be oriented in landscape.

Plan vs Actual Detail

Sample Plan vs Actual Detail Tab

The report will show the variance between the planned distances, work time and travel time, per job (account) on a load (route).

Each of the monitored values will be calculated as follows:

Plan - the planned values against the job.
Actual - the actual values against the job.
Var - the planned value minus the actual value.
% Var - the variance divided by the planned value, expressed as a percentage with 1 decimal place.

Breaks and Depot (Loading/Unloading) jobs will not include account number and postcode.

Loading jobs will not include Distance or Travel Time variances.

The page will be oriented in landscape.

Time Window Summary

Sample Time Window Summary Tab

The report will show a summary of the drops planned on a load, the number achieved within the window, and the variance.

The report will summarise at Load (route) and will display:

No. Planned - a sum of the number of collection or delivery jobs on the load.
No. Achieved - a sum of the number of collection or delivery jobs on the load where the actual arrival time is within the window.
Var - No. Planned minus No. Achieved.
% Var - No. Achieved divided by No. Planned, expressed as a percentage, rounded up. Note: This is not the percentage variance, this is the percentage compliance. The column title will be labelled as '% Comp' rather than '% Var' to reflect this.

A total line will be shown, totalling all the calculated monitored values.

The page will be oriented in portrait.

Time Detail

Sample Time Detail Tab

The report will show the variance between the planned time windows and planned and actual time, per job (account) on a load (route).

The report will display:

Time Window - the time window received for the job.
Planned Arrival Time - Planned Start Time.
Actual Arrival Time - Arrival Time.
Var - Actual Arrival minus planned arrival, in minutes. This should be green background if the arrival time is within 30 minutes (plus or minus) or the planned arrival time, otherwise red background.
Achieved Time Window - Yes or, depending if the arrival time is within the time window. This should be green background if Yes, other red background.

The page will be oriented in landscape.

Time Window Exception

Sample Time Window Exception Tab

The report will show any jobs where the actual arrival time is not within the planned time window, per job (account) on a load (route).

The report will display:

Time Window - the time window received for the job.
Arrival Time - the actual Arrival Time.

The page will be oriented in portrait.

Arrival Time Exception

Sample Arrival Time Exception Tab

The report will show any jobs where the actual arrival time is not within 30 minutes of the planned time, per job (account) on a load (route).

The report will display:

Planned Arrival Time - Planned Start Time.
Actual Arrival Time - Arrival Time.
Var - Actual Arrival minus planned arrival, in minutes.

The page will be oriented in portrait.

Appendix A: Table of SCRs and Ballpark Estimates

SCR#	System	Area	Description	Estimate (Days)	Notes
1	ePOD	Integration	DiPS-EPOD Integration Meeting.	1.00
2	ePOD	Integration	DiPS-ePOD Interface for Load and Order Sequencing.	11.50
3	ePOD	Integration	Add Time Windows.	4.50
4	ePOD	Integration	ePOD Process to automatically set Load In Progress.	2.75
5	ePOD	Integration	ePOD-WEBFLEET Interface Modifications.	1.00	³
6	ePOD	Integration	WEBFLEET-ePOD Job Update Interface.	7.25
7	ePOD	Integration	Update non-working time as a new break.	4.25	⁴
8	ePOD	Admin	Display location of TomTom Order Arrival.	1.25	²
9	ePOD/DiPS	Reports	Planned Vs Actual Report.	11.50

Notes:

Any high level ballpark estimates for development are based on the basic information provided and are subject to detailed design and creation of an SCR.
These changes are optional at extra cost
This change is a product modification and at OBS cost
This change is at additional cost

Appendix B: Document References

B.1 References

Ref No	Document Title & ID	Version	Date
1	obs-eas-DIPS2AXS.xls	N/A	13/12/2013
2	Tom Tom Reports.xlsx	N/A	5/3/2015

B.2 Glossary

Term	Definition
EPOD	Electronic Proof of Delivery. The OBS EPOD system is CALIDUS ePOD.
CALIDUS eSERV	The OBS mobile system to complete Service functionality in the field. This is part of the CALIDUS ePOD system.
PDA	The mobile device on which the C-ePOD system will run in the field. This can be a Phone, EDA or industrial PDA, running Android.
DAL	Data Access Layer. A mechanism for accessing data by the system that is removed from the application, allowing for simplified access and providing protection to the data, as only approved DAL methods can be used to modify it.
GPS	Global Positioning System. A mechanism of retrieving accurate positioning information in the form of Latitude and Longitude (Lat-Long) co-ordinates from a device.
GPRS, 3G, HSDPA, Data Service	All terms referring to mobile device network connectivity, and the speed at which the device connects to the internet.

B.3 Authorised By

Rob Carter	Greene King	_____________________________
Matt Turner	OBS Logistics Product Manager	_____________________________

Anonymous

Search

REQ 332569 Greene King PvA

Namespaces

More

Page actions

Contents

Introduction

Objective

Scope and Limitations

Client Requirements

Overview of Solution