OV 287575 C-ePOD CSV Upload User Guide

Aptean Proof of Delivery - Calidus Edition

C-ePOD CSV Upload

CALIDUS ePOD

9th April 2019 - 4.0.02.49
Reference: OV 287575

Functional Overview

Solution

The inbound CSV Import process is designed to allow Customers to import Orders and Loads.

There are two methods of uploading the CSV file:

1. Manual Upload: Upload the file through the Upload screen in the C-ePOD Application and browse to file in a local folder.
2. Automatic Upload: Copy the file into a specified folder from which configuration setup allows automatic upload.

This document covers many Job and Load CSV upload scenarios, including examples.

Functional Description

Inbound CSV Upload

The inbound CSV Upload file format is specified below and can be created with the following fields:

Col No.	Sheet Col.	Field	Type	Max Length	Example	Required
1	A	Load Delivery Date	Date	8 digits (DDMMYYYY)	10072014	N
2	B	Load Delivery Time	Time	4 digits (HHMM)	1001	N
3	C	End Delivery Date	Date	8 digits (DDMMYYYY)	10072014	N
4	D	End Delivery Time	Time	4 digits (HHMM)	1759	N
5	E	User Id	Character	10	JSmith	N
6	F	Job Code	Character	20	JOBCODE1	Y
7	G	Cust Ref	Character	30	CR1	Y
8	H	Job Type	Character	2	D	Y
9	I	Job Instruction	Character	255	Leave Next Door	N
10	J	Job Delivery Date	Date	8 digits (DDMMYYYY)	10072014	Y
11	K	Job Delivery Time	Time	4 digits (HHMM)	1001	Y
12	L	Job End Delivery Date	Date	8 digits (DDMMYYYY)	10072014	Y
13	M	Job End Delivery Time	Time	4 digits (HHMM)	1031	Y
14	N	Customer Name	Character	50	J Bloggs	Y
15	O	Address 1	Character	40	1 High Street	Y
16	P	Address 2	Character	40	Trumpton	N
17	Q	Address 3	Character	40	Lincoln	N
18	R	Address 4	Character	40	Lincolnshire	N
19	S	Address 5	Character	40		N
20	T	Postcode	Character	8	L23 4hg	Y
21	U	Contact	Character	40	J Bloggs	N
22	V	Telephone	Character	50	01234 34567	N
23	W	Email	Character	255	[email protected]	N
24	X	Container Id	Character	30	C1	Y
25	Y	Product Id	Character	40	P1	N
26	Z	Product Description	Character	40	Prod 1	N
27	AA	Long Prod Description	Character	255	Long Prod 1	N
28	AB	Planned Qty	Character	Max value: 2147483647	1	N
29	AC	Site	Character	10	Site1	Y
30	AD	Job Group	Character	10	Group1	Y
31	AE	Load Id	Character	20	LOAD1	N
32	AF	Cust Code	Character	20	CUST CODE1	N
33	AG	Vehicle Id	Character	10	VEH1	N

Files can be created using Excel or another spreadsheet and saved as CSV format.

For example a simple 1 line upload could be created as follows:

,,,,,JOBCODE1,CR1,D,,10072014,901,10072014,901,J Bloggs,,,,,,,,,,C1,,,,,TEST,TEST,,

Example 1: Single Job, Single Container.

These examples can be pasted into Notepad and then 'saved as' a .csv file by specifying a .csv extension (e.g. example1.csv).

Uploading this would create a Job with 'JOBCODE1' for customer 'J Bloggs' with a single container record. Note: this assumes that the customer 'J Bloggs' already exists on the C-ePOD database.

An example of an upload for a customer that does not exist would be as follows:

,,,,,JOBCODE2,CR2,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C1,,,,,TEST,TEST,,

Example 2: Single Container, Customer Information.

This could be saved as example2.csv.

Upload via Import screen

The import screen lists the following options:

Figure 1: Data Upload - Import screen.

• Job Group: Sets the Job Group the jobs are to be created for. Job Group is a method of separating Jobs for the same site.
• Upload Type: Should be set to 'OBS [CSV]'.
• Header Row: Should be set to 'Yes' if the first row of the CSV file contains a header row. The above examples do not so for these this value should be set to 'No'.

Once these values are set, click the 'Choose File' button and browse to the relevant folder for the file (e.g. example2.csv). Once it has been found, click 'Upload' to upload the data onto C-ePOD.

Validating Upload

