FS 352946 Mash OTIF Report
Mash Purveyors
Mash OTIF report
CALIDUS ePOD
9th October 2018 - 0.3
Reference: FS 352946
Contents
Functional Overview
Client Requirement
The requirement is for CALIDUS ePOD to report On Time delivery performance.
Note: Jobs with no arrival date must also be reported.
Solution Overview
The report will be added to the Reports menu.
On selection of the Mash On Time report, the screen will offer the user to enter the following parameters.
- Date Range - a date range from/to. Today's date will be selected and a date range must be entered.
- Customer - a drop-down list of all customers. "All" may be selected and is the default.
- Route - a drop-down list of all routes within the date range. "All" may be selected and is the default.
- Load - a drop-down list of all routes within the date range, for the route (if selected). "All" may be selected and is the default.
- Type - A drop-down list selecting whether collections, deliveries or all job types are reported on. "All" is the default.
On confirming the parameters, the report will be generated, displaying the following fields:
- Customer account code
- Customer name
- Load ID
- Job Code (Mash order ref)
- Job Status (C = Complete, X = Cancelled)
- Job level reason code
- Job sequence number (within the load)
- Service Level
- Start Planned Time
- Actual arrival time confirmed by the driver
- OT Status
C-ePOD reports are in the form of a spreadsheet extract which can be requested by users and then edited and manipulated in Excel, etc.
Scope
Note that the ‘In Full’ requirement is not part of this request (it may be reported from the Chefserve ERP system or added later in ePOD).
Note that there is no feature in ePOD to schedule reports to run automatically.
This format requires that Route Code is provided on data import through the field EPOD_LOAD.EPL_ROUTE_CODE. If this is not provided, then Route Code selection will allow only the option of "All Routes for Date Range".
Set-up
Pre-requisites
N/A
Menu Structure
N/A
Data
The default values for the Job Type selection will be set up in the database:
- All - NULL (default value)
- Collection Only - "C"
- Delivery Only - "D"
A new option of "Mash On Time Report" (value 5) will be added to the drop-down list of reports, through the data on table EPOD_LIST_ITEMS, for ListName "ddlReportSelect".
These options will be pre-set in the database through an amendment to the EPOD_SETUP_LISTS database procedure.
Functional Description
Database/DAL
Table EPOD_LOAD may require modification to have the following index created:
- EPL_SITE_ID, EPL_ROUTE_CODE, EPL_LOAD_ID
To accommodate this, the EPL_ROUTE_CODE field may need data-fixing to default Route Code to Blank rather than NULL.
Note: To be evaluated as part of development.
Admin Reports Screen
The existing screen ExcelReports will be modified for this change.
A new option of "Mash On Time Report" will be added to the drop-down list of reports, through the data on table EPOD_LIST_ITEMS.
When selected, the screen will offer the following parameters:
- Customer Code - a drop-down list of customers. Required selection?
- Date Range (From/To) - date selection. Required. Date From and To will default to today. If modifying these criteria, the Date To must be later than Date From.
- Route - A drop-down list of field EPL_ROUTE_CODE from table EPOD_LOAD, for Loads within the specified range and default site only. The data for this will be selected based on the Date Range entered (and triggered from either change of these fields or immediately upon clicking this drop-down list). The default value is "All Routes for Date Range".
- Load - A Drop-down list of field EPL_LOAD_ID from table EPOD_LOAD where EPL_SITE_ID is the current site and an EPL_START_ACTUAL_DATE is populated and within the Date Range selected. If a route is selected, it is also only for loads of that Route Code only. The data for this will be selected based on the Date Range and Route entered (and triggered from either change of these fields or immediately upon clicking this drop-down list). The default value is "All Loads for Date Range/Route".
- Job Type - A drop-down list to determine which of these should be included in the report (All/Collection Only/Delivery Only). Values should be based on predefined values set up in the database table EPOD_LIST_ITEMS. Default value is "All".
Note: All data selection for the parameters will be pre-filtered by the logged-on user's defined Site.
Note: The method of triggering data refresh for the Route and Load drop-down lists will be evaluated during development.
Once entered and the Create Excel Spreadsheet button is clicked, the parameters will be validated as above.
If the parameters pass validation, the report will select data and create the spreadsheet. All procedures in this screen will be modified to support this new report, running a new method of XLReports, called MashOTIFReport to generate the SQL and get the results.
This method will select the data by generating a SQL query and returning a data table.
This method will accept parameters of all the parameters listed above, also including:
- db - the database connection
- EPL_SITE_ID - the current site.
Data will be selected from the following tables:
- EPOD_JOB
- EPOD_LOAD
- EPOD_CUSTOMER
These records will be joined in the order above, through their proper indexes and are required to be present to select data (INNER JOIN).
EPOD_JOB will be selected as follows (parameters in {Curly Brackets}):
- EPL_SITE_ID = {Current Site}
- EPL_CUSTOMER_CODE = {Customer ID} if provided.
- EPL_ARRIVAL_DATE is populated.
- EPL_END_ACTUAL_DATE >={Date From}
- EPL_END_ACTUAL_DATE <={Date To}
- EPL_JOB_TYPE = {Job Type} (if provided)
- EPL_LOADING_TYPE = "" (i.e. not Loading or Unloading jobs at the depot)
- EPL_STATUS = "C" or "X".
EPOD_LOAD will be selected as follows:
- EPL_SITE_ID = {Current Site}
- EPL_ROUTE_CODE = {Route Code} if provided.
- EPL_LOAD_ID = {Load ID} if provided.
Note: With this mechanism, only jobs that have been completed - status Cancelled (X) or confirmed delivered/collected (C) - will be selected. They will appear on the report on the day that they were completed.
The process will run this generated SQL, then format the results into an Excel spreadsheet format. Note that the titles of each field must be set as shown in the Column Title below.
The resulting dataset will be loaded into a datatable and the dataset returned.
The process will populate the Excel report in exactly the same way that it does for other reports.
The output in Excel format. Column titles will be formatted as normal, with no additional formatting in fields or additional rows.
Columns reported:
| Title | Field name | Notes |
|---|---|---|
| Customer Code | EPL_CUSTOMER_CODE | Customer account code |
| Customer Name | EPL_CUSTOMER_NAME | Customer name |
| Load ID | EPL_LOAD_ID | Load ID |
| Job Code | EPL_JOB_CODE | Job Code (Mash order ref) |
| Status | EPL_STATUS | Job Status (C = Complete, X = Cancelled, I = In Progress, P = Pending) |
| Reason | EPL_REASON_CODE | Job level reason code |
| Sequence | EPL_SEQUENCE | Job sequence number (within the load) |
| Service | EPL_SERVICE_LEVEL | e.g. "09:30 - 12:00" |
| Planned | EPL_START_PLANNED_DATE/TIME (formatted) | Will match the 'to' time in the service level (12:00 in above example) |
| Actual | EPL_ARRIVAL_DATE/TIME (formatted) | Actual arrival time confirmed by the driver |
| On Time | Calculated | If actual arrival date/time is later than planned date/time then "N", if there is no arrival date/time then "N" otherwise "Y". |
Note: Dates and Times will be formatted as a Date/Time column in Excel and can then be formatted as desired when viewed (for example, to just show the time).
Appendix A: TEST PLAN
| Test Script / Scenario Reference | Mash OTIF report | Call Number(s): 352946 |
| Test Script / Scenario Description | Testing the Mash On Time Report | PASS / ISSUES / FAIL |
| Menu Access | Reports | |
| Pre-requisites | None | Tested By: |
| Test Objective | To test that: the report exists; the report parameters are present and validated correctly and: the report produces correctly showing the correct data. | Date: |
| Step | Action | Result | Remarks | P/F |
| 1 | Admin Reports | |||
| Ensure that there is a variety of load and job data, with Routes. | ||||
| 1.01 | Select the reports screen and select the Mash On Time report. | The option is present and the report requires the correct parameters. | ||
| 1.02 | Enter a date range | The dates are validated correctly. The Route and Load drop-down lists are refreshed with appropriate data. | ||
| 1.03 | Select a Customer | The customers displayed are appropriate to the site. | ||
| 1.04 | Select a route | The routes displayed are appropriate to the date range. The Load drop-down list is refreshed with appropriate data. | ||
| 1.05 | Select a Job Type | The correct values are displayed in the list. | ||
| 1.06 | Select to run the report with a variety of parameters designed to select different data based on your configured data. | The expected data is shown in the report, with the correct columns, title and sequence. On-time is correctly identified in the report. |
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.25 | 0.25 | 750 | £187.50 |
| Functional Specification | 0.25 | 0.25 | 750 | £187.50 |
| Technical Specification | 0.00 | 0.00 | 750 | £0.00 |
| Development | 1.75 | 1.75 | 750 | £1,312.50 |
| Testing and Release | 0.25 | 0.25 | 750 | £187.50 |
| Implementation | 0.25 | 0.25 | 750 | £187.50 |
| Project Management | 0.25 | 0.25 | 750 | £187.50 |
| TOTAL | 3.00 | 3.00 | £2,250.00 | |
| Estimate excludes training, release to live and go live support. |
B.1 References
| Ref No | Document Title & ID | Version | Date |
| 1 |
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
Barry Preece | OBSL Project Manager | _____________________________ |
Steve Carter | Customer Representative | _____________________________ |
