276279

DHL C-TMS

Link to Paragon for tactical plan

FUNCTIONAL SPECIFICATION - 10.5

- 1.0

Reference: FS 276279 AS-84PE88

Client Requirement

Tactical - Paragon integration by journey leg tactical daily, includes collection and delivery activity for same schedule integrated into trips.

Includes standard interface to Paragon system.

Solution

The new code for Dunelm will be based upon the existing C-TMS Paragon interfaces.

The system registry ‘PARAGON_INSTALLED’ will be extended to allow the value ‘DUN’ for the new Dunelm specific code.

The main functional difference is that the system will need to split every order into a collection leg and a delivery leg so that Paragon can plan inbound and outbound consignment movements.

In general the collection legs will be done the day before the delivery is due but this is not always the case. For longer routes and those requiring trailer swaps i.e. for Scottish & Northern Irish orders the collection leg and the delivery leg may require an overnight stop thus requiring an extra transportation day.

The existing slot maintenance will be used to store the extra day offsets for the collection from the suppliers and the delivery to the stores. No program changes will be required for this but data setup will be required to create slots for all of the Dunelm supplier and store locations. NB it is envisaged no slots will be created for Sunday deliveries.

In order to determine when the collection leg and the delivery leg are to be sent to Paragon the interface will reference the slot offsets. The values provided in the SAP interface will be used to determine the collection and delivery windows based on the slot information held in the system.

Further changes will be needed for the creation of the csv format file containing the orders that need to be sent to Paragon for a specific schedule.

As the orders for each supplier on any given day will have been grouped together manually to form manifest’s then these orders need to be kept together by paragon and put on a single trip. As paragon in unable to do this themselves then it will be necessary to group these orders into a consolidated order at manifest level and just to send the totals for the manifest through (using the booking ref with a suffix of ‘-C’ as the unique identifier).

It is expected that C-TMS will be setup with the system registry ‘SCH_SCHED_ORD_DERIVE’ set to ‘EDDT’. i.e. The schedule will be based upon the early delivery date.

It is also expected that C-TMS will be setup with system registry ‘SCH_SCHED_START’ set to about ’22:00’.i.e. All deliveries will depart the National Sortation Centre before 10PM.

The interface export will use the following logic to send legs to Paragon to be planned:

• Collection legs based on collection window and status (UNSCHEDULED)

(Grouped at booking ref)

• Delivery legs based on schedule date and status (SCHED_COLL)

The folder structure and naming standards will be identical to the existing interface but the actual layout will contain a few changes including the suffix for the order number.

The trip information provided by Paragon in the inbound to C-TMS file (csv format) will be in a similar format to that for the existing interface and is configurable as required in the import maintenance.

If the order reference provided has a suffix of ‘-C’ then this means that it is a consolidated collection order and the code will need to loop through all of the orders with a matching booking ref (once suffix removed) instead of using the oms ref.

Any cross-docks for the Scottish and Northern Irish orders will need to be setup in the standard Paragon X-Dock Paths maintenance. The depot expressed in the Paragon CSV file for these trips will show the trailer swap location rather than the National Sortation Centre (NSC).

The current trunking code is for delivery legs only (collections are only returned to the delivery depot and need to be manually trunked back to the original warehouse)..

C-TMS will allow a delivery schedule for trunking to trailer exchange locations using the same slot maintenance function but applied to trailer swap location.

New code will need to be written to allow trunking collections from the suppliers back from the trailer exchange location to the NSC using the existing delivery trips from the previous day.

Scope

This change will be applied to system version 10.5.

Set-up

Pre-requisites

Paragon is already installed.

System Registries

Existing system registry ‘PARAGON_INSTALLED’ will be set to ‘DUN’.

Functional Description

The new inbound and outbound flows between C-TMS and Paragon for Dunelm will be based upon the existing code but a new system registry setting will be needed as there are significant differences in the logic which will only apply to Dunelm. The changes for Dunelm will need to incorporate new logic for consolidating the collection legs of the orders by supplier manifest.

