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.

Trip Generation

Trips will be planned using the pre-defined milk round template. The RTE package will be amended to include a new process SCHEDULE_MILK_ROUNDS. This process will receive the day number i.e. 01 for Monday, 02 for Tuesday and so on, the schedule name (this will always be the next days schedule) and the OWNING DEPOT for which the milk rounds should be created.

The process will then obtain the details of all the relevant milk rounds for the specified day and one by one create a trip and trip stops for each stop on the specified day schedule. As trips are created they will be assigned a unique trip ID prefixed with “MFR” the arrival and departure times of the stops will be taken from the arrival and departure times on the milk round template for each stop and the stop updated to indicate fixed times. The trips will be created at PLANNED status. The route code will also be stored against the trip and can be used to identify the route template used for the trip. A further development “292354 – PM-8LZMNV Visibility of Route Code” will depending on the value of a system parameter add the route code to the trip id in the trip screens to aid the visibility of route codes.

This process will be submitted as a scheduled job which will run at a specified time each day and create all the trips for the next day this will enable the order import to auto plan the orders onto the correct routes. The scheduled job will be specified using the EDI Maintenance screen which will control the run times of the jobs, this can set either as a regular interval or a specified time see examples below

A cost centre based system parameter will be created called MILK_ROUND_TRIP_GENERATION. If this parameter is set to Y and the trip is at PLANNED status the standard trip validation functionality will not delete unused stops on these trips as future orders may still need to be planned which will use these stops. When creating trips, if a trip is found for the route code on the specified schedule which has been created manually the process will not create trips for that template.

Order Processing

When orders are received via the EDI order interface they will be stamped with the Day, Route and Node. The order import package will be amended and based on the value of a cost centre system parameter OMS_ADD_ORDER_TO_MILK a new process will plan these orders onto trips.

ADD_ORD_TO_MILK_ROUND to the RTE package.

The function will receive an OMS_REF, SCHED_NAME, ROUTE CODE and NODE as input parameters. This information will then be used to correctly identify which milk round template the order should be added to. If no valid trip is found the order will not be planned and will remain in the order well for manual planning. If a valid trip is found then the order will be planned onto this trip. If an order is received to be planned with a valid Day and ROUTE but the NODE does not exist in C-TMS the order will be added to the trip and a new stop created for the required NODE as the last stop on the trip.

For more detail on the creation of new addresses refer to - 291356 MS-8KNFBJ Order EDI and Order Quarantine

If an order is received to be planned which has no day number or route code information the value of the MILK_ROUND_ORDERS parameter will be checked, if this value has not been set or is set to N the order will not be planned onto a trip. If the value of the parameter is set to Y the early delivery date of the order will be used to define the day number and schedule the order should be planned to. This information in conjunction with the delivery location of the order will then be used to identify the correct trip to allocate the order to. If no trip can be found the order will remain in the order well for manual planning.

Manual Planning

The trip planning and trip manipulation screens will be amended to add a new right click option to the order well to allow the planning of orders onto milk round routes. The value of the system parameter OMS_ADD_ORDER_TO_MILK will be checked to ensure that milk round processing is authorised for the selected orders, if milk round processing is not allowed an appropriate message will be displayed. If milk round processing is allowed the orders will be planned and the RTE.ADD_ORD_TO_MILK_ROUND procedure will be called to add the orders to the correct template. This will allow orders to be manually planned onto existing milk round trips.

REFERENCES

Ref No	Document Title & ID	Version	Date
1	EST-291397 MS-8KNH8N Milk round Processing	1.0	16/09/11

DOCUMENT HISTORY

Version	Date	Status	Reason	Initials
0.1	03/10/11	Draft	Initial version	CAK
0.2	04/10/11	Draft	Reviewed	MJC
0.3	05/10/11	Draft	Revised	CAK
0.4	05/10/11	Draft	Reviewed	MJC
0.5	06/10/11	Draft	Revised	CAK
1.0	06/10/11	Issue	Reviewed and Issued	DJM
1.1	11/10/11	Draft	Revised	CAK
1.2	26/10/11	Draft	Revised	CAK
1.3	27/10/11	Draft	Revised	CAK
2.0	27/10/11	Issue	Reviewed and Issued	MJC

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager