257146

257146 - EI-7KXQ6A/ New Flow Message to ESI Scotts Orders

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

Request for a new MTS flow that sends delivery information message to ESI, for Scotts orders that have their actual arrive and actual depart dates and times (order debriefed). In case of cross docked orders, only the delivery to the final delivery destination should be reported. This message should also be sent for rebooked orders.

The file format should be comma delimited and contain the following information per record: MTS_OPERATION, CUSTOMER, TRIP_ID, EXTERNAL_REF, ACTUAL_ARRIVE, ACTUAL_DEPART, REBOOK_COMMENTS

The file should be triggered once per day for orders that meet the following criteria: A) CUSTOMER (Client) = Scotts B) The ACTUAL_ARRIVE and ACTUAL_DEPART fields are completed for the delivery stop. D) The order record has not been previously sent in a message except rebooked orders

The naming convention for the output file will be: SCOTTS_DEL_INFO_'YYMMDDHHMM'.CSV. This interface should be developed as a stand alone development. Sample output file attached.

Added by Suk Sandhu 05/11/08: The request for this to be sent once a day has now been changed. The messages should be triggered and ready for ESI to collect in real time. Is it possible for the message to be sent on actuals being entered as a trigger or will it have to be trip status confirmed and include only orders with customer name of Scotts?

Referred by Eugene Iwu 19/11/08: We want updates to the actuals to be sent to ESI after the order was initially saved.

Solution

Within the screens where users can enter actual arrive and depart times, a procedure will be called every time the user does a save in this screen. The screens in MTS where users can set actual arrive/depart times are TRIPDTL and ORD_DEBRIEF. The following steps will be taken when a Save is actioned in each of these screens:

• In TRIP_DTL, the current Trip being debriefed will be checked to see if there are any SCOTTS orders on it. In ORD_DEBRIEF, the current order being debriefed will be checked to see if it has a customer of SCOTTS. If there are any orders that are SCOTTS, then the actual arrive date/time and actual depart date/time on the delivery stop (where the location_id of the Trip Stop matches the to_loc on the order) will be checked to see if both are populated. If all conditions are true, then each order will have its corresponding TRIP_ID, SCHED_NAME, OMS_REF, CUSTOMER, EXTERNAL_REF, FROM_LOC, TO_LOC, CREATED_BY, CREATED DATE written to table INT_ORDER_HEADER. When a new record is inserted into this table the RECORD_STATUS will be set to ‘SEND’.

V2 amended to send updates to delivery stop actuals

• When a user actions a ‘save’ in the screens mentioned, table INT_ORDER_HEADER will be checked to see if the current order to be written has an instance in the table. If there is an instance in the table, then we will only resend the order if the delivery stop actuals have been updated. To check if an orders delivery stop actuals have been updated, when the trip (in TRIPDTL) or order (in ORD_DEBRIEF) is first viewed on screen, the delivery stop actual arrive/depart will be picked up from the database and held in temporary variables. Then when a user updates the record, if the delivery stop actual arrive/depart now differ from those held in the temporary variables, the order record will be written to INT_ORDER_HEADER and sent in a file to ESI.

• Procedure PROCESS_OUTBOUND_ORDER within Package INT_MSG will be updated to collate all records in INT_ORDER_HEADER with a RECORD_STATUS of SEND and a CUSTOMER of SCOTTS. For each record of status SEND the ACTUAL_ARRIVE and ACTUAL_DEPART date/times for the delivery stop will also be retrieved from table SCH_TRIP_STOP. This will send multiple order records per file.

• All of these collated records will be written to a file named SCOTTS_DEL_INFO_YYMMDDHHMMSS.csv. The file will contain the following fields in this order:

MTS Operation e.g. COLV or COPF Customer Trip Id External Ref Actual Arrive Actual Depart Rebook Comments

• Update RECORD_STATUS to SENT and RECORD_ID to the filename in which the order is sent, after each record is written to the csv file.

• Existing System Parameter MTS_OUTBOUND_ORDER_PATH will be referenced to decide which folder the csv will be placed in. This folder will be /webint/<database>/interface/out. ESI will need to be informed that this is where the file can be collected from.

• A database job is already set up that calls procedure PROCESS_OUTBOUND_ORDER approximately every 5 minutes. Therefore, every 5 minutes, table INT_ORDER_HEADER will be checked to see if any SCOTTS orders need to be sent in a csv to ESI.

Note that the Actual Arrive and Actual Depart Date/Times will be taken from the Delivery Stop for the order. A check will be made to ensure that the location_id of the Delivery Stop matches the to_loc on the order. This will ensure that if an order is cross-docked, the delivery actuals are picked up from the correct Trip.

Note that for an Order that has Re-booked comments to be sent in the file, the user must enter the Re-Book comments first, before saving the actuals, otherwise when the data is being written to the csv, the Re-book comments will not be picked up.

Scope

This change will be applied to system version 10.6 on CONTST and once approved CONPRD.

Set-Up

Pre-Requisites

SCOTTS must be set up as a customer within the database.

Functional Description

Sample Layout

Below is how the INT_ORDER_TABLE would look if some users had just entered actuals at the Delivery Stop for customer SCOTTS, and did a Save.

It can be seen that the CUSTOMER field is SCOTTS and the RECORD_STATUS is SEND. These are the fields that will be used to collate all SCOTTS records that are ready to be sent, by INT_MSG.PROCESS_OUTBOUND_ORDER. The MSG_TYPE will also be updated to ‘Scotts Order Actuals’.

The INT_RECORD_ID is unique to this table; therefore any SCOTTS order inserted into this table will have an INT_RECORD_ID in the following format: ‘MTS_SC_seq’. This field will therefore not be able to hold the name of the file in which the order is sent, as suggested in the estimate. The name of the file in which the orders are sent will not be stored in the table, to keep current functionality in outbound order files (e.g. Kraft, Green & Black, Silverspoon) in line.

After the database job for INT_MSG.PROCESS_OUTBOUND_ORDER has run, table INT_ORDER_HEADER will be updated. Below is an example of how it should look after the SCOTTS records have been collated and sent in a file to ESI:

Note that this table does not hold fields ACTUAL_ARRIVE, ACTUAL_DEPART and REBOOK_COMMENTS that are required in the output file. These values will be picked up for each order being outputted to the csv files at the time when the file is being written to. This will be performed within INT_MSG.PROCESS_OUTBOUND_ORDER.

References

Ref No	Document Title & ID	Version	Date
1	EST-257146 EI-7KXQ6A New Flow message to ESI Scotts Orders v1.doc	1	19/11/08
2	EST-257146 EI-7KXQ6A New Flow message to ESI Scotts Orders v2.doc	2	19/11/08
3	SCOTTS_DEL_INFO_0811030100.csv	N/A
4	FS-257146 EI-7KXQ6A New Flow message to ESI Scotts Orders v1.doc	28/11/08

Glossary

Not Available

Document History

Version	Date	Status	Reason	Initials
1a	27/11/08	Draft	Initial version	LAD
1	28/11/08	Issue	Reviewed and Issued	MJC
2a	01/12/08	Amend	Section 1.1 and 1.2 updated to include change to estimate as requested by Eugene Iwu however detail of document does include this change	LAD
2	01/12/08	Issue	Reviewed and Issued	MJC

Authorised By

Matt Crisford	Development Manager
Suk Sandhu	TMSCC MTS Product Manager