For informational purposes the schedule name for Dunelm will be based upon the system registry values of ‘SCH_SCHED_ORD_DERIVE’ set to ‘EDDT’ and ‘SCH_SCHED_START’ set to ’22:00’ (to be confirmed).

In most cases collections will be made from the supplier into the National Sortation Centre (NSC) on day -1 and then delivered from the NSC to the store on the following day (day 0).

There may be trailer swap location(s) somewhere in the north (Lancaster?) for Scottish orders so the solution has to dynamically take this into account and potentially allow an extra day for these collections and deliveries.

Other locations may also be far enough away from the NSC to also require an extra day to make the collection or delivery as the driver will need an overnight stopover en-route.

SAP Order Import

The first change required is not directly linked to Paragon processing but will be required in order to help C-TMS decide when orders need to be sent to Paragon.

SAP will not provide sufficient information to derive collection and delivery time windows for the orders. Therefore, as part of the order import the system will run a new routine to re-calculate the collection start (Early_Avail), collection end (Late_Avail), delivery start (Early_Del) and delivery end (Late_Del) time windows on the order (SCH_ORD) based upon the slots available and the offsets at the supplier location (collection) and store location (delivery).

The proposal is to use the early delivery date on the order provided by SAP to determine the delivery day and using the offset days on the corresponding delivery slot the process will work backwards to determine the delivery window and also when the order needs to depart the NSC. It will then use this day and work backwards again with the offset days on the collection slot to determine the collection window.

For example (with Sunday as a non-working day) :-

The slots for the suppliers will be setup as collection slots and the slots for the stores will be setup as delivery slots.

Firstly the code will use the delivery day and the delivery location (store) to get the correct slot record (it is assumed that only one slot will exist for each day for each location).

Example of a slot for a store with a normal delivery route:-

The delivery slot times and offsets will be used to determine the delivery window.

In this case no offsets for deliveries so delivery windows will be a single day starting at 07:00 and ending at 21:00.

The system will use the collection offset to determine when orders need to arrive at the NSC and in most cases this will be the day before (-1) with the exception of Mondays which will use a Saturday slot (-2) as Sunday is understood to be a non-working day.

The system will then find a slot for the collection location (supplier) for the in question day (working backwards through the week until one is found).

Example of a slot for a supplier with a normal collection route :-

The collection offsets will be used to determine the collection window and in most cases these will be on a single day starting at 08:00 and ending at 20:00. Paragon will then reduce this during planning to adhere with real collection/delivery slots.

If the store being delivered to requires an extra day for its delivery (either a trailer swap for Scottish orders or is a long way away from the NSC) then the day that it needs to arrive at the NSC is a day earlier and the delivery has an additional day offset.

Example of a slot for a store that requires the extra day for the delivery:-

The delivery offset for all days is -1 as the network requires 2 days for delivery except Monday were the offset is -2 as Sunday is not a working day.

This will ensure that the delivery windows span 2 days for the orders for most days.

i.e. Early Del = Monday 07:00 and Late Del = Tuesday 21:00.

Apart from the deliveries on Monday that still require 2 working days so will be :-

Early Del = Saturday 07:00 and Late Del = Monday 21:00.

The collection offsets are all -2 (i.e. the day before the delivery) apart from Monday and Tuesday that require an extra day for the non-working Sunday.

A supplier may also need an extra day for the collection in a similar way so these will be stored on the slot for the collection offset.

Example of a slot for a long delivery :-

All days will have a collection offset of -1 to show that the collection will take an extra day apart from Monday which has -2 to take into account for Sunday.

Collection window for arrival at NSC on Monday will look like :-

Earl Avail = Saturday at 08:00 and Late Avail = Monday at 20:00.

i.e. Window spans 3 days (2 working days).

Other days will look like :-

Early Avail = Wednesday at 08:00 and Late Avail = Thursday at 20:00.

i.e. Window spans 2 days.

Order Export (CSV.Write_Orders_to_Paragon) :-

