257584: Difference between revisions
Middletong (talk | contribs) No edit summary |
Middletong (talk | contribs) |
||
| Line 152: | Line 152: | ||
= Document History = | |||
{| Border="1" | {| Border="1" | ||
| <center>'''Version'''</center> | | <center>'''Version'''</center> | ||
| Line 175: | Line 175: | ||
|} | |} | ||
== Authorised By == | == Authorised By == | ||
{| Border="1" | {| Border="1" | ||
Revision as of 12:17, 21 September 2009
257584 EI-7L8HBY Booking MSG for Kraft BIC Orders
Copyright OBS Logistics © 2008
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 an MTS flow that sends booking information message to ESI, for KRAFT BIC orders, that have their Trip Status as 'ENROUTE'. In case of cross docked orders, only details for the delivery to the TO_LOC destination of the order should be reported. This message should also be sent for rebooked orders and any added rebook suffix in the EXTERNAL_REF should be removed before being reported.
Please note that there are some manually built orders for KRAFT BIC, with EXTERNAL_REF which did not come into MTS through the 'KRAFT ORDERS' inbound interface. We would not want these orders reported in this outbound interface.
The file format should be comma delimited and containing 38 fields as shown in attached sample file. The data in all fields are wrapped with quotation marks. The format for reporting the 'LATE_DEL' field should be "YYYYMMDD HH24:MM:SS" . The LOCATION_NAME field reported is the value in the GEO_LOCATION table where the SCH_ORD.TO_LOC matches the SCH_TRIP_STOP.LOCATION_ID. The decimal-separator for reporting the 'RPE' field should be a comma (e.g. 12,5 not 12.5)
The attached sample .txt file shows the required MTS field variables, hard-coded values and their positions that will be expected in the output .CSV for this interface.. The file should be generated for orders that meet the following criteria: A) The Trip Status is 'ENROUTE' B) CUSTOMER = KRAFT BIC C) The SCH_ORD.EXTERNAL_REF should have come through the inbound 'KRAFT ORDERS' interface or a rebooked version ( Here any added suffix should be removed) D) The date part of the SCH_ORD.LATE_DEL is equal to or greater than the current Date
The naming convention for the output file will be: Sendung_'YYMMDDHHMM'.CSV. This interface should be developed as a stand alone development. Sample output file attached.
Solution
Within the Trip screens, Trip Planning, Trip Manipulation and Waterfall, whenever the status is changed to EN-ROUTE a procedure will be run. The procedure will check if the trip has any KRAFT-BIC orders. If not, the procedure will close.
If there are KRAFT-BIC orders, the next check will be that the value of LATE_DEL is greater than or equal to today. If this is not the case, the procedure will close.
If the LATE_DEL and Customer criteria are met, the next check is that the ORDER has been created via the INBOUND INTERFACE. To identify these records we will check that SOURCE_SYSTEM equals KRAFT SAP. This will exclude all MANUAL and TEMPLATE BATCH records.
If the above criteria is met, the oms_ref, external_ref, late_del, to_loc and booking_ref will be inserted into INT_ORDER_HEADER with a RECORD_STATUS of ‘SEND’
PROCESS_OUTBOUND_ORDER within Package INT_MSG will be amended to select all KRAFT-BIC orders from INT_ORDER_HEADER where the RECORD_STATUS is SEND.
Using the OMS_REF in this table, we will retrieve the CARRIER_CODE and TOTAL_RPE_QTY.
TO_LOC will be used to find the LOCATION_NAME, COUNTY_CODE, POSTCODE and TOWN from GEO_LOCATIONS.
Within the PROCESS_OUTBOUND_ORDER procedure, the data will also be formatted to the specifications, including adding quotation marks and written out to the csv file. An exisiting DATABASE job runs every 15 minutes and calls PROCESS_OUTBOUND_ORDER.
The CSV file will have the naming convention : Sendung_’YYMMDDHHMM’.CSV. This file will be made up of the following information:
"26427", "00000", "", "EXTERNAL_REF", "LATE_DEL", "0", "GB73", "", "", "", "", "", "", "", "", "LOCATION_NAME", "", "", "COUNTRY_CODE", "POSTCODE", "TOWN", "", "BOOKING_REF", "", "", "”, "", "", "", "", "", "", "", "", "RPE", "", "", "CARRIER_CODE"
The data will be written to this file in the order as stated in the attachment; some hard coded data will be included with the field data. The file will be written out to the folder as determined by the system parameter MTS_OUTBOUND_ORDER_PATH. This is currently set as WEBINT/<DATABASE>/INTERFACE/OUT.
Once the record has been successfully added to the csv file, the RECORD_STATUS in INT_ORDER_HEADER will be updated to SENT. The RECORD_ID will be updated to the filename. Launching the procedure only when the status is changed to EN-ROUTE will ensure that the trip records are added to the CSV file once.
Scope
This change will be applied to system version {enter the version} on {enter the test database e.g. CONTST} and once approved {enter the production database e.g.: CONPRD}.
Data
A new record will be added to the table INT_ORDER_HEADER when the following criteria is met
The Trip has a status of EN-ROUTE The trip has orders scheduled for KRAFT The orders were added to the system via the Kraft inbound interface The late delivery date is equal to or greater than today.
The record in INT_ORDER_HEADER will include the oms_ref, status, external_ref, late_del, to_loc , booking_ref and source_system. The field RECORD_STATUS will be set to ‘SEND’. The data in this table is used to determine which records should be selected for the different MTS Flows. Source_system will identify which MTS Flow.
Using the oms_ref stored in INT_ORDER_HEADER, we will select the carrier_id and total_RPE_quantity from sch_ord. We will use to_loc, stored in INT_ORDER_HEADER to select the location_name, postcode, town and country code from GEO_LOCATIONS. The above data will be selected using cursors. They will be slotted into the existing function code:
The data from INT_ORDER_HEADER will be combined with the data from the new cursors and hard coded values to populate the CSV file.
FUNCTIONAL DESCRIPTION
In the Trip Planning, Manipulation and Waterfall screens, when the trip status is changed to EN-ROUTE a process will run in the background.
This process will check the customer, late delivery date and source as described earlier. If the criteria is met a record will be written to the INT_ORDER_HEADER table which will identify the order and the customer as KRAFT-BIC.
Every 15 minutes a database is launched which will run the procedure PROCESS_OUTBOUND_ORDER.
This procedure will check the INT_ORDER_HEADER table for any KRAFT records with a status of SEND. This record will be selected along with further details, using the information stored in the record and the data will be written to a CSV file.
Once the record has been copied to the CSV file the INT_ORDER_HEADER table will be updated; record status will be updated to SENT and the filename inserted as the record id.
Changes will be made to the TRIP_PLAN.fmb, TRIPSUM.fmb and WATERFALL.fmb Screens. The package INT_MSG, specifically the PROCESS_OUTBOUND_HEADER function.
References
| EST 257584 EI-7L8HBY Booking MSG for Kraft BIC Orders v1.doc |
Glossary
Not Available
Document History
| Initial version | ||||
| Reviewed and Issued |
Authorised By
| Matt Crisford | Development Manager | |
| Suk Sandhu | TMSCC MTS Product Manager |
