FS 352946 Mash OTIF Report: Difference between revisions

From Calidus HUB
VisualWikitext
(v0.3 - revisions to data selection and display, to account for jobs that never arrived.)
(v1.0 - Initial release to client)
 
(One intermediate revision by one other user not shown)
Line 4: Line 4:
{{#vardefine:System|''CALIDUS'' ePOD}}
{{#vardefine:System|''CALIDUS'' ePOD}}
{{#vardefine:Doc_Title|Mash OTIF report}}
{{#vardefine:Doc_Title|Mash OTIF report}}
{{#vardefine:Version|0.3}}
{{#vardefine:Version|1.0}}
{{#vardefine:Date|9th October 2018}}
{{#vardefine:Date|23rd October 2018}}
{{#vardefine:Reference|352946}}
{{#vardefine:Reference|352946}}
{{#vardefine:Year|2018}}
{{#vardefine:Year|2018}}
Line 130: Line 130:
* EPL_SITE_ID = {Current Site}
* EPL_SITE_ID = {Current Site}
* EPL_CUSTOMER_CODE = {Customer ID} if provided.
* EPL_CUSTOMER_CODE = {Customer ID} if provided.
* EPL_ARRIVAL_DATE is populated.
* EPL_END_ACTUAL_DATE >={Date From}
* EPL_END_ACTUAL_DATE >={Date From}
* EPL_END_ACTUAL_DATE <={Date To}
* EPL_END_ACTUAL_DATE <={Date To}

Latest revision as of 13:10, 23 October 2018





Aptean Logo.png







Mash Purveyors

Mash OTIF report

CALIDUS ePOD

23rd October 2018 - 1.0
Reference: FS 352946











































Functional Overview

Client Requirement

The requirement is for CALIDUS ePOD to report On Time delivery performance.

Note 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 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 Note: All data selection for the parameters will be pre-filtered by the logged-on user's defined Site.

Note 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_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 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 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 ReferenceMash OTIF reportCall Number(s): 352946
Test Script / Scenario DescriptionTesting the Mash On Time ReportPASS / ISSUES / FAIL
Menu AccessReports 
Pre-requisitesNoneTested By:
 
Test ObjectiveTo 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 NoDocument Title & IDVersionDate
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		_____________________________
Retrieved from ‘https://calidusassist.adcservices.apteancloud.com/calidus-assist/OBS/index.php?title=FS_352946_Mash_OTIF_Report&oldid=6508’