The filename and directory will be set to the standard format however the data layout will be slightly different and the population of the data will be very different as the schedule name will not be used for the collection leg.

Firstly which orders are being selected for inclusion in the export csv file will need to be changed.

Currently only orders that are at status ‘UNSCHEDULED’ and for a matching schedule name are included.

This will be changed so that any orders that need the collection leg sending (status ‘UNSCHEDULED’) will be sent if the collection window overlaps the dates for the current schedule being sent.

This can be done as the collection window will have already been set to sensible values by the import program based upon slots (see previous section).

NB) Booking Ref must also have been set (i.e. placed on a manifest).

Once the appropriate orders have been selected that need the collection leg sending to paragon then these will need to be consolidated at manifest (booking ref) level.

The proposed layout for Dunelm (Paragon Orders Export) is:-

Field	Field Name	Size and format	Paragon keyword	C-TMS Notes
1	Run key	Alphanumeric	CALL.TEXT01	Sched Name for Export Run
2	Location ID	Alphanumeric	CALL.ID	Supplier Collection Point or Store Delivery Location
3	Vehicle Base ID	Alphanumeric	CALL.DEPOTID	Blank
4	R.P.E	Integer	CALL.MEASURE3	Total RPE Quantity
5	Weight in kg	Integer	CALL.MEASURE1	Total Weight
6	Volume in cm3	Integer	CALL.MEASURE2	Total Volume
7	Earliest date and time	yyyymmdd hh:mm	CALL.TEXT02	Early Avail or Del
8	Latest date and time	yyyymmdd hh:mm	CALL.TEXT03	Late Avail or Del
9	Variable stop time	Integer (minutes)	CALL.USER01	Calculated based on whether activity at location is a load or unload and how many du’s being actioned
10	Fixed stop time	Integer (minutes)	CALL.USER02	Calculated based on whether activity at location is a load or unload.
11	Order type (delivery or collection)	D=Delivery, C=Collection	CALL.TYPE	D if to a Store, C if from a Supplier
12	MTS order reference	Numeric	CALL.USER03	Unique Order Reference
13	External order reference	Alphanumeric	CALL.TEXT04	External Reference for deliveries only
14	Product type (chilled or ambient)	Alphanumeric	CALL.TEXT05	Product Type ? (Line Level)
15	Delivery type, carrier type	Alphanumeric	CALL.TEXT06	Blank
16	Special instruction	Alphanumeric	CALL.TEXT08	Special Instructions for deliveries only
17	Phone number	Alphanumeric	CALL.TEXT09	Phone Number for collection or delivery location contact
18	E-mail	Alphanumeric	CALL.TEXT10	E-mail for collection or delivery location contact

For the consolidated collection order (at manifest level – SCH_ORD.BOOKING_REF) then all of the orders will be collected from the same supplier so we can use the collection (supplier) location from the order (SCH_ORD.FROM_LOC).

When all of the orders collection leg is going directly to the NSC (i.e. not needing to use the trailer swap location) then it is this date window that will be sent to Paragon so the earliest date will be set to SO.EARLY_AVAIL and the latest date will be SO.LATE_AVAIL.

However if the order is for a supplier that needs to use a trailer swap area then the earliest date can be set to the same value but the latest delivery date will use the time from the late available window but the date from the early available window.

This will allow us to plan the trunk from the TS to the NSC on the following day.

i.e. Early Avail = 28/06/2010 08:00 and Late Avail = 29/06/2010 18:00 then we will provide paragon with Earliest = 28/06/2010 08:00 and Latest = 28/06/20010 18:00.

All of the totals will be the totals for all of the orders on this manifest.

All of the orders on the manifest should have the same collection window but just in case the code will use the minimum early collection (SCH_ORD.EARLY_AVAIL) and the maximum late collection (SCH_ORD.LATE_AVAIL).

