FS 344086 SCR-344060-07 Oak Furniture Land POD POC Formats
Oak Furniture Land
Oak Furniture Land POD/POC Formats
CALIDUS ePOD
28th July 2017 - 0.2
Reference: FS 344086 SCR-344060-07
Contents
Functional Overview
Client Requirement
SCR-344060-07: Oak Furniture Land POD/POC Formats
Once jobs are completed, then will be confirmed with the C-ePOD server. After this confirmation, a POD/POC report may be produced.
All photos taken by the driver during the execution of the job (i.e. at cancellation of product, change quantity of product, job photos) will be displayed in the C-ePOD Admin system for the administrative users to view, but will not be included in the PDF version of the document (generally the version emailed to customers).
The format attached to the job group is the format that will be produced from the Admin console and displayed for customers when requested through CALIDUS Portal.
Solution Overview
The different job types will require different completion reports, although they will look very similar:
- Radial Deliveries - summing the delivery items into product lines, on a POD report.
- Radial Collections - using the POD report above, but displaying Collection instead of Delivery.
- Radial Loading - using the POD report above.
- Trunk Unloading - displaying the product lines themselves, on a POC report.
The format of the POD note has been prototyped to show capability. The format has been provided and the prototype is shown below:
Prototype Oak Furniture Land POD Format
Each element of the report will be populated as follows.
Page Header, expected to be displayed on every page:
- Company Logo - It is expected that this will be the logo taken from the site.
- "JB DIRECT DELIVERY NOTE" - fixed text, displaying "Collection Note" if the job is a collection.
- "0800 440 2254" - taken from the telephone of the site's address.
- DATE - The planned start date, in YYYY-MM-DD format.
- TIME - The planned start and end times, delimited by a dash, in HH:MM format.
- TYPE - The Job Type
- ORDER NUM # - The Job Code
- CUSTOMER/ADDRESS/POSTCODE/TEL1/2/3 - from the job address if present, otherwise the customer address.
- "DELIVERY INSTRUCTIONS" - Fixed text, displaying "COLLECTION INSTRUCTIONS" if the job is a collection. The job instructions are displayed beneath this.
- Failed Delivery checks (1) - taken from user-defined fields against the job.
- DATE - The actual end date, when the signature was obtained.
- TIME - The actual end time, when the signature was obtained.
- PRINT NAME - The signatory, captured by the driver on the device when the customer signs.
- SIGNATURE - the customer's signature.
Product Details, expected to be shown on every page, expected to show up to 15 products per page. 
Note: This may need to be reduced to account for the digital nature of the signatures on the POD/POC report):
- QTY - The actual quantity of the product delivered, as confirmed by the driver.
- PRODUCT - the product code and full description, as held in the Long Description field in C-ePOD.
- Kg - the unit weight of the product, multiplied by the delivered quantity.
Note: PCS and CBM columns have been agreed to be present but not populated from this table.
A total line will be displayed, summing the Weight column.
Note: The POD report will require a modification to this format to group the delivery items by the product code and sum the values.
Footer, expected to be shown on all pages:
- "SIGN TO ACKNOWLEDGE..." - T&Cs from the completed job, expected to be "SIGN TO ACKNOWLEDGE RECEIPT OF GOODS AND NO DAMAGE TO PROPERTY".
- DATE - The actual end date, when the signature was obtained.
- TIME - The actual end time, when the signature was obtained.
- PRINT NAME - The signatory, captured by the driver on the device when the customer signs.
- SIGNATURE - the customer's signature.
- DIFFICULT DELIVERY DISCLAIMER and all text and signature - Taken from the post-job signature and T&Cs. Note: If they are not present (i.e. they were not captured by the driver) then this section will not be present.
- Failed Delivery checks (2) - taken from user-defined fields against the job. Consisting of:
- REASON FOR FAILURE
- RETURN #
- CARD #
Note: The TIME DOOR COLOR will be removed, as it is expected that this will be made redundant through the process of Job Photos if required.
It has been agreed to keep this format as close as possible to the existing format used by the operation. However, due to the size of digital signatures, some restructuring of the report has taken place in the prototype to accommodate this. These changes must be reviewed and agreed.
Scope
This change will be applied to system version 3.X.
Note: The definition of what data maps to which fields on the POD depends on the mapping of the interface of jobs into .
Set-up
Pre-requisites
Menu Structure
Data
Each Job Group will be configured with one of the POD/POC formats, as follows:
- Job Group "DEL" - Radial Deliveries - POD/POC report formats set to "Oak Furniture Land (Item)".
- Job Group "COL" - Radial Collections - POD/POC report formats set to "Oak Furniture Land (Item)".
- Job Group "LOAD" - Radial Loading - POD/POC report formats set to "Oak Furniture Land (Item)".
- Job Group "UNLOAD" - Trunk Unloading - POD/POC report formats set to "Oak Furniture Land (Product)".
Example:
Job Groups Maintenance
Functional Description
Server
Database/Data Access Layer
The following field will be added to the Job Group table EPOD_JOB_GROUPS:
- ECR_CONTENT_SORT - nvarchar(200), default NULL.
- ECR_CONTENT_GROUP - nvarchar(200), default NULL.
These fields will be added to all stored procedures in the database that require them.
These fields are not required on the device.
These fields are not required to be used when filtering data for selection.
Configurable POD Report Changes
Parameters for the sort are held against the configurable POD format field ECR_CONTENT_SORT, as the detail file affected followed by a colon, plus a comma delimited list:
- Field Name
- Sort Direction (ASC or DESC)
For example:
- EPOD_CONTAINER:EPL_SITE_ID ASC, EPL_CONTAINER_PACKAGE_DESC ASC, EPL_CONTAINER_ID ASC
In this implementation, only one sort item is required:
- EPOD_CONTAINER:EPL_CONTAINER_PACKAGE_DESC ASC
Note: It is recommended that the configuration is extracted using Regex, similarly to the following:
(EPOD.*?)(?:\:)(.*?)(?:\||$)
This pattern will return all configurations matched in the string, grouped by:
- The file affected (EPOD_PRODUCT or EPOD_CONTAINER).
- The fields to sort by.
As this string may in the future contain multiple table sorts, this allows the multiple configurations to be extracted easily in a future
The data will be sorted before reporting (in generateContent) utilising Dynamic Linq. This library (Dynamic.cs) and several additional references must be added to the WebApp project:
- System.Data.Entity
- System.Data.Linq
The Configurable POD (ConfigPOD.aspx.cs) must contain a reference to the dynamic Linq library:
using System.Linq.Dynamic;
The detail lists (dalEPOD_JOB.EPOD_CONTAINERS and/or dalEPOD_JOB.EPOD_PRODUCTS) can then be sorted if required in the Sort string by setting the object to:
obj.AsQueryable().OrderBy(strSort).ToList();
Where obj is the object being sorted and strSort is the sort string in ECR_CONTENT_SORT (minus the detail file affected) .
Note: This sort should be shrouded in a try/catch block, so that errors in the sort do not affect the report being produced.
The Grouping parameters will be specified in ECR_CONTENT_GROUP in the same way as the Sort parameters, but without the indication of ascending or descending.
For example:
- EPOD_CONTAINER:EPL_SITE_ID, EPL_CONTAINER_PACKAGE_DESC, EPL_CONTAINER_ID
In this implementation, only one group item is required:
- EPOD_CONTAINER:EPL_CONTAINER_PACKAGE_DESC ASC
If data is grouped, the process will only report lines when the group has changed. Additionally, certain fields will be calculated:
- EPOD_CONTAINER
- A count of records in the group will be maintained.
- EPL_GROSS_WEIGHT will be summed.
- EPOD_PRODUCT
- A count of records in the group will be maintained.
- EPL_PRODUCT_QTY_PLANNED will be summed
- EPL_PRODUCT_QTY_ACTUAL will be summed
- EPL_PRODUCT_QTY_ORDERED will be summed
- EPL_PRODUCT_WEIGHT will be summed
In the detail reporting loop, if a group has been specified for this table (EPOD_CONTAINER or EPOD_PRODUCT), the group items will be concatenated. The next record will be accessed and the same group items compared. If they are different or there is no next record, report the line.
All summed values will be reset if the group changes.
The function to replace the data tags (replaceDataTags) will be modified to recognise some additional tags:
- GroupCount - as shown above
- SUM keyword (e.g. SUM:EPOD_PRODUCT.EPL_PRODUCT_QTY_PLANNED) - this will access the summed value stored above. If there is no group or summed value, the value from the table will be used instead.
New data objects will be added to the configurable report:
- dalEPOD_SIGNATURE
- dalEPOD_TNCS
These will be loaded from the job in procedure GetDataForReport.
- dalEPOD_SIGNATURE - retrieve this from the Job's EPOD_SIGNATURES collection.
- dalEPOD_TNCS - set this from the dalEPOD_SIGNATURE record's EPOD_TNC record.
See the Waiver report for more details on this.
The data contained in these DAL objects will be accessible when replacing data tags in the procedure replaceDataTags as:
- EPOD_TNCS
- EPOD_SIGNATURE
The existing handling of TNCS through the field name EPL_TNC will be extended to handle these Pre-job TNCs in field TNC_VALUE. It is recommended that the pre- and post-formatting checks in replaceDataTags be changed to check for any field containing the string "TNC" to accommodate this.
Format "Oak Furniture Land (Product)"
The report will be produced through the Configurable POD Report format (ConfigPOD.aspx).
A data report (on EPOD_CONFIG_REPORT) will be created matching the prototype format created.
The format created will have the name "Oak Furniture Land (Product)" (in ECR_NAME) and will added to the list of POD/POC formats that can be selected from the Site and Job Group screens (being added to EPOD_LIST_ITEMS for the drop-down lists on these screens).
Settings for the configurable report are stored on EPOD_CONFIG_REPORT and are as follows:
| Data Field | Value | Description |
|---|---|---|
| ECR_NAME | "Oak Furniture Land (Product)" | The name of the report. |
| ECR_ROWS_FIRST_PAGE | 11 | The number of detail rows on the first page of a multi-page report, including the title line. |
| ECR_MAX_ROWS_SINGLE_PAGE | 11 | The number of detail rows on a single page report, including the title line. |
| ECR_ROWS_CONTENT_PAGE | 10 | The number of detail rows on a (non-first or last) page of a multi-page report. |
| ECR_ROWS_LAST_PAGE | 10 | The number of detail rows on the last page of a multi-page report |
| ECR_ALT_ROWS_IND | 0 | Whether odd and even detail rows are formatted differently (1) or not (0). |
| ECR_INC_CANCELLED_IND | 0 | Whether to include cancelled details lines (containers or products) on the report (1) or not (0). |
| ECR_INC_IMAGES_IND | 1 | Whether to include images on the viewed report (1) or not (0). |
| ECR_EMAIL_IMAGES_IND | 1 | Whether to include images on the PDF report generated for emails (1) or not (0). |
| ECR_INC_PRODUCTS_IND | 1 | Whether Products are included on the report (1) or not (0). |
| ECR_PDF_ORIENT | P | The orientation of the report: (P)ortrait or (L)andscape. |
| ECR_CONTENT_SORT | NULL | The fields on which to sort detail (product or container) records, delimited by commas. |
| ECR_CONTENT_GROUP | NULL | The fields on which to group detail (product or container) records, delimited by commas. |
The format is populated as follows:
The Page Header will be defined in ECR_PAGE_HEADER and will be displayed on every page. The data will be populated as follows:
| Report Item | Data Fields | Description |
|---|---|---|
| Company Logo | EPOD_SITE.EPL_LOGO | The logo taken from the site. |
| "JB DIRECT DELIVERY NOTE" | Fixed text, displaying "JB DIRECT COLLECTION NOTE" if the job is a collection. | |
| "0800 440 2254" | EPOD_SITE_CUSTOMER.EPL_TELEPHONE(0) | Taken from a customer record linked to the site. |
| DATE | EPOD_JOB.EPL_START_PLANNED_DATE | The planned start date, in yyyy-MM-dd format. |
| TIME | EPOD_JOB.EPL_START_PLANNED_TIME EPOD_JOB.EPL_END_PLANNED_TIME |
The planned start and end times, delimited by a dash, in HH:MM format. |
| TYPE | D) | The Job Type |
| ORDER NUM # | EPOD_JOB.EPL_JOB_CODE | The Job Code |
| CUSTOMER/ADDRESS/POSTCODE/TEL1/2 | EPOD_JOB_ADDRESS | Taken from the job address if present, otherwise the customer address. Note: "TEL3" is removed, as only two telephone numbers are supported in the C-ePOD data structure.
|
| "DELIVERY INSTRUCTIONS" | EPOD_JOB.EPL_JOB_INSTRUCTION | Fixed text, displaying "COLLECTION INSTRUCTIONS" if the job is a collection. The job instructions are displayed beneath this. |
| Failed Delivery checks (1) | Taken from user-defined fields against the job. See below for details | |
| DATE | EPOD_JOB.EPL_END_ACTUAL_DATE | The actual end date, when the signature was obtained. |
| TIME | EPOD_JOB.EPL_END_ACTUAL_TIME | The actual end time, when the signature was obtained. |
| PRINT NAME | EPOD_JOB.EPL_CUST_SIGNATORY | The signatory, captured by the driver on the device when the customer signs. |
| SIGNATURE | EPOD_JOB.EPL_JOB_SIGNATURE | The customer's signature. |
The Failed Delivery Checks will be extracted from the UDF as follows. Note: The text of each check will not be extracted from the checks themselves - this will be fixed text in the report. The values will be extracted from the UDF fields as described.
| Report Item | Data Fields |
|---|---|
| Have the delivery team tried to get the item into the room of your choice? | EPOD_JOB.EPL_UDF_JOBDETS.DELIVERY_ATTEMPTED |
| Is it likely that the item will fit unpacked? | EPOD_JOB.EPL_UDF_JOBDETS.FIT_UNPACKED |
| Is there another solution that we can identify? | EPOD_JOB.EPL_UDF_JOBDETS.ALT_SOLUTION |
| Is the customer aware of the reason this cannot be delivered? | EPOD_JOB.EPL_UDF_JOBDETS.CUST_AWARE |
Product details will be shown on every page, showing up to 10 products per page, as defined by the report settings above.
Note: This is reduced to account for the digital nature of the signatures on the POD/POC report).
The reported elements will be taken from EPOD_PRODUCT. Cancelled products (i.e. those with a status of "X" and a quantity of 0) will not be shown in this section.
The detail table header will be defined in ECR_CONTENT_TITLE as shown below.
Each page will fill the detail table to the maximum amount per page, defined by entering an empty row definition in ECR_CONTENT_EMPTY.
Each row will be defined in ECR_CONTENT_ROW as follows:
| Report Item | Data Fields | Description |
|---|---|---|
| QTY | EPOD_PRODUCT.EPL_PRODUCT_QTY_ACTUAL | The actual quantity of the product delivered, as confirmed by the driver. |
| PRODUCT | EPOD_PRODUCT.EPL_DESCRIPTION_LONG | The product code and full description, as held in the Long Description field in C-ePOD. |
| PCS | Not populated. | |
| CBM | Not populated. | |
| Kg | EPOD_PRODUCT.EPL_PRODUCT_WEIGHT * EPOD_PRODUCT.EPL_PRODUCT_QTY_ACTUAL |
The total weight of the delivered product. This is a calculated value (denoted by "CALC:") of the weight multiplied by the delivered quantity. This will be formatted as a 1 decimal place weight, and sub-totalled. |
Note: PCS and CBM columns have been agreed to be present but not populated on this detail table.
A total line will be displayed, displaying the Kg (Weight) subtotal, formatted with no decimal places. This is defined in ECR_CONTENT_TOTAL.
The Page Footer will be defined in ECR_PAGE_FOOTER and will be displayed on every page. The data will be populated as follows:
| Report Item | Data Fields | Description |
|---|---|---|
| "SIGN TO ACKNOWLEDGE..." | EPOD_JOB.EPL_TNCS | T&Cs as signed for by the customer at the point of signing for the job,. These are configured against the Job Group and are expected to be "SIGN TO ACKNOWLEDGE RECEIPT OF GOODS AND NO DAMAGE TO PROPERTY". Note that each job group may have different configured T&Cs. |
| DATE | EPOD_JOB.EPL_END_ACTUAL_DATE | The actual end date, when the signature was obtained. |
| TIME | EPOD_JOB.EPL_END_ACTUAL_TIME | The actual end time, when the signature was obtained. |
| PRINT NAME | EPOD_JOB.EPL_CUST_SIGNATORY | The signatory, captured by the driver on the device when the customer signs. |
| SIGNATURE | EPOD_JOB.EPL_JOB_SIGNATURE | The customer's signature. |
| DIFFICULT DELIVERY DISCLAIMER and all text and signature | EPOD_TNC.TNC_VALUE | Taken from the post-job signature and T&Cs. Note: If they are not present (i.e. they were not captured by the driver) then this section will not be present.
|
| Failed Delivery checks (2) | Taken from user-defined fields against the job. See below for details. |
The Failed Delivery Checks will be extracted from the UDF as follows. Note: The text of each check will not be extracted from the checks themselves - this will be fixed text in the report. The values will be extracted from the UDF fields as described.
| Report Item | Data Fields |
|---|---|
| REASON FOR FAILURE | EPOD_JOB.EPL_UDF_JOBDETS.FAILURE_REASON |
| RETURN # | EPOD_JOB.EPL_UDF_JOBDETS.RETURN_NUM |
| CARD # | EPOD_JOB.EPL_UDF_JOBDETS.CARD_NUM |
Format "Oak Furniture Land (Item)"
Note: The layout of this format will be functionally identical to the report shown above. The differences between the report is in how the detail section is built, as this will be from the Items (EPOD_CONTAINER) grouped by the product id (mapped into EPL_CONTAINER_PACKAGE_DESC).
As such, only the differences are described in this section.
The format created will have the name "Oak Furniture Land (Item)" (in ECR_NAME) and will added to the list of POD/POC formats that can be selected from the Site and Job Group screens (being added to EPOD_LIST_ITEMS for the drop-down lists on these screens).
Settings for the configurable report are stored on EPOD_CONFIG_REPORT and are as follows:
| Data Field | Value | Description |
|---|---|---|
| ECR_NAME | "Oak Furniture Land (Item)" | The name of the report. |
| ECR_ROWS_FIRST_PAGE | 11 | The number of detail rows on the first page of a multi-page report, including the title line. |
| ECR_MAX_ROWS_SINGLE_PAGE | 11 | The number of detail rows on a single page report, including the title line. |
| ECR_ROWS_CONTENT_PAGE | 10 | The number of detail rows on a (non-first or last) page of a multi-page report. |
| ECR_ROWS_LAST_PAGE | 10 | The number of detail rows on the last page of a multi-page report |
| ECR_ALT_ROWS_IND | 0 | Whether odd and even detail rows are formatted differently (1) or not (0). |
| ECR_INC_CANCELLED_IND | 0 | Whether to include cancelled details lines (containers or products) on the report (1) or not (0). |
| ECR_INC_IMAGES_IND | 1 | Whether to include images on the viewed report (1) or not (0). |
| ECR_EMAIL_IMAGES_IND | 1 | Whether to include images on the PDF report generated for emails (1) or not (0). |
| ECR_INC_PRODUCTS_IND | 0 | Whether Products are included on the report (1) or not (0). |
| ECR_PDF_ORIENT | P | The orientation of the report: (P)ortrait or (L)andscape. |
| ECR_CONTENT_SORT | "EPOD_CONTAINER:EPL_CONTAINER_PACKAGE_DESC" | The fields on which to sort detail (product or container) records, delimited by commas. |
| ECR_CONTENT_GROUP | "EPOD_CONTAINER:EPL_CONTAINER_PACKAGE_DESC" | The fields on which to group detail (product or container) records, delimited by commas. |
Item details will be shown on every page, showing up to 10 consolidated lines per page, as defined by the report settings above.
The reported elements will be taken from the Items (EPOD_CONTAINER), grouped from the Product Code data in EPL_CONTAINER_PACKAGE_DESC. Cancelled items (i.e. those with a status of "X") will not be shown in this section.
Each row will be defined in ECR_CONTENT_ROW as follows:
| Report Item | Data Fields | Description |
|---|---|---|
| QTY | GroupCount | Counted from the number of items delivered per product (EPL_CONTAINER_PACKAGE_DESC). |
| PRODUCT | EPOD_CONTAINER.EPL_DESCRIPTION_LONG | The product code and full description, as held in the Long Description field. |
| PCS | Not populated. | |
| CBM | Not populated. | |
| Kg | EPOD_CONTAINER.EPL_GROSS_WEIGHT_WEIGHT * GroupCount |
The total weight of the delivered product. This is a calculated value (denoted by "CALC:") of the item weight multiplied by the delivered quantity. This will be formatted as a 1 decimal place weight, and sub-totalled. |
Note: PCS and CBM columns have been agreed to be present but not populated on this detail table.
A total line will be displayed, displaying the Kg (Weight) subtotal, formatted with no decimal places. This is defined in ECR_CONTENT_TOTAL.
Appendix A: TEST PLAN
| Test Script / Scenario Reference | Oak Furniture Land POD/POC Formats | Call Number(s): 344086 SCR-344060-07 |
| Test Script / Scenario Description | Testing the new POD report formats | PASS / ISSUES / FAIL |
| Menu Access | N/A | |
| Pre-requisites | A system configured as Oak Furniture Land. | Tested By: |
| Test Objective | To test that the two report formats work as expected. | Date: |
| Step | Action | Result | Remarks | P/F |
| 1 | Admin | |||
| 1.01 | Check the Job Groups and Site screens that the new Oak Furniture Land report formats can be set. | The new Oak Furniture Land report formats can be set. |
| Step | Action | Result | Remarks | P/F |
| 2 | POD/POC Reports | |||
| Ensure there is a completed job with Products, on the "UNLOAD" job group, configured to the "Oak Furniture Land (Product)" POD/POC format.
Ensure that there is a completed job with Items, on a different job group, configured to the "Oak Furniture Land (Item)" POD/POC format. Both jobs should have Pre-job Signatures captured for them. |
||||
| 2.01 | Run the report on the job with Products. | The report layout is as expected. The pre-job signature section is displayed correctly. | ||
| 2.02 | Run the report on the job with Items. | The format is as per the Product format. The pre-job signature section is displayed correctly. The reported data grouped by Product. The reported data is in Product sequence. The calculated quantities are correct. |
Appendix B: Quote & Document References
| Cost Details | ||||
| Activity | Estimate No. of Days |
No. of Days | Rate per Day (£) | Cost (£ Exc. VAT) |
| Requirements | 0.00 | 0.00 | 750 | £0.00 |
| Change Request Evaluation | 0.00 | 0.00 | 750 | £0.00 |
| Functional Specification | 1.00 | 1.00 | 750 | £750.00 |
| Technical Specification | 0.00 | 0.00 | 750 | £0.00 |
| Development | 4.00 | 4.00 | 750 | £3,000.00 |
| Testing and Release | 0.50 | 0.50 | 750 | £375.00 |
| Implementation | 0.25 | 0.25 | 750 | £187.50 |
| Project Management | 0.25 | 0.25 | 750 | £187.50 |
| TOTAL | 6.00 | 6.00 | £4,500.00 | |
| Estimate excludes training, release to live and go live support. |
B.1 References
| Ref No | Document Title & ID | Version | Date |
| 1 | REQ 344060 Oak Furniture Land Solution Design | 1.0 | 24/07/2017 |
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
Matt Turner | OBSL Account Manager | _____________________________ |
Louis Merrett | Customer Representative | _____________________________ |
