OBS Logistics Ltd

Asset Counting

Functional Specification

12th August 2019 - 0.2
Reference: FS 359211 SCR-358196-1/2/3

FUNCTIONAL OVERVIEW

Client Requirement

Asset counting functionality is to be added to CALIDUS ePOD.

This includes:

Asset Count report.
Asset Count maintenance.

This is asset counting based on the count of products collected and delivered from locations, not asset tracking.

Solution Overview

New site-level configuration flags will be added to control the asset counting new functionality.

A new screen in C-ePOD Admin (Asset Counts) will be created to allow the entry and maintenance of counts of DU types against particular customer codes.

Business logic will be added to the database to count items into and out of customer locations, based on the customer of the jobs completed, the type of job (collection or delivery, loading or unloading), and the status of the job (completed or cancelled).

This will update the asset counts against a customer.

Products in the system will be modified to allow products to be identified as collection products or delivery products against a job, regardless of the job type.

The C-ePOD Admin screen that displays and maintains products against a job will be modified to show this new Activity indicator, and will allow this to be entered and edited.

This Activity property will be allowed to be specified on products through the OBS XML format, through web services and flat file. If this is not provided, the property will default to the value of the job type (C - collection or D - delivery).

A report will be created that can be run from the C-ePOD Admin console. This report will allow the selection of customer and date range.

The report will produce an Excel spreadsheet containing 2 sheets:

Summary - a count of all DU types and the amount at that location.
Detail - a history of all transactions to or from that customer location that affected the DU counts, by site, date, job, activity and DU type, indicating the quantity of each DU type moved.

Scope

This change will be applied to the latest version of CALIDUS ePOD version 4.5.

Impact

None - this is a new implementation of CALIDUS ePOD.

CONFIGURATION SET-UP

Pre-requisites

The CALIDUS ePOD system must be implemented as described in the solution design document, referenced in the appendices.

Menu Structure

The new report will be added to the Reports screen on the Tasks menu:

Asset Count report.

A new menu option will be added to the Maintenance menu in the CALIDUS ePOD Admin console:

Asset Counts

Data

This development is being completed for multiple customers.

For those who will have quantities of assets (products) collected and delivered on the same jobs (e.g. Booker), the sites are expected to be configured to use Asset Tracking by Product, and to count all jobs.

For those who will have separate warehouse sites for loading and unloading jobs (e.g. The Delivery Group), the sites are expected to be configured to use Asset Tracking by Product. The warehouse sites are expected to be configured to count all jobs, whereas the depot sites will be configured to count jobs excluding loading and unloading jobs.

DU Types are required to be configured through the Codes maintenance screen.

Site customers must be configured i.e. a customer created with the same ID as the Site ID.

Jobs are expected to be received through an interface or created within the C-ePOD Admin console, with a Unit Type of the DU code, indicating the activity. In general, the following is how the products should be created.

Product Code - anything, but expected to be the DU type.
Sequence - anything, but must be unique for the job and product code.
Description - the DU description. For those who will have quantities of assets (products) collected and delivered on the same jobs (e.g. Booker), the description should indicate the DU Type description and the activity i.e. "Collect Pallet", "Deliver Cage", etc.
Unit Type - the DU Type.
Activity - For those who will have quantities of assets (products) collected and delivered on the same jobs (e.g. Booker), the activity should indicate whether this is a collection or delivery.

Implementation Advice

None

FUNCTIONAL DESCRIPTION

System

Business logic will be added to the system to count items into and out of customer locations, based on the customer of the jobs completed, the type of job (collection or delivery, loading or unloading), and the status of the job (completed or cancelled).

This is controlled by new site-level configuration elements, shown below.

If Asset Tracking is enabled as "Count Assets from Products", and Asset Configuration is set to anything except "None", the system will update the asset counts against a customer.

For every completed delivery job at a customer location, the asset count of the DU associated to the unit type of the products will be increased by the quantity of that product line.
For every completed collection job at a customer location, the asset count of the DU associated to the unit type of the products will be decreased by the quantity of that product line.

If the Asset Configuration is set to "All", the collection jobs will include those jobs specified as Loading jobs, and the delivery jobs will include those specified as Unloading jobs.

Site Maintenance

New site-level configuration flags will be added to control the new asset counting functionality.

The new flags will be added to the Site Maintenance screen, on the Admin tab.

The new configuration field controlling the type of asset counting will be labelled as Asset Tracking and will provide a drop-down list of options:

Disabled.
Count Assets from Products.

Note: This flag is the main control element - if this is not set, the system will ignore the setting on the following flag.

The new configuration flag controlling the configuration will be labelled as Asset Configuration and will provide a drop-down list of options:

All - all collection and delivery jobs will be counted, including loading and unloading jobs, using the tracking type above.
All excl. Load/Unload - only collection and delivery jobs will be counted, excluding loading and unloading jobs, using the tracking type above.
None - the default option. If this is selected, this is the equivalent of disabling asset tracking in the flag above.

Asset Counts Screen

A new screen in C-ePOD Admin (Asset Counts) will be created to allow the entry and maintenance of counts of DU types against particular customer codes. The screen will be accessible from the main menu, from the Maintenance sub-menu.

The screen will operate similarly to the other screens in the system, in that it will have:

A search panel, to enter search criteria.
A results table, showing the results of the search.

The search panel will allow you to enter all or part of the customer code.

The user will then be able to click Search and the DU types and counts for that customer will be shown in the results table.

The result table will show:

DU Type.
DU Description.
Count.

The user can edit the counts for the customer by clicking on the result line - the screen will display an entry panel to allow changing of the quantity for that DU type.

Products Screen

Products in the system will be modified to allow products to be identified as collection products or delivery products against a job, regardless of the job type.

The C-ePOD Admin screen that displays and maintains products against a job will be modified to show this new Activity indicator, and will allow this to be entered and edited. This activity will be available to be displayed on the Products results table, and in the Add/Edit pop-up window.

Products are expected to be created within the C-ePOD Admin console with an Unit Type of the DU code, indicating the activity. The activity against a product will default to the job type (i.e. collection of delivery).

