260498

260498 - JB-7NHG8Z/ Line Level Integration

Copyright OBS Logistics © 2009

The information contained herein is the property of OBS Logistics and is supplied without liability for errors or omissions. No part may be reproduced or used except as authorised by contract or other written permission. The copyright and foregoing restriction on reproduction and use extend to all media in which the information may be embodied

Functional Overview

Client Requirement

LOTS Phase 2 : MTS Changes

• Change to Order interface between Bawtry Unison and MTS to include product line information.
• Allow storage of product line information into MTS
• Generate flag within MTS to identify Microlise client.

Phase 2 of LOTS requires product line information to be entered into MTS for integration with Microlise. We require product code, description and case qty. The understanding is that the product line information will be a pass through for and therefore will not need to be stored in MTS in any static data table.

Solution

The scope of MTS modifications covered in this estimate to support LOTS phase 2 developments is as follows and is inclusive of format changes to the XML message format and Microlise EDI flows and related processing in MTS;

• New Unison WMS orders flow loaded into MTS including line level information
• Modified MTS order updates (booking events) generated to LOTS
• New Trip and Trip updates generated simultaneously to LOTS and Microlise
• New Trip execution updates flow from Microlise to MTS
• Modified execution updates from LOTS to MTS
• Modified POD updates from MTS to LOTS (triggered from Tokairo or manual POD flag entry in MTS)

1 New Unison WMS orders flow loaded into MTS including line level information

Currently Unison orders are transferred into MTS using a CSV format data flow. This data transfer is controlled manually within Unison. Files of orders are generated in a Unison CSV format which is transformed by ESI into an MTS CSV format. MTS receives the orders files and creates transport orders identified uniquely by the customer (owner in Unison) and SO reference. This flow will remain unchanged and continue to be used as required in Consumer Networks controlled by site users.

A new Unison to MTS orders flow will be developed and data exchanged using the newly designed common XML format to support LOTS. A control screen in Unison will enable this interface separately for LOTS and for MTS. It is assumed that initially this will be configured so that Unison will send order and order update messages only for Reckitts to LOTS and for all owner customers managed from Bawtry to MTS. The implication being that the existing CSV will be superseded for Bawtry but still available for other sites. In simple terms, Bawtry won’t need to run the CSV interface once the new XML flow is enabled. Other sites could take advantage of the new XML flow using the Unison configuration screen to enable functionality described above.

The XML flow will be automatically triggered from Unison from a configured set of WMS statuses and will contain the line level segment required to load order stock line details into MTS. The supported WMS status trigger points are any of created, allocated, pick listed, pick confirmed, despatched and cancelled.

MTS will accept order updates up until scheduled on an ACCEPTED status trip – this means order updates are rejected beyond PLANNED trip status. Any order update received will delete and recreate the item line data and update the DU level data in MTS to represent the latest position from the WMS process.

The MTS data model will be extended to allow stock line details to be stored. A new table will be included called SCH_ORD_ITEMS with the following columns;

OMS_REF (MTS unique order number) CUSTOMER (customer code) EXTERNAL_REF (SO customer reference) PRODUCT_TYPE (product code e.g. AMBIENT) ITEM_IDENTIFIER (Unison sock code) ITEM_AKA_CODE (alternative stock code of retailer) ITEM_DESCRIPTION (stock description) ITEM_FACTOR (UOM, e.g. CASE) LIFTS STACK QTY_ORDERED QTY_TO_DELIVER QTY_DELIVERED WEIGHT VOLUME

A subordinate table to this will be created called SCH_ORD_ITEMS_REASONS and this will allow MTS to store multiple reason code and qty discrepancies. This change is in anticipation of MTS receiving delivery confirmation information from Microlise at item line level.

OMS_REF (MTS unique order number) CUSTOMER (customer code) EXTERNAL_REF (SO customer reference) PRODUCT_TYPE (product code e.g. AMBIENT) ITEM_IDENTIFIER (Unison sock code) REASON_CODE (Reason discrepancy) QTY (plus or minus discrepancy)

MTS will be modified to look for XML orders files on a polling cycle. The data will be loaded into Interface error tables as normal (although extended to carry the additional line level data) and validated using existing rules. If successfully validated the order records, order line and items will be written to MTS order tables. A unique OMS reference will be generated as normal.