The quantity for each du type for each order (SCH_ORDER_LINE.DU_TYPE, QUANTITY) will be used with the standard code GEO.GET_ACTIVITY_RATE (using the supplier’s loading rate GEO_LOCATION.LOADING_RATE) to give the variable stop time which will be totalled for all order lines on all of the orders on the manifest.

The fixed stop time can be obtained using the standard code GEO.GET_FIXED_MINS.

The unique reference will be the manifest number (SCH_ORD.BOOKING_REF) with an appended suffix of ‘-C’ to identify it as a consolidated collection order.

The external reference, product type and special instructions will be blank for collections as there are multiple values from the various orders.

Phone number will be the first contact for the supplier (GEO_CONTACT.PHONE for SCH_ORD.FROM_LOC).

E-mail will again be for the first contact for the supplier (GEO_CONTACT.EMAIL).

In addition to the consolidated collection orders then each individual order that requires delivery on that schedule will also need to be sent to paragon.

It will need to send any orders at ‘SCHED_COLL’ status with a matching schedule name.

The schedule name can be used as the order import will have amended the early delivery date (and the late delivery date) to the correct value which in turn would have adjusted the schedule name accordingly.

When the orders delivery leg is going directly out of the NSC (i.e. not needing to use the trailer swap location) then it is this delivery date window that will be sent to Paragon so the earliest date will be set to SO.EARLY_DEL and the latest date will be SO.LATE_DEL.

However if it is a store needs to use the trailer swap area then the latest date can be set to the same value but the earliest date will use the time from early delivery window but the date from the late delivery window.

This will allow us to plan the trunk from the NSC to the TS on the previous day.

i.e. Early Del = 28/06/2010 08:00 and Late Del = 29/06/2010 18:00 then we will provide paragon with Earliest = 29/06/2010 08:00 and Latest = 29/06/2010 18:00.

As a line will be created per order then the totals will be directly of the corresponding SO record.

The variable and fixed unloading times at the store can be obtained in the same manner as the loading times (as above) but using the unloading rate for the store (GEO_LOCATION.UNLOADNG_RATE for SO.TO_LOC).

The unique reference can just be SO.OMS_REF.

External reference can be set for deliveries because it is at order level SO.EXTERNAL_REF.

Product Type is at line level so what happens when mixed orders ?

Special Instructions will be SO.SPECIAL_INSTRUCTIONS.

The phone number and e-mail can be obtained as above but using the first contact for the store.

Trip Import (PAR.Process_Paragon_Data) :-

The trip information provided by Paragon in the csv will again be in a very similar format to the existing clients and is configurable as required in the import maintenance form.

The proposed form for Dunelm is :-

Field	Field Name	Size and format
1	Paragon route number	Numeric
2	Paragon trip number	Numeric
3	Paragon drop number	Numeric
4	Paragon trip position	Numeric
5	MTS order reference	Alphanumeric
6	External order reference	Alphanumeric
7	Vehicle type	Alphanumeric
8	Location ID	Alphanumeric
8	Vehicle Base ID	Alphanumeric
9	Depot Code (Call.DepotName)	Alphanumeric
10	Vehicle group name (Carrier)	Alphanumeric
11	Arrival at call	yyyymmdd hh:mm
12	Departure from call	yyyymmdd hh:mm
13	Route start time	yyyymmdd hh:mm
14	Trip start time	yyyymmdd hh:mm
15	Departure from depot	yyyymmdd hh:mm
16	Return time to depot	yyyymmdd hh:mm
17	Trip end time	yyyymmdd hh:mm
18	Route end time	yyyymmdd hh:mm
19	Total break duration from previous call	minutes
20	Total break duration at this call	minutes
21	Total break duration until next call	minutes

The order reference provided by Paragon will have a ‘-C’ suffix for the consolidated collection orders.

Currently the code determines if any of the trips provided by Paragon for this schedule (determined using the depot departure time) already exist in MTS (uses schedule name and route code) and if they do then the whole file is treated as a re-spin rather than an initial upload.

NB) The code for the original upload and a re-spin are totally independent from each other.