The Unit Type against the product will offer a lookup of DU codes.

Note: The screen will not prevent saving products where the DU type has not been specified in the Unit Type field.

External System Interface

If the implementation is creating jobs through an interface from another system (i.e. jobs are not being manually entered in C-ePOD Admin) then they will identify the DU type and activity on the interface file.

The activity property will be allowed to be specified on products through the OBS XML format, through web services and flat file. If this is not provided, the property will default to the value of the job type (C - collection or D - delivery).

Asset Count report

A report will be created that can be run from the C-ePOD Admin console.

When selected, the report will prompt for the parameters:

Date range - this cannot be more than a month apart from each other. A date lookup will be provided. The dates will default to 31 days prior to todays' date, up to today's date.
Customer - from a drop-down list, defaulting to "All Customers".

When the user clicks Run Report, the application will validate the parameters. The validation will appear as a box-out on the parameter that is wrong, stating the issue.

In this case, the Date To parameter will be validated, showing that the date range must be 31 days or less.

The report will not run if there are validation errors.

When the user clicks Run Report, the application will create and then open up the report. Depending on the browser settings, this could be through the browser, through download or directly in Microsoft Excel (if locally installed).

The report will produce an Excel spreadsheet containing 3 sheets, as shown in the following sections.

The report will be named for download as follows:

   Asset Count Report-{CUSTOMER}-{DATEFROM}-{DATETO}.xlsx

So, running the report for all customers from 08/07/2019 - 08/07/2019, the report would be named:

   Asset Count Report-ALL-20190708-20190708.xlsx

Parameters sheet

This sheet will display the parameters for the report, as well as the report title.

The sheet will show:

A Logo, derived from the site logo of the site that the user logged on with when the report was run.
Report Title, fixed to "Asset Count Report"
Parameters, showing:
- The selected Customer code and name.
- The Date From selected.
- The Date To selected.
- The site, derived from the site that the user logged on with when the report was run.

Asset Counts sheet

This sheet will display a summary of the current counts of DU types at a customer location.

The table will show:

Site - the site code.
Customer - the customer code.
Name - the customer name.
DU Type - the DU (item) type of the product.
Description - the description of the DU, from the codes standing data.
Count - a count of the items at this location.

Only details of DUs for the customer selected will be shown.

Asset History sheet

This sheet will display the detailed history of all transactions to and from the selected customer locations, in Site, Customer, Date, Time and DU type sequence.

The table will show:

Site - the site code.
Customer - the customer code.
Name - the customer name.
Job Code - the main job reference.
Cust Ref - the customer's reference.
Date - the actual end date of the job.
Time - the actual end time of the job.
DU Type - the DU (item) type of the product.
Description - the description of the DU, from the codes standing data.
Count - a count of the items at this location.
Activity - "Collected" or "Delivered", depending on that product line's activity.

Only details of jobs and DUs for the customer selected within the date range will be shown.

TECHNICAL NOTES

Modules Changed

Module Name	Module Type	Notes
product_containers.aspx	WinForm
product_containers.aspx.cs	WinForm code
EPOD_DATASERVICE.cs	DAL object
EPOD_PRODUCT.cs	DAL object
EPOD_SITE.cs	DAL object
AssetCounts.cs	DAL/BAL objects
site_header.aspx	WinForm
site_header.aspx.cs	WinForm code
Screens/AssetCounts/Index	MVC Form
{Date}_{Time}-{SysVer}-359211_AssetCounting.sql	Database script

Table Updates

The following fields will be added to table EPOD_SITE:

Name	Type	Nullable	Default	Storage	Comments
EPL_ASSET_TRACKING_IND	smallint	N	0
EPL_ASSET_CONFIG_IND	smallint	N	0

The following fields will be added to table EPOD_PRODUCT:

Name	Type	Nullable	Default	Storage	Comments
EPL_ACTIVITY_CODE	nvarchar(20)	N	See Notes		The field will not default in the database - the DAL object and/or interface will default this to the job type of the job.

A new table EPOD_ASSET_COUNT will be created:

Name	Type	Nullable	Default
EAC_SITE_ID	nvarchar(10)	N
EAC_CUSTOMER_CODE	nvarchar(20)		See Notes
EAC_DU_TYPE	nvarchar(20)	N
EAC_QUANTITY	int32/64	N	0

Note:

A primary index of PK_EPOD_ASSET_COUNT should be created with the columns EAC_SITE_ID, EAC_CUSTOMER_CODE and EAC_DU_TYPE.

Developer Notes

Database/DAL Procedures

The system will be modified to maintain the count of all products quantities as they are confirmed as completed.

This can be done within the EPOD_JOB or EPOD_PRODUCT DAL objects or as triggers on the EPOD_JOB or EPOD_PRODUCTS table. It is recommended that this is done against the EPOD_PRODUCTS table or DAL object.

When job (or product, if this is easier) moves to status "C" (confirmed)
Check site asset counting flags to determine whether this asset counting functionality is enabled for products (EPOD_SITE.EPL_ASSET_TRACKING_IND = 1 and EPOD_SITE.EPL_ASSET_CONFIG_IND >0).
Check job unloading flag (EPOD_JOB.EPL_LOADING_FLAG) and check if the site requires loading/unloading tasks to be processed (EPOD_SITE.EPL_ASSET_CONFIG_IND = 2).
Check that the product's unit type is not blank and exists as a DU type before continuing.
- Check EPOD_PRODUCT.EPL_UNIT_TYPE is not blank.
- Check EPOD_PRODUCT.EPL_UNIT_TYPE is on EPOD_REASON_CODES, with site set to the job or product's EPL_SITE_ID, EPL_REASON_TYPE is "DU" and EPL_REASON_CODE is EPL_UNIT_TYPE.
Get the current count of the DU (referred to as HERE following) - create it if not found.
- Get EPOD_ASSET_COUNT.EAC_QUANTITY for the EPOD_ASSET_COUNT record where EPOD_ASSET_COUNT.EAC_SITE_ID = EPOD_PRODUCT.EPL_SITE_ID, EPOD_ASSET_COUNT.EAC_CUSTOMER_CODE = EPOD_JOB.EPL_CUSTOMER_CODE and EPOD_ASSET_COUNT.EAC_DU_TYPE = EPOD_PRODUCT.EPL_UNIT_TYPE.
Check the new flag EPOD_PRODUCT.EPL_ACTIVITY_CODE.
If "C" (Collection), take EPOD_PRODUCT.EPL_PRODUCT_QTY_ACTUAL away from count of HERE. Note that the quantity may not be reduced to below zero - if it is, set it to zero.
If "D", Delivery, add EPOD_PRODUCT.EPL_PRODUCT_QTY_ACTUAL to count of HERE.

