FS 336019 332569-9 GK Planned Vs Actual Report
Greene King
Planned Vs Actual Report
CALIDUS ePOD
23rd August 2016 - 1.0
Reference: FS 336019 332569-9
Contents
Functional Overview
Client Requirement
A series of Planned vs Actuals reports, comparing planned data from DiPS and actual data from TomTom WEBFLEET.
Solution Overview
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
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.
Scope
Set-up
Pre-requisites
The jobs must be received from DiPS for the planned values.
The jobs must be sent to TomTom WEBFLEET and updated from there to achieve the actual values. The set-up of these interfaces are covered in the relevant specification referenced in Appendix B.
Menu Structure
Reports
Data
The data used by these reports is derived directly from the DiPS interface (for planned information) and WEBFLEET Update interface (for actuals information), defined in the relevant specifications referenced in Appendix B.
Functional Description
Admin Parameters Screen
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
If the system type is "PvA" (EPL_SYSTEM_TYPE of EPOD_LOAD), the drop-down list of reports should have the value "TomTom Planned Vs Actuals Report" pre-selected and the parameters already shown. If the system type is any other value, the report screen should operate as now, in that the reports drop-down list should be showing "Please select a Report", with no parameters shown.
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 (option "All Depots"). The list of depots accessible for the report will be set against the system in pre-configured List Items for Greene King (EPOD_LISTS and EPOD_LIST_ITEMS). Note: If the Depot selected is changed, any select value in Driver, Vehicle and Customer Account Number will be reset.
- 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 option "All Drivers"). This list of drivers will be built after the selection of Depot. The drivers will be listed showing Site and Name and sorted this way e.g. "EAS-A Driver", This is built from EPL_SITE_ID and EPL_USER_NAME from EPOD_USER.
- 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 option "All Vehicles"). This list of vehicles will be built after the selection of Depot. The vehicles will be listed showing Site and Reg and sorted this way e.g. "EAS-AB12 CDE", This is built from EPL_SITE_ID and EPL_VEHICLE_REG from EPOD_VEHICLE.
- 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. If the From date selected is after the To date, the To date will default to the From date. If the To date selected is before the From date, the From date will default to the To date. The user will be provided a Calendar pop-up to select dates.
- 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 option "All Customers"). This list of Customers will be built after the selection of Depot. The customers will be listed showing Site and Name and sorted this way e.g. "EAS-Ashby Mill Road Club [Scunthorpe]", This is built from EPL_SITE_ID and EPL_CUSTOMER_NAME from EPOD_CUSTOMER.
Admin Reports Screen, showing prototyped report parameters
Note: Parameters selected as "All ..." will have the parameter value as blank.
Once selected, the user will run the report by clicking Create Excel Spreadsheet and the system will generate a Microsoft Excel file with the results.
Note: :
- Only completed or cancelled jobs and loads will be selected.
- The report will be named based on the parameters selected:
- PVA_{Depot}_{Driver}_{Vehicle}_{Customer}_{Date}.xls
- Depot: The selected Depot ID (EPL_SITE_ID) or "ALL", plus an underscore (_).
- Driver: The selected Driver ID (EPL_USER_ID) plus an underscore (_), or omitted if All selected.
- Vehicle: The selected Vehicle ID (EPL_VEHICLE_ID) plus an underscore (_), or omitted if All selected.
- Customer: The selected Customer Code (EPL_CUSTOMER_CODE) with all spaces replaced as underscores, plus an underscore (_), or omitted if All selected.
- Date: The Date From and To, in YYYYMMDD format, concatenated with an underscore (_).
Examples:
- Where all parameters are left at the default:
- PVA_ALL_20160629_20160629.xls
- Where a specific depot (Eastwood) is selected:
- PVA_EAS_20160629_20160629.xls
- Where a specific driver (A Driver) is selected:
- PVA_EAS_adriver_20160629_20160629.xls
- Where a specific vehicle (AB12 BCD) is selected:
- PVA_EAS_adriver_AB12BCD_20160629_20160629.xls
- Where a specific Customer (268349 001 - Ashby Mill Road Club [Scunthorpe]) is selected:
- PVA_EAS_adriver_AB12BCD_268349__001_20160629_20160629.xls
- Where a 3-day range is selected (from 27/06 to 29/06):
- PVA_EAS_adriver_AB12BCD_268349__001_20160627_20160629.xls
Report Parameters
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.
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 selected parameters should be displayed as follows:
- All parameters will be labelled and have values and descriptions displayed as below:
- Label "Depot": The selected Depot ID (value) and Name (description) (EPL_SITE_ID and EPL_DESCRIPTION) or "All" if the parameter value is blank.
- Label "Driver": The selected Driver ID (value) and Name (description) (EPL_USER_ID and EPL_USER_NAME) or "All" if the parameter value is blank.
- Label "Vehicle": The selected Vehicle ID (value) and Reg (description) (EPL_VEHICLE_ID and EPL_VEHICLE_REG) or "All" if the parameter value is blank.
- Label "Date Range": The Date From and To, in YYYYMMDD format.
- Label "Customer": The selected Customer Code (value) and Name (description) (EPL_CUSTOMER_CODE and EPL_CUSTOMER_NAME) or "All" if the parameter value is blank.
- The columns used will be as follows:
- C - The parameter Label.
- E - The parameter value. For Date Range, the From date.
- F - " to ", only for the Date Range parameter, blank for all others.
- G - The parameter Name if the parameter value is not blank. For Date Range, the To date.
The page will be oriented in landscape.
General Tab Notes:
- All pages will include the title bar, consisting of the tab title alone. This is specified as the title of each page section following.
- 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, an additional change will be required at additional cost.
- All tables will be outlined with single borders.
- All page titles, column titles and total 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 by manually modifying the report after production.
- Page headers, Tables, Titles and Totals (where required) will always be displayed, even if there are no other rows on the table. In this case, for a page that displays a totals row, the total row will show zero values in the summarised columns.
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.
The columns displayed on the report are:
- Date: Load Planned End Date (EPL_LOAD_PLANNED_START_DATE).
- Vehicle Reg: The registration of the vehicle that completed the load (EPL_VEHICLE_REG of EPOD_VEHICLE linked from EPL_VEHICLE_ID of EPOD_LOAD).
- Route: The Route Code of the load (EPL_ROUTE_CODE). If this is blank, report the Load ID instead (EPL_LOAD_ID).
- Distance (KMs)
- Plan: The sum of the planned travel distance on all the jobs on the load (summed from EPL_DISTANCE_PLANNED).
- Actual: The sum of the actual travel distance on all the jobs on the load (summed from EPL_DISTANCE_ACTUAL).
- Var: the planned summed travel distance minus the actual summed travel distance.
- % Var: the summed variance (calculated above) divided by the summed planned value calculated above, expressed as a percentage with 1 decimal place.
- Work Time (Mins)
- Plan: The sum of the planned work time on all the jobs on the load (summed from EPL_WORK_PLANNED).
- Actual: The sum of the actual work time on all the jobs on the load (summed from EPL_WORK_ACTUAL).
- Var: the planned summed work time minus the actual summed work time.
- % Var: the summed variance (calculated above) divided by the summed planned value calculated above, expressed as a percentage with 1 decimal place.
- Travel Time (Mins)
- Plan: The sum on the planned travel time on all the jobs on the load (summed from EPL_TRAVEL_PLANNED).
- Actual: The sum of the actual travel time on all the jobs on the load (summed from EPL_DRIVING_TIME).
- Var: the planned summed travel time minus the actual summed travel time.
- % Var: the summed variance (calculated above) divided by the summed planned value calculated above, expressed as a percentage with 1 decimal place.
Note: The values should be calculated with a SQL statement specific to this report, summing, grouping and ordering in the mechanism specified above. The following is an example of how this might be achieved for distance:
SELECT
, el.EPL_LOAD_END_PLANNED_DATE
, ev.EPL_VEHICLE_REG
, el.EPL_ROUTE_CODE
, el.EPL_LOAD_ID
, SUM(ej.EPL_DISTANCE_PLANNED) DistancePlan
, SUM(ej.EPL_DISTANCE_ACTUAL) DistanceActual
FROM EPOD_LOAD el
JOIN EPOD_JOB ej
ON ej.EPL_SITE_ID = el.EPL_SITE_ID
AND ej.EPL_LOAD_ID = el.EPL_LOAD_ID
JOIN EPOD_VEHICLE ev
ON ev.EPL_SITE_ID = el.EPL_SITE_ID
AND ev.EPL_VEHICLE_ID = el.EPL_VEHICLE_ID
WHERE ej.EPL_STATUS IN ('C', 'X')
AND el.EPL_STATUS IN ('C', 'X')
-- Other Criteria Here
GROUP BY
el.EPL_LOAD_END_PLANNED_DATE
, ev.EPL_VEHICLE_REG
, el.EPL_ROUTE_ID
, el.EPL_LOAD_ID
ORDER BY
el.EPL_LOAD_END_PLANNED_DATE
, ev.EPL_VEHICLE_REG
, el.EPL_ROUTE_ID
, el.EPL_LOAD_ID
Variance and Percentage values will be calculated from the returned Plan and Actual values.
A total line will be shown, totalling all the calculated monitored values and the total number of lines (routes). This will be calculated by the process. The total line will have all columns in bold.
The columns totalled in detail are:
- Route - A count of the lines.
- Distance
- Plan - A sum of all the row values.
- Actual - A sum of all the row values.
- Var - A sum of all the row values.
- % Var - The summed Var value divided by the summed Plan value.
- Work Time - All columns totalled as above.
- Travel Time - All columns totalled as above.
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).
The columns displayed on the report are:
- Date: Load Planned End Date (EPL_LOAD_PLANNED_START_DATE).
- Vehicle Reg: The registration of the vehicle that completed the load (EPL_VEHICLE_REG of EPOD_VEHICLE linked from EPL_VEHICLE_ID of EPOD_LOAD).
- Route: The Route Code of the load (EPL_ROUTE_CODE). If this is blank, report the Load ID instead (EPL_LOAD_ID).
- Account Name: The name of the Customer on the job (see below).
- Account Number: The Customer Code from the job (EPL_CUSTOMER_CODE). This cell should be blank and have a grey background if the job is a depot loading or unloading job (EPL_LOADING_TYPE is not blank) or the job is a Break (EPL_CUSTOMER_CODE is "BREAK").
- Postcode: The Post Code from the job address or customer address (EPL_POSTCODE - see below). This cell should be blank and have a grey background if the job is a depot loading or unloading job (EPL_LOADING_TYPE is not blank) or the job is a Break (EPL_CUSTOMER_CODE is "BREAK").
- Distance (KMs)
- Plan: The planned travel distance of the job on the load (EPL_DISTANCE_PLANNED). This cell should be blank and have a grey background if the job is a depot loading job (EPL_LOADING_TYPE is "L") and the planned and actual distance travelled is 0.
- Actual: The actual travel distance of the job on the load (EPL_DISTANCE_ACTUAL). This cell should be blank and have a grey background if the job is a depot loading job (EPL_LOADING_TYPE is "L") and the planned and actual distance travelled is 0.
- Var: the planned travel distance minus the actual travel distance. This cell should be blank and have a grey background if the job is a depot loading job (EPL_LOADING_TYPE is "L") and the planned and actual distance travelled is 0.
- % Var: the variance (calculated above) divided by the planned value above, expressed as a percentage with 1 decimal place. This cell should be blank and have a grey background if the job is a depot loading job (EPL_LOADING_TYPE is "L") and the planned and actual distance travelled is 0.
- Work Time (Mins)
- Plan: The planned work time of the job on the load (EPL_WORK_PLANNED).
- Actual: The actual work time of the job on the load (EPL_WORK_ACTUAL).
- Var: the planned work time minus the actual work time.
- % Var: the variance (calculated above) divided by the planned value above, expressed as a percentage with 1 decimal place.
- Travel Time (Mins)
- Plan: The planned travel time of the job on the load (EPL_TRAVEL_PLANNED). This cell should be blank and have a grey background if the job is a depot loading job (EPL_LOADING_TYPE is "L") and the planned and actual distance travelled is 0.
- Actual: The actual travel time of the job on the load (EPL_DRIVING_TIME). This cell should be blank and have a grey background if the job is a depot loading job (EPL_LOADING_TYPE is "L") and the planned and actual distance travelled is 0.
- Var: the planned travel time minus the actual travel time. This cell should be blank and have a grey background if the job is a depot loading job (EPL_LOADING_TYPE is "L") and the planned and actual distance travelled is 0.
- % Var: the variance (calculated above) divided by the planned value above, expressed as a percentage with 1 decimal place. This cell should be blank and have a grey background if the job is a depot loading job (EPL_LOADING_TYPE is "L") and the planned and actual distance travelled is 0.
Note: Customer Names and Post Codes should be obtained from the Job Address if this exists for the job type (EPL_JOB_TYPE and EPL_JOB_ID of EPOD_JOB_ADDRESS linked from EPL_JOB_TYPE and EPL_JOB_ID of EPOD_JOB respectively). If this record exists, use EPL_NAME and EPL_POSTCODE respectively, if the values are not blank. If this record does not exist or the values are blank, use EPL_CUSTOMER_NAME and EPL_POSTCODE respectively from EPOD_CUSTOMER, linked from EPL_CUSTOMER_CODE of EPOD_JOB.
Note: Driver-generated Break Jobs will have the same sequence as the following or in-progress job when generated. To ensure that they are displayed after they job that they interrupt, the result will also be sorted in Actual Start Date and Time sequence as well as Job Sequence.
Note: The values should be returned with a SQL statement specific to this report, ordering in the mechanism specified above. The following is an example of how this might be achieved for distance:
SELECT
el.EPL_SITE_ID
, el.EPL_LOAD_ID
, el.EPL_LOAD_END_PLANNED_DATE
, ev.EPL_VEHICLE_REG
, ej.EPL_CUSTOMER_CODE
, ec.EPL_CUSTOMER_NAME
, ec.EPL_POSTCODE
, eja.EPL_NAME
, eja.EPL_POSTCODE
, ej.EPL_JOB_TYPE
, ej.EPL_LOADING_TYPE
, ej.EPL_DISTANCE_PLANNED
, ej.EPL_DISTANCE_ACTUAL
FROM EPOD_LOAD el
JOIN EPOD_JOB ej
ON ej.EPL_SITE_ID = el.EPL_SITE_ID
AND ej.EPL_LOAD_ID = el.EPL_LOAD_ID
JOIN EPOD_VEHICLE ev
ON ev.EPL_SITE_ID = el.EPL_SITE_ID
AND ev.EPL_VEHICLE_ID = el.EPL_VEHICLE_ID
JOIN EPOD_CUSTOMER ec
ON ec.EPL_SITE_ID = ej.EPL_SITE_ID
AND ec.EPL_CUSTOMER_CODE= ej.EPL_CUSTOMER_CODE
LEFT JOIN EPOD_JOB_ADDRESS eja
ON eja.EPL_SITE_ID = ej.EPL_SITE_ID
AND eja.EPL_JOB_TYPE = ej.EPL_JOB_TYPE
AND eja.EPL_JOB_ID = ej.EPL_JOB_ID
WHERE ej.EPL_STATUS IN ('C', 'X')
AND el.EPL_STATUS IN ('C', 'X')
-- Other Criteria Here
ORDER BY
el.EPL_LOAD_END_PLANNED_DATE
, ev.EPL_VEHICLE_REG
, el.EPL_ROUTE_ID
, el.EPL_LOAD_ID
, ej.EPL_SEQUENCE
, ej.EPL_START_ACTUAL_DATE, ej.EPL_START_ACTUAL_TIME
Variance and Percentage values will be calculated from the returned Plan and Actual values.
A total line will be shown, totalling all the calculated monitored values. This will be calculated by the process. The total line will have all columns in bold.
The columns totalled in detail are:
- Distance
- Plan - A sum of all the row values.
- Actual - A sum of all the row values.
- Var - A sum of all the row values.
- % Var - The summed Var value divided by the summed Plan value.
- Work Time - All columns totalled as above.
- Travel Time - All columns totalled as above.
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 against the delivery/collection windows provided.
The columns displayed on the report are:
- Date: Load Planned End Date (EPL_LOAD_PLANNED_START_DATE).
- Vehicle Reg: The registration of the vehicle that completed the load (EPL_VEHICLE_REG of EPOD_VEHICLE linked from EPL_VEHICLE_ID of EPOD_LOAD).
- Route: The Route Code of the load (EPL_ROUTE_CODE). If this is blank, report the Load ID instead (EPL_LOAD_ID).
- No. Planned: The number of jobs on the load with a unique sequence (EPL_SEQUENCE, excluding Loading/Unloading (EPL_LOADING_TYPE is not blank) or Break (EPL_CUSTOMER_CODE is "BREAK") jobs.
- No. Achieved: Of the jobs selected, those with a delivery time (EPL_END_ACTUAL_TIME) within the Job or Customer Delivery Windows - see below.
- Var: Calculated Planned above minus calculated achieved above
- % Comp: Note: This is a percentage Compliance column, not a percentage variance column. Calculated Achieved above divided by calculated Planned above, with no decimal places.
Note: The values should be returned with a SQL statement specific to this report, ordering in the mechanism specified above. The following is an example of how this might be achieved:
SELECT
ej.EPL_SITE_ID
, ej.EPL_LOAD_ID
, ej.EPL_LOAD_END_PLANNED_DATE
, ej.EPL_VEHICLE_REG
, COUNT(ej.EPL_SEQUENCE) NoPlanned
, SUM(Achieved) Achieved
FROM (
SELECT
ej.EPL_SITE_ID
, ej.EPL_LOAD_ID
, ej.EPL_SEQUENCE
, MAX(el.EPL_LOAD_END_PLANNED_DATE) EPL_LOAD_END_PLANNED_DATE
, MAX(ev.EPL_VEHICLE_REG) EPL_VEHICLE_REG
, MAX(ej.EPL_CUSTOMER_CODE) EPL_CUSTOMER_CODE
, MAX(CASE WHEN
ej.EPL_END_ACTUAL_TIME >= COALESCE(etwj.ETW_TIME_START, 0)
AND ej.EPL_END_ACTUAL_TIME <= COALESCE(etwj.ETW_TIME_END, 23599999)
AND ej.EPL_END_ACTUAL_TIME >= COALESCE(etwc.ETW_TIME_START, 0)
AND ej.EPL_END_ACTUAL_TIME <= COALESCE(etwc.ETW_TIME_END, 23599999)
THEN 1 ELSE 0 END) Achieved
FROM EPOD_LOAD el
JOIN EPOD_JOB ej
ON ej.EPL_SITE_ID = el.EPL_SITE_ID
AND ej.EPL_LOAD_ID = el.EPL_LOAD_ID
JOIN EPOD_VEHICLE ev
ON ev.EPL_SITE_ID = el.EPL_SITE_ID
AND ev.EPL_VEHICLE_ID = el.EPL_VEHICLE_ID
LEFT JOIN [EPODV3_DEV].[dbo].EPOD_TIME_WINDOW etwj
ON etwj.ETW_SITE_ID = ej.EPL_SITE_ID
AND etwj.ETW_TYPE = 'J'
AND etwj.ETW_FK_ID = ej.EPL_JOB_ID
LEFT JOIN [EPODV3_DEV].[dbo].EPOD_TIME_WINDOW etwc
ON etwc.ETW_SITE_ID = ej.EPL_SITE_ID
AND etwc.ETW_TYPE = 'C'
AND etwc.ETW_FK_ID = ej.EPL_CUSTOMER_CODE
WHERE ej.EPL_STATUS IN ('C', 'X')
AND el.EPL_STATUS IN ('C', 'X')
AND ej.EPL_LOADING_TYPE =
-- Other Criteria Here
GROUP BY ej.[EPL_SITE_ID]
,ej.[EPL_LOAD_ID]
,ej.[EPL_SEQUENCE]
) as ej
GROUP BY ej.EPL_SITE_ID
, ej.EPL_LOAD_ID
, ej.EPL_LOAD_END_PLANNED_DATE
, ej.EPL_VEHICLE_REG
Note: The SQL shown here consolidated the Customer and Job windows. The report will display only the achievement status and window against a single Job window. With the data expected in this implementation, this process will work. If customer windows are added or multiple windows are required, the achievement status will show correctly, however changes will be required to display the window correctly. These changes should be included as part of the work to implement multiple windows.
A total line will be shown, totalling all the calculated monitored values. This will be calculated by the process. The total line will have all columns in bold.
The columns totalled in detail are:
- No. Planned - A sum of all the row values.
- No. Achieved - A sum of all the row values.
- Var - A sum of all the row values.
- % Comp - The summed variance plus the summed No. Planned, divided by the summed No. Planned, expressed as a percentage with no decimal places.
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 columns displayed on the report are:
- Date: Load Planned End Date (EPL_LOAD_PLANNED_START_DATE).
- Vehicle Reg: The registration of the vehicle that completed the load (EPL_VEHICLE_REG of EPOD_VEHICLE linked from EPL_VEHICLE_ID of EPOD_LOAD).
- Route Number: The Route Code of the load (EPL_ROUTE_CODE). If this is blank, report the Load ID instead (EPL_LOAD_ID).
- Account Name: The name of the Customer on the job (see below).
- Account Number: The Customer Code from the job (EPL_CUSTOMER_CODE).
- Postcode: The Post Code from the job address or customer address (EPL_POSTCODE - see below).
- Time Window: Concatenated from the job time window (ETW_TIME_START and ETW_TIME_END, if either is a non-zero value)
- Planned Time: The Planned Start Time (EPL_START_PLANNED_TIME).
- Arrival Time: The Arrival Time (EPL_ARRIVAL_TIME) if present, else the Actual End Time (EPL_END_ACTUAL_TIME). Referred to as the Arrival Time here on in.
- Var: The Arrival Time above minus the planned time above, in minutes. This should be green background if the arrival time is within 30 minutes (plus or minus) of the planned arrival time, otherwise red background.
- Time Window Achieved Yes/No: Whether the arrival time was within the Job Time Window. This should be green background if Yes, other red background.
Note: Customer Names and Post Codes should be obtained from the Job Address if this exists for the job type (EPL_JOB_TYPE and EPL_JOB_ID of EPOD_JOB_ADDRESS linked from EPL_JOB_TYPE and EPL_JOB_ID of EPOD_JOB respectively). If this record exists, use EPL_NAME and EPL_POSTCODE respectively, if the values are not blank. If this record does not exist or the values are blank, use EPL_CUSTOMER_NAME and EPL_POSTCODE respectively from EPOD_CUSTOMER, linked from EPL_CUSTOMER_CODE of EPOD_JOB.
Note: The values should be returned with a SQL statement specific to this report, ordering in the mechanism specified above. The following is an example of how this might be achieved:
SELECT
ej.EPL_SITE_ID
, ej.EPL_LOAD_ID
, ej.EPL_SEQUENCE
, MAX(el.EPL_LOAD_END_PLANNED_DATE) EPL_LOAD_END_PLANNED_DATE
, MAX(ev.EPL_VEHICLE_REG) EPL_VEHICLE_REG
, MAX(ej.EPL_CUSTOMER_CODE) EPL_CUSTOMER_CODE
, MAX(ec.EPL_CUSTOMER_NAME) EPL_CUSTOMER_NAME
, MAX(ec.EPL_POSTCODE) EPL_CUSTOMER_POSTCODE
, MAX(eja.EPL_NAME) EPL_NAME
, MAX(eja.EPL_POSTCODE) EPL_POSTCODE
, MAX(ej.EPL_JOB_TYPE) EPL_JOB_TYPE
, MAX(ej.EPL_LOADING_TYPE) EPL_LOADING_TYPE
, MAX(ej.EPL_START_PLANNED_TIME) EPL_START_PLANNED_TIME
, MAX(CASE WHEN ej.EPL_ARRIVAL_TIME > 0 THEN ej.EPL_ARRIVAL_TIME ELSE ej.EPL_END_ACTUAL_TIME END) EPL_ARRIVAL_TIME
, MAX(etwj.ETW_TIME_START) ETW_TIME_START
, MAX(etwj.ETW_TIME_END) ETW_TIME_END
, MAX(CASE WHEN
ej.EPL_END_ACTUAL_TIME >= COALESCE(etwj.ETW_TIME_START, 0)
AND ej.EPL_END_ACTUAL_TIME <= COALESCE(etwj.ETW_TIME_END, 23599999)
AND ej.EPL_END_ACTUAL_TIME >= COALESCE(etwc.ETW_TIME_START, 0)
AND ej.EPL_END_ACTUAL_TIME <= COALESCE(etwc.ETW_TIME_END, 23599999)
THEN 1 ELSE 0 END) Achieved
FROM EPOD_LOAD el
JOIN EPOD_JOB ej
ON ej.EPL_SITE_ID = el.EPL_SITE_ID
AND ej.EPL_LOAD_ID = el.EPL_LOAD_ID
JOIN EPOD_VEHICLE ev
ON ev.EPL_SITE_ID = el.EPL_SITE_ID
AND ev.EPL_VEHICLE_ID = el.EPL_VEHICLE_ID
JOIN EPOD_CUSTOMER ec
ON ec.EPL_SITE_ID = ej.EPL_SITE_ID
AND ec.EPL_CUSTOMER_CODE= ej.EPL_CUSTOMER_CODE
LEFT JOIN EPOD_JOB_ADDRESS eja
ON eja.EPL_SITE_ID = ej.EPL_SITE_ID
AND eja.EPL_JOB_TYPE = ej.EPL_JOB_TYPE
AND eja.EPL_JOB_ID = ej.EPL_JOB_ID
LEFT JOIN [EPODV3_DEV].[dbo].EPOD_TIME_WINDOW etwj
ON etwj.ETW_SITE_ID = ej.EPL_SITE_ID
AND etwj.ETW_TYPE = 'J'
AND etwj.ETW_FK_ID = ej.EPL_JOB_ID
LEFT JOIN [EPODV3_DEV].[dbo].EPOD_TIME_WINDOW etwc
ON etwc.ETW_SITE_ID = ej.EPL_SITE_ID
AND etwc.ETW_TYPE = 'C'
AND etwc.ETW_FK_ID = ej.EPL_CUSTOMER_CODE
WHERE ej.EPL_STATUS IN ('C', 'X')
AND el.EPL_STATUS IN ('C', 'X')
AND ej.EPL_LOADING_TYPE =
-- Other Criteria Here
GROUP BY ej.[EPL_SITE_ID]
,ej.[EPL_LOAD_ID]
,ej.[EPL_SEQUENCE]
Note: The SQL shown here consolidated the Customer and Job windows. The report will display only the achievement status and window against a single Job window. With the data expected in this implementation, this process will work. If customer windows are added or multiple windows are required, the achievement status will show correctly, however changes will be required to display the window correctly. These changes should be included as part of the work to implement multiple windows.
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 columns displayed on the report are:
- Date: Load Planned End Date (EPL_LOAD_PLANNED_START_DATE).
- Vehicle Reg: The registration of the vehicle that completed the load (EPL_VEHICLE_REG of EPOD_VEHICLE linked from EPL_VEHICLE_ID of EPOD_LOAD).
- Route: The Route Code of the load (EPL_ROUTE_CODE). If this is blank, report the Load ID instead (EPL_LOAD_ID).
- Account Name: The name of the Customer on the job (see below).
- Account Number: The Customer Code from the job (EPL_CUSTOMER_CODE).
- Postcode: The Post Code from the job address or customer address (EPL_POSTCODE - see below).
- Time Window: Concatenated from the job time window (ETW_TIME_START and ETW_TIME_END, if either is a non-zero value)
- Arrival Time: The Arrival Time (EPL_ARRIVAL_TIME) if present, else the Actual End Time (EPL_END_ACTUAL_TIME).
Note: Customer Names and Post Codes should be obtained from the Job Address if this exists for the job type (EPL_JOB_TYPE and EPL_JOB_ID of EPOD_JOB_ADDRESS linked from EPL_JOB_TYPE and EPL_JOB_ID of EPOD_JOB respectively). If this record exists, use EPL_NAME and EPL_POSTCODE respectively, if the values are not blank. If this record does not exist or the values are blank, use EPL_CUSTOMER_NAME and EPL_POSTCODE respectively from EPOD_CUSTOMER, linked from EPL_CUSTOMER_CODE of EPOD_JOB.
Note: The same query used to create the Time Detail report may be used again here. The query should not be executed again - the same result recordset may be used again. In this case, however, only records that have a window that is not achieved should be displayed here.
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 columns displayed on the report are:
- Date: Load Planned End Date (EPL_LOAD_PLANNED_START_DATE).
- Vehicle Reg: The registration of the vehicle that completed the load (EPL_VEHICLE_REG of EPOD_VEHICLE linked from EPL_VEHICLE_ID of EPOD_LOAD).
- Route Number: The Route Code of the load (EPL_ROUTE_CODE). If this is blank, report the Load ID instead (EPL_LOAD_ID).
- Account Name: The name of the Customer on the job (see below).
- Account Number: The Customer Code from the job (EPL_CUSTOMER_CODE).
- Postcode: The Post Code from the job address or customer address (EPL_POSTCODE - see below).
- Planned Time: The Planned Start Time (EPL_START_PLANNED_TIME).
- Arrival Time: The Arrival Time (EPL_ARRIVAL_TIME) if present, else the Actual End Time (EPL_END_ACTUAL_TIME). Referred to as the Arrival Time here on in.
- Var: The Arrival Time above minus the planned time above, in minutes.
Note: The same query used to create the Time Detail report may be used again here. The query should not be executed again - the same result recordset may be used again. In this case, however, only records that have a calculated Var value of +/- 30 minutes should be displayed here.
The page will be oriented in portrait.
Appendix A: TEST PLAN
| Test Script / Scenario Reference | Planned Vs Actual Report | Call Number(s): 336019 332569-9 |
| Test Script / Scenario Description | To test the new Planned Vs Actual report content and production. | PASS / ISSUES / FAIL |
| Menu Access | Reports | |
| Pre-requisites | Data will be set up as per the test load in the DiPS sample file (Load 46004, Route E2010). The data should be completed as follows:
Ensure that there is at least one other load on another depot, day, driver and vehicle. Ensure that there are other pending loads. Ensure that the system type is configured as not PvA. Ensure the available Depot list for the report is configured for the two depots. | Tested By: |
| Test Objective | To test that: the report parameters screen acts as required; the report is produced as required; the report is formatted and contains the data as required. | Date: |
| Step | Action | Result | Remarks | P/F |
| 1 | Parameter Screen/Basic Report Production. | |||
| 1.01 | Select the Reports menu item. | The Reports screen should show, requesting the user to "Please select a Report". No parameters should be shown. The Drop-down list should contain the "TomTom Planned Vs Actuals Report" item. | ||
| 1.02 | Select the "TomTom Planned Vs Actuals Report" item. | The parameters required for this report are shown. | ||
| 1.03 | Set the Site System Type to "PvA". Select the Reports menu item. | The Reports screen should show, automatically showing the "TomTom Planned Vs Actuals Report" item. The parameters required for this report are shown. | ||
| 1.04 | Run the report for the default parameters. | The report should run and offer to download the result with the correct naming convention. When downloaded, the Report Parameter page should show the selected parameters correctly - ALL for all parameters, except Date Range, which should show today's date in the From and To values. | ||
| 1.05 | Run the report for a single Depot. | The Drop-down list for Depot should show all configured depots. The report should run and offer to download the result with the correct naming convention. When downloaded, the Report Parameter page should show the selected parameters correctly - The Depot ID and Name selected, ALL for all other parameters, except Date Range, which should show today's date in the From and To values. | ||
| 1.06 | Run the report for a single Driver. | The Drop-down list for Driver should show all drivers for all depots. The report should run and offer to download the result with the correct naming convention. When downloaded, the Report Parameter page should show the selected parameters correctly - The Driver ID and Name selected, ALL for all other parameters, except Date Range, which should show today's date in the From and To values. | ||
| 1.07 | Run the report for a single Vehicle. | The Drop-down list for Vehicle should show all vehicles for all depots. The report should run and offer to download the result with the correct naming convention. When downloaded, the Report Parameter page should show the selected parameters correctly - The Vehicle ID and Reg selected, ALL for all other parameters, except Date Range, which should show today's date in the From and To values. | ||
| 1.08 | Run the report for a single Customer. | The Drop-down list for Customer should show all customers for all depots. The report should run and offer to download the result with the correct naming convention. When downloaded, the Report Parameter page should show the selected parameters correctly - The Customer Code and Name selected, ALL for all other parameters, except Date Range, which should show today's date in the From and To values. | ||
| 1.09 | Select all Depots. Select a Customer, Driver and Vehicle. Change the Date. Select a single Depot. | The Drop-down lists for Driver, Vehicle and Customer should change to have only values associated to the selected depot. These parameters should reset to the default "All ..." values. | ||
| 1.10 | Run the report for a selection of parameters that produces no results on any page. Check the report. | All pages (tabs) should be produced. The parameter page should be produced as expected. All other pages should have headers, tables, titles and, where applicable, footers with zero totals. | ||
| 1.11 | Test that the combination of selected parameters produces the correct selected results. | A combination of selected parameters builds the file name correctly. The Report is produced with a correct parameter page. The selected data on each report page is correct. |
| Step | Action | Result | Remarks | P/F |
| 2 | Report Details | |||
| 2.01 | Select parameters to show all loads for the all depots, customers, drivers and a date range that covers both completed loads and some incomplete loads. | The report is created. | ||
| 2.02 | Check the Plan vs Actual Detail Tab. | Only the 2 completed loads and completed or cancelled jobs on those loads are selected. The Plan, Actual, Variance and Percentage Variance columns for Distance, Work Time and Travel Time are all populated correctly for each job. For Loading, Unloading and Break jobs, the Account Number and Postcode are greyed out with no values. For Loading jobs, the Distance and Travel Time sections are greyed out with no values. The total values are correct for each of the summarised columns. The Driver-generated beak is visible. The travel and work time and distance on the driver-generated break is taken off the job during which it was completed. | ||
| 2.03 | Check the Plan vs Actual Summary Tab. | Only the 2 completed loads are selected. The Plan, Actual, Variance and Percentage Variance columns for Distance, Work Time and Travel Time are all populated correctly for each load, summarising the values from the Details tab above. The total values are correct for each of the summarised columns. | ||
| 2.04 | Check the Time Detail tab. | Only the 2 completed loads and completed or cancelled jobs on those loads are selected, excluding all breaks and loading/unloading jobs. Only the drops are displayed, ignoring multiple jobs at the same stop. The Job Time window is displayed if present. The planned and arrival times are displayed. The time variance is displayed correctly. Variances or +/- 30 minutes are displayed with a red background, within the tolerance as a green background. The Time Window is correctly shown as being achieved (with a green background) or not achieved (with a red background). | ||
| 2.05 | Check the Time Window Summary tab. | Only the 2 completed loads are selected. The No. Planned and Achieved against each load correctly total for the drops only (ignoring multiple jobs at the same stop), summarising the values from the Time Detail tab above. The Variance is calculated correctly. The % Compliance is calculated and labelled correctly. The total values are correct for each of the summarised columns. | ||
| 2.06 | Check the Time Window Exception tab. | Only the 2 completed loads and completed or cancelled jobs on those loads are selected, excluding all breaks and loading/unloading jobs. Only the drops are displayed, ignoring multiple jobs at the same stop. Only the drops with an arrival time outside the time window are displayed. The Job Time window and Arrival Time are displayed. | ||
| 2.07 | Check the Arrival Time Exception tab. | Only the 2 completed loads and completed or cancelled jobs on those loads are selected, excluding all breaks and loading/unloading jobs. Only the drops are displayed, ignoring multiple jobs at the same stop. Only the drops with an arrival time differing from the planned time by more than 30 minutes window are displayed. The Planned and Arrival Time are displayed. The Variance is calculated and displayed correctly. |
Appendix B: Quote & Document References
| Cost Details | ||||
| Activity | Estimate No. of Days |
No. of Days | Rate per Day (£) | Cost (£ Exc. VAT) |
| Requirements | 0.00 | 0.00 | 750 | £0.00 |
| Change Request Evaluation | 0.00 | 0.00 | 750 | £0.00 |
| Functional Specification | 1.50 | 1.50 | 750 | £1,125.00 |
| Technical Specification | 1.50 | 1.50 | 750 | £1,125.00 |
| Development | 6.75 | 6.75 | 750 | £5,062.50 |
| Testing and Release | 1.25 | 1.25 | 750 | £937.50 |
| Implementation | 0.00 | 0.00 | 750 | £0.00 |
| Project Management | 0.50 | 0.50 | 750 | £375.00 |
| TOTAL | 11.50 | 11.50 | £8,625.00 | |
| Estimate excludes training, release to live and go live support. |
B.1 References
| Ref No | Document Title & ID | Version | Date |
| 1 | 336010/SCR-332569-2 DiPS-ePOD Interface for Load and Order Sequencing | 1.0 | 23/08/2016 |
| 2 | 336015/332569-6 WEBFLEET-ePOD Job Update Interface | 1.0 | 23/08/2016 |
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 Representative | _____________________________ |
Matt Tipping | OBSL Representative | _____________________________ |
