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.