FS 352946 Mash OTIF Report - Revision history

BAP: v1.0 - Initial release to client

2018-10-23T11:10:53Z

v1.0 - Initial release to client

← Older revision		Revision as of 13:10, 23 October 2018
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.4}}		{{#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}}

Anw: v0.4 - Corrections after review

2018-10-09T14:21:55Z

v0.4 - Corrections after review

← Older revision		Revision as of 16:21, 9 October 2018
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\|0.4}}
	{{#vardefine:Date\|9th October 2018}}		{{#vardefine:Date\|9th October 2018}}
	{{#vardefine:Reference\|352946}}		{{#vardefine:Reference\|352946}}
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}

Anw: v0.3 - revisions to data selection and display, to account for jobs that never arrived.

2018-10-09T14:01:29Z

v0.3 - revisions to data selection and display, to account for jobs that never arrived.

← Older revision		Revision as of 16:01, 9 October 2018
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.2}}		{{#vardefine:Version\|0.3}}
	{{#vardefine:Date\|~~8th~~ October 2018}}		{{#vardefine:Date\|9th October 2018}}
	{{#vardefine:Reference\|352946}}		{{#vardefine:Reference\|352946}}
	{{#vardefine:Year\|2018}}		{{#vardefine:Year\|2018}}
Line 24:		Line 24:
	== Client Requirement ==		== Client Requirement ==
	The requirement is for ''CALIDUS'' ePOD to report On Time delivery performance.		The requirement is for ''CALIDUS'' ePOD to report On Time delivery performance.

			{{Note}} Jobs with no arrival date must also be reported.


Line 49:		Line 51:
	* OT Status		* 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.		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.


Line 120:		Line 122:

	Data will be selected from the following tables:		Data will be selected from the following tables:
			* EPOD_JOB
	* EPOD_LOAD		* EPOD_LOAD
	* EPOD_JOB
	* EPOD_CUSTOMER		* EPOD_CUSTOMER
	These records will be joined ~~as normal~~ through their proper indexes and are required to be present to select data (INNER JOIN).		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 ~~(parameters in {Curly Brackets})~~:		EPOD_LOAD will be selected as follows:
	* EPL_ACTUAL_START_DATE >={Date From}
	* EPL_ACTUAL_START_DATE <={Date To}
	* EPL_SITE_ID = {Current Site}		* EPL_SITE_ID = {Current Site}
	* EPL_ROUTE_CODE = {Route Code} if provided.		* EPL_ROUTE_CODE = {Route Code} if provided.
	* EPL_LOAD_ID = {Load ID} if provided.		* EPL_LOAD_ID = {Load ID} if provided.

	~~EPOD_JOB~~ will be selected ~~as follows:~~
	* EPL_CUSTOMER_CODE = {Customer 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.
	* EPL_ARRIVAL_DATE is populated.
	* EPL_ARRIVAL_DATE >={Date From}
	* EPL_ARRIVAL_DATE <={Date To}

	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 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.
Line 159:		Line 167:
	\| Job Code \|\| EPL_JOB_CODE \|\| Job Code (Mash order ref)		\| Job Code \|\| EPL_JOB_CODE \|\| Job Code (Mash order ref)
	\|-		\|-
	\| Status \|\| EPL_STATUS \|\| Job Status (C = Complete, X = Cancelled, P = In Progress)		\| Status \|\| EPL_STATUS \|\| Job Status (C = Complete, X = Cancelled, I = In Progress, P = Pending)
	\|-		\|-
	\| Reason \|\| EPL_REASON_CODE \|\| Job level reason code		\| Reason \|\| EPL_REASON_CODE \|\| Job level reason code
Line 167:		Line 175:
	\| Service \|\| EPL_SERVICE_LEVEL \|\| e.g. "09:30 - 12:00"		\| Service \|\| EPL_SERVICE_LEVEL \|\| e.g. "09:30 - 12:00"
	\|-		\|-
	\| Planned \|\| ~~EPL_START_PLANNED_TIME~~ \|\| Will match the 'to' time in the service level (12:00 in above example)		\| Planned \|\| EPL_START_PLANNED_DATE/TIME (formatted) \|\| Will match the 'to' time in the service level (12:00 in above example)
	\|-		\|-
	\| Actual \|\| ~~EPL_ARRIVAL_TIME~~ \|\| Actual arrival time confirmed by the driver		\| 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", otherwise "Y".		\| 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).

Anw: v0.2 - Added Quote section.

2018-10-08T13:51:53Z

v0.2 - Added Quote section.

← Older revision		Revision as of 15:51, 8 October 2018
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.1}}		{{#vardefine:Version\|0.2}}
	{{#vardefine:Date\|8th October 2018}}		{{#vardefine:Date\|8th October 2018}}
	{{#vardefine:Reference\|352946}}		{{#vardefine:Reference\|352946}}
Line 84:		Line 84:
	= Functional Description =		= Functional Description =
	== Database/DAL ==		== Database/DAL ==
	Table EPOD_LOAD ~~will be modified~~ to have the following ~~indexes~~ created:		Table EPOD_LOAD may require modification to have the following index created:
	* EPL_SITE_ID, EPL_ROUTE_CODE, EPL_LOAD_ID		* 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.		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.


Line 103:		Line 105:

	{{Note}} All data selection for the parameters will be pre-filtered by the logged-on user's defined Site.		{{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.		Once entered and the Create Excel Spreadsheet button is clicked, the parameters will be validated as above.
Line 220:		Line 224:
	\|Test={{ #vardefineecho: SubCycle \| {{ #expr: {{ #var: SubCycle }} + 0.01 }} }}		\|Test={{ #vardefineecho: SubCycle \| {{ #expr: {{ #var: SubCycle }} + 0.01 }} }}
	\|Action=Select to run the report with a variety of parameters designed to select different data based on your configured data.		\|Action=Select to run the report with a variety of parameters designed to select different data based on your configured data.
	\|Result=The expected data is shown in the report, with the correct columns, title and sequence.		\|Result=The expected data is shown in the report, with the correct columns, title and sequence. On-time is correctly identified in the report.
	\|Remarks=		\|Remarks=
	\|PassFail=		\|PassFail=
Line 228:		Line 232:
	{{Doc_Appendix		{{Doc_Appendix
	\|Appendix=B		\|Appendix=B
	\|Estimate=N		\|Estimate=Y
	\|Glossary=EPOD		\|Glossary=EPOD
	\|Ref1=		\|Ref1=
Line 234:		Line 238:
	\|RefDate1=		\|RefDate1=
	\|REQ=0		\|REQ=0
	\|EST=0.5		\|EST=0.25
	\|FS=0.5		\|FS=0.25
	\|TS=0		\|TS=0
	\|DEV=2.0		\|DEV=1.75
	\|ST=0.5		\|ST=0.25
	\|IMP=0.25		\|IMP=0.25
	\|PM=0.25		\|PM=0.25

Anw: v0.1 - Initial Draft

2018-10-08T13:37:39Z

v0.1 - Initial Draft

New page

<div class="noprint">
{{#vardefine:Client|MASH}}
{{#vardefine:ClientName|Mash Purveyors}}
{{#vardefine:System|''CALIDUS'' ePOD}}
{{#vardefine:Doc_Title|Mash OTIF report}}
{{#vardefine:Version|0.1}}
{{#vardefine:Date|8th October 2018}}
{{#vardefine:Reference|352946}}
{{#vardefine:Year|2018}}
</div>
{{Doc_Title
|Client={{#var:ClientName}}
|System={{#var:System}}
|Title={{#var:Doc_Title}}
|Reference=FS {{#var:Reference}}
|Version={{#var:Version}}
|Date={{#var:Date}}
|Year={{#var:Year}}
}}


<div class="noprint">
= Functional Overview =
== Client Requirement ==
The requirement is for ''CALIDUS'' ePOD to report On Time delivery performance.

== 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 will be modified to have the following indexes 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.

== 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.

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_LOAD
* EPOD_JOB
* EPOD_CUSTOMER
These records will be joined as normal through their proper indexes and are required to be present to select data (INNER JOIN).

EPOD_LOAD will be selected as follows (parameters in {Curly Brackets}):
* EPL_ACTUAL_START_DATE >={Date From}
* EPL_ACTUAL_START_DATE <={Date To}
* EPL_SITE_ID = {Current Site}
* EPL_ROUTE_CODE = {Route Code} if provided.
* EPL_LOAD_ID = {Load ID} if provided.

EPOD_JOB will be selected as follows:
* EPL_CUSTOMER_CODE = {Customer ID} if provided.
* EPL_ARRIVAL_DATE is populated.
* EPL_ARRIVAL_DATE >={Date From}
* EPL_ARRIVAL_DATE <={Date To}

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:
{| class="wikitable" border="1"
|- bgcolor="silver"
!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, P = In Progress)
|-
| 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_TIME || Will match the 'to' time in the service level (12:00 in above example)
|-
| Actual || EPL_ARRIVAL_TIME || Actual arrival time confirmed by the driver
|-
| On Time || Calculated || If actual arrival date/time is later than planned date/time then "N", otherwise "Y".
|}



= Appendix A: TEST PLAN =
{{TestPlan_Header
|Title={{#var:Doc_Title}}
|Log={{#var:Reference}}
|Description=Testing the Mash On Time Report
|MenuAccess=Reports
|Prerequisites=None
|Objective=To test that: the report exists; the report parameters are present and validated correctly and: the report produces correctly showing the correct data.
}}
{{ #vardefine: Cycle | 0 }}{{ #vardefine: SubCycle | 0 }}
{{TestPlan_CycleHeader
|Cycle={{ #vardefineecho: Cycle | {{ #expr: {{ #var: Cycle }} + 1 }} }}{{ #vardefine: SubCycle | {{ #var: Cycle }} }}
|Title=Admin Reports
|Notes=Ensure that there is a variety of load and job data, with Routes.
}}  {{TestPlan_Test
|Test={{ #vardefineecho: SubCycle | {{ #expr: {{ #var: SubCycle }} + 0.01 }} }}
|Action=Select the reports screen and select the Mash On Time report.
|Result=The option is present and the report requires the correct parameters.
|Remarks=
|PassFail=
}} {{TestPlan_Test
|Test={{ #vardefineecho: SubCycle | {{ #expr: {{ #var: SubCycle }} + 0.01 }} }}
|Action=Enter a date range
|Result=The dates are validated correctly. The Route and Load drop-down lists are refreshed with appropriate data.
|Remarks=
|PassFail=
}} {{TestPlan_Test
|Test={{ #vardefineecho: SubCycle | {{ #expr: {{ #var: SubCycle }} + 0.01 }} }}
|Action=Select a Customer
|Result=The customers displayed are appropriate to the site.
|Remarks=
|PassFail=
}} {{TestPlan_Test
|Test={{ #vardefineecho: SubCycle | {{ #expr: {{ #var: SubCycle }} + 0.01 }} }}
|Action=Select a route
|Result=The routes displayed are appropriate to the date range. The Load drop-down list is refreshed with appropriate data.
|Remarks=
|PassFail=
}} {{TestPlan_Test
|Test={{ #vardefineecho: SubCycle | {{ #expr: {{ #var: SubCycle }} + 0.01 }} }}
|Action=Select a Job Type
|Result=The correct values are displayed in the list.
|Remarks=
|PassFail=
}} {{TestPlan_Test
|Test={{ #vardefineecho: SubCycle | {{ #expr: {{ #var: SubCycle }} + 0.01 }} }}
|Action=Select to run the report with a variety of parameters designed to select different data based on your configured data.
|Result=The expected data is shown in the report, with the correct columns, title and sequence.
|Remarks=
|PassFail=
}} {{TestPlan_CycleFooter}}


{{Doc_Appendix
|Appendix=B
|Estimate=N
|Glossary=EPOD
|Ref1= 
|RefV1= 
|RefDate1= 
|REQ=0
|EST=0.5
|FS=0.5
|TS=0
|DEV=2.0
|ST=0.5
|IMP=0.25
|PM=0.25
|Client={{#var:Client}}
|Year={{#var:Year}}
|FSEST=Y
|Rev1=Barry Preece
|Rev1Title=OBSL Project Manager
|Rev2=Steve Carter
|Rev2Title=Customer Representative
}}</div>
[[Category:{{#var:Client}} FS]]