DAL Objects

The EPOD_PRODUCTS DAL object (EPOD_PRODUCTS.cs, part of the EPODServer project) will be modified to add a new property of EPL_ACTIVITY_CODE against a product.

This will be populated from the new field EPOD_PRODUCT.EPL_ACTIVITY_CODE, and can be added, updated and read.

This will be added to the ToExportXML method, for export of jobs when they are completed.

The EPOD_SITE DAL object (EPOD_SITE.cs, part of the EPODServer project) will be modified to add new properties as follows:

EPL_ASSET_TRACKING_IND
EPL_ASSET_CONFIG_IND

This will be populated from the new fields on EPOD_SITE and can be added, updated and read.

A new EPOD_ASSET_COUNT DAL object will be created in the new style, within the EPODDatabaaseClassLibrary project.

The DAL object will support all CRUD options for the table.

A BAL object will also be written to accommodate retrieving a list of records by Site and Customer Code and DU Type, as well as retrieving a single record by Site, Customer Code and DU Type.

The EPOD_PRODUCT DAL object will be modified to default the new field EPL_ACTIVITY_CODE to the job type (EPL_JOB_TYPE of EPOD_JOB, if the activity code has not been provided (i.e. is null or a zero-length string).

Note: This is to be present as a 'fall-back' - in most cases, the activity code should be provided as a default when creating EPOD_PRODUCT records, through the already specified changes to:

The Admin Products screen
The OBS XML import web service.
EBB Paper import
Partnerlink CSV import
OBS CSV import.

These changes are specified below.

Job Import

The OBS XML import processes will be modified to allow the definition of the EPL_ACTIVITY_CODE field against products.

EPOD_DATASERVICE.cs has procedure ProcessXMLImport, which is called by the web services and by the AutoImport process. This method calls ProcessJobs, which calls ProcessProducts. This ProcessProducts procedure can also be called from ProcessContainers.

ProcessProducts and ProcessContainers will be modified to receive the EPOD_JOB DAL object by reference.

ProcessJobs and ProcessContainers will pass the EPOD_JOB DAL object by reference to these two procedures, when called.

The ProcessProducts method will set EPL_ACTIVITY_CODE from the value in the XMl (xePROD) if this tag is found. If this is not found, this will default to the value of EPL_JOB_TYPE of the EPOD_JOB DAL object passed to the procedure.

The C-ePOD import and export documentation and schemas will be modified to reflect this change, namely:

EPODServer project:
- EPOD_JOB.xsd
WebApp project:
- XMLUpload.xsd
Documentation in CALIDUS ePOD Assist:
- CALIDUS ePOD Interfacing
Project Documentation in P:\Development\EPOD\Documentation\XML Interface:
- EPOD Import Mapping v4.x.x.x.xlsx (new versions created for the new system version)
- EPOD Export Mapping v4.x.x.x.xlsx (new versions created for the new system version)

This then changes the product for all creation of products at import except the following:

EBB Paper import
Partnerlink CSV import
OBS CSV import.

These will be modified to default the product's EPL_ACTIVITY_CODE field from the job's type (EPL_JOB_TYPE of EPOD_JOB).

Admin Site screen

New site-level configuration flags will be added to the Admin Site Maintenance screen (site_header.aspx) to control the new asset counting functionality.

The new flags will be added to the Site Maintenance screen, on the Admin tab, in the indicated positions.

The new configuration field, id "ddlAssetTracking", controlling the type of asset counting will be labelled as Asset Tracking and will provide a drop-down list of options:

Text "Disabled", value 0.
Text "Count Assets from Products", value 1.

The field will be sourced and populated from EPOD_SITE.EPL_ASSET_TRACKING_IND.

The field will have pop-up help (i.e. a title) of "A drop-down list that controls whether Asset Tracking is enabled for this site, and what kind of Asset Tracking is enabled. Currently only Asset Counting from Products is supported."

The field values will be populated from a new EPOD_LIST and EPOD_LIST_ITEMS for name "ddlAssetTracking", with the acceptable values stored on EPOD_LIST_ITEMS, through a call to EPODUtils.LoadDDL in existing page method PopulatePage in site_header.aspx.cs.

The new configuration flag controlling the configuration will be labelled as Asset Configuration and will provide a drop-down list of options:

All - all collection and delivery jobs will be counted, including loading and unloading jobs, using the tracking type above.
All excl. Load/Unload - only collection and delivery jobs will be counted, excluding loading and unloading jobs, using the tracking type above.
None - the default option. If this is selected, this is the equivalent of disabling asset tracking in the flag above.

The new configuration field, id "ddlAssetConfig", configuration will be labelled as Asset Configuration and will provide a drop-down list of options::

Text "None", value 0.
Text "All excl. Load/Unload", value 1.
Text "All", value 2.

The field will be sourced and populated from EPOD_SITE.EPL_ASSET_CONFIG_IND.

The field will have pop-up help (i.e. a title) of "A drop-down list that controls what kind of jobs are processed for counting assets for this siteSelect from 'All' for all collection and delivery jobs, 'All excl. Load/Unload' for collection and delivery jobs excluding loading and unloading jobs, or 'None' - the default option. If this is selected, this is the equivalent of disabling asset tracking in the flag above."

The field values will be populated from a new EPOD_LIST and EPOD_LIST_ITEMS for name "ddlAssetConfig", with the acceptable values stored on EPOD_LIST_ITEMS, through a call to EPODUtils.LoadDDL in existing page method PopulatePage in site_header.aspx.cs.

The new fields will be updated when the site record is saved, in the event handling method saveBTN_Clicked.

Admin Products screen

Products in the system will be modified to allow products to be identified as collection products or delivery products against a job, regardless of the job type.

The C-ePOD Admin screen that displays and maintains products against a job (product_containers.aspx) will be modified to show this new Activity indicator, and will allow this to be entered and edited. This activity will be available to be displayed on the Products results table, and in the Add/Edit pop-up window.

The existing table is "productTable" will be modified:

Change the existing header columns to use text resources for the labels.
Add a header column before "Status" labelled as "Activity".
Add a data column within the Repeater element for the Activity column, sourced from EPL_ACTIVITY_CODE. This should be translated (from "C" to "Collection" and "D" to "Delivery").

The div id "productPopup" will be modified as follows:

Change the existing field labels to use text resources.
Change the "Weight" cells (label and text box) to be no colspan.
Add a cell to the right of the "Weight" cells that are colspan 3.
Add a label to the first cell with a resource label from text "Activity".
Add a drop-down list, id "ddlProductActivity" to the second cell.
Populate the ddlProductActivity with values as follows:
- "C" - Collection
- "D" - Delivery.
Default the activity drop-down list from the value in EPL_ACTIVITY_CODE. If this is a new product, populate it from the value of EPOD_JOB.EPL_JOB_TYPE.
The existing cell housing text box "ppuUnitTypeTB" will have a new drop-down list field added next to it, called "ddlUnitType". This will be made visible only if the EPOD_SITE.EPL_ASSET_TRACKING_IND is 1.
The existing text box "ppuUnitTypeTB" will only be made visible if the EPOD_SITE.EPL_ASSET_TRACKING_IND is 0.
The Page Load methods will populate the options of field "ddlUnitType" from a call to DAL object EPOD_REASON_CODE.GetReasonCodes, passing the DB connection, the site and the reason type of "DU". The resulting list will be added as the datasource of the field, specifying the value field as EPL_REASON_CODE and the test field as EPL_DESCRIPTION. A default value of "" will be added as the first item, text "Select DU Type".
When selecting a product for edit or display, the drop-down list ddlUnitType will be defaulted from the value in EPL_UNIT_TYPE. If this is not found, the value should default to the blank value.

Note: The screen will not prevent saving products where the DU type has not been specified in the Unit Type field.

Event processing method edpSaveBtn_Clicked will be modified to check whether the new field ddlUnitType is visible. If so, this is used in preference to the value in ppuUnitTypeTB.

This method will also set the value of EPL_ACTIVITY_CODE from the selected value in ddlProductActivity.

Admin Asset Count screen

A new Asset Counts MVC screen (Screens/AssetCounts/Index) will be created in C-ePOD Admin to allow the entry and maintenance of counts of DU types against particular customer codes. The screen will be accessible from the main menu, from the Maintenance sub-menu. This will be achieved by adding values to the existing menu in the Data Script associated to this change.

The screen will operate similarly to the other screens in the system, in that it will have:

A search panel, to enter search criteria. This will be shown on entering the page.
A results panel, showing the results of the search on a results table. Results will not be shown if a search has not yet been completed.

The search panel will allow the user to enter all or part of the customer code. This will operate similarly to existing customer code entry in other screens, with the entry of any part of the customer code showing an auto-complete list of customers for the site. Any part of the name or code should produce matches.

The user will then be able to click Search and the DU types and counts for that customer will be shown in the results table.

The result table will show:

DU Type.
DU Description.
Count.

A title on the results panel will show the customer code selected and the customer name.

The following buttons will be available on this page:

Find - to show or hide the search panel.
New - to create a new Customer/DU type count.

The user can edit the counts for the customer by clicking on the result line - the screen will display an entry page to allow changing of the quantity for that DU type. This will be a new page which will show the following:

The customer code in a text box, disabled.
The customer name is a text box, disabled.
The DU Type in a text box, disabled.
The count, in a text box.

Each field will be labelled appropriately from the text resources.

The following buttons will be available on this page:

Delete - to delete the selected record, with a confirmation dialogue.
Save - to save changes and return to the main page.
Close - to return to the main page without saving.

Once edited, the results table will be refreshed to show the asset counts for that customer.

New DU type counts can be added by pressing on a New button on the main page. This will call a similar page to the edit page above. The page will show the following:

The customer code in a text box.
The customer name is a text box, disabled.
The DU Type in a drop-down list.
The count, in a text box.

If the page has been shown after a customer has been found, the customer text box should show the customer code and name from the main page.

The panel will allow the user to enter all or part of the customer code. This will operate similarly to existing customer code entry in other screens, with the entry of any part of the customer code showing an auto-complete list of customers for the site. Any part of the name or code should produce matches.

The DU type will be a drop-down list populated from the DU types in the system i.e. EPOD_REASON_CODE records of EPL_REASON_TYPE "DU" site. A default entry should be shown as "Please Select"

The screen will not allow creation of a DU type that already exists for the customer.

The following buttons will be available on this page:

Save - to save changes and return to the main page.
Close - to return to the main page without saving.

Once created, the results table will show the asset counts for that customer. If the customer was changed when entering, the search criteria will be changed to that customer code.

Note: Although this description shows this as multiple full-page screens, this could be achieved as effectively using a pop-open entry panel at the top of the screen, to edit or enter new codes.

Report Data Extract

A new method AssetCountReport will be added to XLReports.cs.

This will receive the following parameters:

The database connection
The Site ID.
The Date From.
The Date To.
The Customer Code.

This will create a DataSet and add tables by calling new procedures and adding the returned datatables to the Tables collection of the DataSet:

getACRSummary
getACRHistory

The method will return the DataSet.

Method getACRSummary will receive the following parameters:

The database connection
The Site ID.
The Customer Code.

The process will get all data using the following SQL (which may be contained in a stored procedure in the database):

   SELECT 
       eac.EAC_SITE_ID as [Site],
       eac.EAC_CUSTOMER_CODE as [Customer],
       c.CUSTOMER_NAME as [Name],
       eac.EAC_DU_TYPE as [DU Type],
       erc.EPL_DESCRIPTION as [Description],
       ep.[EAC_QUANTITY] as [Count]
   FROM EPOD_ASSET_COUNT eac
   INNER JOIN CUSTOMERS c
   ON c.EPL_SITE_ID = eac.EAC_SITE_ID
       AND c.EPL_CUSTOMER_CODE = eac.EAC_CUSTOMER_CODE
   INNER JOIN EPOD_REASON_CODES erc
   ON erc.EPL_SITE_ID = eac.EAC_SITE_ID
       AND erc.EPL_REASON_TYPE = 'DU'
       AND erc.EPL_REASON_CODE = eac.EAC_DU_TYPE
   WHERE eac.EAC_SITE_ID = pSite
       AND (eac.EAC_CUSTOMER_CODE = pCust OR pCust = "")

The parameters for the query will be set from the received parameters.

A DataTable called "ACRSummary" will be created and the data read into it from the resulting datareader, adding rows for each row in the reader. Every column will be added.

The process will return the datatable.

Method getACRHistory will receive the following parameters:

The database connection
The Site ID.
The Date From.
The Date To.
The Customer Code.

The process will get all data using the following SQL (which may be contained in a stored procedure in the database):

   SELECT ep.EPL_SITE_ID as [Site],
       ej.EPL_CUSTOMER_CODE as [Customer],
       ec.EPL_CUSTOMER_NAME as [Name],
       ej.EPL_JOB_CODE as [Job Code],
       ej.EPL_CUST_REF as [Cust Ref],
       ej.EPL_END_PLANNED_DATE as [Date],
       ej.EPL_END_PLANNED_TIME/10000 as [Time],
       ep.[EPL_UNIT_TYPE] as [DU Type],
       erc.EPL_DESCRIPTION as [Description],
       ep.[EPL_PRODUCT_QTY_ACTUAL] as [Count],
       CASE ep.EPL_ACTIVITY_CODE
        WHEN 'C' THEN 'Collected'
        WHEN 'D' THEN 'Delivered'
       END as [Activity]
   FROM EPOD_PRODUCT ep
       INNER JOIN EPOD_JOB ej
       ON ej.EPL_SITE_ID = ep.EPL_SITE_ID
           AND ej.EPL_JOB_ID = ep.EPL_JOB_ID
       INNER JOIN EPOD_CUSTOMER ec
       ON ec.EPL_SITE_ID = ej.EPL_SITE_ID
           AND ec.EPL_CUSTOMER_CODE = ej.EPL_CUSTOMER_CODE
       INNER JOIN EPOD_REASON_CODE erc
       ON erc.EPL_SITE_ID = ep.EPL_SITE_ID
           AND erc.EPL_REASON_TYPE = 'DU'
           AND erc.EPL_REASON_CODE = ep.EPL_UNIT_TYPE
   WHERE ep.EPL_UNIT_TYPE <> 
       AND ej.EPL_STATUS = 'C'
       AND ep.EPL_PRODUCT_QTY_ACTUAL <> 0
       AND (pCustCode =  OR ej.EPL_CUSTOMER_CODE = pCustCode)
       AND ep.EPL_SITE_ID = pSite
       AND ej.EPL_END_ACTUAL_DATE >= pDateFrom
       AND ej.EPL_END_ACTUAL_DATE <= pDateTo
   ORDER BY ej.EPL_SITE_ID,
       ej.EPL_CUSTOMER_CODE,
       ej.EPL_END_PLANNED_DATE,
       ej.EPL_END_PLANNED_TIME,
       ep.EPL_UNIT_TYPE;

The parameters for the query will be set from the received parameters.

A DataTable called "ACRHistory" will be created and the data read into it from the resulting datareader, adding rows for each row in the reader. Every column will be added.

The process will return the datatable.

Admin Reports screen

The reports screen ExcelReports.aspx will be modified.

In ExcelReports.aspx.cs:

Add "Asset Count report" manually as value "8" to the drop-down list of reports ddlReportSelect, after population from the database. Label it as such in the drop-down list. Ensure that the report is only added if EPOD_SITE.EPL_ASSET_TRACKING_IND is set to value 1.

Re-utilise the Date Range code from the Service report, and the the list of customers from the Transport report. Load the ddlACRCustomer drop-down list from the customer list. The customer list should default to "All Customers". The default "All Customers" value should be a value that cannot be entered for a site in C-ePOD, such as "*", space or null.

Note: If the Collection Performance report has been developed, source the code for the date range and validation from that report. This change is referenced in the appendices.

Add a new case "8" to the select in the CreateXLS_Click event handler. Set the parameters and call a new method AssetCountReport, storing the resulting dataset.

Create a new method here called CreateAssetCountReport, passing the dataset and the parameters. Call this only if the report type (exportTypeDDL) is "xlsx".

Exclude report "8" from the standard report processing.

A new method CreateAssetCountReport will be created and called. This can be copied in structure from the existing Resource Activity report:

Create a new workbook.
Create 3 new worksheets: Parameters, Asset Counts and Asset History.
Update the Parameters worksheet with the parameters and the logo.
Add the data from the ACRSummary Data Set to the Asset Counts sheet. Add all columns and titles from the data set.
Add the data from the ACRHistory Data Set to the Asset History sheet. Add all columns and titles from the data set.
Write the worksheet to the Response OutputStream with the correct name and close the Response.
- Naming convention is:

   Asset Count Report-{DEPOT}-{DATEFROM}-{DATETO}.xlsx

- So, for all depots from 08/07/2019 - 08/07/2019, the report would be named:

   Asset Count Report-ALL-20190708-20190708.xlsx

The report layout has been prototyped and is shown in the appendices.

In ExcelReports.aspx: Add a panel for the parameters for the new report, called pnlAssetCounting.

Add a Date From (tbACRDateFrom) and Date To (tbACRDateTo), both with appropriate labels sourced from the resource library, with calendar extenders, and the Date To with a validation extender to check that the two dates are not more than 31 days apart. Add javascript checking if necessary. The dates will both default to yesterday's date.

Add a drop-down list ddlACRCustomer.

The Clear button will reset the dates to today's date and the customer to "All Customers".

Data Modification Script

A data modification script will be required to add the following screen to the default menu under the Maintenance (MNT) sub-menu, on any already-created menus. The screen will be sequenced as the last menu item.

On table EPOD_ADM_MENU_OPTION:

AMO_MENU_TEXT	AMO_CALLED_PROG	AMO_OPTION_HELP
Asset Counts	Screens/AssetCount/Index	Find and maintain asset counts for a customer.

On table EPOD_ADM_PROGRAM:

APR_DESCRIPTION	APR_NAME	APR_PROGRAM_TYPE
Asset Counts	Screens/AssetCounts/Index	MVC

The icon used on the menu (and referenced in APR_IMAGE_FILENAME against the specific screen in EPOD_ADM_PROGRAM) should come from the following FontAwesome icon:

Asset Count - fas fa-pallet.

The font colour is set to rgba(68,191,251,1).

The script will add drop-down list values to support the new flags against the Site screen, adding the following to EPOD_LISTS:

ddlAssetTracking
ddlAssetConfig

The acceptable values (specified in the Admin Site section above) will be added to EPOD_LIST_ITEMS with ListId equal to the ID of the record on EPOD_LISTS.

This script will also include the database table modifications specified earlier in this section. A single script will be required, listed in the modules changed.

This will be named {Date}_{Time}-{SysVer}-359211_AssetCounting.sql, with Date, Time and System version are set from the current date, time and system version respectively.

The script must be added to the EPOD_DatabaseScriptsLibrary project and set to "Copy Always" to output directory.

TEST PLAN

Test Script / Scenario Reference	Asset Counting	Call Number(s): 359211 SCR-358196-1/2/3
Test Script / Scenario Description	Testing Asset Counting functionality	PASS / ISSUES / FAIL
Menu Access	Maintenance/Asset Counts
Pre-requisites	None.	Tested By:
Test Objective	To test that; Asset counts can be set up; products are created with activities through screens and imports; counts are maintained by the system and; the asset count report works as expected.	Date:

Step	Action	Result	Remarks	P/F
1	Import

1.01	Create a load import file in OBS XML format without activity specified against products. Import it.	The products are created with an activity code set to the job type.
1.02	Create a load import file in OBS XML format with activity specified against products. Ensure that there are 2 products and that the activity code is different on both. Import it.	The products are created with an activity code as specified in the file.
1.03	Create an OBS CSV import file with products. Import it.	The products are created with an activity code set to the job type.
1.04	Create a Partnerlink CSV import file with products. Import it.	The products are created with an activity code set to the job type.

Step	Action	Result	Remarks	P/F
2	Admin

2.01	Check the menu.	The new Asset Counts screen is shown on the menu in the appropriate place.
2.02	Start the Site Maintenance screen. Click the Admin tab.	The new Asset fields have been added in the apprpriate area, with the correct values in the drop-down list.
2.03	Set the new Asset fields in the list and save.	The asset fields are set accordingly.
2.04	Open the jobs screen. Find a collection or delivery job with products and choose the option to see the details of the job.	The products results table shows the activity code (or the field can be chosen to be displayed).
2.05	Click select against an existing product.	The pop-up shows the activity code.
2.06	Ensure that the new flags are not enabled. Click select against an existing product.	The pop-up shows the activity code. The Item Type offers a plain text entry box.
2.07	Ensure that the new flags are enabled. Click select against an existing product.	The pop-up shows the activity code. The Item Type offers drop-down list of DU types.
2.08	Open the Asset Counts screen.	The search criteria are displayed and correct.
2.09	Enter part of a customer code.	Matching customers are displayed for selection.
2.10	Enter part of a customer name.	Matching customers are displayed for selection.
2.11	Select any customer (without assets) and search.	The screen displays no results.
2.12	Choose to add an asset.	The screen allows entry of the DU type from a drop-down list, and quantity through a text box. The customer and description is populated and allowed to be changed.
2.13	Set the customer, DU type and count and save.	The count is displayed in the results table for that customer.
2.14	Click the created asset.	The screen allows modification of the quantity, or deletion of the asset count.

Step	Action	Result	Remarks	P/F
3	Process

3.01	Ensure that asset counting is disabled. Complete a collection or delivery job. Check asset counts using the screen for the job's customer.	No asset counts have been created or updated.
3.02	Ensure that asset counting is enabled. Complete a delivery job with multiple DU Types, some that exist and some that don't. Change the product quantities on one line, and cancel another. Check asset counts using the screen for the job's customer.	Asset counts are created or incremented with the appropriate quantity. The cancelled line creates no asset counts.
3.03	Complete a collection job with multiple DU Types, some that exist and some that don't. Change the product quantities on one line, and cancel another.	Check asset counts using the screen for the job's customer. Ensure that one product line being collected exceeds the asset count for that customer. Asset counts are created or decremented with the appropriate quantity. The cancelled line creates no asset counts. The over-collection of the asset results in a an asset count of zero.

Step	Action	Result	Remarks	P/F
4	Admin Reports
	Ensure that there have been several completed jobs across several customers, and there are several asset counts over multiple customers.
4.01	Choose the Reports menu item.	The screen allows selection of an Asset Count report.
4.02	Select the Asset Count report.	The screen displays a date range and customer selection. The date range is set to be from 31 days prior, to today's date. The customer drop-down is set to All Customers.
4.03	Run the report.	A spreadsheet is produced, named correctly. The parameters are shown correctly on the parameters sheet. All customer counts are shown. The history shows all the jobs for all customers in that date range.
4.04	Set the parameters to be for a single customer with asset counts. Run the report.	A spreadsheet is produced, named correctly. The parameters are shown correctly on the parameters sheet. Asset counts are shown just for that customer. The history shows all the jobs for that customer in that date range.

APPENDIX A: QUOTE & DOCUMENT HISTORY

Cost Details
Activity	Estimate No. of Days	No. of Days	Rate per Day (£)	Cost (£ Exc. VAT)
Requirements	0.00	0.00	0	£0.00
Change Request Evaluation	0.00	0.00	0	£0.00
Functional Specification	2.00	2.00	0	£0.00
Technical Specification	0.00	0.00	0	£0.00
Development	8.00	8.00	0	£0.00
Testing and Release	1.50	1.50	0	£0.00
Implementation	0.25	0.25	0	£0.00
Project Management	1.00	1.00	0	£0.00

TOTAL	12.75	12.75		£0.00

Estimate excludes training, release to live and go live support.

References

Ref No	Document Title & ID	Version	Date
1	SDD 358307 The Delivery Group Solution Design	0.4	05/08/2019
2	File:Asset Count Prototype.xlsx	N/A	04/07/2019
3	FS 359234 SCR-358307-8 DELG Collection Performance Report	1.0	07/08/2019

Glossary

Term or Acronym	Meaning
General Definitions
EPOD	Electronic Proof of Delivery. The OBSL EPOD system is CALIDUS ePOD. This also comprises the basis of the Service Completion system CALIDUS eServ.
Server	The portion of the CALIDUS ePOD/eServ systems that controls all the data and sends information to and receives updates from the mobile device.
Mobile Device; PDA	The device used by the driver to perform the jobs. Typically an Android mobile device or tablet.
Site	The site usually defines the depot, business or the transport group (carrier). It can be set to any value required by the customer. All transactions data (for example, loads and jobs) and standing data (for example, vehicles and uses) belong to a site. An EPOD user, on a device or in the Admin screen, can only see data for one site at a time.
Load	A single journey for the driver with a set of work attached. A load is identified by a unique load ID. This may also be referred to as a worklist or workload.
Job	Also Consignment. A single task for the driver as a specific location. This could be the collection of goods or the delivery of goods. Jobs may also be Services (for example, servicing, installing or de-installing a boiler). A job is identified by a unique job ID but can also have other references held against the job (e.g. job code, SO number, customer reference and external reference).
Job Group	Jobs must be tagged with a Job Group. All jobs tagged with a single job group are processed in the same way. The job group has configuration associated to it to control such items as: POD/POC Report settings; Pre-Job actions (such as signing at a gatehouse); Post-Job actions (such as who signs for the item, are photos required); configurable fields required for entry for the jobs; Terms and Conditions displayed and; driver/user process (such as photos required for cancellation, comments/notes allowed). The job group can be used for any or all Sites, and the configuration against the job group can be different in each site. Job Groups can also be restricted from Admin and Remote users, so that certain users only see jobs for certain groups.
Container	A generic term for any object that contains the items being collected or delivered. Examples of containers are: Pallet; Package; Carton; Item; Cage. A special container "Loose Products" - see Product below. A container is identified by a container ID which is unique to this physical container.
Product	A product is any goods that are being collected or delivered where the product has a 'Product Code' which identifies what the product is but which does not uniquely identify each individual item. A product will also have a quantity associated with it to indicate how many items of this 'Product Code' are being collected or delivered. Products can either be processed within a 'Container' or as 'Loose Products' without a 'Container'.
Owner	The owner of the order that created the job. Typically this is the sales team that took the order and will be responsible for dealing with queries from the customer regarding the status.
Operator; Executor	The Site (depot or carrier) that is executing the load or loads that are involved in the delivery of the items.
Item Related Definitions
Job Code	A reference associated with a job or job(s). This reference is common to connected jobs, for example this would be the same on both the collection of goods and the associated delivery of the same goods. Typically this would be the transport unique reference.
SO Number	A reference associated with a job which indicates the "Sales Order Number" this job is associated with.
Customer Reference	A reference associated with a job which has been provided by and will be recognised by the customer.
External Reference	A reference associated with a job which does not match any of the existing references, usually because it has been provided by an external system.
Pallet	An alternative for 'Container'. The term pallet is used when the operation only uses portable platforms as the container for goods.
Package	An alternative for 'Container'. The term package is used when the operation only uses boxes or wrapping as containers for goods.
Package Code	A code representing the type of 'Container'.
Package Desc	A description of the type of 'Container'.
Product Code	A code which identifies what a product is.
Item	A generic term for any individual item that can be collected or delivered. An item can represent a 'Container' or a 'Product'. This can also be used as an alternative for 'Container' when the operation only treats the goods as individual items, i.e. not as identifiable products.
Service Item	An item which will be serviced by a service job. See action 'Service'.
Issue Life	The time after which an item is no longer fit for purpose.
Pack Size; Case Quantity	A product may consist of a full quantity of items, inside a pack. The Pack Size (or Case Quantity) defines the amount of this product contained in a single pack. For example, if there are 85 items to deliver, with a pack size of 24, the number of full packs is determined to be 3 (24 * 3, or 72), with the remaining (13) being 'loose' quantity. This is displayed as "3/13" on the mobile application.
UOM; Item Type	Unit of Measure; The major (case) UOM. This can optionally be displayed on the mobile device when changing product quantities.
Product Type	A classification of the product being delivered. For example, a company may deliver 7 different mortar products and 80 different concrete slab products. The Product Types may be set to "MORTAR" and "SLABS". This may be used to attach additional configuration, changing the data required when collecting or delivering these product types.
Status Definitions
Status	An indicator of how far through the processing a 'Job', 'Container' or 'Product' has progressed.
Pending	A status indicating that the processing has not yet started, but is required to be completed.
In Progress	A status indicating that processing has started but not yet finished.
Complete	A status indicating that the 'Job', 'Container' or 'Product' has been collected or delivered.
Complete (Amended)	A status indicating that the 'Job', 'Container' or 'Product' has been collected or delivered but that some changes or amendments have been made. This means that not everything that was planned to be collected or delivered was collected or delivered, some items may have been cancelled or some products may only have had some of the planned quantities collected or delivered.
Complete (Claused)	A status indicating that the processing has been finished but that a 'Clause' condition has been recorded for this item.
Claused	See 'Complete (Claused)' and action 'Clause'.
Cancelled	A status indicating that the processing of this item or job is no longer required.
Cancelled at Collection	A status indicating that the delivery of a container or product is no longer required because the associated collection of this container or product was cancelled.
Submitted	An optional status that applies only to a 'Job' and which occurs after the 'Job' has been completed. This indicates that any time and expenses information recorded for the 'Job' has been submitted back to the server and can no longer be altered.
Action Definitions
Start	An action associated with a 'Job' meaning the driver is about to start the processing of this job or jobs. This action will mark the job(s) with a status of 'In Progress'.
Arrive	A conditional action associated with a 'Job' meaning the driver has arrived at the location the goods should be collected from or delivered to.
Continue	An action associated with a 'Job' meaning the driver has previously performed the 'Start' and/or 'Arrive' action and has exited the processing screen but is now going to continue the processing.
Collect	An action associated with a specific 'Container' or a 'Product' meaning the driver has collected the 'Container' or 'Product'. This action will mark the 'Container' or 'Product' with a status of 'Complete' or 'Complete (Amended)'.
Collect Claused	An action associated with a specific 'Container' or a 'Product' meaning the driver has collected the 'Container' or 'Product' but with a condition under which the collection was accepted. This action will accept the clause condition and then mark the 'Container' or 'Product' with a status of 'Complete (Claused)'.
Deliver	An action associated with a specific 'Container' or a 'Product' meaning the driver has delivered the 'Container' or 'Product'. This action will mark the 'Container' or 'Product' with a status of 'Complete' or 'Complete (Amended)'.
Deliver Claused	An action associated with a specific 'Container' or a 'Product' meaning the driver has delivered the 'Container' or 'Product' but with a condition under which the delivery was accepted. This action will accept the clause condition and then mark the 'Container' or 'Product' with a status of 'Complete (Claused)'.
Clause	An action associated with a specific 'Container' or a 'Product' that has already been collected or delivered meaning the collection or delivery has been accepted with a condition. This action will accept the clause condition and then mark the 'Container' or 'Product' with a status of 'Complete (Claused)'.
Cancel	An action associated with a 'Job', 'Container' or 'Product' meaning the collection or delivery will not be performed for this 'Job', 'Container' or 'Product'.
Submit	An optional action which can conditionally be carried out after a 'Job' has been collection or delivered meaning that any/all required expense or time recording for this 'Job' has been completed and can be submitted back to the server.
Service	A service of a service item or items. Typically, Installation, Deinstallation or Service. The process of a service usually encompasses Pre- and Port-work checks, information gathering and diagnosis and resolution notes. Additional references (MC Refs) may also be captured.
Actioned	A general term describing completing a job. So, 'Actioned' may be used instead of 'Collected', 'Serviced', 'Delivered'.
Consolidate	The action of taking several jobs and linking them together, so they are actioned at the same time with one start, arrive and signature.
Deconsolidate	The action of taking a consolidation of jobs and breaking them down into the component jobs again.
Job Swap	The action of selecting an existing load not assigned to the user, and picking jobs to transfer onto the user's load.
Signature Capture	Usually the final action of a job, where the customer's name and signature are entered.
Other Definitions
Reason Code	A code which represents the reason that a job was cancelled or an item was cancelled or claused.
Vehicle	The vehicle used for transporting the goods.
Vehicle Checks	Also Defect Checks. A series of questions representing the results of checks intended to ensure the vehicle is in an acceptable condition.
Metrics Entry	A series of questions to capture information either at the start or end of a 'Load'.
Driver	The person performing the collections or deliveries; the user of the device/application.
Engineer	The person performing the services; the user of the device/application.
Customer	The person/company the goods are being collected from or delivered to.
Signatory	The name of the person providing a signature.
T&Cs	Terms and Conditions. The T&Cs are shown when signatures are prompted for. The text of the T&Cs are defined in the system itself.
Transfer Load	A load select from which to swap jobs to the user's load.
Base	E.g. 'Return to Base'. Typically the depot from which the driver departed.
Unplanned Ad Hoc Collection	A collection job that is created by the driver, usually after delivering to a customer.
Ad Hoc Container Entry/Scanning	The process of adding containers (items) to a job that have not been pre-advised on the job.
Completion Report	POD, POC, Service/Work Report.
Load Assignment	The action of assigning a vehicle and/or a driver to a load.
Job Assignment	The action of putting jobs onto a load.
Collection/Delivery Windows; Access Windows	Periods of time between which it is acceptable to deliver or collect from that customer. This has limited use in the system, mostly for reporting purposes.
Location/Map Terms
Lat-Longs; GPS Co-ordinates, GPS Position	Latitude and Longitude co-ordinates, specified together as a single entity, identifying the exact position of a location. There are multiple formats - CALIDUS ePOD uses decimal notation, for example "53.3490818,-2.8521498" identifies the OBS Logistics office building in Liverpool.
GPS	Global Positioning System; the satellite system used to obtain a GPS position, for use with navigation and location positioning.
Geocode; Reverse Geocode	Geocoding is the process of obtaining lat-longs from an address. Reverse Geocoding is the process obtaining an address from lat-longs.
Geofence; Geofence Break	A Geofence is a perimeter around a location. A Geofence Break occurs when a device passes through this perimeter on entry or exit from the location.

Authorised By

Murray Middleton

OBS Project Manager

_____________________________

Anonymous

Search

FS 359211 SCR-358196-1-2-3 PROD Asset Counting

FUNCTIONAL OVERVIEW

Client Requirement

Solution Overview

Scope

Impact

CONFIGURATION SET-UP

Pre-requisites

Menu Structure

Data

Implementation Advice

FUNCTIONAL DESCRIPTION

System

Site Maintenance

Asset Counts Screen

Products Screen

External System Interface

Asset Count report

Parameters sheet

Asset Counts sheet

Asset History sheet

TECHNICAL NOTES

Modules Changed

Table Updates

Developer Notes

Database/DAL Procedures

DAL Objects

Job Import

Admin Site screen

Admin Products screen

Admin Asset Count screen

Report Data Extract

Admin Reports screen

Data Modification Script

TEST PLAN

APPENDIX A: QUOTE & DOCUMENT HISTORY

Navigation

Wiki tools

Page tools

Categories