291110
DHL C-TMS
Modify 'to' location RIO 3a
FUNCTIONAL SPECIFICATION - 10.7
26/08/2011 - 0.3
Reference: FS 291110 AR-8KLMYE
Functional Overview
Client Requirement
Change Request Summary:
Modify the ‘to location’ of order after Paragon planning to reflect the scrap off point. Suggested RIO 3aPaul McGoran/Manchester/UK/NFC
Change Request Details:
On the interface importing routes from Paragon, provide a mechanism to modify the destination of orders to the planned scrap & waste site, or when that is not create radial trips with delivery back to the vehicle depot.
Benefits identified as a result of the change:
Provide CTMS with valid delivery destinations for orders
Solution
Segregation
To ensure this change has no impact on existing Customers within the Industrial shared Database a new cost centre parameter will be created (PARAGON_INSTALLED). As part of this RIO the parameter will be created and set to the Cost Centre configured for British Gas Waste. For other databases this parameter will not be generated.
Changes to Import File
In order to allow processing of additional fields within the Paragon interface, three new fields will be added to the paragon interface table for each order planned to each trip:
| Field Name | Data Type |
| WASTE_SITE | VARCHAR2 (12) |
| WASTE_ARRIVE | DATE TIME |
| WASTE_DEPART | DATE TIME |
The data to populate the new fields will be extracted from the paragon export file as part of the import into C-TMS functionality.
Once the data has been inserted into the interface table, the Paragon Import package (PAR) will run to process the records which will add the orders provided onto trips and stops.
Before an order is applied to a trip, the order’s ‘deliver to’ location (TO_LOC) will be updated to match the value in the new waste site field (WASTE_SITE) provided from Paragon. When the order is subsequently applied to a trip, the drop off stop will then be the waste site.
Changes to Import Processing
For Saturday afternoon orders the waste site field passed back from Paragon are expected to be blank. Therefore, the order’s ‘deliver to’ location (TO_LOC) will remain unchanged. The C-TMS process which adds orders to trips (ADD_ORD_TO_TRIP) will in this case require modification. An extra parameter will be passed to this process based on the existing depot id (DEPOT_ID) passed back from Paragon which describes the DHL depot carrying out the work. C-TMS will interpret this as the order being effectively cross docked via the DHL depot In these instances the order will be left at Scheduled for Collection (SCHED_COLL) status until the Tuesday deliveries into the relevant waste sites are planned.
A new update is required when setting stop times on the trip (SET_TIMES_FROM_PAR).,This will run based on the above cost centre parameter. The new update will set the planned arrive and depart times on the stop for the current trip where the location id is the waste site.
Note that sometimes, due to volume of waste collected in a driver’s day, there might be two or three planned visits to a waste site or even different waste sites on one trip. I.e,: the trip plan is the driver collects from the first 4 addresses by which time the vehicle is full, and then tips all the orders to a waste site, he then collects from another 6 addresses and finally tips at another waste site on the way back to depot to end the trip.
Note that the Paragon file is organised with a route number, then a trip number. Normally, C-TMS expects the trip number in a route to always be value 1. The understanding is that the trip number will increment at each waste site. The impact of this needs to be defined and any development changes included to retain the integrity of the interface.
Scope
This change will be applied to system 10.7.0.
Set-up
Pre-requisites
PARAGON_INSTALLED set to British Gas Waste’s Cost Centre
Menu Structure
Unchanged
Data
Three new fields will be added to the PAR_TRIP_DTL table as detailed below:
| Field Name | Data Type |
| WASTE_SITE | VARCHAR2(12) |
| WASTE_ARRIVE | DATE TIME |
| WASTE_DEPART | DATE TIME |
Implementation Advice
A new COST CENTRE parameter will be created called PARAGON_INSTALLED, the cost centre value should be set to BGW by a system super user in the system parameters screen as displayed below:
Functional Description
Overview
Orders will be sent to Paragon to be planned onto trips with a nominal ‘deliver to’ location which will be set within the QJI order interface or manually added for those order which are keyed directly into C-TMS, for CSV import orders it is expected that this value will be set within the import template . Paragon processing will decide when a vehicle is full and requires a drop off at a waste disposal/recycling site. Paragon will also decide the nearest appropriate waste site to unload the orders along with the carrier (DHL Fleet) and depot who will carry out the trip.
C-TMS will then use the new information passed back to build the orders onto trips to reflect Paragon’s decisions.
Segregation
All functionality described in this document will be controlled by the new Cost Centre level parameter: PARAGON_INSTALLED being set to the British Gas Waste configured cost centre, e.g. ‘BGWCC’.
Import Format Changes
Three new fields will be added to C-TMS’s Paragon Interface Table (PAR_TRIP_DTL) to enable C-TMS to process the new fields required for British Gas Waste.:
| Field Name | Data Type |
| WASTE_SITE | VARCHAR2(12) |
| WASTE_ARRIVE | DATETIME |
| WASTE_DEPART | DATETIME |
Order Updates
Existing functionality in C-TMS would currently consolidate unload activities at waste sites based on overlapping delivery windows. As ALL British Gas Waste Orders for a given day are expected to have open delivery windows 08:00-16:00, to ensure the trip created in C-TMS matches the trip planned by Paragon, C-TMS order delivery windows will be updated based on the new fields received from Paragon for arrival and departure from a Waste Site (WASTE_ARRIVE and WASTE_DEPART) .
This will mean that orders unloaded at a waste site on the same trip, but at different stops/times will not have overlapping delivery windows, preventing C-TMS from any stop consolidation.
The Waste Site passed from Paragon (WASTE_SITE) will be used to update the order’s ‘deliver to’ location (TO_LOC)
Developer Guide:
| SCH_ORD field (updated) | TRIP_PAR_DTL field (source) |
| TO_LOC | WASTE_SITE |
| EARLY_DEL | WASTE_ARRIVE |
| LATE_DEL | WASTE_DEPART |
Scheduling orders onto trips
After the order records have been updated they will be scheduled onto trips in C-TMS. Where a waste site (WASTE_SITE) has been returned by Paragon, the standard C-TMS process to add orders onto trips (ADD_ORD_TO_TRIP) will be used.
For Saturday afternoon orders the waste site field passed back from Paragon are expected to be blank. Therefore, the order’s ‘deliver to’ location (TO_LOC) will remain unchanged. The C-TMS process which adds orders to trips (ADD_ORD_TO_TRIP) will in this case require modification. An extra parameter will be passed to this process based on the existing depot id (DEPOT_ID) passed back from Paragon which describes the DHL depot carrying out the work. C-TMS will interpret this as the order being effectively cross docked via the DHL depot. In these instances the order will be left at ‘Scheduled for Collection’ (SCHED_COLL) status until the Tuesday deliveries into the relevant waste sites are planned.
When the trip is saved, all orders on the trip will be re validated. Any orders which were not updated with a waste site will have the current location set to the depot of the carrier and the status will be updated to ‘SCHED_COLL’.
Carrier’s Hub locations can be maintained in the below form:
Updating trips
In previous Paragon/C-TMS integrations C-TMS received data from the Paragon export with the trip number set to 1 and a unique route number per paragon ‘trip’. The route number and trip number were then combined to identify unique trip in C-TMS. For British Gas Waste, each drop at a waste site will increment the trip number. Without changing C-TMS this would cause a new trip to be created in C-TMS each time a waste site was visited on a trip. Rather than treating the multiple waste drops as different trips, C-TMS will be modified so that the system can identify the multiple waste drops as being on the same trip.
To achieve this, the maximum value of Paragon’s trip number per Paragon route number will be combined with the route number to identify unique trips, ensuring that more than one unload at a waste site for a Paragon trip does not result in more than one C-TMS trip.
| ROUTE NUMBER from paragon | TRIP NUMBER from paragon | ROUTE CODE IN C-TMS | Sample TRIP in C-TMS |
| 3 | 1 | 3-3 | PAR-00001234 |
| 3 | 2 | 3-3 | PAR-00001234 |
| 3 | 3 | 3-3 | PAR-00001234 |
| 4 | 1 | 4-2 | PAR-12345678 |
| 4 | 2 | 4-2 | PAR-12345678 |
| 5 | 1 | 5-2 | PAR-24681357 |
| 5 | 2 | 5-2 | PAR-24681357 |
Table Updates Required
Three new fields will be added to the PAR_TRIP_DTL table as detailed below:
| Field Name | Data Type |
| WASTE_SITE | VARCHAR2(50) |
| WASTE_ARRIVE | DATE TIME |
| WASTE_DEPART | DATE TIME |
References
| EST-291110 AR-8KLMYE Modify 'to' location RIO 3a v1.0 | |||
Glossary
| C-TMS | Calidus TMS |
Document History
| Initial version | ||||
| Reviewed | ||||
| Reviewed | ||||
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
