FS 341168 Palletline Integration
Partnerlink
Palletline Interface
CALIDUS ePOD
13th April 2017 - 0.4
Reference: FS 341168
Contents
Functional Overview
Client Requirement
Andy Ward advises that David Hathaway Transport are joining the Palletline network and require the POD debrief information to be sent from CALIDUS ePOD to Palletline's Contrado system.
Solution Overview
The process will be as follows:
- Palletline-specific reason codes will be set up within CALIDUS ePOD, conforming to the list of delivery codes used by Palletline. These reason codes will be exclusive to the Palletline job group, and the Palletline job group will be configured so that only those codes can be used on Palletline jobs.
- New Palletline Jobs will be sent to Partnerlink through the existing JobShare format, or directly to the back-end TMSs.
- When planned, these jobs will be sent to CALIDUS ePOD through the existing interface in use for the existing partners, and will be identified with a new Job Group e.g. PLINE. The Pallet Depot will be set to the Palletline depot code provided to the carrier, in Hathaway's case, this is "053". The Pallet Consignment will be sent in as the Palletline ID.
Note: This will require change to the sending process from the Partner's TMS system to accommodate these new values for every partner that wishes to join the Palletline network. - The jobs will be completed like any other normal delivery.
- When jobs are completed, the existing AutoExport functionality will update the Partners (identified through their Partner Codes) using a flat-file export of the job details in OBS Logistics' XML format, as it does now.
- The AutoExport process will also export the files to Palletline Contrado system, identified through the Job's Group and the configuration attached to it.
- The AutoExport functionality will store the success or failure of the export, along with any of the returned reasons from the export.
The development required will be as follows:
The mobile device application will be modified to capture the signature in vector format as well as the standard mechanism of an image format. The application will be configured to store this and use it for the Palletline export process.
The Admin Export Configuration screen will be changed to allow Palletline export types to be configured in it.
The Partnerlink Import process will be modified to identify the Palletline job group (e.g. "PLINE"). This will then generate Pallet Ids in the normal way.
The CALIDUS ePOD AutoExport function will be modified to recognise jobs with the Palletline job group and export a file in the Palletline format.
This format will identify the pallets delivered and any reason codes against them, including claused and cancelled pallets. This will also identify the signatory and signature (if delivered), the GPS coordinates (if available) and the status (POD Uploaded for pallets that were delivered, the selected reason code for clauses and cancellations).
The format will be in XML and will be named following the conventions specified by Palletline. A single file will be created per job completed or cancelled.
Note: It is as yet undecided what mechanism will be used to send this file to Palletline. It will however be one of the following:
- Create the file locally to the CALIDUS ePOD server and host an FTP service so that Palletline can pick up the file.
- Create the file and send immediately through FTP to the Palletline FTP server.
The process will support either case as standard.
Scope
Assumptions:
- Jobs will be sent to Partnerlink and through to CALIDUS ePOD using the existing JobShare interfaces, specified in FS 326965 Partnerlink EPOD Interface, referenced in Appendix B.
- Pallets for the Palletline pallet network will be labelled with their own network's pallet labels, and not Partnerlink labels. It is expected that pallet IDs will be created by CALIDUS ePOD in the normal way, identifying the individual pallets by assigning each pallet a unique ID, counting from the 1 to the total number of pallets.
- Palletline's Contrado system will be updated separately to the partner's TMS systems.
- The Consignment will already exist within the Palletline Contrado system, ready for updating with POD information from CALIDUS ePOD.
Warning: If this process requires the files to be sent to an FTP destination, the FTP server, username and password for this remote server must be provided.
Warning: The configuration and the development below assumes that the files created for the Palletline Contrado system will always be placed in the same folder or FTP destination for the depot. If this requires to be identified through different sub-folders for each owning (rather than executing) depot, then this will require additional development.
Changes will be made to latest version of CALIDUS ePOD only, and will require an update to all application components and full system and user acceptance testing.
The reason codes used by Palletline are not currently used in the Partnerlink system, so these will not cause any contention in the system. It is assumed that any conflicts between these reason codes and any generic or partner-specific reason codes of the same type will be resolved by the partners.
Set-up
Pre-requisites
Menu Structure
Data
A new Auto-Export configuration will be created for each partner that requires it, linked to the job group PLINE:
- EPL_XF_CONFIG_ID - As required. For example, for Hathaways, this may be "PLINE_L02".
- EPL_XF_ID - PLINE
- EPL_XF_TYPE - FILE
- EPL_XF_DIRECTION - O
- EPL_XF_DESTINATION - E:\ftpserver\PartnerLink\PALLETLINE_TST\L02\OUT
- EPL_EXPORT_JOB_TYPES - D
Note: This configuration is for the test system and assumes that the folder will be created.
Warning: This configuration above assumes that the files will be placed in the CALIDUS ePOD local file system - in this case, an FTP service, username and password must be provided to Palletline's Contrado system to collect the files from this location only. Alternatively, if this process requires the files to be sent to an FTP destination, the FTP server, username and password for this remote server must be provided.
Warning: Further to this, the configuration and the development below assumes that the files created for the Palletline Contrado system will always be placed in the same folder or FTP destination for the depot. If this requires to be identified through different sub-folders for each owning (rather than executing) depot, then this will require additional development.
A new Job Group "PLINE" will be set up for Palletline jobs. This will initially be a copy of the Palletforce job group "PALLET". The Export Config ID will be set to the Export Configuration created above (e.g. "PLINE_L02").
Palletline Reason Codes will be added for all sites that require the ability to complete Palletline jobs, as follows:
| Code | Description | Type | Note |
|---|---|---|---|
| DELD | Delivered damaged | CLA | |
| DELS | Delivered with Shortages | CLA | |
| DNDS | Delivered DSC Failure | CLA | |
| MPOD | Delivered OK | Normal Successful Delivery | |
| NDNV | Not Del Not On Vehicle DSC | DET | |
| NDOE | Not Del Order Error | DET | |
| NDOT | Not Del Out of Time | JOB | |
| NDPC | Not Del Premise Closed | JOB | |
| NDRD | Not Del Refused Damaged | DET | |
| NDRE | Not Del Refused and Return | DET | |
| NDRS | Not Del Refused Short | JOB | |
| NDTC | Not Del T and Cs refused | JOB |
The interface of job data from the host TMS systems will be modified to identify the Pallet System in use and the Consignment ID. For Palletline jobs, this will be:
- PF Tracking Number - Consignment ID. Note: The Palletline ID is noted to be field 4 in the Palletline manifest download file.
- Tracking System - "PLINE"
Functional Description
Database
Stored Procedure EPOD_SETUP_LISTS will be modified to add PLINE to EPOD_LIST_ITEMS for Export Types (List 38 - EXPORTS_CLIENT_TYPE). Note: This meta-data needs to be added to the system upon implementation by OBSL staff to ensure that Palletline jobs trigger messages to the Palletline Contrado system. No code modifications are required to the trigger to write the control records, as adding this record will then handle that.
The EPOD_JOB table and DAL object will be modified to add the following new fields:
- EPL_JOB_SIGNATURE_SVG - nvarchar(MAX)
- EPL_ENG_SIGNATURE_SVG - nvarchar(MAX)
These fields will be added to all required stored procedures in the database. Note: These fields are not to be added to the fields for searching or selecting data.
These fields will be added to the standard XML export process, if populated. If not, the tags will not be added.
Note: The Export XSDs must be modified to add these tags as optional tags (minOccurs 0).
Note: When this code is included in a build for release to a customer, the standard Export documentation must be created for this version including these new tags.
The EPOD_JOB_GROUPS table and DAL object will be modified to add the following new field:
- EPL_SVG_SIGNATURE_IND - smallint, not null, default 0
This field will be added to all required stored procedures in the database. Note: this field is not to be added to the fields for searching or selecting data.
This field will be added to the XML export, returned when a mobile device logs on to the system.
The indexes on table EPOD_USER_AUDIT must be examined for completeness - an compound index must exist called IX_EPOD_USER_AUDIT_JOB_LOAD_SITE_GPS with the following fields:
- EPL_JOB_ID
- EPL_LOAD_ID
- EPL_SITE_ID
- EPL_GPS_COORD
Admin
Export Configuration Screen
The Admin Export Configuration screen will be changed to allow the parameters for the new Palletline extract to be specified against it.
Export Configuration Maintenance
The ID drop-down list will be modified to allow the new values below when entering a new configuration:
- PLINE, description "Palletline"
Note: The Filename field will be increased in length to match the Destination field.
Note: When complete, the standard interface configuration documentation must be modified to add the new Palletline interface. This is referenced in Appendix B.
Job Group Maintenance
A new Job Group flag EPL_SVG_SIGNATURE_IND will be added to the PDA tab under the Driver Signatures, with values Enabled (Y) or Disabled (N), with label "SVG Signatures", and a popup tooltip (title) of "Capture SVG signature as well as JPG signature on device" added to the input field and label.
Job Group Maintenance
Mobile Device Application
Database/DAL
The EPOD_JOB_GROUP table and DAL object will have field EPL_SVG_SIGNATURE_IND added to them, allowing a numeric value defaulting to 0. The field will be populated upon log-on through the Logon Response message from the server, defaulting to 0.
The EPOD_JOB database table and DAL object will have fields EPL_JOB_SIGNATURE_SVG and EPL_ENG_SIGNATURE_SVG added to them, allowing the maximum text.
The PDA_JOBS DAL object will be modified to have the two fields EPL_JOB_SIGNATURE_SVG and EPL_ENG_SIGNATURE_SVG added to it as properties.
Signature
The signature captured by the device will be 25% shorter by changing the signature height from the base height sigHeight from 235 to 180.
The signature itself will be positioned to the top of the available space rather than the centre.
The captured vector signature will be blue, and 2 pixels wide.
The vector SVG tag will include the height and width as integers, set from the base width and height values (sigHeight and sigWidth).
The vector signature result will be parsed to remove all decimal components and the results passed back to the success callback function p_fncSuccess. The decimal components can be removed using a Regular Expression as follows (example):
vars.vector = vars.vector.replace(/\.\d*?(\,|\s)/g, $1);
Note: The vector SVG signature should still be captured on touching the screen on the device, as this is used to determine whether the user has entered a signature. However, this will only be returned to the function if the new Job Group EPL_SVG_SIGNATURE_IND flag is enabled (set to 1).
The Signature Terms and Conditions view will be increased in size from 40% to 50%, making better use of the available space from the smaller signature.
The utility function to control the sequence of tasks after a job is complete, including signatures (funNextJobEndTask) will be modified to add an extra parameter p_strSVG to the success callback function definition. This will then be used to update all the jobs being completed with the SVG Signature returned. This will be done for both Customer and Driver Signature, updating the appropriate fields on PDA_JOB (respectively EPL_JOB_SIGNATURE_SVG and EPL_ENG_SIGNATURE_SVG).
The Job Update XML will be modified so that the SVG signatures are returned as well as the normal signatures. If there is no data in the SVG signature fields, the tags should not be present. These tags will be added to the CONFIRMATION tag, after the existing elements.
...
<CONFIRMATION>
<EPL_ENG_SIGNATURE></EPL_ENG_SIGNATURE>
<EPL_JOB_SIGNATURE></EPL_JOB_SIGNATURE>
<EPL_JOB_SIGNATURE_SVG></EPL_JOB_SIGNATURE_SVG>
<EPL_ENG_SIGNATURE_SVG></EPL_ENG_SIGNATURE_SVG>
</CONFIRMATION>
...
Note: This device work has been extensively prototyped and is available to be implemented.
Server Job Update Processing
When processing the job update back from the server (in EPODServer.MessageProcess.ProcessJobUpdate), the two new fields EPL_JOB_SIGNATURE_SVG and EPL_ENG_SIGNATURE_SVG will be saved to the EPOD_JOB record, if the tags exist and contain data. Unlike JPEG signatures, these SVG signatures need only be stored directly in the database, not translated and saved to the filesystem.
Note: The SVG signatures can only be populated if normal signatures are also populated, therefore no further work is required to use the SVG signatures in any other modules other than the Palletline export at this time (such as the POD/POC reports).
Auto-Export
The auto-export process retrieves all completed jobs that have not yet been transferred out of the system, and checks whether they require transfer.
Any that do not require transfer will be marked as transferred.
Any that have an applicable export configuration (see the database package modification above) will be passed to a procedure to transfer out according to the type (XF_ID) and parameters set against the configuration.
This function (EPOD_SYS_EXPORT.ExportXFControl) will be modified when checking dalEPOD_XF_CONFIG.EPL_XF_ID, adding another case:
- PLINE - for Palletline exports (see next section) - calls new function EPOD_SYS_EXPORT.GeneratePlineContent, defined below.
The Filename will be defined in the Export Configuration as (EPL_PF_DEPOT)-(DATE)-(TIME)-(UID).xml Where:
- EPL_PF_DEPOT - the PF Depot for the job.
- DATE - date sent in YYYYMMDD format.
- TIME - time sent in HHMMSS format.
- UID - a 2-digit unique sequence for every file sent.
The file will always be "xml" extension.
Note: The EPODServer.EPOD_SYS_EXPORT.translateFilename function will be modified to achieve the above.
Once all the data is created, the function then sends the message in the format specified on the transfer configuration record, as now.
New Palletline Auto-Export Process
A new export process EPOD_SYS_EXPORT.GeneratePlineContent will be created to handle this export.
Full details of the Palletline export format can be found in the Service - DSC Imports specification, referenced in Appendix B.
The file will be created in XML format, as follows:
<Root>
<Job>
<Id> </Id>
<PodDate> </PodDate>
<PodName> </PodName>
<PodTime> </PodTime>
<Signature> </Signature>
<PhotosAvailable> </PhotosAvailable>
<Latitude> </Latitude>
<LatitudeIndicator> </LatitudeIndicator>
<Longitude> </Longitude>
<LongitudeIndicator> </LongitudeIndicator>
<Pallets>
<Item>
<No></No>
<Status>
<Code> </Code>
<Notes> </Notes>
</Status>
</Item>
</Pallets>
</Job>
</Root>
The field are populated as follows:
| Field | EPOD Field | Notes |
|---|---|---|
| Id | EPL_PF_TRACKING_NO | |
| PodDate | EPL_ACTUAL_END_DATE in YYYY-MM-DD format | |
| PodName | EPL_CUST_SIGNATORY | |
| PodTime | EPL_ACTUAL_END_TIME in HH:MM:SS format, zero-filled | |
| Signature | EPL_SVG_CUST_SIGNATORY, formatted | See following for format details. |
| PhotosAvailable | "0", always | |
| Latitude | See following. | |
| LatitudeIndicator | See following. | |
| Longitude | See following. | |
| LongitudeIndicator | See following. | |
| Item | One created per pallet on the job. All fields below come from EPOD_CONTAINER. | |
| No | Last 2 digits of EPL_CONTAINER_ID | |
| Status | EPL_REASON_CODE if populated, else "MPOD" | See following. |
| Notes | EPL_CUST_COMMENTS if populated, else do not include the tag. |
Note: Unless noted otherwise, all fields are obtained from EPOD_JOB.
GPS Coordinates
The GPS co-ordinates will be obtained from the Job Update user audit record for this job.
The GPS coordinates will be added only if present/found - if not, the tags will be omitted. The GPS coordinate is split to latitude and longitude, which are then converted to degrees, minutes and seconds, then sent as DDMM.S format.
For example:
+52° 43' 19.80", -1° 21' 46.21" (52.722166, -1.362835)
52.722166 becomes +52° 43' 19.80". This is converted and stored as 5243.20.
Note: The seconds are stored and displayed as strings with no zero filling.
Therefore:
- if there are 3 seconds, this is shown as ".3", not ".03".
- if there are 20 seconds, this is shown as ".20", not ".2".
For conversion of GPS coordinates to custom format:
- See P:\EPOD\Support\Utils\Web Tools\JSTest\jstest.html, function convertGPS for details.
Signature
The signature is sent in a bespoke format, converted from SVG into HEX couplets.
At the same time, the signature is also re-sized to the required size.
See P:\EPOD\Support\Utils\Web Tools\SVGHEXConvert\SVGHEXConvert.html for details and capability of testing results.
Note: This is coded in JavaScript to show capability and test the results - it is not expected that this test/prototype code will be used within the export process. Note that the code uses basic string manipulation and conversion routines as follows:
• RegExp replaces
• String.split
• parseInt
• toString and toSting(16)
The conversion from HEX back to SVG in this test page is required only to aid testing, and is not required in the server or export processes.
Status Codes
- For claused pallets - The reason code entered will be included in the Code tag at all times if present. If this is not present, use the code MPOD. The user comment (if present) will be included in the Notes tag.
- For cancelled jobs - The Job reason code will be identified against all pallets on the job, in the Code field. No comment is required.
- For delivered jobs - Any pallets with a reason code against them will have the reason code entered in the Code tag. For pallets that have been successfully delivered, use the code MPOD.
Note: Should the job be cancelled through the Admin system rather than from a device, each pallet on the job should be sent in this message with the code NDNV.
Sample file
<Root>
<Job>
<Id>CON12345678</Id>
<PodDate>2017-03-09</PodDate>
<PodName>Jack Jones</PodName>
<PodTime>08:33:00</PodTime>
<Signature>...</Signature>
<PhotosAvailable>0</PhotosAvailable>
<Latitude>5243.20</Latitude>
<LatitudeIndicator>N</LatitudeIndicator>
<Longitude>00121.46</Longitude>
<LongitudeIndicator>W</LongitudeIndicator>
<Pallets>
<Item>
<No>01</No>
<Status>
<Code>MPOD</Code>
</Status>
</Item>
<Item>
<No>02</No>
<Status>
<Code>MPOD</Code>
</Status>
</Item>
<Item>
<No>03</No>
<Status>
<Code>NDRC</Code>
</Status>
</Item>
<Item>
<No>04</No>
<Status>
<Code>DELD</Code>
<Notes>Some Clause Notes</Notes>
</Status>
</Item>
</Pallets>
</Job>
</Root>
See sample files created for Palletline:
- P:\EPOD\Estimates & Specifications\Estimates\341168 - PalletLine Interface\053-20170309-083300-01.xml
- P:\EPOD\Estimates & Specifications\Estimates\341168 - PalletLine Interface\053-20170309-083300-02.xml
Appendix A: TEST PLAN
| Test Script / Scenario Reference | Palletline Interface | Call Number(s): 341168 |
| Test Script / Scenario Description | Testing Exports out to the Palletline Contrado system from CALIDUS ePOD. | PASS / ISSUES / FAIL |
| Menu Access | Administration/Auto-Export | |
| Pre-requisites | A system configured as Partnerlink Test. | Tested By: |
| Test Objective | To test: Auto-Export may be configured for Palletline exports and; Auto-Export exports the jobs correctly to the Palletline Contrado system. | Date: |
| Step | Action | Result | Remarks | P/F |
| 1 | Admin System | |||
| 1.01 | Ensure that Palletline exports may be configured through the Auto-Export screen. | New Export Configurations may be created of ID PLINE, allowing selection of this through the drop-down grid and displayed on the table on the screen. | ||
| 1.02 | Ensure that the new SVG Signatures flag can be set and amended on new and existing job groups. | New Job Groups may be created with the SVG Signature flag set or unset and saved with no issues. Job groups may be edited and the SVG Signatures flag set and unset and saved with no issues. |
| Step | Action | Result | Remarks | P/F |
| 2 | SVG Signature Capture | |||
| Ensure that there are 3 jobs, 2 with a job group that requires the SVG signature, and one that does not. Each should have multiple containers.
Ensure that the job requires driver and customer signature. Ensure the correct reason codes are entered for the Job Group requiring SVG signatures, and that they are the only ones that can be used for that job. |
||||
| 2.01 | Cancel one SVG job.
Complete both of the other jobs, cancelling some containers with different reasons. Clause some delivered containers. Check the database. |
The jobs requiring SVG signatures have a value in both SVG signature fields. The SVG signatures are valid, and have width and height attributes with integer values only (check the data, and load the SVG into a website to check formatting - one has been created in WebTools for this purpose). The job not requiring SVG signatures has both fields blank. |
| Step | Action | Result | Remarks | P/F |
| 3 | Auto-Export | |||
| Ensure that the Job Group with SVG signatures required is linked to an XF Configuration for type PLINE. Ensure that this configuration points to a file location. Ensure that the other job's job group is not linked to this XF Configuration. | ||||
| 3.01 | Check the export directory. | The Job requiring export that was completed has a Palletline export file created for it, with the correct name. The file is populated correctly. The GPS is set correctly. The Signature is converted correctly (use the WebTools page for checking this). The containers are included in the file - all normally delivered containers are marked with status MPOD, while all others have the correct reasons. The claused container has the correct clause code and notes against it.
The Job requiring export that was cancelled has a Palletline export file created for it, with the correct name and contents as before, but all pallets are cancelled with correct job cancellation reason code, and no signature is included. The job not requiring export has no file. |
||
| 3.02 | Create a new job requiring export to Palletline with multiple containers and cancel using the Admin console. | The Job has a Palletline export file created for it, with the correct name. The file is populated correctly. The GPS is not present. There is no signature. The containers are included in the file, marked with status NDNV. |
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 | 650 | £0.00 |
| Change Request Evaluation | 1.50 | 1.50 | 650 | £975.00 |
| Functional Specification | 1.50 | 1.50 | 650 | £975.00 |
| Technical Specification | 0.00 | 0.00 | 650 | £0.00 |
| Development | 7.00 | 7.00 | 650 | £4,550.00 |
| Testing and Release | 1.25 | 1.25 | 650 | £812.50 |
| Implementation | 0.25 | 0.25 | 650 | £162.50 |
| Project Management | 0.50 | 0.50 | 650 | £325.00 |
| Hathaways Discount 50% | ||||
| TOTAL | 12.00 | 12.00 | £3,900.00 | |
| Estimate excludes training, release to live and go live support. |
B.1 References
| Ref No | Document Title & ID | Version | Date |
| 1 | EST 341168 PalletLine Integration | 1.0 | 30/03/2015 |
| 2 | FS 326965 Partnerlink EPOD Interface | 1.0 | 22/05/2015 |
| 3 | Service - DSC Imports.docx | 0.17 | 16/07/2010 |
| 4 | Standard Interface Configuration (CALIDUS Assist) |
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 | _____________________________ |
Andy Ward | Customer Representative | _____________________________ |
