263313
263313 - JB-7QNVRZ Birds Eye InOut EDI Flow
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
Functiona Overview
Client Requirement
DHL have won a transport contract for BEIG (Birds Eye) due for transition on May 27th.
In order for this to occur, DHL require automated file interfaces for their Managed Transport System (MTS) for incoming Order Information, outgoing Order Information and Trip data. All data transmissions will be XML documents.
Requirements: Interface 1: DAI Matflo WMS to MTS (T_ORD message advising of order receipt) Interface 2: MTS to DAI Matflo WMS (T_LOAD message to advise of load plans and alterations to load plans: the trigger being orders added or removed, changes to loading times, changes to delivery sequence or the order reaching planned status).
Solution
Interface 1: (Inbound to the MTS System)
OBS will set up an automated process running at an agreed interval to capture and process XML documents sent to the MTS server via ‘ESI file transmission’.
When these XML document files are captured, the data will be stripped out of them, inserted into the MTS system, and as appropriate, the files will be placed in an archive or failure file depending on their processing status: success or failure.
Once this data has been captured from the incoming XML document, it will be validated according to an agreed set of minimum data requirements and disseminated into two interim (interface control) tables: Int_xml_ord_header and int_xml_ord_detail.
OBS has XML document handling infrastructure that can be modified to suit this task.
From this point, the user of the MTS system will be able to see the processing status of each received file using an existing tab on the interface errors display screen. From this screen the user will be able to see validation errors, and reasons why processing of inbound XML documents have failed.
The MTS system has this functionality built in – within the current build. The current Interface errors screen will have the current functionality checked and modified (If required.) for the ‘new’ information provided.
Once the data has been uploaded into the inbound header and detail tables, it will be refined further, using an agreed set of minimum data requirements into either an update to a previously existing order, or a new order.
OBS has an existing processing engine that it uses for this task that can be modified to accept DHL’s minimum data requirements in this case.
NB:
- File formats to be used will be latest Generic MTS XML format.
Interface 2: (Outbound From the MTS System)
OBS will set up an automated process to send outgoing XML documents containing load information back to the DAI Matflo system.
OBS has an existing processing engine that can be modified to suit this task. The modifications to this processing engine will comprise of the ‘trigger events’ which transmit the file back to the DAI Matflo system. Each trigger event will take ½ a day to program into the processing engine, with a further ¼ of a day to test.
The requirement for this system lists triggers to send outgoing files as five ‘occurrences’:
a) Changes to Loading Times
b) When an order is added to a trip
c) When an order is removed from a trip
d) Changes to the delivery sequence of the load.
e) The order’s status changes to ‘planned’.
NB:
- Triggers for message creation must be confirmed before specification is agreed. This may affect the cost of this change.)
- The outbound file will have the same structure as the inbound file – the latest generic MTS XML format.
Scope
The scope of this functional specification applies to changes to be made to system version 10.5.0 on CONTST and once approved, CONPRD.
Components (oracle objects or oracle forms) mentioned in this specification; described as “modified for ‘generic’ use” within the MTS system to facilitate the input and processing of BEIG XML documents may be re-used in other DHL systems or subsequently modified for other (DHL) XML document processes not involving BEIG.
Therefore: Modification of these ‘generic’ objects within the CONTST and CONPRD databases for use for BEIG is within the scope of this specification. Further modification of these generic objects for non-BEIG XML documents is outside the scope of this specification, even if this modification occurs on the CONTST and CONPRD database. The scope of this functional specification is dependent on the inbound and outbound XML documents being identical in structure to documents currently used within other (DHL) MTS file transmissions. The modification of the MTS system and/or the XML document handling framework and/or the XML files themselves to meet the requirements of the current XML document handling framework is outside the scope of this specification. Explanation: as per section 1.2.1, The file formats to be used; are the latest Generic XML format, and the files passed from the DAI Matflo system to MTS (and vice-versa) must meet this standard. Any changes required to the XML files or the system to cater for non-standard XML will incur additional charges, as it falls outside the scope of this change.
Set-UP
Pre-Requisites
The Oracle Package ‘XML’ needs to be compiled on the database(s) specified within the scope of this change. The package is used for the generic conversion of XML tag data (hierarchal format) to a normalized data representation that’s able to be used within an oracle database.
The ancillary table: XML_EXTRACT needs to exist on the database(s) specified within the scope of this change.
Menu Structure
Within the Interface errors screen, xml_orders tab, the menus will be modified to show BEIG (Birds Eye) so that the inbound and outbound files and their status can be displayed.
As per the estimate (no change), modification of the Interface screen is specified at: ½ day development, ¼ of a day testing.
As per the screen shot below: (screen shot vertically condensed to only show relevant information.)
Data
Scripts will have to be created to insert the data into database table: adm_system_param,
Functional Description
Interface 1
An automated job will be set up to run every five minutes to check if XML Documents have been placed in the inbound location.
OBS staff will modify the DB Package: DP_FILE_HANDLING to accept a BEIG file stream from ESI. The XML files will be delivered into the inbound directory specified in section 2.3 Data.
Modification details of DP_FILE_HANDLING
DP_FILE_HANDLING will be changed to accept the passed parameter: BEIG, from the automated job. When this occurs, the following associate actions will be triggered:
Using the parameter: BEIG as a catalyst; necessary parameters will be selected from the database table ADM_SYSTEM_PARAM. These parameters are detailed in section ‘2.3 Data’ and include:
a) name of the inbound file,
b) list of the files to be processed,
c) name of the script that will move the files from inbound, to archive, or failure directories,
d) the location of the inbound directory,
e) the location of the archive directory where successfully processed files are saved and,
f) the location of the failure directory where files that have failed processing are saved.
The file name will be checked to see if it has already been processed, if so, an error message will be given in the table INT_XML_ORD_HEADER.
Once these parameters are passed to DP_FILE_HANDLING, and the files have been listed for processing (passed validation as above), the content of the files can be scraped into the generic area xml_extract, and validation performed.
File Content Extraction
The file content extraction is performed by a procedure called XML_EXTRACT part of the database package ‘XML’. This is a generic component, part of the OBS XML document handling infrastructure, and does not need to be modified. Note that this component is a pre-requisite component to this specification.
XML.XML_EXTRACT will place the content of the XML document into the XML_EXTRACT table.
===File content (XML_EXTRACT content) initial validation===The XML documents for Order processing do not contain versioning information, which dictates that they are only submitted once, with no-resend facility, the validation of the content of the file will be conducted from existing framework rules:
The following date vadility (syntax on incoming file) will be checked:
- Early Available Date
- Late Available Date
- Early Delivery Date
- Late Delivery Date
- Booking Date
If an invalid date is found, the following error message is displayed: date_name is an invalid date.
Each order will require a Departure and Delivery location (from the geo_location table), neither can be omitted. From Location(DEP) cannot be NULL To Location(DEL) cannot be NULL The From and To location must also exist within the MTS system, and be ACTIVE.
The following items will also be validated from various sources inside the database:
- Customer
- Cost Centre
- Source System
- Delivery Type
- Group name
If any fail the referential integrity check, an appropriate error message will be given: data_name does not exist on thie MTS system.
Once validation of the incoming data has been completed it is inserted into two interface tables:
INT_XML_ORD_HEADER and INT_XML_ORD_DETAIL
Display of the inbound status within the MTS application
Once inserted into the .._ORD_HEADER and ..ORD_DETAIL tables, the status of the inbound file will be displayed in the interface errors table, under the XML_ORDERS tab. As per the description given in section 2.2: menu structure, the XML orders tab will need to be modified to display the BEIG information.
File content pre-upload validation
Once the data is inserted into the .._ORD_HEADER and ..ORD_DETAIL tables, additional processing and validation occurs to ensure that the data is suitable for dissemination into the MTS system. This occurs in the PROCESS_ORDER procedure.
The validation of the file content will be based upon existing framework rules:
- Message type cannot be D: “Delete action is not allowed”,
- Action Field cannot be Null
- Duplicates are not allowed, checked against OMS_REF for Customer and Customer Reference
- On Update the external reference must be specified,
- Orders cannot be modified in the status of SCHEDULED OR CANCELLED,
- Orders can only be cancelled if they are in status: VALID, INVALID, LOADED, UPDATED, or UNSCHEDULED.
As per the estimate (no change), the setup/modification of the Process_order procedure to perform validation of file content is specified at: 1½ days development to modify, ¾ days testing.
Insertion of validated file data into the MTS system.
Once the data is fully validated, as part of the PROCESS_ORDER procedure, the data will be disseminated to the rest of the MTS system. This dissemination will follow the existing file handling framework rules, and will not be modified for the BEIG data.
The validated data is inserted into the SCH_ORDER table in MTS, as per existing data framework rules.
From this key record, details are inserted into: SCH_ORDER_LINE and/or SCH_ORD_ITEMS as per existing data framework rules.
From this point the Order can be changed and manipulated as per normal MTS system operation(s).
As per the estimate (no change), modification of the existing XML handling infrastructure is specified at: 2½ days development to modify, 1 day testing.
Interface 2
Trigger and Event Specification
A trigger on the SCH_ORD table will be set up to insert a row into the INT_XML_CONTROL table whenever a ‘trigger’ event happens.
As per the scope of this document, the trigger events are defined as:
a) Changes to Loading Times
b) When an order is added to a trip
c) When an order is removed from a trip
d) Changes to the delivery sequence of the load.
e) The order’s status changes to ‘planned’.
The triggers used will be of standard OBS design, as used in the current XML outbound transmission process.
Outbound file Transmission
An automated job will be set up every five minutes to scan for rows in the INT_XML_CONTROL table which need an XML file generated for them.
Upon finding one of these entries, an XML file will be generated from OBS’s generic XML creation framework. The file will then be placed in the outbound file area as listed in section 2.3 Data, for an external FTP service to ‘Pull’ the file to an external source.
As per the estimate (no change), the creation of the triggers to create outbound XML documents and modifications to the processing engine to cater for BEIG outbound files is specified at: 2½ days development to modify, 1 day to test.
References
| Rio – Work Request.mht |
Document History
| Initial version | ||||
| Reviewed and Issued |
Authorised By
| Matt Crisford | Development Manager | |
| Suk Sandhu | TMSCC MTS Product Manager |