Currently the code for the initial upload processes all of the trunk legs first for deliveries based upon the total weights for radial trips and slots.

This is not going to be used by Dunelm as there is only a single trailer swap location (TS) so we will be using code for each order individually as though it was a re-spin.

Any suppliers or stores that need to utilise a TS will still need to be setup in the standard Paragon X-Dock Paths maintenance if they want to add the extra leg for the order to a trunk trip.

NB) Delivery depot provided by Paragon for these trips will be the TS rather than the National Sortation Centre (NSC).

The existing trunking code is for delivery legs only (collections are only returned to the delivery depot and need to be manually trunked back to the original warehouse) so new code is required for the trunk of the collection.

Use the same trips to trunk both ways with the trailer left overnight in the trailer swap location.

i.e Collections made on Monday by a carrier based at the TS will be taken to the TS where they will be left overnight and then will be trunked on Tuesday back to the NSC by a carrier based at the NSC.

Also deliveries leaving the NSC on Monday by a carrier based at the NSC will not be delivered until Tuesday by a carrier based at the TS.

Once all of the above processing has been completed then the form currently prompts the user if they want to change any orders still at status ‘NEW’ back to ‘UNSCHEDULED’ .

This will need tweaking to set them back to ‘SCHED_COLL’ instead if the CURRENT_DEPOT on the order is not null or is not the same as FROM_LOC.

Original Spin

The new code will need to loop through the trips (creating them) and orders (adding them to the trip).

For each paragon delivery order it will need to optionally (if going via the trailer swap location) add the corresponding mts order to a trunk trip (creating it if necessary) and then add it to the radial trip.

For each paragon collection order (at manifest level) it will need to loop through the corresponding mts orders and for each one it will add the mts order to the radial trip and then optionally (if going via a trailer swap location) add the mts order to a trunk trip (creating it if necessary).

The pseudo code for the original spin which will process the paragon data looks like :-

Loop through the unique paragon trips

Create the radial trip

Loop through each order on the paragon trip

Check if delivery or collection

If delivery

Check if trailer swap required

If swap then call delivery trailer swap

Add order to radial trip

Else collection

Loop through mts orders (booking ref minus suffix)

Add order to radial trip

Check if trailer swap

If swap then call collection trailer swap

End Loop (mts orders)

End If (delivery / collection)

End Loop (paragon orders)

Update radial trip (carrier, times etc)

End Loop (paragon trips)

Re-Spin

It has been agreed that the re-spin will just be to add collection orders to the end of existing delivery trips (or possibly creating new trips just for the collections but they will be sequenced at the end to avoid effecting the route code on the existing delivery trips).

When a paragon re-spin (matching MTS trips already exist) is run then it will need loop through the paragon trips (creating the mts trip if necessary).

It will then loop through the paragon orders and for each paragon delivery order then add the corresponding mts order to the correct trunk (if going via the trailer swap) and then add it to the mts radial trip.

For each paragon collection order (at manifest level) it will loop through all of the corresponding mts orders and for each mts order it add it to the radial trip and optionally (if going via the trailer swap location) add it to the correct trunk trip (creating it if necessary).

The pseudo code for the re-spin to process the paragon data looks like :-

Loop through the unique paragon trips

Check if existing MTS radial trip (Sched and Route)

If new trip

Create radial trip

End If (New trip)

Loop through each order on the paragon trip

Check if delivery or collection

If delivery

Nothing (as delivery orders will be unaffected by the re-spin)

Else collection

Loop through MTS orders (Booking Ref minus suffix)

Add order to radial trip

Check if trailer swap

If swap then call collection trailer swap

End Loop (MTS orders)

End If (delivery / collection)

End loop (paragon orders)

Update radial trip (carrier, times etc)

End Loop (trips)

Delivery Trailer Swap

For every delivery location (store) that needs to use the trailer swap then it will be necessary to set it up in the location in the Paragon X-Dock maintenance form.

The delivery to the store (0510) from the NSC (DHLDUN) will be made via the trailer swap (TS) location (DHLPEN).

