FS 328407 EPOD-TTM Interface Changes
OBS Logistics Ltd
EPOD-TTM Interface Changes
CALIDUS ePOD
14th August 2015 - 0.3
Reference: FS 328407
Contents
Functional Overview
Client Requirement
As identified for Cooper Callas contract, EPOD-TTM interface must change to pass in the correct level of information, mapped correctly.
Cooper Callas want to use CALIDUS Portal TTM for customer service, transport and reporting purposes, but also for the customers to track their package through the Customer Tracking Gateway. This is similar to what was developed for PORTAL TMS, but works of TTM data, and already exists.
The problem with the EPOD TTM interface is that the customer's reference is never passed to TTM from EPOD.
The system currently maps TMS_REF as EPL_JOB_ID, and SO_REF as EPL_JOB_CODE. This is as per the Partnerlink interface.
Solution Overview
The interface must be amended to pass this information in, and to ensure that the orders are interfaced only once with the correct address information.
The system currently maps TMS_REF as EPL_JOB_CODE, and SO_REF as EPL_JOB_ID. This is as per the Partnerlink interface.
This needs to be amended to be TMS_REF as EPL_JOB_CODE, and SO_REF as EPL_CUST_REF.
Additionally, the following reference fields should be populated where possible in the XML:
- PO_REF - EPL_SO_NUMBER
- BOOK_REF - EPL_EXT_REF
Note: If we have no data in these other fields, the tags should not be added.
Due to the way that ORD messages are saved in TTM, this means that several other changes must also be made:
- Change to set the DEP and DEL messages correctly from the original collection location and ultimate destination
- Set the new tags ORDER_TYPE and TRACK_BY based on the addresses returned.
- Only send one ORD to TTM per External reference (EPL_CUST_REF if populated, else EPL_JOB_CODE), not one per job.
The following bugs will also be fixed:
- When files are updated through the data webservices, the flag controlling the sending of ORD and TRP messages to CALIDUS Portal should be reset, to force a resend.
- When building detail data for products, the Planned quantity should be used for the To Deliver quantity, not the actual quantity.
Additionally, a new message type of "COL" should be written, when confirming and order has been collected (i.e. the job type is "C" - Collection).
The CALIDUS Portal system should be modified to allow a configuration to call the correct POD/POC note through the Site, Job Code and Job Type, where the Job Code is set from the TMS Reference. This will be through configuration of the PODPOC URL to be type "EPOD3"
Scope
The changes will be made to the latest version (version 3) of CALIDUS ePOD.
The processes below have been validated for a range of different scenarios, namely:
- Cross-dock with and without loading/unloading at the depot
- Radial collect with and without unloading at the depot
- Radial delivery with and without loading at the depot
These processes are validated if the TMS reference and customer reference are unique for that whole transport instruction, and if the customer reference and TMS reference change to new values for attempted redelivery (rebooking) or partial delivery (part pick).
For CALIDUS ePOD, if the system is provided original collection and final delivery locations on the first and last job, this will work. Without these, the system is dependent on whether the TMS processes and plans the sequence of jobs and times correctly, which in some cases may not be true.
Note: For customers who only send Collection jobs to CALIDUS ePOD, those orders will look incomplete on CALIDUS Portal, as they will never be seen to have been delivered.
During the production of an overall guide document, several other bugs and changes were noted, but are not part of this specification, namely:
- When uploading data through the manual upload screen, the flag controlling the sending of ORD and TRP messages to CALIDUS Portal should be reset, to force a resend.
- When manually changing data in the Admin system, the flag controlling the sending of ORD and TRP messages to CALIDUS Portal should be reset, to force a resend.
- Do not send Timezone information if you do not have it in the system
- Clausing by Reason code only stores the comment for a clause reason code, not the reason code itself when sending the detail to CALIDUS Portal.
- Trailer ID should be used in preference to Vehicle ID if present.
Set-up
Pre-requisites
Menu Structure
Data
A CALIDUS ePOD Remote user must be configured within the system, and it must have access to the Site being used for testing.
The CALIDUS Portal system should be modified to allow a configuration to call the correct POD/POC note through the Site, Job Code and Job Type, where the Job Code is set from the TMS Reference. This will be through configuration of the PODPOC URL to be type "EPOD3". The EPOD Webservice and user and password must also be configured correctly.
Description of Changes
Database/DAL
Add index IX_EPOD_JOB_CODE_REF on table EPOD_JOB, using the following fields:
- EPL_SITE_ID
- EPL_JOB_CODE
- EPL_CUST_REF
Modify the existing database package EPOD_XFER_SELECT_TTM, to sort the retrieved records by EPL_SITE_ID, EPL_JOB_CODE, EPL_CUST_REF.
A new database package EPOD_XFER_SELECT_JOB_ADDRESSES_TTM will be created. This new database procedure will return a recordset of up to two job addresses, a DEP address and a DEL address.
The procedure will receive the following parameters - all are required:
- EPL_SITE_ID
- EPL_JOB_ID
- EPL_JOB_CODE
- EPL_CUST_REF
The procedure will do the following:
- Get 1 distinct EPOD_JOB_ADDRESS record for each EPL_JOB_TYPE = "F" or "O" for any jobs with the specified EPL_SITE_ID, EPL_JOB_CODE and EPL_CUST_REF.
- Store the records found to be returned to the calling process.
- If there is not a record for either EPL_JOB_TYPE = "F" or "O"
- Select all EPOD_JOB records with the specified EPL_SITE_ID, EPL_JOB_CODE and EPL_CUST_REF, ordered by EPL_START_PLANNED_DATE, EPL_START_PLANNED_TIME,
- If no EPL_JOB_TYPE "O" record has been found:
- Select the EPOD_JOB_ADDRESS of the first collection job, selecting the job and address for EPL_JOB_TYPE = "C". If found, store this record to be returned to the calling process, with EPL_JOB_TYPE set to "O".
- If no EPO_JOB_ADDRESS if found for the first collection job, select the EPOD_CUSTOMER record for this job. This record should be selected as if it were an EPOD_JOB_ADDRESS record of EPL_JOB_TYPE = "O", with all fields named accordingly - see notes later.
- Store this record to be returned to the calling process.
- If no EPL_JOB_TYPE "F" record has been found:
- Select the EPOD_JOB_ADDRESS of the last delivery job, selecting the job and address for EPL_JOB_TYPE = "D". If found, store this record to be returned to the calling process, with EPL_JOB_TYPE set to "F".
- If no EPO_JOB_ADDRESS if found for the last delivery job, select the EPOD_CUSTOMER record for this job. This record should be selected as if it were an EPOD_JOB_ADDRESS record of EPL_JOB_TYPE = "F", with all fields named accordingly - see notes later.
- Store this record to be returned to the calling process.
All stored records should then be returned to the calling process.
Note: EPOD_CUSTOMER records selected by this process should be mapped as follows:
| EPOD_JOB_ADDRESS | EPOD_CUSTOMER |
|---|---|
| EPL_SITE_ID | EPL_SITE_ID |
| EPL_JOB_ID | Null |
| EPL_ADDRESS_1 | EPL_ADDRESS_1 |
| EPL_ADDRESS_2 | EPL_ADDRESS_2 |
| EPL_ADDRESS_3 | EPL_ADDRESS_3 |
| EPL_ADDRESS_4 | EPL_ADDRESS_4 |
| EPL_ADDRESS_5 | EPL_ADDRESS_5 |
| EPL_POSTCODE | EPL_POSTCODE |
| EPL_CONTACT | EPL_CONTACT |
| EPL_TELEPHONE | EPL_TELEPHONE |
| EPL_EMAIL | EPL_EMAIL |
| EPL_LAST_CHANGED_DATE | EPL_LAST_CHANGED_DATE |
| EPL_LAST_CHANGED_TIME | EPL_LAST_CHANGED_TIME |
| EPL_JOB_TYPE | As Directed Above |
| EPL_NAME | EPL_CUSTOMER_NAME |
| EPL_ADDRESS_ID | EPL_CUSTOMER_ID |
EPOD_JOB
That is a change to procedure EPOD_JOB.ToXElementTTM.
This needs to be amended to populate the references differently:
- TMS_REF populated with EPL_JOB_CODE
- SO_REF populated with EPL_CUST_REF if present, otherwise EPL_JOB_CODE.
The following tags should only be created if data exists in the indicated fields in EPOD_JOB:
- PO_REF populated with EPL_SO_NUMBER
- BOOK_REF populated with EPL_EXT_REF
Additionally, the Delivery and Departure addresses must be populated differently.
Two order header addresses should be built, one for DEP (the original collection location) and one for DEL (the ultimate delivery location).
For DEP addresses, use the addresses in this sequence:
- Use the Origin Job Address (type O) if this exists.
- Use the Job Address of the first Collection Job if this exists.
- Use the Customer Address of the customer of the first Collection Job if this exists.
- If there is no collection job or no origin address, do not write this address.
For DEL addresses:
- Use the Final Destination Job Address (type F) if this exists.
- Use the Job Address of the last Delivery Job if this exists.
- Use the Customer Address of the customer of the last Delivery Job if this exists.
- If there is no delivery job or no destination address, do not write this address.
A new method to EPOD JOB, called AddressesToXElementTTM is only to be called for messages of type ORD, instead of existing call to EPOD_JOB.DeliveryAddressToXElementTTM from EPOD_JOB.ToXElementTTM.
This will call the new database package EPOD_XFER_SELECT_JOB_ADDRESSES_TTM defined above, passing in EPL_SITE_ID, EPL_JOB_ID, EPL_JOB_CODE and EPL_CUST_REF from the EPOD_JOB DAL object. which will return the DEP and DEL addresses to be used. These will be loaded into a list of EPOD_JOB_ADDRESS DAL objects, from the values returned.
The data will then be formatted into a single XML fragment to be returned, including both addresses (if present).
For each address returned from the package, create an XML fragment as follows:
<ORDER_HEADER_ADDRESS>
<ADDRESS_TYPE></ADDRESS_TYPE>
<ADDRESS_ID></ADDRESS_ID>
<ADDRESS_NAME></ADDRESS_NAME>
<ADDRESS_LINE_1></ADDRESS_LINE_1>
<ADDRESS_LINE_2></ADDRESS_LINE_2>
<ADDRESS_TOWN></ADDRESS_TOWN>
<ADDRESS_POSTCODE></ADDRESS_POSTCODE>
</ORDER_HEADER_ADDRESS>
Each element will be populated as follows:
- ADDRESS_TYPE - DEP if EPL_JOB_TYPE = "O" or DEL if EPL_JOB_TYPE = "F"
- ADDRESS_ID - EPL_ADDRESS_ID
- ADDRESS_NAME - EPL_NAME
- ADDRESS_LINE_1 - EPL_ADDRESS_1
- ADDRESS_LINE_2 - EPL_ADDRESS_2
- ADDRESS_TOWN - EPL_ADDRESS_3
- ADDRESS_POSTCODE - EPL_POSTCODE
Two new tags will be added onto the end of this produced address XML, depending on whether a DEL (Job Type "F") address has been returned:
| Tag | With DEL | Without DEL |
|---|---|---|
| ORDER_TYPE | "O" | "C" |
| TRACK_BY | "DEL" | "COL" |
The formatted XML of both addresses and the new tags should be returned from this procedure.
EPOD_PRODUCT
In method ToXElementTTM, set XElement "TO_DELIVER" from EPL_PRODUCT_QTY_PLANNED.
Auto Export
ExportTTM
In EPOD_SYS_EXPORT.ExportTTM, ORD messages are sent by retrieving all EPOD_JOB records that require sending, though the existing procedure EPOD_JOB_XFCONFIG.GetJobsXferTTM, calling database package EPOD_XFER_SELECT_TTM. This procedure will be modified as part of this change to order the data by EPL_SITE_ID, EPL_JOB_CODE and EPL_CUST_REF.
The procedure loops through each record returned to send an ORD message (through existing procedure EPOD_SYS_EXPORT.ExportORD). This will be modified to store the last values of EPL_SITE_ID, EPL_JOB_CODE and EPL_CUST_REF that have had an ORD message sent this run (starting as null values). If the record being processed has the same values of EPL_SITE_ID, EPL_JOB_CODE and EPL_CUST_REF as were stored on the last record processed, the ORD message does not need to be sent. Instead, the record should simply be marked as send, through setting of EPODJOBXFCONFIG.EPL_XFER_TTM_FLAG = EPODYesNo.No.
Additionally, a new message type of COL should be sent. This is functionally identical to a DEL message.
The existing procedure EPOD_SYS_EXPORT.ExportTTM retrieves all EPOD_JOB records that have been completed and require CAN or DEL messages sending, through the existing procedure EPOD_JOB_XFCONFIG.GetJobsCompleteXferTTM, calling database package EPOD_XFER_SELECT_JOB_COMPLETE_TTM. This retrieves jobs of types Collection and Delivery (EPOD_JOB_TYPE = "C" or "D"). The procedure then calls existing procedure EPOD_SYS_EXPORT.ExportCANDEL to send the message to TTM, and then EPOD_SYS_EXPORT.GenerateORDContent to generate the content of the message.
In this procedure, when DEL messages are built, the final part of the procedure builds the message package around the content, including the event message type being sent. If the message type is DEL, check the value of EPOD_JOB.EPL_JOB_TYPE. If this value is "C", change the event message type to be "COL" instead of "DEL".
Data Webservices
When files are updated through the data webservices (ePOD_DataService.asmx and ePOD_DataService2.asmx), the flag controlling the sending of ORD and TRP messages to CALIDUS Portal should be reset, to force a resend. This should be based on the code to do the same in the Auto-Import of a CSV file, as seen in To achieve this:
- Modify EPOD_DATASERVICE.ProcessLoad to explicitly set the value of EPL_XFER_TTM_FLAG to "". Do this just before updating the load (through EPOD_LOAD.Update();), currently line 549.
- Modify EPOD_DATASERVICE.ProcessJob to explicitly set the value of EPL_XFER_TTM_FLAG to "". Do this just before updating the load (through EPOD_JOBS.Update();), currently line 1344.
Additionally, Loads and Jobs should be processed after the standing data, not before. Modify EPOD_DATASERVICE.ProcessXMLImport to achieve this.
Appendix A: TEST PLAN
| Test Script / Scenario Reference | EPOD-TTM Interface Changes | Call Number(s): 328407 |
| Test Script / Scenario Description | To test the EPOD-TTM interface | PASS / ISSUES / FAIL |
| Menu Access | N/A | |
| Pre-requisites | Tested By: | |
| Test Objective | To show that the TTM messages are sent with the correct details mapped, that the ORD messages are sent only once per cust ref, and that the departure and delivery addresses are sent correctly, across multiple different transport configuration types. | Date: |
| Step | Action | Result | Remarks | P/F |
| 1 | TRP and ORD messages - Cross-Dock | |||
| Ensure the TTM Interface is enabled. | ||||
| 1.01 | Create jobs with the same job code/cust ref being picked up from a supplier and delivered to a depot on one trip, then picked up from the depot and delivered to the customer on a second trip. Ensure these use Customer addresses only. Ensure all the planned start times are set reflecting reasonable delivery times. | 2 TRP records and a single ORD record should be created. The DEP address for the ORD should be the customer address from the first job. The DEL address should be the customer address of the last job. The fields should be mapped correctly (specifically checking ORDER_TYPE, TRACK_BY, TMS_REF, SO_REF, PO_REF and BOOK_REF. | ||
| 1.02 | Create jobs with the same job code/cust ref being picked up from a supplier on one trip, then delivered to the customer on a second trip. Ensure these use Customer addresses only. Ensure all the planned start times are set reflecting reasonable delivery times. | 2 TRP records and a single ORD record should be created. The DEP address for the ORD should be the customer address from the first job. The DEL address should be the customer address from the last job. The fields should be mapped correctly. | ||
| 1.03 | Create jobs with the same job code/cust ref being picked up from a supplier and delivered to a depot on one trip, then picked up from the depot and delivered to the customer on a second trip. Ensure these use Job addresses only. Ensure all the planned start times are set reflecting reasonable delivery times. | 2 TRP records and a single ORD record should be created. The DEP address for the ORD should be the job address from the first job. The DEL address should be the job address from the last job. The fields should be mapped correctly. | ||
| 1.04 | Create jobs with the same job code/cust ref being picked up from a supplier on one trip, then delivered to the customer on a second trip. Ensure these use Job addresses only. Ensure all the planned start times are set reflecting reasonable delivery times. | 2 TRP records and a single ORD record should be created. The DEP address for the ORD should be the job address from the first job. The DEL address should be the job address from the last job. The fields should be mapped correctly. | ||
| 1.05 | Create jobs with the same job code/cust ref being picked up from a supplier and delivered to a depot on one trip, then picked up from the depot and delivered to the customer on a second trip. Ensure the first job has a Final Delivery address, and the last job has an Origin address. Ensure all the planned start times are set reflecting reasonable delivery times. | 2 TRP records and a single ORD record should be created. The DEP address for the ORD should be the origin address from the last job. The DEL address should be the Final Delivery address from the first job. The fields should be mapped correctly. | ||
| 1.06 | Create jobs with the same job code/cust ref being picked up from a supplier on one trip, then delivered to the customer on a second trip. Ensure the first job has a Final Delivery address, and the last job has an Origin address. Ensure all the planned start times are set reflecting reasonable delivery times. | 2 TRP records and a single ORD record should be created. The DEP address for the ORD should be the origin address from the last job. The DEL address should be the Final Delivery address from the first job. The fields should be mapped correctly. |
| Step | Action | Result | Remarks | P/F |
| 2 | TRP and ORD messages - Radial Collect | |||
| Ensure the TTM Interface is enabled. | ||||
| 2.01 | Create jobs with the same job code/cust ref being picked up from a supplier and delivered to a depot on one trip. Ensure these use Customer addresses only. Ensure all the planned start times are set reflecting reasonable delivery times. | A single TRP and ORD record should be created. The DEP address for the ORD should be the customer address from the first job. The DEL address should be the customer address of the last job. The fields should be mapped correctly. | ||
| 2.02 | Create a job being picked up from a supplier on one trip. Ensure it uses a Customer address. | A single TRP and ORD record should be created. The DEP address for the ORD should be the customer address. There should be no DEL address. The fields should be mapped correctly. | ||
| 2.03 | Create jobs with the same job code/cust ref being picked up from a supplier and delivered to a depot on one trip. Ensure these use Job addresses only. Ensure all the planned start times are set reflecting reasonable delivery times. | A single TRP and ORD record should be created. The DEP address for the ORD should be the job address from the first job. The DEL address should be the job address from the last job. The fields should be mapped correctly. | ||
| 2.04 | Create a job being picked up from a supplier on one trip. Ensure it uses a Job address. | A single TRP and ORD record should be created. The DEP address for the ORD should be the job address. There should be no DEL address. The fields should be mapped correctly. | ||
| 2.05 | Create jobs with the same job code/cust ref being picked up from a supplier and delivered to a depot on one trip. Ensure the first job has a Final Delivery address, and the last job has an Origin address. Ensure all the planned start times are set reflecting reasonable delivery times. | A single TRP and ORD record should be created. The DEP address for the ORD should be the origin address from the last job. The DEL address should be the Final Delivery address from the first job. The fields should be mapped correctly. | ||
| 2.06 | Create a job being picked up from a supplier on one trip. Ensure it has a Final Delivery address and a customer address. | A single TRP and ORD record should be created. The DEP address for the ORD should be the customer address. The DEL address should be the Final Delivery address. The fields should be mapped correctly. |
| Step | Action | Result | Remarks | P/F |
| 3 | TRP and ORD messages - Radial Delivery | |||
| Ensure the TTM Interface is enabled. | ||||
| 3.01 | Create jobs with the same job code/cust ref being picked up from a depot and delivered to a customer on one trip. Ensure these use Customer addresses only. Ensure all the planned start times are set reflecting reasonable delivery times. | A single TRP and ORD record should be created. The DEP address for the ORD should be the customer address from the first job. The DEL address should be the customer address of the last job. The fields should be mapped correctly. | ||
| 3.02 | Create a job being delivered to a customer on one trip. Ensure it uses a Customer address. | A single TRP and ORD record should be created. The DEL address for the ORD should be the customer address. There should be no DEP address. The fields should be mapped correctly. | ||
| 3.03 | Create jobs with the same job code/cust ref being picked up from a depot and delivered to a customer on one trip. Ensure these use Job addresses only. Ensure all the planned start times are set reflecting reasonable delivery times. | A single TRP and ORD record should be created. The DEP address for the ORD should be the job address from the first job. The DEL address should be the job address from the last job. The fields should be mapped correctly. | ||
| 3.04 | Create a job being delivered to a customer on one trip. Ensure it uses a Job address. | A single TRP and ORD record should be created. The DEL address for the ORD should be the job address. There should be no DEP address. The fields should be mapped correctly. | ||
| 3.05 | Create jobs with the same job code/cust ref being picked up from a depot and delivered to a customer on one trip. Ensure the first job has a Final Delivery address, and the last job has an Origin address. Ensure all the planned start times are set reflecting reasonable delivery times. | A single TRP and ORD record should be created. The DEP address for the ORD should be the origin address from the last job. The DEL address should be the Final Delivery address from the first job. The fields should be mapped correctly. | ||
| 3.06 | Create a job being delivered to a customer on one trip. Ensure it has an Origin address and a customer address. | A single TRP and ORD record should be created. The DEP address for the ORD should be the Origin address. The DEL address should be the customer address. The fields should be mapped correctly. |
| Step | Action | Result | Remarks | P/F |
| 4 | Resend Messages | |||
| Ensure the TTM Interface is enabled. | ||||
| 4.01 | Reprocess one of the test loads from above through the data webservices - change any data sent to TTM on the load and jobs to identify the changed messages. | The TRP and ORD messages should be resent |
| Step | Action | Result | Remarks | P/F |
| 5 | Product Quantities | |||
| Ensure the TTM Interface is enabled. | ||||
| 5.01 | Create a delivery job with an ordered qty of 10 and a planned qty of 5. | An ORD message should be sent, the ORDERED tag showing 10 and the TO_DELIVER tag showing 5. | ||
| 5.02 | Complete the delivery on the mobile device, delivering a quantity of 3. | A DEL message should be sent, the ORDERED tag showing 10, the TO_DELIVER tag showing 5 and the DELIVERED tag showing 3. |
| Step | Action | Result | Remarks | P/F |
| 6 | COL messages | |||
| Ensure the TTM Interface is enabled. | ||||
| 6.01 | Complete a collection job. | A COL message should be sent to TTM, populated identically to a DEL message (except for the message type). |
Appendix B: Quote & Document References
| Cost Details | |||
| Activity | No. of Days | Rate per Day (£) | Cost (£ Exc. VAT) |
| Requirements | 0.00 | 0 | £0.00 |
| Change Request Evaluation | 0.50 | 0 | £0.00 |
| Functional Specification | 1.50 | 0 | £0.00 |
| Technical Specification | 0.00 | 0 | £0.00 |
| Development | 6.25 | 0 | £0.00 |
| Testing and Release | 2.00 | 0 | £0.00 |
| Implementation | 0.00 | 0 | £0.00 |
| Project Management | 0.50 | 0 | £0.00 |
| TOTAL | 10.75 | £0.00 | |
| Estimate excludes training, release to live and go live support. |
B.1 References
| Ref No | Document Title & ID | Version | Date |
| 1 |
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
Murray Middleton | OBS Development Manager | _____________________________ |
Matt Tipping | OBS Project Manager | _____________________________ |
