FS 342131 EPOD HazChem Integration
Partnerlink
HazChem Interface
CALIDUS ePOD
18th December 2017 - 1.0
Reference: FS 342131
Contents
Functional Overview
Client Requirement
Colin Vickers advises that Knights of Old transport are completing work for the HazChem network and require the POD debrief information to be sent from Calidus ePOD to HazChem's HazchemOnline system.
Solution Overview
The process will be as follows:
- Hazchem-specific reason codes will be set up within CALIDUS ePOD, conforming to the list of delivery codes used by Hazchem. These reason codes will be exclusive to the Hazchem job group, and the Hazchem job group will be configured so that only those codes can be used on Hazchem jobs.
- New Hazchem 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. HAZCHEM. The Pallet Depot will be set to the Hazchem depot code provided to the carrier, to be advised by the customer. The Pallet Consignment will be sent in as the Hazchem ID.
Note: This will require change to the sending process from the TMS system to accommodate these new values for every partner that wishes to join the Hazchem 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 Hazchem HazchemOnline 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 Hazchem export process.
The Admin Export Configuration screen will be changed to allow Hazchem export types to be configured in it.
The Partnerlink Import process will be modified to identify the Hazchem job group (e.g. "HAZCHEM"). This will then generate Pallet Ids in the normal way.
The CALIDUS ePOD AutoExport function will be modified to recognise jobs with the Hazchem job group and export a file in the Hazchem 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 (EDC - Item Delivered - 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 Hazchem. 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 Hazchem. It will however be one of the following:
- Create the file locally to the CALIDUS ePOD server and host an FTP service so that Hazchem can pick up the file.
- Create the file and send immediately through FTP to the Hazchem 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.
- Pallets for the Hazchem 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.
- Hazchem's HazchemOnline system will be updated separately to the partner's TMS systems. This will require a modification to identify consignments as being completed for Hazchem.
- The Consignment will already exist within the Hazchem HazchemOnline 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 HazChemOnline 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.
Note: This development is dependent on the HazChem interface developed within CALIDUS ePOD - see FS 341168 Palletline Integration for details. This change applies only to Customer and Driver signatures at Job Confirmation. Other signatures (for example, Vehicle Checks, Pre-job signatures, etc) are not affected and will not produce SVG signatures. They are not required for this functionality.
It is assumed that modifications to this HazChem interface since this was developed are equally valid to the HazChem system, and this specification will note those differences. These are specified here: FS 344795 Palletline Integration Arrival Info.
Warning: The reason codes used by HazChem are currently used in the Partnerlink system for partner site L26 (Nelson Distribution) only. Any partner other that L26 may set up and use the required HazChem reason codes and complete HazChem jobs with no issues. Due to restrictions on the uniqueness of reason codes in the system, partner L26 may not be able to be set up to complete HazChem jobs - the reason codes would require removing from Job Group L26 and either make them general reason codes or specific to the HAZCHEM job group.
It is not assumed that this will cause a problem to the partners, and no development costs are included in this specification for resolution to problems relating to this. If this is required to be included, this change must be respecified with this work included.
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 HAZCHEM:
- EPL_XF_CONFIG_ID - As required. For example, for Knights of Old, this may be "HAZCHEM_L03".
- EPL_XF_ID - HAZCHEM
- EPL_XF_TYPE - FILE
- EPL_XF_DIRECTION - O
- EPL_XF_DESTINATION - E:\ftpserver\PartnerLink\HAZCHEM_TST\L03\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 the HazChemOnline 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 HazChemOnline 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 "HAZCHEM" will be set up for HazChem 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. "HAZCHEM_L03").
HazChem Reason Codes will be added for all sites that require the ability to complete HazChem jobs, as follows:
| Code | Description | Type | Note |
|---|---|---|---|
| EDC | Item Delivered | Normal item delivered status | |
| RFS | Refused | JOB, DET | |
| RTS | Return to Sender | JOB | |
| CRD | Carded | JOB | |
| CLS | Closed | JOB | |
| DAC | Damaged at Consignee | CLA | |
| UNC | Un-Checked | CLA | |
| OTH | Other | JOB |
Warning: This will not be possible for existing partner L26, as this partner already uses these reason codes.
Due to restrictions on the uniqueness of reason codes in the system, partner L26 may not be able to be set up to complete HazChem jobs - the reason codes would require removing from Job Group L26 and either make them general reason codes or specific to the HAZCHEM job group.
It is not assumed that this will cause a problem to the partners, and no development costs are included in this specification for resolution to problems relating to this. If this is required to be included, this change must be respecified with this work included.
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 HazChem jobs, this will be:
- PF Tracking Number - the unique HazChem consignment ID, TrackingCode
- Tracking System - "HAZCHEM"
Functional Description
Database & Data Access Layer
Stored Procedure EPOD_SETUP_LISTS will be modified to add HAZCHEM 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 HazChem jobs trigger messages to the HazChem HazChemOnline system. No code modifications are required to the trigger to write the control records, as adding this record will then handle that.
The existing trigger TRG_EPOD_USER_AUDIT_INSERT on table EPOD_USER_AUDIT will be modified for this change. This will trigger writing of a control record for the HAZCHEM interface for action "ARRI", if the job is arrived via the mobile device, and the job arrived is configured for the HAZCHEM interface.
The value of EPL_MESSAGE TYPE will be checked. If this is set to "JOB_ARRIVAL", the process will check to see if the job associated to the job arrival (on EPOD_JOB, through EPL_SITE_ID and EPL_JOB_ID on EPOD_USER_AUDIT) has a "HAZCHEM" Export configuration. This will be checked by retrieving all EPOD_XF_CONFIG records associated to the Site (EPOD_SITE) and Job Group (EPOD_JOB_GROUP) of the job.
Note: As this is an extension to the existing Palletline functionality and the SQL should include both the PLINE and HAZCHEM Config IDs.
If a "PLINE" or "HAZCHEM" export configuration is found, write an EPOD_XF_CONTROL record with EXC_TYPE "PLINE" or "HAZCHEM", EXC_ACTION "ARRI".
The record created will be populated as follows:
C.EPL_XF_CONFIG_ID AS EXC_EXTERNAL_SYSTEM,
'ARRI' AS EXC_ACTION,
'P' AS EXC_STATUS,
C.EPL_XF_ID AS EXC_TYPE,
I.EPL_SITE_ID AS EXC_SITE_ID,
I.EPL_LOAD_ID AS EXC_LOAD_ID,
I.EPL_JOB_ID AS EXC_JOB_ID,
I.EPL_JOB_TYPE AS EXC_JOB_TYPE,
I.EPL_CUSTOMER_CODE AS EXC_CUSTOMER_CODE,
AS EXC_VEHICLE_ID,
@iCurrentDate AS EXC_CREATED_DATE,
@iCurrentTime AS EXC_CREATED_TIME,
C.EPL_XF_CONFIG_UID AS EXC_XF_CONFIG_UID
Note: The process should only insert a record to EPOD_XF_CONTROL if there is not a record already on the table for this Site, Job Type, Config UID with action 'ARRI' at Status "P", e.g.
NOT EXISTS (SELECT 1 FROM dbo.EPOD_XF_CONTROL WHERE EXC_SITE_ID = I.EPL_SITE_ID AND EXC_JOB_ID = I.EPL_JOB_ID AND EXC_JOB_TYPE = I.EPL_JOB_TYPE AND EXC_ACTION = 'ARRI' AND EXC_XF_CONFIG_UID = c.EPL_XF_CONFIG_UID AND EXC_STATUS = 'P')
SQL examples can be taken from the existing trigger TRG_EPOD_JOB_UPDATE, as in the following example:
SELECT C.EPL_XF_CONFIG_ID AS EXC_EXTERNAL_SYSTEM, 'ARRI' AS EXC_ACTION, 'P' AS EXC_STATUS, C.EPL_XF_ID AS EXC_TYPE,
I.EPL_SITE_ID AS EXC_SITE_ID, I.EPL_LOAD_ID AS EXC_LOAD_ID, I.EPL_JOB_ID AS EXC_JOB_ID, I.EPL_JOB_TYPE AS EXC_JOB_TYPE,
I.EPL_CUSTOMER_CODE AS EXC_CUSTOMER_CODE, el.EPL_VEHICLE_ID AS EXC_VEHICLE_ID, @iCurrentDate AS EXC_CREATED_DATE, @iCurrentTime AS EXC_CREATED_TIME,
C.EPL_XF_CONFIG_UID AS EXC_XF_CONFIG_UID
FROM Inserted I
INNER JOIN dbo.EPOD_JOB_GROUPS jg ON jg.EPL_SITE_ID = I.EPL_SITE_ID AND jg.EPL_JOB_GROUP = I.EPL_JOB_GROUP
INNER JOIN dbo.EPOD_LOAD el ON el.EPL_SITE_ID=I.EPL_SITE_ID and EL.EPL_LOAD_ID = I.EPL_LOAD_ID
INNER JOIN dbo.EPOD_XF_CONFIG C ON C.EPL_XF_CONFIG_ID = jg.EPL_XF_CONFIG
AND CHARINDEX(I.EPL_JOB_TYPE, C.EPL_EXPORT_JOB_TYPES) > 0
AND C.EPL_XF_DIRECTION = 'O'
AND C.EPL_XF_ID IN ('PLINE', 'HAZCHEM')
INNER JOIN #tmpTblEpodList AS tblEpodList ON C.EPL_XF_ID = tblEpodList.VALUE
WHERE (c.EPL_XF_ID NOT IN (SELECT XF_ID FROM @tblExportOnlyComplete) OR (c.EPL_XF_ID IN (SELECT XF_ID FROM @tblExportOnlyComplete) AND i.EPL_STATUS = 'C'))
AND NOT EXISTS (SELECT 1 FROM dbo.EPOD_XF_CONTROL WHERE EXC_SITE_ID = I.EPL_SITE_ID AND EXC_JOB_ID = I.EPL_JOB_ID AND EXC_JOB_TYPE = I.EPL_JOB_TYPE AND EXC_ACTION = 'ARRI'
AND EXC_XF_CONFIG_UID = c.EPL_XF_CONFIG_UID AND EXC_STATUS = 'P')
The existing trigger TRG_EPOD_JOB_UPDATE on table EPOD_JOB will be modified to check against the action when looking for XF Control records that already exist. This occurs multiple times in the package, and should be changed as in the following example
AND NOT EXISTS (SELECT 1 FROM dbo.EPOD_XF_CONTROL WHERE EXC_SITE_ID = I.EPL_SITE_ID AND EXC_JOB_ID = I.EPL_JOB_ID AND EXC_JOB_TYPE = I.EPL_JOB_TYPE AND EXC_ACTION = 'A'
AND EXC_XF_CONFIG_UID = c.EPL_XF_CONFIG_UID AND EXC_STATUS = 'P')
Admin
Export Configuration Screen
The Admin Export Configuration screen will be changed to allow the parameters for the new HazChem 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:
- HAZCHEM, description "HazChem"
Note: When complete, the standard interface configuration documentation must be modified to add the new HazChem interface. This is referenced in Appendix B.
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:
- HAZCHEM - for HazChem exports (see next section) - This will call the existing function EPOD_SYS_EXPORT.GeneratePlineContent.
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.
Once all the data is created, the function then sends the message in the format specified on the transfer configuration record, as now.
PalletLine Auto-Export Process
The existing PalletLine Auto-Export process will:
- Send Arrival messages (type "ARRI")
- Send POD messages (type "EDC")
This process will be changed to recognise that this is a HazChem export and not a Palletline export (by the Config Id) and will make the following changes to the export format:
- write EDC reasons instead of EPOD reasons for successfully delivered pallets
- write ARR reasons instead of ARRI for arrived pallets
- substitute TrackingCode instead of Id for the tracking ID tag name.
The full definition of these processes as they were written can be found in the following specifications:
Appendix A: TEST PLAN
| Test Script / Scenario Reference | HazChem Interface | Call Number(s): 342131 |
| Test Script / Scenario Description | Testing Exports out to the HazChem HazChemOnline 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 HazChem exports and; Auto-Export exports the jobs correctly to the HazChem HazChemOnline system. | Date: |
| Step | Action | Result | Remarks | P/F |
| 1 | Admin System | |||
| 1.01 | Ensure that HazChem exports may be configured through the Auto-Export screen. | New Export Configurations may be created of ID HAZCHEM, allowing selection of this through the drop-down grid and displayed on the table on the screen. |
| Step | Action | Result | Remarks | P/F |
| 2 | Auto-Export | |||
| 2.01 | Cancel a job. Complete a job up to arrival. Check the export directory. | The Job has a Palletline export files created for it, with the correct name. The file is populated correctly. The GPS is set correctly. The Signature and PodName is present but has no value. The containers are included in the file - all arrived containers are marked with status ARR. 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. | ||
| 2.02 | Complete this job, cancelling and clausing some containers. Cancel another job completely. Check the export directory. | The Job has a Palletline export files created for it, with the correct name. The file is populated correctly. The GPS is set correctly. The Signature is converted correctly. The PodName is populated correctly. The containers are included in the file - all normally delivered containers are marked with status EDC, while all others have the correct reasons. The claused container has the correct clause code and notes against it. |
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 | 0.25 | 0.25 | 650 | £162.50 |
| Functional Specification | 1.25 | 1.25 | 650 | £812.50 |
| Technical Specification | 0.00 | 0.00 | 650 | £0.00 |
| Development | 5.00 | 5.00 | 650 | £3,250.00 |
| Testing and Release | 1.00 | 1.00 | 650 | £650.00 |
| Implementation | 0.25 | 0.25 | 650 | £162.50 |
| Project Management | 0.50 | 0.50 | 650 | £325.00 |
| TOTAL | 8.25 | 8.25 | £5,362.50 | |
| Estimate excludes training, release to live and go live support. |
B.1 References
| Ref No | Document Title & ID | Version | Date |
| 1 | EST 342131 EPOD HazChem Integration | 0.1 | 09/03/2017 |
| 2 | FS 326965 Partnerlink EPOD Interface | 1.0 | 22/05/2015 |
| 3 | EDC File Spec.pdf | 4 | 01/01/2016 |
| 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 | _____________________________ |
Colin Vickers | Customer Representative | _____________________________ |