NB) It is assumed that only a single cross-dock leg will ever exist for Dunelm.

The carrier based at the NSC will do the trunk leg between the NSC and the TS.

The carrier based at the TS will do the delivery between the TS and the store.

When paragon provide a trip needing this trailer swap then they would supply the location ID (delivering depot) as ‘DHLPEN’.

It is assumed that the delivery window for the order will be over 2 days and that the first day would be the trunk and the second day would be the delivery.

We will be using the standard slot functionality at the trailer swap location to keep track of when the trunks will be made and also which trip is performing the trunk on any given day.

This means that slots needs to be maintained for the trailer swap locations as follows :-

This assumes a single slot every day with the trunk starting at the NSC (DHLDUN) early morning (8-10) arriving at the TS (DHLPEN) about midday (12-14) which then gives it enough time to return to the TS (probably with a collection trunk see later).

NB) If we need more than one slot per day then are we going to use rpe quantity from the delivery orders to decide when the trunk is full and we need to create the next one ?

It is assumed that the trunk from the NSC to the TS will occur the day before the delivery out of the TS except when this is over the weekend.

This has already been coded for in the order import for the calculation of the delivery window on the order so we will use the start of the delivery window (SO.EARLY_DEL) as the day that the trunk occurs which will give us the correct slot.

A trip (if required for that day) will be stored against each slot for each day which will be held on the standard table TRIP_SLOT_USAGE (TSU).

If no record exists on TSU for the required trunk date then a new trunk trip will be created (will probably need to create the SU stop at the NSC with the correct time – start of collect window from slot) and the TSU record created.

The trunk leg for the order can then be added to the trip.

The order will have already been collected from the supplier and moved into the NSC so this trunk leg will be between the NSC (current location) and the TS.

Collection Trailer Swap

For every collection location (supplier) that needs to use the trailer swap then it will be necessary to set it up in the location in the Paragon X-Dock maintenance form.

The supplier (LOV01) will cross-dock via the trailer swap location (DHLPEN) before being trunked down to the NSC (DHLDUN).

The collection from the supplier will be done by the carrier based at the TS (DHLPEN).

NB) Labelled Delivery Leg Carrier on the form.

The trunk from the TS to the NSC will be done by the carrier based at the NSC (DHLDUN).

NB) This also assumes that Dunelm will only ever cross-dock via a single trailer swap location.

When paragon provide an order that needs to use this trailer swap then they will provide the location ID (collecting depot) as the TS (DHLPEN).

It is assumed that the trunk leg for the collection order will be taken care of as a back-haul on the following day (taking into account the weekend) on the same trip as the trunks for the deliveries on that day.

This has already been coded for in the order import for the calculation of the collection window on the order so we will use the end of the collection window (SO.LATE_COLL) as the day that the trunk occurs which will give us the correct slot.

NB) It is perceived that the first spin of paragon will create the delivery trips with no collections and that the collections will only be included on the second spin.

Use the slot to get the corresponding TSU record to assign the correct trunk trip and if not already existing then create the trunk trip (will probably need to create the SU stop at the NSC with the correct time – start of collect window from slot) and the corresponding TSU record.

NB) For a delivery on Thursday then the trunk trip will be created for Wednesday.

A collection on Tuesday will be trunked back to the NSC on the back-haul on Wednesay.

Meaning if the planners on Monday firstly plan the collections for Tuesday adding them to the trips for the deliveries for Monday already created then the trunk trips for the deliveries on Thursday will not yet have been created so it will be the collections that always create the trunk trips first.

Once the correct trip has been identified then the trunk leg for the order can be added to the trip.

The order will already have been added to the radial trip at this point so will already be in the TS.

This trip will be to trunk the order from the TS (current location) to the NSC.

Document History

Version	Date	Status	Reason	Initials
0.1	10/09/10	Draft	Initial version	DRM
0.2	16/06/10	Review	Reviewed	MJC
0.3	23/06/10	Draft	Further changes	DRM

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager