FS 344795 Palletline Integration Arrival Info
Partnerlink
Palletline Integration Arrival Info
CALIDUS ePOD
14th August 2017 - 0.1
Reference: FS 344795
Contents
Functional Overview
Client Requirement
A Palletline Arrival interface is required to Palletline's Contrado system, as an amendment to the existing Palletline interface.
Additionally, changes will be made to the Palletline interface resulting from changes to the Contrado interface (based on the latest specification received by OBS).
Solution Overview
The system will be modified to trigger a message to Palletline when the job is updated as Arrived. This occurs when the driver marks the job as Arrived on the mobile device.
No new Interface configuration is required - this will be triggered as part of the existing Palletline interface.
A message will be generated to Palletline, indicating that the job has arrived, with the following content:
- Arrival Date and Time
- GPS at the time of arrival, if known
- Each pallet, with the "ARRI" status and any known reason codes and notes.
Note: It is not expected that these pallets will have any reason codes against them at the time of arrival.
All other tags will not be sent as part of this Arrival message.
In the case of Arrival ("ARRI") and Proof of Delivery ("EPOD") messages to the Palletline Contrado system, the Statuses sent will be modified to send through multiple lines:
- One for the message status (ARRI or EPOD)
- One for the delivery status (the cancellation or clause reason code and description/notes).
In all ways other than the statuses, the existing Palletline message will not be affected by this change - the system will still send the "EPOD" message when the job is updated to complete.
Scope
The existing Palletline interface will be modified for this change, in version 3.X and 4.X of CALIDUS ePOD.
Note: The allowed reason codes for Palletline job group ("PLINE") have changed with the new definition of the Palletline interface provided to OBS Logistics. The reason codes set up for this job group will require reviewing and setting up for all partners that execute jobs on behalf of Palletline.
Set-up
Pre-requisites
Menu Structure
Data
An 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.
A 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 | |
| EPOD | Successful Clean Delivery DSC | 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 | |
| RTRD | Returned refused By Driver | 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.
- Tracking System - "PLINE"
Functional Description
Database & Data Access Layer
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 PLINE interface for action "ARRI", if the job is arrived via the mobile device, and the job arrived is configured for the PLINE 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 "PLINE" 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.
If a "PLINE" export configuration is found, write an EPOD_XF_CONTROL record with EXC_TYPE "PLINE", 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 = 'PLINE'
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')
Auto-Export
The export process EPOD_SYS_EXPORT will be modified to achieve the necessary changes.
Full details of the Palletline export format can be found in DSC technical specification, referenced in Appendix B.
When calling procedure GeneratePlineContent from ExportXFControl, the process will pass through what type (ARRI or EPOD) from XF_Control as a new parameter to the procedure.
The procedure GeneratePlineContent will receive the new parameter (e.g. p_strMessageType) and check this.
The audit record (EPOD_XF_AUDIT) is currently retrieved using the EPL_MESSAGE_TYPE "JOB_UPDATE". This will be modified to be conditional on the new parameter, for example:
(p_strMessageType == 'ARRI' ? 'JOB_ARRIVAL' : 'JOB_UPDATE)
The process currently extracts the job end date and time (EPL_ACTUAL_END_DATE and EPL_ACTUAL_END_TIME) and formats them. In the case where the new parameter is set to "ARRI", the process will instead extract the arrival date and time (EPL_ARRIVAL_DATE and EPL_ARRIVAL_TIME) and format them instead. It is recommended that the existing variables and processing to do this are used, renaming to more generic names.
The process currently sets the Signature and PodName tags. In the case where the new parameter is "ARRI", the process will conditionally set these tags to a zero-length string instead, ensuring that the tags are sent, but with no value in them.
The process currently sends a single status tag in the message per container (EPOD_CONTAINER) record, conditionally set as "EPOD" if there is no clause or reason code. This will be modified to:
- Send a Status tag for the message type, conditionally set based on the new parameter. If set to "ARRI", send "ARRI", otherwise set to "EPOD".
- If a reason code is present on the EPOD_CONTAINER record, send a second Status tag with this reason code. On this Status tag only, populate the Notes tag with the Clause notes, as now.
This will result in the following XML being built for each message type:
Sample ARRI file
<Root>
<Job>
<Id>CON12345678</Id>
<PodDate>2017-03-09</PodDate>
<PodName></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>
<Statuses>
<Status>
<Code>ARRI</Code>
</Status>
</Statuses>
</Item>
<Item>
<No>02</No>
<Statuses>
<Status>
<Code>ARRI</Code>
</Status>
</Statuses>
</Item>
<Item>
<No>03</No>
<Statuses>
<Status>
<Code>ARRI</Code>
</Status>
<Status>
<Code>NDRC</Code>
</Status>
</Statuses>
</Item>
<Item>
<No>04</No>
<Statuses>
<Status>
<Code>ARRI</Code>
</Status>
<Status>
<Code>DELD</Code>
<Notes>Some Clause Notes</Notes>
</Status>
</Statuses>
</Item>
</Pallets>
</Job>
</Root>
Sample EPOD 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>
<Statuses>
<Status>
<Code>EPOD</Code>
</Status>
</Statuses>
</Item>
<Item>
<No>02</No>
<Statuses>
<Status>
<Code>EPOD</Code>
</Status>
</Statuses>
</Item>
<Item>
<No>03</No>
<Statuses>
<Status>
<Code>EPOD</Code>
</Status>
<Status>
<Code>NDRC</Code>
</Status>
</Statuses>
</Item>
<Item>
<No>04</No>
<Statuses>
<Status>
<Code>EPOD</Code>
</Status>
<Status>
<Code>DELD</Code>
<Notes>Some Clause Notes</Notes>
</Status>
</Statuses>
</Item>
</Pallets>
</Job>
</Root>
No changes will be made to the sending procedure or to any other functionality in this package.
Appendix A: TEST PLAN
| Test Script / Scenario Reference | Palletline Integration Arrival Info | Call Number(s): 344795 |
| Test Script / Scenario Description | Testing Exports (Arrival and Delivery) 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 exports Arrivals in an Arrival interface to Contrado and; the current export of POD messages to Contrado still occurs and matches the required format for Status tags. | Date: |
| Step | Action | Result | Remarks | P/F |
| 1 | Auto-Export | |||
| Create 2 jobs with pallets. 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. | ||||
| 1.01 | Complete another 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 ARRI. 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. | ||
| 1.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 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. |
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.50 | 0.50 | 650 | £325.00 |
| Functional Specification | 1.00 | 1.00 | 650 | £650.00 |
| Technical Specification | 0.00 | 0.00 | 650 | £0.00 |
| Development | 3.50 | 3.50 | 650 | £2,275.00 |
| Testing and Release | 0.75 | 0.75 | 650 | £487.50 |
| Implementation | 0.25 | 0.25 | 650 | £162.50 |
| Project Management | 0.25 | 0.25 | 650 | £162.50 |
| TOTAL | 6.25 | 6.25 | £4,062.50 | |
| Estimate excludes training, release to live and go live support. |
B.1 References
| Ref No | Document Title & ID | Version | Date |
| 1 | EST 344795 Palletline Integration Arrival Info | 0.1 | 08/08/2017 |
| 2 | FS 326965 Partnerlink EPOD Interface | 1.0 | 22/05/2015 |
| 3 | DSC technical specifications.doc | ||
| 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 | _____________________________ |
