291367

DHL C-TMS

Milkround Processing

FUNCTIONAL SPECIFICATION - 10.7

27/10/11 - 2.0
Reference: FS 291367 MS-8KNH8N

FUNCTIONAL OVERVIEW

Client Requirement

A new csv import function is required to upload the routes into the C-TMS fixed routes master data. Milk-round routes will be developed to allow orders to automatically plan with reference to the milk-round template master data. The auto-plan function will consider each order as it created in C-TMS and will create each new trip or add into existing trips for each milk-round route. Milk-round route planning should be limited to certain accounts as part of the C-TMS set-up (non Heavy only).

Solution

A CSV format template of routes will be created off system in the following row format

• Day – This column describes the day of the week the route runs e.g 01 02 etc
• Route Number – This column describes the route number for the day
• Drop Sequence – The column is a number that describes the drop sequence within the route
• Node – This column describes the location code known as the MSP (material supply point)
• Despatch Day – A number that denotes the despatch day offset – normally 0 meaning despatch on the Day of the template. A value of 1 indicates despatch the next day
• Enterprise Code – Always value NR
• Planned Arrival – arrival time at stop
• Planned Departure – departure time at stop

(Additionally, a column defining the origin depot location code will be included. For NR, this will always be the Worcester Warehouse. An effective from date so future dated templates can be uploaded early. A template name field so the template data can be reference by name in C-TMS i.e. “NRQ12012”)

CSV Import – A new CSV import will be developed to allow the route data described above to be uploaded into C-TMS. This includes the ability to create an imports data template in C-TMS to match the format of the routes data described above. The import function will allow the user to browse a local drive for the CSV filename and upload the data. Any validation errors will be displayed showing erroneous data not uploaded by exception. An effective from date will be included in the data set.

Maintenance – A maintenance form will be developed in C-TMS to allow the Milk Round routes data to be queried and any manual additions, changes or deletions to be captured.

Trip Generation – A scheduled batch job will be implemented that will at a pre-defined time automatically generate the schedule of trips for the next day according to the template data. As trips are created, they will be assigned a unique C-TMS trip number and the schedule of arrival and departure times taken from the milk round template for the particular stop. A new trip prefix will be introduced to highlight Milk Round generated trips ‘MFR-’ (Milk Round Fixed Route). The route code of the template will alos be stored against the trip. The maintenance screen described above will include a button that will allow trips to be generated from the template on demand manually for a selected future schedule day. Note that a new parameter will be included to preclude the standard trip validation functionality from deleting unused stops. This solution requires that trips are generated in advance and stops should exist with no activity until orders are received and planning process is completed. When manually generating the schedule of trips, the system will warn if any route codes already exist for that schedule day and ask the user to continue or abandon the function. If the schedule of trips has already been created manually, the automatic batch job will take no further action for that schedule.

Order Processing – The order interface (EDI) from DISC (WMS) will be implemented so that transport orders received into C-TMS are stamped with the Day and Route and Node. Immediately an order is uploaded into C-TMS order well from the interface, if a satisfactory trip is available (as generated from the template) for the Day, Route and Node (delivery location) the order will automatically be planned to that trip and delivery stop. Note that orders can be received in advance and in some cases days in advance which means that perhaps a trip doesn’t yet exist to accommodate the order (see Manual Run below).

Manual Run – A function will be provided to run the allocation to trip function (with reference to the Milk-round routes template) manually over all unscheduled orders in the order well. This function will be provided to cover the situation where orders are received into C-TMS but the respective day template has not been generated as trips in advance.

Exceptions / Other Considerations

If an order is received with Day and Route that doesn’t exist in the template at all, the order will not be planned (scheduled) and will remain in the order well for manual planning.

If an order is received with Day and Route that exists in the template, and the trip has been generated in advance, but the ‘to location’ of the order is not a known node in the route, the order will be allocated to the trip and a new stop generated.

It is assumed that all routes in the template start from the Worcester Warehouse. As noted above, the template data includes an origin depot. The origin depot will be used to define the loading location on the trips generated from the template.

Parameter / Future Proof

A system parameter will be included into C-TMS to allow the Order Processing mechanism to be configured. The parameter will define whether the Day and Route field is provided from the WMS system or not. If not, C-TMS will plan the orders based on the provided delivery day of the order (earliest delivery date and time) and the delivery location code.

Scope

This change will be applied to system version 10.7

SET-UP

Menu Structure

A new form Milk Round Route Maintenance will be added under the Maintenance drop down.

Data

A new cost-centre based system parameter OMS_ADD_ORDER_TO_MILK will be created indicating auto planning onto milk round templates is required.

A new cost centre based system parameter MILK_ROUND_TRIP_GENERATION will be created indicating that empty stops should not be deleted from MILK ROUND trips. (Empty unused stops should be deleted when the trip is set to ACCEPTED status - note that this delete should be completed before the electronic manifest is generated and sent to Microlise)

A new cost centre based system parameter MILK_ROUND_ORDERS will be created indicating that orders without a day and route provided can still be planned onto trips using the early delivery date of the order and the delivery location.

MILK_ROUND_ROUTES table will be created.

SEQ_MILK_ROUND_ROUTES sequence will be created

MILK_ROUND_AUDIT table will be created.

SEQ_MILK_ROUND_AUDIT sequence will be created.