The upload screen will list the result of the CSV upload, showing the upload information in the additional 'Validation' and 'Reason for Failure' columns:

Figure 2: Data Upload - Validation.

In this case, there was only one record to upload and the Validation field indicates the record was successfully uploaded.

The details of the Job created can be checked by entering the job details screen and searching for the Job Code.

Figure 3: Job Details - Uploaded job, load number defaulted.

Note: A 'Load Id' was not specified in the upload file. In this case, the system has generated a load number 'YTE-0010844O'.

Automatic Upload

It is possible to setup an inbound polling process that will automatically upload files from a certain folder. In the "Export Config" screen within C-ePOD, a record should be defined as follows:

Figure 4: Export Configuration - Configuration Details.

• Config ID: Inbound CSV
• Description: Inbound CSV Upload
• Type: FILE
• ID: JOB
• Destination: Upload Folder (C:\Inbound in this example)
• Direction: I (Inbound)

This indicates that files copied to the 'C:\Inbound' folder on the server which C-ePOD is running will be uploaded automatically into C-ePOD. Note: Some additional configuration is required to schedule an inbound polling job which picks up this configuration.

Validating Upload

For example, if we copy the example1.csv file mentioned above into the C:\Inbound folder.

Enter the 'Export Audit' screen and search for records created on the current date:

Figure 5: Export Audit - Verify upload.

In this example, the description field indicates that the record could not be uploaded. This is due to the fact that for the data listed above for example1 does not contain customer address details.

In order to fix this error, the Customer should be manually created within the C-ePOD system. Enter the Customer Maintenance screen and create a new record for 'J Bloggs':

Figure 6: Customer Maintenance - New Customer.

The previous example1.csv file can now be re-uploaded. In order to do this, the file should be moved from the error folder (e.g. C:\Inbound\errors\) back into the upload folder. Once the file has re-processed, check the 'Export Audit' screen again:

Figure 7: Export Audit - Verify upload.

The Description field of 'Uploaded' on the last line indicates that the job was successfully created.

Import File Examples

Including a Header Record

An extra line can be included at the front of the file to include Header Text for each column. This makes it much easier to create records within Excel or another spreadsheet tool. For this example (example3.csv) we will create a new upload file with a header row and two new job codes:

EPL_LOAD_START_PLANNED_DATE, EPL_LOAD_START_PLANNED_TIME, EPL_LOAD_END_PLANNED_DATE, EPL_LOAD_END_PLANNED_TIME, EPL_USER_ID, EPL_JOB_CODE, EPL_CUST_REF, EPL_JOB_TYPE, EPL_JOB_INSTRUCTION, EPL_START_PLANNED_DATE, EPL_START_PLANNED_TIME, EPL_END_PLANNED_DATE, EPL_END_PLANNED_TIME, EPL_CUSTOMER_NAME, EPL_ADDRESS_1, EPL_ADDRESS_2, EPL_ADDRESS_3, EPL_ADDRESS_4, EPL_ADDRESS_5, EPL_POSTCODE, EPL_CONTACT, EPL_TELEPHONE, EPL_EMAIL, EPL_CONTAINER_ID, EPL_PRODUCT, EPL_DESCRIPTION, EPL_DESCRIPTION_LONG, EPL_PLANNED_QTY, EPL_SITE_ID, EPL_JOB_GROUP, EPL_LOAD_ID, EPL_CUST_CODE

,,,,,JOBCODE3,CR3,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C3,,,,,TEST,TEST,,

,,,,,JOBCODE4,CR4,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C4,,,,,TEST,TEST,,

Example 3: Two Jobs for Single Containers with Customer Information and Header Row.

Browse to this file from the upload screen, this time setting the 'Header Row' as 'Yes':

Figure 8: Data Upload - Import screen.

Uploading this file using the manual upload screen gives the following result:

Figure 9: Data Upload - Validation.

This shows both records were successfully created.

Creating New Products

For the jobs created so far, product details have not been included. If we examine the Job with Job Code 'JOBCODE1', we can see that a job was created with a container with Id 'C1' but the container does not have any corresponding products:

Figure 10: Data Upload - Import screen.

If more details are required to be stored against each job, then it is possible to create product records for each container. This example will create a job with one container and 2 product codes:

,,,,,JOBCODE5,CR5,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C5_1,PROD1,PRODDESC1,LONGDESC1,1,TEST,TEST,,

,,,,,JOBCODE5,CR5,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C5_1,PROD2,PRODDESC2,LONGDESC2,2,TEST,TEST,,