The XML order format for Consumer will carry ‘order details’ at line level which means the MTS interface will be modified to calculate DU level information for the order. This will be done using the message product type (order line for each), default DU type for the product, a calculated DU QTY and sum of item line weight, volume and case qty. RPE will be derived as now from the calculated DU QTY. DU QTY will be calculated by summing the number of lifts (and rounding up) for each stack value.

The MTS Order Details screen will be modified to include a new tab for Items. The view will be designed to look like the ‘body’ of the delivery note and will show stock code, alternative stock code, description, UOM, ordered qty, to deliver qty and delivered qty. The view will allow right click on a stock line to drill down to see a list of discrepancy reasons and plus or minus qty counts.

2 Modified MTS order updates (booking events) generated to LOTS

For LOTS phase 1, MTS creates a message to update the order (SO reference) in LOTS when;

Booking in flag is ticked (denoting the delivery time window is agreed at delivery location) Or The order late delivery date/time is changed Or The order is cancelled on MTS

These trigger points will remain unchanged. The message creation processing will be modified to generate the data in the new XML format using the order segments (not trips segments as these events are before planning).

3 New Trip and Trip updates generated simultaneously to LOTS and Microlise

The new XML format has been designed to allow a complete Trip to be messaged from MTS to either LOTS or Microlise or both simultaneously. The structure of the message allows MTS to send trip header and detail, stop header and detail, order header and order detail (inclusive of either order line stock items or DU level) data to LOTS and Microlise.

The triggers to send a trip message will differ between LOTS requirements and Microlise requirements; however MTS will always send a complete data set of all the trip stops on a trip. For LOTS only the orders for LOTS enabled owner customers will be included for the relevant stops. For Microlise, all customer orders will be included.

For LOTS, MTS will trigger a trip message when LOTS enabled customer orders (or rebooked orders, customer collections and factory collections) are planned onto a trip (scheduled or sched_coll) And When a carrier is assigned or changed on the trip. And When a Vehicle is assigned that is Microlise enabled (LOTS needs to know which trips are Microlise executed and this will not be known until resource is allocated to a trip).

Note that a trip message sent to LOTS could represent a mixed customer load with multiple delivery stops, for example (where SC JOHNSON is not LOTS enabled and RECKITTS is LOTS enabled);

Stop 1 has SC JOHNSON order – only the stop data segments will be sent to LOTS – no order detail Stop 2 has RECKITT order – stop and order segments sent to LOTS including item line level Stop 3 has SC JOHNSON and RECKITT order – stop segment sent but only the RECKITTS order detail.

For Microlise a mechanism is needed to define which trips are messaged from MTS. This largely depends on which vehicles are enabled in the Bawtry fleet. The resource maintenance in MTS will be modified to allow each vehicle equipped (tractor) to be flagged accordingly with the execution enabling system name. This means trip messages will only be generated for trips assigned to Microlise equipped vehicles. This would include where a Bawtry fleet vehicle might be sub-contracted to another DHL site.

The trigger to generate a trip message to Microlise will be;

A trip set to EN-ROUTE status for a Microlise enabled vehicle

The XML message format enables MTS to send either item line level or DU level data (as the order detail segment of an order). MTS will populate the data structure as item line if available and otherwise at DU level.

4 New Trip execution updates flow from Microlise to MTS

Microlise will send execution updates to both LOTS and MTS. Essentially Microlise will send the same trip message back to MTS supplemented with stop and order detail actual times and any non-conformance and reason codes.

The execution updates will provide actual departure from trip startup (SU) location and will accordingly set the trip status to EN_ROUTE. Actual arrival and departure at trip stops will be captured. The actual qty delivered of the order details (whether provided originally by MTS at item level or DU level) and multiple non-conformance qty and reason codes will also be captured. These updates will be visible in MTS in the new orders detail tab (for item line qty) and the order tracking and debrief screens (for actual stop times) in MTS.

Microlise will not provide actual DU qty (whether expressed as lifts or units delivered) for MTS if stock line level data is being used by Microlise to confirm deliveries as will be the situation for Consumer Networks. Similarly, Microlise will provide non-conformance updates at stock line level for Consumer Networks and non-conformance at DU level in MTS will either not be entered or completed manually.

The trip update message will be pushed to the MTS server by ESI. MTS will poll for new trip files and these will be loaded and validated through a new interface errors table and screen.

5 Modified execution updates from LOTS to MTS

Actual trip stop times, delivered quantities and non-conformance reason codes can be manually entered in LOTS. This will usually be for non Microlise enabled trips. This debrief information is messaged to MTS and provides data to update as described in the section above.

This functionality already exists in MTS from Phase 1 LOTS but will need to be enhanced to align with the new XML data formatting.

6 Modified POD updates from MTS to LOTS (triggered from Tokairo or manual POD flag entry in MTS)

Similarly, the existing data flow of POD flag from MTS to LOTS will need to be enhanced to align with the new XML data formatting.

The trigger for this message from MTS to LOTS is simply based on the delivery POD flag being set to Y for the order. This can be set automatically from the in message into MTS from Tokairo or set manually through debrief functionality within MTS.

Microlise data will not update the MTS POD flag so as not to interfere with the Tokairo – MTS – LOTS updates.

Scope

This change will be applied to system version 10.6.

Data

System Parameters are required for the Inbound flow.

WMS_INBOUND_PATH - Area where files are placed to be processed WMS_INBOUND_ARCH - Area where processed files are placed. WMS_INBOUND_FAIL - Area where failed files are placed. WMS_INBOUND_ORD_IDENTIFIER - Filename Prefix for Order files WMS_INBOUND_ALC_IDENTIFIER - Filename Prefix for Allocate files WMS_INBOUND_MAR_IDENTIFIER - Filename Prefix for Mar files WMS_INBOUND_LOA_IDENTIFIER - Filename Prefix for Load files WMS_INBOUND_CAN_IDENTIFIER - Filename Prefix for Cancellation files WMS_INBOUND_LISTING_NAME - List which will hold files to be processed. WMS_LISTING_SCRIPT_NAME - Script will process the files

New sequence seq_int_xml_ord will be created to add a unique reference to the new tables.

New control table INT_XML_CONTROL will be used by triggers to inform the database job that an outbound XML is required.

This table will have a trigger to populate some fields including the unique identier populated by the new sequence seq_int_xml.

Also new system parameters required for the outbound flow to LOTS :-

LOTS_OUTBOUND_PATH - Area where files are placed once processed LOTS_OUTBOUND_ARCH - Area for a safe copy of processed files. LOTS_OUTBOUND_FAIL - Area where failed files are placed.

Also new system parameters required for the outbound flow to Microlise :-

MIC_OUTBOUND_PATH - Area where files are placed once processed MIC_OUTBOUND_ARCH - Area for a safe copy of processed files. MIC_OUTBOUND_FAIL - Area where failed files are placed.

If the file is to be automatically FTP’d out to ESI rather than waiting on ESI to collect it then the additional parameters are also required  :-

MIC_FTP_DESTINATION_IP_ADDRESS - IP of destination machine MIC_FTP_DESTINATION_PORT - Port on destination machine MIC_FTP_DESTINATION_DIRECTORY - Directory on destination machine MIC_FTP_DESTINATION_USERNAME - Username to logon to destination machine MIC_FTP_DESTINATION_PASSWORD - Password for the user logon

Inbound from Microlise :-

MIC_INBOUND_PATH -Path for yet to be processed XML files from Microlise MIC_INBOUND_FAIL -Path for Microlise XML failures MIC_INBOUND_ARCH -Path for Microlise XML archiving MIC_INBOUND_IDENTIFIER -Pattern for MIC Trip Inbound MIC_INBOUND_LISTING_NAME -Filename for list of files in directory for MIC XML MIC_LISTING_SCRIPT_NAME -Script name for MIC XML Trip Inbound

Inbound from LOTS :-

LOTS_INBOUND_PATH -Path for yet to be processed XML files from Microlise LOTS_INBOUND_FAIL -Path for Microlise XML failures LOTS_INBOUND_ARCH -Path for Microlise XML archiving LOTS_INBOUND_IDENTIFIER -Pattern for MIC Trip Inbound LOTS_INBOUND_LISTING_NAME -Filename for list of files in directory for MIC XML LOTS_LISTING_SCRIPT_NAME -Script name foe MIC XML Trip Inbound