Records will be inserted into the IMP_RECORD_FORMAT, IMP_IMPORT_TYPE and IMP_FIELD tables to accommodate the CSV import of Milk Round Templates

FUNCTIONAL DESCRIPTION

CSV Processing

Two new tables will be created MILK_ROUND_ROUTES, and MILK_ROUND_AUDIT details of which are included in Appendix A. The IMP package will be amended to add a new function PROCESS_MILK_ROUND. The format of the CSV import file is as follows

The data contained within the CSV file must be validated to ensure that all locations/nodes already exist in C-TMS if an invalid location is found the complete template will be rejected no partial templates will be saved. Duplicate milk-round templates are not allowed. If an existing template name is received in the CSV import the day and route number will be checked. If these are also found to be duplicates the original will be updated to populate the status column to “I” (inactive) to indicate this route is no longer active and the new route will then be added. No trips will be created for routes which are inactive in the Trip Generation Process. For new records the status column will default to A.

The owning depot column must be a valid location and will be validated against the location table as type RDC. If an invalid record is found the template should be rejected. The owning depot will be considered the SU (load) location for the generated trips.

An effective date from will be contained in the import data if this value has not been populated the effective date from will default to the system date.

It is expected that the enterprise code in this instance will always be populated with the value “NR”. The value in the enterprise code will define the cost-centre that the template is uploaded for.

The drop sequence will be validated to ensure that no duplicate sequences are contained within a template if this is found to be the case the template will be rejected.

The Planned arrival and planned departure indicate the static time windows for the stop.

The despatch day contains a number which denotes the despatch day offset this value will normally be 0 indicating despatch on the day of the template.

The MILK_ROUND_ROUTES table also has an ID column which will be populated using a new database sequence (SEQ_MILK_ROUND_ROUTES) this will then be used as the primary key of the table.

All rejected records will be displayed with an appropriate error message to allow the user to correct errors and re-import the data in a new csv file. Each time a record is created or updated within the data import a record will be added to the MILK_ROUND_AUDIT table to record the action on this particular template. This table also has an ID column as its primary key which will be populated using another new database sequence (SEQ_MILK_ROUND_AUDIT).

Milk Round Maintenance

A new oracle form will be required to allow routes to be created amended and deleted manually. The form will be developed in the line with the current C-TMS formats.

On entry to the form, the Route Template canvas will be displayed and a suggested layout is shown below

This page will be used to display the details from the MILK_ROUND_ROUTES table. At the top of the screen there will be a day and route field which can be entered by the user and when the Refresh button is pressed the details of all templates corresponding to this day and route will be displayed. If no details are entered in the search fields when the refresh button is pressed all records will be displayed and will then be ordered and sequenced on the screen by day, route and template name. The information on this screen is display only and can not be edited.

There will be 5 buttons on the screen

Audit – Once selected this button will navigate to a new display only audit screen which will contain the audit information from the MILK_ROUND_AUDIT table for the currently selected route. The screen will contain an exit button which will return the user to the main canvas. A suggested layout is displayed below.

New – If this button is selected a new record screen will be displayed to enter the details of a new template or a new route or new stop into an existing template. A suggested layout is shown below.

Multiple rows can be captured and the details entered must be validated to ensure that a duplicate template or route or stop is not entered. A duplicate would be a template with the same template name, day and route number as an existing record.

If a duplicate record is entered an appropriate error message will be displayed asking the user to confirm if this is an additional stop in an existing template or a complete replacement for the existing template. If the template is a replacement the existing template records will be set to status “I” (Inactive). If a new stop is entered into an existing template the drop sequence must be validated to ensure that if a drop is inserted mid way through a template the drop sequences that occur after the insert must be updated.

The drop sequences can not be duplicated and must be in ascending order. A list of values should be provided for the location id and owning depot. If the effective date from is not entered this will default to the system date. The status of a new entry will be defaulted to A for active.

The location id and owning depot must be selected from the list of values and duplicate locations will not be allowed within a template. There will be three buttons on this screen

• Save will perform all validation and create the record(s).
• Cancel will exit back to the main screen without saving any changes.
• Close will return the user back to the main screen

Route Details – If this button is selected a new display only screen will be shown detailing all the stops on a given route in drop sequence order. A suggested Layout is shown below

The screen will contain a further two buttons

• Close will return to the main screen without saving any changes.
• Edit will display the details of the currently selected row in a new screen and allow the details to be amended performing the same validation as previously described. A suggested layout is displayed below

There will be three buttons on the screen

• Save will perform all required validation update the record and return to the main screen.
• Cancel will discard the changes and return to the main screen.
• Close will return the user to the main screen

Create Trips – If this button is selected a new screen will be displayed to allow the creation of all trips on a given day number for a future schedule. A suggested layout is shown below

The day field will indicate the day of the week the routes should be created for and must be between 01 and 07, the schedule should be a drop down list of at least 4 weeks of future schedules. There will be two buttons on the screen

• Create Trips – If this button is selected a check should first be performed to ascertain if there are already milk round trips created for this schedule and if found an appropriate error displayed. If no trips are found then all trips for the given day and schedule will be created by calling the trip generation functionality described in 3.3 Trip Generation. Once all trips have been created an information message should be displayed confirming trip creation.
• Exit- returns to the main screen

Exit - Will return the user to the menu screen

The action buttons at the top of each column will allow the data in each column to be sorted in ascending or descending order.