Example 4: Single Job, Single Container, Multiple Products.

Figure 11: Data Upload - Validation.

Check the Job created in the 'Job Details' screen:

Figure 12: Job Details - Uploaded Job.

Figure 13: Job Details - Container and Product Details.

This shows the two product records that were created.

Assigning to Users

For the examples we have created so far, the User Id has not been specified. This means the Loads created have been set at status 'Pending'. In reality, the load may already have been assigned a driver for delivery. This example shows a Job that has a user 'User1' already assigned to it:

,,,,User1,JOBCODE6,CR6,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C6,,,,,TEST,TEST,,

Example 5: Single Job, Single Container, assigned to user.

Uploading the file and check the load created:

Figure 14: Data Upload - Validation.

This time the load has been created with status 'Assigned' with the user set as 'User1':

Figure 15: Loads Screen - User assigned.

Note: The site parameter 'Import Create Standing Data' has to be checked in order for new users to be created:

Figure 16: Site Maintenance - Import Create Standing Data.

Multiple Loads

For the example so far, a single load has been created for each upload. If two different usernames are included, then two separate Load Ids will be generated:

,,,,User1,JOBCODE7,CR7,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C7,,,,,TEST,TEST,,

,,,,User2,JOBCODE8,CR8,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C8,,,,,TEST,TEST,,

Example 6: Two Jobs, Two Users, two separate Loads.

Uploading this file results in two separate loads being created:

Figure 17: Data Upload - Validation.

Figure 18: Job Details - Load One.

Figure 19: Job Details - Load Two.

Modifying and Re-uploading loads

If a job is at status 'Pending' or 'Assigned' then it is possible to modify the details of that job by uploading another file.

For example the Customer Reference field could be modified for 'JOBCODE8' from the original 'CR8' to 'CR8v2' using the data below:

,,,,User2,JOBCODE8,CR8v2,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C8,,,,,TEST,TEST,,

Example 7: Single Job, Updating Customer Reference information.

Figure 20: Data Upload - Validation.

Figure 21: Job Details - Updated Information.

Moving Jobs to a new load

It is possible to swap a Job from one Load to another Load by changing the user and load data. In order to do this, we have to specify the load id the jobs will belong to.

Firstly, we create a new load with three jobs by assigning them all to the same load 'LOAD1':

,,,,User1,JOBCODE9,CR9,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C9,,,,,TEST,TEST,Load1,

,,,,User1,JOBCODE10,CR10,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C10,,,,,TEST,TEST,Load1,

,,,,User1,JOBCODE11,CR11,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C11,,,,,TEST,TEST,Load1,

Example 8: Three Jobs, Single Load.

Figure 22: Data Upload - Validation.

Figure 23: Job Details - Load One.

Then if we change the load of one of the records and re-upload it, the record will be moved to a different load:

,,,,User2,JOBCODE10,CR10,D,,10072014,901,10072014,901,J Smith,1 High Street,Wimbledon,,,London,W12 8TZ,Mr J Smith,01234,[email protected],C10,,,,,TEST,TEST,Load2,

Example 9: Existing Job, New User, New Load.

Figure 24: Data Upload - Validation.

Job code 'JOBCODE10' is now assigned to 'Load2':

Figure 25: Job Details - Load2.

'Load 1' now consists of only two jobs:

Figure 26: Job Details - Load1.

Note that a job that is added to an existing load will be added with the highest sequence number, so that job will be the last job to be processed on the device.

Sequencing Jobs on a new load

If multiple jobs are added to a load, the sequence the jobs appear on the device can be determined by setting a Site level parameter.

Within the Site Maintenance screen, in the Admin tab there is a 'CSV Upload Sequence' list:

Figure 27: Site Maintenance - CSV Upload Sequence setting.

The value 'Default' will cause the jobs to be added in the order of the Load Planned Start Date/Time passed in, e.g. columns 1 and 2.

The value 'Order Provided' will cause the jobs to be added in the sequence they are included in the file.

Appendix A: Document References

A.1 References

Ref No	Document Title & ID	Version	Date
1	UG 291094 EPOD Admin User Guide	4.5.00.02	09/04/2019
2	UG 291097 EPOD Client User Guide	4.0.2.21	02/04/2019

A.2 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.

A.3 Authorised By

Murray Middleton	OBS Project Manager	_____________________________
Tony Walker	OBS Consultant	_____________________________