291212

DHL C-TMS

Time Window Derivation

FUNCTIONAL SPECIFICATION - 10.7

07/09/2011 - 1.0
Reference: FS 291212 NW-8KNCK4

Functional Overview

Client Requirement

Where orders are created via an inbound interface into C-TMS the following rules should be applied:

1. Where delivery date is specified apply a delivery window of 08:00 – 17:00 on that date with a collection window from -48 hours from the earliest delivery time to the latest delivery date and time.
2. Where an Order does not have a specified delivery date derive the delivery window from the Wholesale Schedule template assigning the Order to its next feasible delivery day with a time window of 15 minutes preceding its delivery time on the Wholesale Schedule.
3. Where an Order does not have a specified delivery date and does not match the Wholesale Schedule apply a delivery window from 08:00 – 17:00 72 hours in the future based on working days (an example placed on a Friday would be applied to the Tuesday schedule).
4. Where an Order is backdated and does not match the Wholesale Schedule apply a delivery window from 08:00 – 17:00 72 hours in the future based on working days (an example placed on a Friday would be applied to the Tuesday schedule).
5. Where an HUK Order corresponds to a stop on the Wholesale Schedule the Delivery Type should be set to “On Sched”, where an HUK Order does not correspond to a stop on the Wholesale Schedule the Delivery Type should be set to “Off Sched”.

There should also be the ability for a super user to be able to maintain and configure these rules going forward.

Solution

A new table will be developed to hold the details of the scheduling rules.

This table will hold the Rule Name, Cost Centre, Offset and Early and Late time windows.

There will be a new maintenance screen developed to allow super users to maintain the time windows, cost centres and time windows, all other users will have access to the screen but will not be able to update any values.

The information contained within the schedule rules table will then be used in the order import functionality to derive the collection and delivery times of an order if these are not present in the imported data.

The delivery type of the order will be set to “On Sched” if there is a specified route template available for the day the order is received on and the delivery location of the order if no route template is found this value will be set to “Off Sched”.

Scope

This change will be applied to system version 10.7.

Set-up

Pre-requisites

None

Menu Structure

Schedule Rules maintenance screen should be added to the maintenance tab.

Data

A new table SCHEDULE_RULES will be developed.

A new sequence SCHEDULE_RULES_SEQ will be developed.

Rules data will be inserted into the SCHEDULE_RULES table.

The “On Sched” value will be inserted into the CNT_DEL_TYPE table.

Functional DESCRIPTION

3.1 Schedule Maintenance.

A new table SCHEDULE_RULES will be added to the database, the columns contained within the table will be:

COLUMN NAME	DATA TYPE	LENGTH	MANDATORY
ID	NUMERIC	10	Yes
COST_CENTRE	CHARACTER	12	Yes
RULE_NAME	CHARACTER	40	Yes
OFFSET	NUMERIC	10	No
EARLY_TIME	CHARACTER	5	No
LATE_TIME	CHARACTER	5	No

A sequence will be created to populate the ID column which will be used as the primary key for all further inserts by selecting the next value from the sequence.

A data script will be executed to populate the COST_CENTRE and RULE NAME VALUES for the delivery and collection rules.

A new system function will be created MAINTAIN_SCHEDULE_RULES examples of existing functions are displayed below. This function will indicate if a specific user can update schedule rules.

A new maintenance screen will be developed to manage the scheduled rules. This screen will be developed in line with the current C-TMS design standards a suggested layout is displayed below. The screen will be accessed from the maintenance tab of the main C-TMS menu.

If the user is a Super User and can maintain the schedule rules they will be able to update all of the values associated with the rule except for the rule name, if this function has not been applied to the user logged on the screen will be view only.

Three buttons will be included in the screen

• Cancel will exit the screen and return to the menu without saving any changes.
• Save will save any changes made
• Exit will return to the main menu

A scroll bar will be positioned at the right hand side of the data to allow all the data to be displayed.

Upon entry to the screen the data within the SCHEDULE_RULES table will be queried for the cost centre of the user logging on and only display the data relevant to that cost centre.

When the data is amended or inserted checks will be performed to validate the data

• Duplicate rules will not be allowed within a cost centre
• The Early Time must be earlier than the Late time
• The Early Time and Late time must be in a valid time format e.g. 09:00
• The Cost Centre will be defaulted to the current users cost centre and can not be updated, if the user has visibility of more than one cost centre this must be entered by the user and must be validated from a list of values the user has access to

3.2 Order Import.

Orders will be imported using either the XML flow process or from a CSV file, the processing described below must be applied in both places.

When orders are received the current day number must be determined which is used to validate the route templates and delivery windows the following processing will then be performed.

• If the order data contains a delivery date the early delivery and late delivery columns on the orders table will be populated using this date value with the delivery window between 08:00 and 17:00 appended to this information. The collection window will be populated with the same values as the delivery window minus 2 days. E.G. Delivery Date 03/09/2011 would equate to early delivery date of 03/09/2011 08:00 and late delivery date 03/09/2011 17:00 the early collection would be 01/09/2011 08:00 and late collection 01/09/2011 17:00.
• If the order data has no specified delivery date and the current day is not a Friday the delivery information must be derived by selecting the offset, early time and late time from the SCHEDULE_RULES table for the rule type of DELIVERY The early and late delivery dates will then be set to the current system date plus the offset value with appropriate time window appended to the date value using the early and late times. The information for early and late collection times will be obtained in the same way but for the rule name of COLLECTION the offset value for collections should be used to subtract that number of days from the current date
• If the order has no specified delivery date and the current day is Friday the time windows will be obtained from the SCHEDULE_RULES table for the COLLECTION and DELIVERY rules as described above, but the date value must be set to the current date plus 3 days which will equate to the next Tuesday.
• If an order has a delivery date specified but this value is less than the current date then the same logic will be applied to obtain the delivery/collection information offsets and time windows also validating that the order is not being processed on a Friday which as before will default to using 3 days as an offset.

Once the delivery and collection dates have been established the delivery type of the order must be validated, this will be achieved by checking the fixed routes which have been created for the cost centre, and delivery location of the order and the current day number.

If a route template is found this order can potentially be added to that route/trip so the delivery type id of the order will be set on “On sched” if no route template can be found the delivery type id will be set to “Off Sched”.

Table Updates Required

[DATA_ADM_FUNCTION_291212.sql DATA_ADM_FUNCTION_291212.sql]

[CT_SCHEDULE_RULES_291212.sql CT_SCHEDULE_RULES_291212.sql]

[CT_SCHEDULE_RULES_SEQ_291212.sql CT_SCHEDULE_RULES_SEQ_291212.sql]

[DATA_SCHEDULE_RULES_291212.sql DATA_SCHEDULE_RULES_291212.sql]

[DATA_CNT_DEL_TYPE_291212.sql DATA_CNT_DEL_TYPE_291212.sql]

Glossary

Term or Acronym	Meaning
C-TMS	Calidus TMS

Document History

Version	Date	Status	Reason	Initials
0.1	06/09/11	Draft	Initial version	CAK
0.1	07/09/11	Draft	Review of the initial version	PJH
0.2	07/09/11	Draft	Updated after review	CAK
0.2	07/09/11	Draft	Review of v0.2	PJH
1.0	07/09/11	Issue	Issued to client	PJH

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager