291356
DHL C-TMS
Order EDI & Order Quarantine
FUNCTIONAL SPECIFICATION - 10.7
21/10/11 - 3.0
Reference: FS 291356 MS-8KNFBJ
FUNCTIONAL OVERVIEW
Client Requirement
Consulting on message formats and implementation support. Scope covers DISC order flow and implementation of flow from spreadsheet input via DHL Link. All orders that fail validation in EDI will be available to be viewed and changed / corrected in interface errors. A reload function will be developed to allow orders to be re-validated and loaded into the order well.
Orders to be processed via DHL Link, or form a CSV file. Development of new Order correction facility to allow the correction and re-loading of new orders.
Solution
EDI Orders import (non Heavy):
Non Heavy Orders from DISC WMS will be received in an XML format file transferred and mapped via DHL Link. The orders will be presented to C-TMS in the OBSL TripOrder XML schema format.
The interface will be developed to allow the milk-round day and route code to be uploaded into C-TMS from DISC.
This order interface will provide orders for C-TMS once shipments have been picked and packed in DISC and the interface will contain the LPN pallet numbers for each product. The LPN number is an important update because this drives the final revenue calculation for the transport service charged to NR. The LPN numbers will need to be stored and displayed against the order item lines in C-TMS.
Although a requirement for Heavy orders which will be uploaded into C-TMS using the CSV import functionality, for completeness, the EDI flow will be modified to allow a requested vehicle type to be optionally interfaced. This will be satisfied by extending the OBSL XML schema to allow a vehicle type to be included at order header.
The C-TMS interface processing will be modified to created locations as ‘child’ addresses of the master MSP (Material Supply Point). This function covers the scenario where a delivery location is provided in the interface to the known main MSP code (node) but the address varies. Operationally, the driver might actually drop the delivery some miles away from the main MSP and this has to be catered for in terms of storing the actual delivery address down to postcode and association with the MSP. The location codes for the MSP addresses will be setup in C-TMS as for example NR_UG. The UG value defines the specific MSP. Child addresses created from the through the interface will be coded using the same master MSP code suffixed with a sequence number, for example NR_UG/00001.
Note that the master location code provided in the interface will be used to derive the appropriate delivery drop on the milk-round day and route, for example NR_UG. The suffix address will be used as the delivery address of the order and a stop for this will be generated in the milk-round trip. It is possible that, for example, 3 deliveries to NR_UG are provided as orders through the interface and if these all have different addresses, then 3 distinct DL stops will be generated into the milk-round trip.
DISC WMS will not create duplicate orders (amendments) through the interface to C-TMS. An order can be released from DISC to C-TMS over a number of days or weeks and these orders will have the same customer reference which will be stored in the Customer Ref field in C-TMS but will contain a different shipment number each time which will be stored in the Del Point Ref field in C-TMS.
To allow this processing a new EDI parameter (DUPLICATE_SHIPMENTS_ALLOWED) will be created within the EDI flow. If an order fails validation as a duplicate (Customer Reference already exists in C-TMS) and this parameter is set to ‘Y’ a further check will be made to ensure that the Del Point Ref field on the stored order is not the same as the shipment number on the interface record. If this check fails i.e. a duplicate is found the interface order will be rejected and processing will continue to the next order on the file. Otherwise, for these subsequent shipments against the original customer order, C-TMS will generate a numeric suffix to uniquely identify the order and store this against the Customer Ref in C-TMS ; the original Customer Ref will not have a suffix, then the subsequent shipment will be suffixed /02 then /03 and so on.
When order items are created the ITEM_IDENTIFIER (product code) will be validated to ensure that it exists in the PRD_PRODUCT_ITEM table (this table will be the product code master list maintained in C-TMS) and that the PRODUCT_TYPE and DU_TYPE for this item are correct for the order line record. This validation will depend an a new parameter setting against the customer account in C-TMS. For non Heavy orders interfaced from DISC, the product codes will be stored on the order but not validated against a master list. The Product Master list will be used to validate Heavy orders.
All other interface checks and validation will be performed as normal and any failures highlighted in the transactions in the interface errors screens.
Once the order is successfully loaded the PRODUCT_TYPE (commodity) will be updated on the order header according to the order lines. For non-Heavy, it is assumed that all orders will be received with product type AMBIENT.
When the EDI upload process for DISC orders completes, C-TMS will the auto-plan the order to the appropriate milk-round. A new system parameter will be included to support this development called OMS_ADD_ORDER_TO_MILK. A new field will be added to CUSTOMERS so that the non-Heavy account can be flagged for auto-planning.
Orders Length:
There is a requirement to capture and store a length value on the order line of an order optionally. This is to satisfy a requirement for Rails. The planner needs to have an understanding of the rail lengths ordered as there are some constraints in planning certain different lengths together on one vehicle. The orders screen has the ability to flip the volume field by click to a field called footprint on the form. This footprint field will be used to store the length value and the label will be configured for NR to be Length. This information will be made available for Heavy orders through the CSV upload.
Order correction from Interface Errors:
Orders that fail interface validation and so are not uploaded into C-TMS can be viewed in the Interface Errors XML Orders tab; this tab page will be modified to include a button titled Correct Errors and a button titled Re-Process.
These buttons will be displayed depending on the value of a new EDI flow based system parameter. When this Correct Errors button is selected, the user will highlight the order containing the errors they wish to correct by mouse click to bring the specific row into focus. Once this button is selected the form will navigate to a new screen which will display all errors for the selected order and order line and order items. The user will then be able to selectively overtype the fields with new values. The new values entry will be supported by list of values against master data where appropriate. The new values will be validated immediately and only valid new entries will be saved. Once the user has completed any changes necessary, the changes screen will be closed and the user can then select the reprocess button to upload the order into C-TMS.
Note that it will be possible to re-process orders that have failed in the interface to cover the scenario where for example the resolution to the problem was to add in additional master data, for example the order interfaced was for a DU type that was unknown to C-TMS.
CSV Import:
Heavy (and Ad-hoc etc.) orders will be uploaded into C-TMS using the CSV imports functionality. Orders placed and authorised in the DHL provided web order portal will also be uploaded into C-TMS using the same functionality.
The CSV import functionality relies on a template being maintained in C-TMS master data that describes the data in each column of the presented CSV for upload. Many different variations of template can be created, each named separately, to allow differently formatted CSV formats to be uploaded. Ideally though, the order data received by DHL Control Tower should be presented to C-TMS in a consistent format regardless of source.
The CSV import functionality for orders will be developed and enhanced to allow the following additional capabilities;
The CSV file will include values to define the order header, order line and order item.
The child location processing described above for EDI needs to be managed and processed consistently for data uploaded from CSV imports.
The CSV file import will provide new fields to allow the NR cost-centre and the requested vehicle type to be uploaded into C-TMS. The NR cost centre will be stored as an additional reference for the order. The CSV upload will be modified to write to the Additional References tab in orders for this data. The requested vehicle will saved in a new field added to the order header in C-TMS and will be made visible in the order detail screen and the planning screens.
The CSV import function will also be modified to process the subsequent shipments and customer reference suffix and duplication checking as described above for EDI.
When orders are uploaded using the CSV import function, the PRODUCT_TYPE (commodity) of the order lines will be updated against the order header.
Note that standard error checking and validation will be used to upload CSV order files into C-TMS. Any failures are notified and a failures CSV file created of the invalid records. The failures CSV file can be reworked on the user desktop and re-submitted to C-TMS.
Scope
This change will be applied to system version 10.7.0
SET-UP
Pre-Requisites
MS-8KNH8N Milk Round Routes
MS-8KNFRL Order Addresses & DU & Del Type
Implementation Advice
The EDI maintenance screen will be used to define the new Orders EDI flow for Network rail.
The super user who sets up the EDI flow will be responsible for assigning the following four parameter records, all set to Y.
A super user will be responsible for setting the values of two new cost centre parameters called OMS_ADD_ORDER_TO_MILK and ORD_LINE_LENGTH. The parameter values will be set in the System Parameter Maintenance screen as displayed below.
FUNCTIONAL DESCRIPTION
Non Heavy Order records will be received from DISC to be loaded into C-TMS. Non Heavy orders will be received in XML format, transferred and mapped via DHL Link. Heavy orders will be received via manual CSV import. Orders will be identified as Heavy and Non-Heavy from the CUSTOMER stored on the order header.
New Order Information
There is a requirement for new fields to be created at Order Header and Line. The interface schema file (XSD) will be amended to include the following new fields specifically for Network Rail and fields will be created in the relevant order tables (see 291358 MS-AKNFRL Order Addresses and Du and Del Type):
To process the new fields added to the interface schema, additional corresponding new fields will be added to the Interface control tables:
Order Header Control Table
The route code and route day will be received in one field in the interface files, ORDER_HEADER_TMS.ROUTE_CODE. Part of the interface process will be to extract the data for each and populate in the separate interface fields ROUTE_DAY and ROUTE_CODE.
The orders comments and contacts will relate to the order FROM_LOC or TO_LOC locations. When comments and contacts are received the data will be written to a new SCH_ORD_INFORMATION table keyed by the OMS_REF and LOCATION_ID.
Order Details Control Table
For non Heavy orders, the order interface file will be processed and generated from DISC once product is picked and packed. The interface file will allow the pallet numbers (LPN) to be saved against order lines as the order is initially created in C-TMS. Note one large order could be packed to many pallets so many LPN per order. Many small orders can be packed to one pallet.
Order Contracts Control Table
The value of the address_contact_type can be O or L in the interface. This value will indicate where the comment and contact details should be stored. If the type is set to L, the contact information will be processed as usual and added to the master location record. If the type is set to O, the contact level information (forename, surname, number, email, job) will be loaded into the new SCH_ORD_INFORMATION table with the OMS_REF and LOCATION_ID. This new feature allows comments and contacts to be stored against an order for the either the collection location or delivery location or both.
Comment and contact records will be populated in the following format on the new SCH_ORD_INFORMATION table:
Order Tasks Control Table
The TASK_NAME will identify the type of order tasks that are being sent through the interface. For Network Rail, the task name will be set to SERVICES and the data will be mapped to the SCH_ORD_SERVICES table.
Locations
The interface will be amended to process child addresses sent in from DISC. A child address will be identified as having the same location id as the parent address (MSP) but the address details will be different.
An example of the Parent (MSP) / Child data sent in XML
The parent (MSP) and child records will be uploaded from DISC via the XML interface with the same Location id. There are three checks that the system will perform before the location is processed and saved to the C-TMS database:
C-TMS will first establish if the location id already exists in C-TMS. Where a location does not exist, it will be created based on the existing EDI parameter CREATE_UNKNOWN_LOCS.
Where the location already exists in C-TMS, based on the new EDI parameter CHILD_LOCATIONS, C-TMS will be required to identify whether the location details in the XML file differ from the parent location details stored on C-TMS.
If the details are the same, then the order is to be delivered to the parent location.
Where the details differ, the order is to be delivered to a child location which must be created in C-TMS. However, the system must first check if the child location has previously been created in C-TMS. Where the child location has been created, the location id on the delivery location will be updated to the child location id.
Child locations will be created with location ids which are based on the parent location id. In the data example above, the child location will be created with the location id NR_MK/000001. The next time a child location is received for NR_MK, it will be created as NR_MK/000002.
All child records will be created based on the location id of the parent record followed by a sequence. The sequence will be incremented per parent location
Order Validation
Currently in C-TMS, if an order is uploaded with an external reference which already exists in C-TMS, the upload of that order will fail (or will be interpreted as an amendment depending on the configuration of the interface flow). Orders can be released from DISC to C-TMS over a number of days and these orders will have the same external reference but different shipment numbers.
To allow these orders to load, a new EDI parameter will be created called DUPLICATE_SHIPMENTS_ALLOWED. Where an external reference is found to already exist in C-TMS, if this parameter has been set to Y, the system will continue processing the record and will check the value of DEL_POINT_REF.
The DEL_POINT_REF field will store the value of the shipment and if this is unique to the CUSTOMER_REF, the system will allow the order to be processed with an existing EXTERNAL_REF value. If this check finds the DEL_POINT_REF is not unique, the order will fail to be processed and considered a duplicate.
Where more than one shipment is processed for an external_ref, the external_ref will be altered in C-TMS as shown in the table below:
The external reference will be retained for the original order processed on C-TMS. For all subsequent orders with the same EXTERNAL_REF, the external reference will be amended to include a suffix.
Order items will be validated to ensure the ITEM_IDENTIFIER exists in the PRD_PRODUCT_ITEM table depending on configuration of the customer account. A new parameter will be included on the customer master screen that will allow validation of products in orders against the product master to be performed or not. The system will also check the DU TYPE and PRODUCT TYPE are valid for the order line record by reference to the respective master data maintained in C-TMS.
When the order is successfully validated and uploaded into C-TMS, the product_type on the order header will be updated based on the Product Type of the lines. Non Heavy orders are expected to be received with the product type Ambient. If the order has multiple order lines, each with a different product type, the order header value will be set as ‘MIXED’.
Milkround Processing
The Milk Round is a template of fixed routes used for generating trips each day. All fixed routes will be created using MSP locations. Where an orders delivery location is a child location, to schedule the order, C-TMS will find the route which references the parent (MSP) location and create a new stop in the trip for any / each child location.
Milk Round routes will be identified based on the MSP location, but stops will be created based on the delivery locations, which may be child locations.
A new cost centre parameter called OMS_ADD_ORDER_TO_MILK will be created and will control which cost centres are available for auto planning. A new field will be added to the customer maintenance parameter screen called Auto_Planning. The field will be displayed as a flag in the parameters tab on the customer maintenance screen and will allow users to control which customers are considered for auto planning to Milkround Templates.
If the cost centre and customer indicate that the order can be auto planned, a final check will be run to ensure collection and delivery locations (FROM_LOC and TO_LOC) have latitude and longitude coordinates value set. Latitude and Longitude will be automatically set by existing C-TMS integration with NAVTEQ to support the planning logic.
Where the locations are missing the lat long information (NAVTEQ fails to provide the information), the order will not be auto planned and will be left unscheduled in the order well for manual intervention.
Before auto planning starts, the system will check that the Milk Round has been processed for the SCHED_NAME on the order. This check will be based on a count of MFR prefixed trips for the specified schedule. If there are no trips available, the system will call the Milk round process, passing in the ROUTE_DAY, SCHED_NAME and FROM_LOC from the order table. The procedure to create the Milk Round is stored in the RTE package, called Schedule_Milkround. This logic is designed to check whether the trips defined by the milk round template have already been generated for the schedule. If they haven’t, the process will generate all the trips so orders can be assigned to them. Operationally, the schedule of milk round trips might be created some days in advance of execution to allow manual planning of non DISC orders into the milk round trips.
C-TMS will auto plan all orders with the relevant cost centre, customer and lat long information. First C-TMS will identify if a valid trip exists. A valid trip will be identified using the Schedule name, concatenated ROUTE_DAY and ROUTE_CODE and TRIP_ID starting with MFR.
The above checks are based on the schedule name on the order being derived from the Early Collection date. It will also be necessary to compare the from location of the order with the owning depot on the trip, to ensure segregation is maintained if in future another warehouse location is introduced. Currently, all DISC orders are pick, packed and despatched from the one location at Worcester.
C-TMS trips generated from the Milk Round processing will be pre-fixed with MFR-.
If a suitable trip is identified, TRM.ADD_ORD_TO_TRIP will be called to schedule the order onto the trip. VALIDATE_TRIP will be called to ensure the Order does not cause any issues on the trip.
Where a suitable trip cannot be found, the order will remain unscheduled in the order well and will be available for manual planning
Order Length (Heavy)
A length value will optionally be interfaced at order line level; this is to satisfy requirements for rails, where the planner needs to know rail lengths ordered as an operational constraints exist when planning certain incompatible lengths on vehicles. C-TMS will be developed to allow the lengths value to be visible but will not constrain the planner actions in any way to satisfy and length loading compatibility – this will be a manual planning decision.
Currently, in the order lines, users are able to right click the Volume field to enter Footprint information. Based on a new Cost Centre parameter ORD_LINE_LENGTH, the FOOTPRINT field in Order lines will be used to store the length data and the label will be updated from Footprint to Length.
Currently, the footprint is summed at Order Header level as shown in the screen shot below:
Based on the value of the cost centre parameter, ORD_LINE_LENGTH, the TOTAL_FOOTPRINT field at Order header will be updated to the MAX(LENGTH) rather than a sum of the length fields.
This will allow planners to know the largest rail length on the order. To aid the planning process, TOTAL_FOOTPRINT will be added to the configurable items in the Order well on the Trip Manipulation, Trip Planning and Execution screens. Based on the value of the cost centre parameter ORD_LINE_LENTH, the TOTAL_FOOTPRINT field will be labelled MAX_LENGTH in the configurable layout and on the order well.
Order Correction From Interface Errors Screen
Currently any order failures are available to view in the XML_Orders tab of the Interface Errors screen. Order details received through EDI are at Line level or Item level, which is indicated by a D for lines or an S for items.
Network Rail DISC interface will be send order details at line and item level, any failures will be report at both levels. To achieve this, the DETAIL_TYPE from the INT_XML_ORD_DETAILS table will be added to the screen at detail level, after the Line no. This will allow users to view both Line and items records and any associated errors
The DETAIL_TYPE will be displayed based on the value of a new EDI flow based system parameter. This system parameter will also be used to control the display of two new command buttons, ‘Correct Errors’ and ‘Re-Process’.
Correct Errors
In the screen shot of the interface errors screen above, the user can see that one order header record is selected and this record is highlighted yellow. When a user selects the new ‘Correct Errors’ button, a new screen will be displayed which will allow any errors to be corrected.
This will display all the information which has been stored in the interface control tables for the failed orders. Users will be able to overwrite any value which has failed validation and caused the order processing to fail.
For one order header record there will be multiple detail records and the screen will be developed to show a single header record and the related detail records.
Fields that are validated on the values of reference master tables in C-TMS will be supported by lists of values lookups. Users will only be able to select data from the list of values, for example Product Type, Du Type, Customer etc.
The new screen will include a Save and Close button. The close button will return the user to the XML_Orders tab of the Interface Errors screen.
Re-Process
When the user selects the new Re-Process button, the re-validate and reprocess functionality will be applied to the current record highlighted in the XML_Orders tab.
The status of the record in the Interface header control table will be set to New, ensuring the record is processed on the next run of the EDI flow.
When an order has been successfully processed, the validate order code will be called and if this process returns no errors the order will be loaded into C-TMS and considered for auto planning.
CSV Import
The CSV data will be sent to C-TMS as Order headers, lines and items. This is the first example of CSV data being received in this format. Currently users send order CSV data in Order and Lines OR Orders and items.
To accommodate the change in structure a new IMPORT TYPE called ORD_LINE_ITEM and 5 new record types will be created, ORDER, ORD_LINES, ITEMS, ORD_TASKS and ORD_ SUB_REFS. The ORDER, ORD_LINES and ITEMS will be based on the format of the Order_and_Line and Line record formats.
The data in the CSV file must be received in the following structure:
The REC_TYPE field will be populated in the first column of the CSV file and will indicate the record type for each record.
Currently, if a CSV import provides item data, the line data is created as a consolidation of the items. This consolidation will not be required as the line records are being provided in the data file.
A new procedure will be added to the IMP package called PROCESS_ORD_LINE_ITEM. In addition to processing the order, line and item records, the data will be validated in the same way as described for the EDI (See section 3.2 and 3.3) The validation will include:
- Locations and the creation of Child Locations
- Order Location Contacts and Comments
- Order Tasks mapped to services
- Duplicate Customer References
- Line and Item validation (Product type and Du type)
- Product Type update on the Order Header
When uploading a CSV format order file, validation will check the content. Any validation failures will be notified in a generated CSV format results file and any failures will be included in a failures file in the same format as the file was presented in. The failures file can then be amended and changed on the user desktop and re-submitted to C-TMS.
In addition to the new fields added to the database being available to import to, the following fields will also be available
LPN Order Update via EDI
The order interface will send an order to C-TMS once as a shipment is picked and packed in DISC. There will no update / amend flow.
Each non Heavy order will be created in C-TMS and an order item record will be created for each product code and pallet Id combination.
Uploading the data in this format means;
- Order items within an order could have a repeating product code (item_identifier) each with a different pallet Id – relevant for pallet picks in DISC.
- Order Items within an order could be associated with the same pallet Id – relevant for part picks to same pallet
- A pallet Id could feature on item lines across a number of order – relevant where small orders are picked to a single pallet.
Any combination of these order line configurations can be possible for an order uploaded into C-TMS from DISC.
C-TMS will automatically create an order line to define the media and qty. Two DU types will be used to define media, namely ‘PALLET Chargeable’ and ‘PALLET non-Chargeable’ and these will be defined as parameter attributes against the customer (Non Heavy).
C-TMS will count the distinct pallet Ids within the order item lines and create a PALLET Chargeable DU order line for the qty. If the pallet Id has already been included in a PALLET Chargeable qty on another order, C-TMS will create a PALLET non-Chargeable DU order line and set the qty accordingly – usually 1 but could be more than 1.
For these order lines for non Heavy customer, the product type will always be set to AMBIENT and default this value from the order item records uploaded from the DISC interface.
The rating engine will be configured to only rate for PALLET Chargeable DU type on order lines.
Note that by comparison, for Heavy orders, the CSV upload format will allow order lines in orders to be generated with a specific product type (corresponding to the commodity) and the DU type will default automatically according to the product type. As an example, the product type provided in the interface might be ‘SLEEPSTL’ meaning ‘Sleepers Steel’ and the default DU associated might be ‘SLEEPER’. The CSV upload format will then allow the order item line to be generated with a specific product code that will be validated against product master data. As an example the product code might be ‘0057/068567/001’ with an item description of ‘SLEEPER/Steel Sleeper W560 UTX-S 113a’
Consolidation at Delivery Stop
Orders will be consolidated at delivery stop and re-rated based on the combined quantities. Network Rail rate orders by lines to capture chargeable and non-chargeable pallets. Currently the system consolidates orders at header level and new functionality will be required to rate orders by line level. When consolidating a group of orders for delivery, the system will refer to the orders as a shipment.
Two new procedures will be created in the Rating engine to firstly calculate the quantities per du type in the shipment, then to rate the shipment and apportion the revenue back to the orders in the shipment. An example of the how the new consolidation must work is described below:
has been returned, this must be apportioned back to the relevant orders. The relevant orders are identified as those orders that have contributed to the rate.
In the example above the revenue should be apportioned to orders 12345 and 67891, as these are the only orders which have chargeable pallets. The rating should be applied as follows:
