260498: Difference between revisions
Middletong (talk | contribs) (New page: ==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 er...) |
Middletong (talk | contribs) |
||
(2 intermediate revisions by the same user not shown) | |||
Line 200: | Line 200: | ||
LOTS_INBOUND_ARCH -Path for Microlise XML archiving | 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 | 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 | ||
= Functional Description = | |||
== File Retrival == | |||
The inbound files will be placed in the directory specified in the system registry setting WMS_INBOUND_PATH. By default this will be /webint/<dbname>/interface/WMS/IN. | |||
[[Image:260498_1.png]] | |||
A database job will be set up to run at a set interval (by default this is every 5 minutes). The job will call the new package INT_XML passing in the required parameters. These parameters have been detailed above. | |||
Within INT_XML a call will be made to the existing DP_FILEHANDLING package. This package will retrieve the files from the server so they can be processed into MTS. | |||
== Data Extract == | |||
Once the files have been retrieved the data will be extracted using XML_EXTRACT. The data will then be validated and stored in INT_XML_ORD_HEADER and INT_XML_ORD_DETAIL. | |||
There are a number of fields that are validated before the file can be loaded successfully. | |||
Early Available Date - Date must be valid | |||
Late Available Date - Date must be valid | |||
Early Delivery Date - Date must be valid | |||
Late Delivery Date - Date must be valid | |||
Booking Date - Date must be valid | |||
From Location - Cannot be Null | |||
To Location - Cannot be Null | |||
Customer - Must exist in MTS | |||
Cost Centre - Must exist in MTS | |||
Source System - Must exist in MTS | |||
Delivery Type - Must exist in MTS | |||
Group Name - Must exist in MTS | |||
Each order in the file will be validated separately, so if one order fails validation the others will continue to load. | |||
If the order passes validation a record will be inserted into INT_XML_ORD_HEADER, and one or more records will be inserted into INT_XML_ORD_DETAIL. | |||
== Order Creation == | |||
Once the data from the file has been loaded into the interface table orders can be created. One order is created for each valid INT_XML_ORD_HEADER record. | |||
If the file is of type ORD a new order will be created. An OMS ref will be generated for each order and a schedule created if a suitable one does not already exist. | |||
If the file is of type ALC, MAR or LOA then the order within the file will be update. The External Ref (SO_REF) will be used to find the existing order in MTS. If the order does not exist an appropriate error will be displayed. If the order does exist the order will be updated with the new data from the file. | |||
If the file is of type CAN, the order will be cancelled. Again the External Ref will be used to locate the file in MTS, and the order status will be set to Cancelled. No further changes will be made to the order for a file type of CAN. | |||
[[Image:260498_2.png]] | |||
== Decoding Values == | |||
The from location (address ID DEP) and the to location (address ID DEL) will be decoded to match values setup in MTS. | |||
[[Image:260498_3.png]] | |||
The source value is taken from the file, and the target value is the equivalent in MTS. Each customer can have their own values setup. | |||
The customer can also be decoded. This is done using the LOTS_OWNER field on the Customers data table. The Lots Owner will be the value passed in from the inbound file, this is translated to the MTS value which is then used when creating the order. | |||
In example below the value passed in from the file is ‘173’ which is translated to ‘RECKITHEAL’ | |||
[[Image:260498_4.png]] | |||
== LOTS Order Outbound Changes == | |||
There is already a trigger in MTS written for LOTS phase 1 which is used for sending a BOO/RBO message to LOTS when the Booked In flag is initially set or the Late Delivery Date/Time is changed. | |||
For LOTS phase 2 this trigger will be changed to populate the new INT_XML_CONTROL table. | |||
Also a similar trigger exists for sending a SCN message to LOTS when the POD flag is ticked (this is updated automatically when an appropriate message is received from Tokairo) to indicate that the Delivery Note has been scanned into Tokairo and can be viewed via the appropriate link in LOTS. | |||
Again for phase 2 this will write to the new INT_XML_CONTROL table instead of the existing lots table. | |||
Yet another trigger exists for sending a CAN message when orders are cancelled. | |||
Again for phase 2 this will write to the new INT_XML_CONTROL table instead of the existing lots table. | |||
These will all be treated by LOTS as amendments to the orders that it will have already received from WMS. | |||
Within phase 2 there is no scope for MTS to send orders entered manually within MTS to LOTS as ORD messages as not all of the required fields will be known by MTS. | |||
The database job that picks up the lots records and creates the lots interface file will be replaced by a new database job that will pick up the records from the new INT_XML_CONTROL table and create the interface file in XML format. | |||
== TRP Message to LOTS == | |||
On certain new triggers then an appropriate TRP XML message will need to be sent to LOTS. | |||
The triggers for LOTS are :- | |||
Adding or removing Orders (related to a LOTS customer) from a Trip. | |||
Updating a Trip (which has at least one Order related to a LOTS customer) to status ACCEPTED, EN-ROUTE or DELETED. | |||
Carrier_ID, Driver_ID or Tractor_ID is changed on a trip at status ACCEPTED or EN-ROUTE (which has at least one Order related to a LOTS customer). | |||
A database job will run at regular intervals and identify any TRP messages that need sending to LOTS and produce the file in the XML format specified in appendix A using the system registries for the folder name. | |||
A safe copy of the file will be kept in the archive folder. | |||
NB) All stops for the trip will be sent to LOTS but only Orders that are for customers that are LOTS related will be sent on the stop that the order is being unloaded on. | |||
== TRP Message to Microlise == | |||
On certain new triggers then an appropriate TRP XML message will need to be sent to Microlise. | |||
It will also need to be FTP’d to ESI. | |||
In order to identify whether Microlise needs the TRP message sent for a trip then an additional field TRACKING_ENABLED will be added to the Tractor and Trailer tables along with there corresponding maintenance form (RESOURCE). | |||
If this is set to MICROLISE for the tractor assigned to the trip or is set to MICROLISE for the trailer assigned to the first stop on the trip then a TRP message is required for Microlise. | |||
In addition there is a system registry ‘MIC_SEND_ACCEPTED’ which is used to indicate whether message are to be sent to Microlise when the Trip is as ACCEPTED status or only when EN-ROUTE. | |||
The triggers for Microlise are similar to those for LOTS and are :- | |||
Updating a Trip status to EN-ROUTE, DELETED or ACCEPTED (and MIC_SEND_ACCEPTED is set to Y) and the trip has a tractor that is Microlise enabled or the first stop has a trailer that is Microlise enabled. | |||
Carrier, Driver or Tractor is changed for a Trip at status EN-ROUTE, DELETED or ACCEPTED (and MIC_SEND_ACCEPTED is set to Y) and the trip has a tractor that is Microlise enabled or the first stop has a trailer that is Microlise enabled. | |||
Changing the actual despatched quantity on an order line for an order on a trip at status ACCEPTED (and MIC_SEND_ACCEPTED is set to Y). | |||
Changing the Trailer on stop 1 for a trip at status EN-ROUTE or ACCEPTED (and MIC_SEND_ACCEPTED is set to Y) and the trip has a tractor that is Microlise enabled or the old/new trailer is Microlise enabled. | |||
Changing the planned arrival or departure times on a stop for a Trip at status EN-ROUTE or ACCEPTED (and MIC_SEND_ACCEPTED is set to Y) and the trip has a tractor that is Microlise enabled or the first stop has a trailer that is Microlise enabled. | |||
Adding or removing Orders from a trip at status EN-ROUTE or ACCEPTED (and MIC_SEND_ACCEPTED is set to Y) and the trip has a tractor that is Microlise enabled or the first stop has a trailer that is Microlise enabled. | |||
A database job will run at regular intervals and identify any TRP messages that need sending to Microlise and produce the file in the XML format specified in appendix A. | |||
The XML will include all stops on the trip and all orders on the trip. | |||
The orders will be under the stop whose location does not match the hub location for the carrier on the order. | |||
The file will then need to be FTP’d to ESI using the system registries described earlier. | |||
A safe copy of the file will be kept in the archive folder. | |||
==Microlise Update to MTS == | |||
All messages provided by Microlise via ESI to MTS will be in the standard XML format (see appendix A). | |||
There are 3 types of message provided by Microlise at different times of the trip life cycle :- | |||
System Events when the driver arrives or leaves a location (breaks a Geo Fence). | |||
PodPoc messages when the driver confirms the delivered quantities on an order. | |||
Journey Summary when the trip is completed. | |||
A database job will be run at regular intervals to check if any new files of the correct name have been placed in the appropriate folder. | |||
The data will then be loaded and used to update MTS and then moved into either the failures or the archive folder. | |||
The system event message will be used to update the actual arrival and departure times | |||
for the appropriate trip stop. These times will be updated even if they have already set. | |||
The podpoc will again be used to update any arrival/departure times not provided by system events and also to update the delivered quantities on either the order lines or the order items as appropriate. It will also create the appropriate non-conformity records with matching reason codes. The actual times will only be updated if they are currently blank. | |||
The journey summary will also be used to update any missing actual arrival or departure times (only update blank times) as well as providing the fuel drawn and ODO readings for the trip. | |||
An interface trip stop record will be written for every trip stop in the interface to show the trip stop information that was loaded. | |||
On successful completion of the MTS update then this will be marked accordingly. | |||
If there are any errors with the data at trip stop level occur then an appropriate error will be written to the trip stop interface record. | |||
Also an interface order record will be written for every order in the interface to show the order level information (additional levels for details and non-conformities). On successful completion of the MTS update then this will be marked accordingly. | |||
If there are any errors with the data at order level then an appropriate error will be shown. If the error is at a lower level (line or reason code) then an error is also written at this lower level. | |||
When an actual departure time is provided for the first stop on a trip and the system registry ‘TRM_SET_TO_ENROUTE_AFTER_ACTUALS’ is set then the trip will be moved to status EN-ROUTE. | |||
When all of the orders on a trip have had there despatched quantities and delivered quantities populated (even with zero) and all of the actual arrival and actual departure times for all of the stops on the trip have been populated by Microlise then depending on a new system registry ‘MIC_SET_TO_COMP_CONF’ then the trip status will be moved onto to ‘COMPLETED’ or ‘CONFIRMED’ on completion of the Microlise updating. | |||
==LOTS to MTS Updates == | |||
LOTS will also send updates to MTS via the standard XML format. | |||
Another database job will be run at regular intervals to check if any new files of the correct name have been placed in the appropriate folder. | |||
The data will then be loaded and used to update MTS and then moved into either the failures or the archive folder. | |||
There will only be one type of message from LOTS and it will be similar to the Microlise podpoc message. | |||
The provided XML file will be split into updates to a trip stop and updates to the order for delivered quantity and any non-conformities. | |||
The message will be used to update any arrival/departure times not already populated in MTS and also to update the delivered quantities on either the order lines or the order items level as appropriate. It will also create the appropriate non-conformity records with matching reason codes. | |||
On successful completion of the MTS updates then these record will be marked accordingly. | |||
If there are any errors with the data at trip stop level occur then an appropriate error will be written to the trip stop interface record. | |||
Also an interface order record will be written for every order in the interface to show the order level information (additional levels for details and non-conformities). On successful completion of the MTS update then this will be marked accordingly. | |||
If there are any errors with the data at order level then an appropriate error will be shown. If the error is at a lower level (line or reason code) then an error is also written at this lower level. | |||
== Interface Errors == | |||
3 new tabs will be added to the INT_ERR screen so that the Inbound files at Order Level or Trip Stop level can be monitored and also the XML outbound files. | |||
The XML Orders tab will include a section for the header information and a section for the details. Each will include a field to display any errors that have occurred during the file integration and order creation \ update. | |||
The data can be filtered on Message type, Customer, From Location and success \ failure. | |||
[[Image:260498_5.png]] | |||
This tab will show orders imported to MTS from WMS for order creation/amendment and also imports from both Microlise and LOTS for debriefing the order (delivered quantities and non-conformities). | |||
Only the pocpod messages from Microlise will be visible on this tab as the other message types relate to trips only. | |||
Do we need to add the reason code level to the bottom of this tab ? | |||
The XML Trip tab will show the interfaced XML records at trip stop level. | |||
The source will allow you to select which XML flow you which to query on. | |||
The ‘Include Success’ check box will allow queries on just the Failures. | |||
[[Image:260498_6.png]] | |||
This tab will show imports from both Microlise and LOTS. | |||
All 3 types of messages from Microlise will be shown in this tab. | |||
XML Outbound Tab will show all of the outbound XML flows. | |||
The source will allow you to query just the flow (External System) that you are interested in. | |||
[[Image:260498_7.png]] | |||
This will include data for the LOTS XML interface and also the Microlise interface. | |||
When initially triggered from within the MTS system the records will have a blank filename and the processed check box will not be ticked. | |||
Once the database job kicks in and processes the records then the processed box will become checked and the filename will be populated. | |||
== Line Level == | |||
To facilitate the input of line level data a new tab will be created on the Orders screen. This will display the line level information for a particular order. The data will be stored in a new table SCH_ORD_ITEMS. | |||
[[Image:260498_8.png]] | |||
The inbound order flow will be amended to accept and process order files with additional order item information. The existing table INT_XML_ORD_DETAILS will be reused to store the inbound data. | |||
When the orders are created the line level data will be inserted into the SCH_ORD_ITEMS table. This information will then be consolidated to create the SCH_ORDER_LINE data. | |||
The items lines will be grouped by product type (E.G. Ambient). The values for each line will then be combined to give a total for the product type which will be displayed at the line level. | |||
[[Image:260498_9.png]] | |||
=== Item Reason Codes === | |||
Reason codes for non-conformance can be entered against each item line. A new set of reason codes will be setup for Item non-conformance, these will be labelled as ITEM_NON_CON. | |||
The reason codes can be inserted via the XML Interface, or they can be entered manually from the order items tab. | |||
[[Image:260498_10.png]] | |||
=== Changes To Rebook === | |||
The Re-book process will be changed to incorporate the item level information. When a Re-book takes place the item information will be transferred to the new booking in the same way as the line level information. | |||
If an amendment file is sent in for an order that has been rebooked, the data on the new order will be amended. | |||
= References = | |||
Not Available | |||
= Glossary = | |||
Not Available | |||
= Document History = | |||
{| Border="1" | |||
| <center>'''Version'''</center> | |||
| <center>'''Date'''</center> | |||
| <center>'''Status'''</center> | |||
| <center>'''Reason'''</center> | |||
| <center>'''Initials'''</center> | |||
|- | |||
| <center>0.1</center> | |||
| <center>12/03/09</center> | |||
| <center>Draft</center> | |||
| Initial version | |||
| <center>DNG</center> | |||
|- | |||
| <center>0.2</center> | |||
| <center>05/05/09</center> | |||
| <center>Draft</center> | |||
| XML Changes Added | |||
| <center>DRM</center> | |||
|- | |||
| <center>0.3</center> | |||
| <center>08/05/09</center> | |||
| <center>Draft</center> | |||
| Reviewed | |||
| <center>MJC</center> | |||
|- | |||
| <center>0.4</center> | |||
| <center>08/05/09</center> | |||
| <center>Draft</center> | |||
| Fixes Following Review | |||
| <center>DRM</center> | |||
|- | |||
| <center>0.5</center> | |||
| <center>27/05/09</center> | |||
| <center>Draft</center> | |||
| Client Requested Changes | |||
| <center>DRM</center> | |||
|- | |||
| <center>1.0</center> | |||
| <center>03/06/09</center> | |||
| <center>Issued</center> | |||
| Reviewed and Issued | |||
| <center>MJC</center> | |||
|} | |||
= Authorised By = | |||
{| Border="1" | |||
| '''''Matt Crisford''''' | |||
| Development Manager | |||
| | |||
|- | |||
| '''''Suk Sandhu''''' | |||
| TMSCC MTS Product Manager | |||
| | |||
|} |
Latest revision as of 17:01, 21 September 2009
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
Functional Description
File Retrival
The inbound files will be placed in the directory specified in the system registry setting WMS_INBOUND_PATH. By default this will be /webint/<dbname>/interface/WMS/IN.
A database job will be set up to run at a set interval (by default this is every 5 minutes). The job will call the new package INT_XML passing in the required parameters. These parameters have been detailed above.
Within INT_XML a call will be made to the existing DP_FILEHANDLING package. This package will retrieve the files from the server so they can be processed into MTS.
Data Extract
Once the files have been retrieved the data will be extracted using XML_EXTRACT. The data will then be validated and stored in INT_XML_ORD_HEADER and INT_XML_ORD_DETAIL.
There are a number of fields that are validated before the file can be loaded successfully.
Early Available Date - Date must be valid Late Available Date - Date must be valid Early Delivery Date - Date must be valid Late Delivery Date - Date must be valid Booking Date - Date must be valid From Location - Cannot be Null To Location - Cannot be Null Customer - Must exist in MTS Cost Centre - Must exist in MTS Source System - Must exist in MTS Delivery Type - Must exist in MTS Group Name - Must exist in MTS
Each order in the file will be validated separately, so if one order fails validation the others will continue to load.
If the order passes validation a record will be inserted into INT_XML_ORD_HEADER, and one or more records will be inserted into INT_XML_ORD_DETAIL.
Order Creation
Once the data from the file has been loaded into the interface table orders can be created. One order is created for each valid INT_XML_ORD_HEADER record.
If the file is of type ORD a new order will be created. An OMS ref will be generated for each order and a schedule created if a suitable one does not already exist.
If the file is of type ALC, MAR or LOA then the order within the file will be update. The External Ref (SO_REF) will be used to find the existing order in MTS. If the order does not exist an appropriate error will be displayed. If the order does exist the order will be updated with the new data from the file.
If the file is of type CAN, the order will be cancelled. Again the External Ref will be used to locate the file in MTS, and the order status will be set to Cancelled. No further changes will be made to the order for a file type of CAN.
Decoding Values
The from location (address ID DEP) and the to location (address ID DEL) will be decoded to match values setup in MTS.
The source value is taken from the file, and the target value is the equivalent in MTS. Each customer can have their own values setup.
The customer can also be decoded. This is done using the LOTS_OWNER field on the Customers data table. The Lots Owner will be the value passed in from the inbound file, this is translated to the MTS value which is then used when creating the order.
In example below the value passed in from the file is ‘173’ which is translated to ‘RECKITHEAL’
LOTS Order Outbound Changes
There is already a trigger in MTS written for LOTS phase 1 which is used for sending a BOO/RBO message to LOTS when the Booked In flag is initially set or the Late Delivery Date/Time is changed.
For LOTS phase 2 this trigger will be changed to populate the new INT_XML_CONTROL table.
Also a similar trigger exists for sending a SCN message to LOTS when the POD flag is ticked (this is updated automatically when an appropriate message is received from Tokairo) to indicate that the Delivery Note has been scanned into Tokairo and can be viewed via the appropriate link in LOTS.
Again for phase 2 this will write to the new INT_XML_CONTROL table instead of the existing lots table.
Yet another trigger exists for sending a CAN message when orders are cancelled.
Again for phase 2 this will write to the new INT_XML_CONTROL table instead of the existing lots table.
These will all be treated by LOTS as amendments to the orders that it will have already received from WMS.
Within phase 2 there is no scope for MTS to send orders entered manually within MTS to LOTS as ORD messages as not all of the required fields will be known by MTS. The database job that picks up the lots records and creates the lots interface file will be replaced by a new database job that will pick up the records from the new INT_XML_CONTROL table and create the interface file in XML format.
TRP Message to LOTS
On certain new triggers then an appropriate TRP XML message will need to be sent to LOTS.
The triggers for LOTS are :-
Adding or removing Orders (related to a LOTS customer) from a Trip.
Updating a Trip (which has at least one Order related to a LOTS customer) to status ACCEPTED, EN-ROUTE or DELETED.
Carrier_ID, Driver_ID or Tractor_ID is changed on a trip at status ACCEPTED or EN-ROUTE (which has at least one Order related to a LOTS customer).
A database job will run at regular intervals and identify any TRP messages that need sending to LOTS and produce the file in the XML format specified in appendix A using the system registries for the folder name.
A safe copy of the file will be kept in the archive folder.
NB) All stops for the trip will be sent to LOTS but only Orders that are for customers that are LOTS related will be sent on the stop that the order is being unloaded on.
TRP Message to Microlise
On certain new triggers then an appropriate TRP XML message will need to be sent to Microlise.
It will also need to be FTP’d to ESI.
In order to identify whether Microlise needs the TRP message sent for a trip then an additional field TRACKING_ENABLED will be added to the Tractor and Trailer tables along with there corresponding maintenance form (RESOURCE).
If this is set to MICROLISE for the tractor assigned to the trip or is set to MICROLISE for the trailer assigned to the first stop on the trip then a TRP message is required for Microlise.
In addition there is a system registry ‘MIC_SEND_ACCEPTED’ which is used to indicate whether message are to be sent to Microlise when the Trip is as ACCEPTED status or only when EN-ROUTE.
The triggers for Microlise are similar to those for LOTS and are :-
Updating a Trip status to EN-ROUTE, DELETED or ACCEPTED (and MIC_SEND_ACCEPTED is set to Y) and the trip has a tractor that is Microlise enabled or the first stop has a trailer that is Microlise enabled.
Carrier, Driver or Tractor is changed for a Trip at status EN-ROUTE, DELETED or ACCEPTED (and MIC_SEND_ACCEPTED is set to Y) and the trip has a tractor that is Microlise enabled or the first stop has a trailer that is Microlise enabled.
Changing the actual despatched quantity on an order line for an order on a trip at status ACCEPTED (and MIC_SEND_ACCEPTED is set to Y).
Changing the Trailer on stop 1 for a trip at status EN-ROUTE or ACCEPTED (and MIC_SEND_ACCEPTED is set to Y) and the trip has a tractor that is Microlise enabled or the old/new trailer is Microlise enabled.
Changing the planned arrival or departure times on a stop for a Trip at status EN-ROUTE or ACCEPTED (and MIC_SEND_ACCEPTED is set to Y) and the trip has a tractor that is Microlise enabled or the first stop has a trailer that is Microlise enabled.
Adding or removing Orders from a trip at status EN-ROUTE or ACCEPTED (and MIC_SEND_ACCEPTED is set to Y) and the trip has a tractor that is Microlise enabled or the first stop has a trailer that is Microlise enabled.
A database job will run at regular intervals and identify any TRP messages that need sending to Microlise and produce the file in the XML format specified in appendix A.
The XML will include all stops on the trip and all orders on the trip.
The orders will be under the stop whose location does not match the hub location for the carrier on the order.
The file will then need to be FTP’d to ESI using the system registries described earlier.
A safe copy of the file will be kept in the archive folder.
Microlise Update to MTS
All messages provided by Microlise via ESI to MTS will be in the standard XML format (see appendix A).
There are 3 types of message provided by Microlise at different times of the trip life cycle :-
System Events when the driver arrives or leaves a location (breaks a Geo Fence).
PodPoc messages when the driver confirms the delivered quantities on an order.
Journey Summary when the trip is completed.
A database job will be run at regular intervals to check if any new files of the correct name have been placed in the appropriate folder.
The data will then be loaded and used to update MTS and then moved into either the failures or the archive folder.
The system event message will be used to update the actual arrival and departure times
for the appropriate trip stop. These times will be updated even if they have already set.
The podpoc will again be used to update any arrival/departure times not provided by system events and also to update the delivered quantities on either the order lines or the order items as appropriate. It will also create the appropriate non-conformity records with matching reason codes. The actual times will only be updated if they are currently blank.
The journey summary will also be used to update any missing actual arrival or departure times (only update blank times) as well as providing the fuel drawn and ODO readings for the trip.
An interface trip stop record will be written for every trip stop in the interface to show the trip stop information that was loaded.
On successful completion of the MTS update then this will be marked accordingly.
If there are any errors with the data at trip stop level occur then an appropriate error will be written to the trip stop interface record.
Also an interface order record will be written for every order in the interface to show the order level information (additional levels for details and non-conformities). On successful completion of the MTS update then this will be marked accordingly.
If there are any errors with the data at order level then an appropriate error will be shown. If the error is at a lower level (line or reason code) then an error is also written at this lower level.
When an actual departure time is provided for the first stop on a trip and the system registry ‘TRM_SET_TO_ENROUTE_AFTER_ACTUALS’ is set then the trip will be moved to status EN-ROUTE.
When all of the orders on a trip have had there despatched quantities and delivered quantities populated (even with zero) and all of the actual arrival and actual departure times for all of the stops on the trip have been populated by Microlise then depending on a new system registry ‘MIC_SET_TO_COMP_CONF’ then the trip status will be moved onto to ‘COMPLETED’ or ‘CONFIRMED’ on completion of the Microlise updating.
LOTS to MTS Updates
LOTS will also send updates to MTS via the standard XML format.
Another database job will be run at regular intervals to check if any new files of the correct name have been placed in the appropriate folder.
The data will then be loaded and used to update MTS and then moved into either the failures or the archive folder.
There will only be one type of message from LOTS and it will be similar to the Microlise podpoc message.
The provided XML file will be split into updates to a trip stop and updates to the order for delivered quantity and any non-conformities.
The message will be used to update any arrival/departure times not already populated in MTS and also to update the delivered quantities on either the order lines or the order items level as appropriate. It will also create the appropriate non-conformity records with matching reason codes.
On successful completion of the MTS updates then these record will be marked accordingly.
If there are any errors with the data at trip stop level occur then an appropriate error will be written to the trip stop interface record.
Also an interface order record will be written for every order in the interface to show the order level information (additional levels for details and non-conformities). On successful completion of the MTS update then this will be marked accordingly.
If there are any errors with the data at order level then an appropriate error will be shown. If the error is at a lower level (line or reason code) then an error is also written at this lower level.
Interface Errors
3 new tabs will be added to the INT_ERR screen so that the Inbound files at Order Level or Trip Stop level can be monitored and also the XML outbound files.
The XML Orders tab will include a section for the header information and a section for the details. Each will include a field to display any errors that have occurred during the file integration and order creation \ update.
The data can be filtered on Message type, Customer, From Location and success \ failure.
This tab will show orders imported to MTS from WMS for order creation/amendment and also imports from both Microlise and LOTS for debriefing the order (delivered quantities and non-conformities).
Only the pocpod messages from Microlise will be visible on this tab as the other message types relate to trips only.
Do we need to add the reason code level to the bottom of this tab ?
The XML Trip tab will show the interfaced XML records at trip stop level.
The source will allow you to select which XML flow you which to query on.
The ‘Include Success’ check box will allow queries on just the Failures.
This tab will show imports from both Microlise and LOTS.
All 3 types of messages from Microlise will be shown in this tab.
XML Outbound Tab will show all of the outbound XML flows.
The source will allow you to query just the flow (External System) that you are interested in.
This will include data for the LOTS XML interface and also the Microlise interface.
When initially triggered from within the MTS system the records will have a blank filename and the processed check box will not be ticked.
Once the database job kicks in and processes the records then the processed box will become checked and the filename will be populated.
Line Level
To facilitate the input of line level data a new tab will be created on the Orders screen. This will display the line level information for a particular order. The data will be stored in a new table SCH_ORD_ITEMS.
The inbound order flow will be amended to accept and process order files with additional order item information. The existing table INT_XML_ORD_DETAILS will be reused to store the inbound data.
When the orders are created the line level data will be inserted into the SCH_ORD_ITEMS table. This information will then be consolidated to create the SCH_ORDER_LINE data.
The items lines will be grouped by product type (E.G. Ambient). The values for each line will then be combined to give a total for the product type which will be displayed at the line level.
Item Reason Codes
Reason codes for non-conformance can be entered against each item line. A new set of reason codes will be setup for Item non-conformance, these will be labelled as ITEM_NON_CON.
The reason codes can be inserted via the XML Interface, or they can be entered manually from the order items tab.
Changes To Rebook
The Re-book process will be changed to incorporate the item level information. When a Re-book takes place the item information will be transferred to the new booking in the same way as the line level information.
If an amendment file is sent in for an order that has been rebooked, the data on the new order will be amended.
References
Not Available
Glossary
Not Available
Document History
Initial version | ||||
XML Changes Added | ||||
Reviewed | ||||
Fixes Following Review | ||||
Client Requested Changes | ||||
Reviewed and Issued |
Authorised By
Matt Crisford | Development Manager | |
Suk Sandhu | TMSCC MTS Product Manager |