249378

From CTMS

249378 (PA-7DDFH9) Check and enhance EFX data export

Copyright OBS Logistics © 2007


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


Client Requirement

Check Data compilation for EFX extract FileAdd 'Expiry Date and Time' to output fields.


Solution

The EFX file will be sent when the following conditions are met:-

  • A trip was at a status of planned
  • EFX Carrier assigned
  • EFX Box ticked
  • Pop up payment box populated with rate etc
  • OK button pressed
  • Trip Set to Status Tendered

Two extra fields will be added to the generation of the output file. These fields will be taken from the column TRIP_EXPIRY_TIME on the SCH_TRIP table. This column contains both the expiry date and time. The payments popup screen will be amended to allow the user to enter the Expiry date and time. The existing fields on Trip manipulation and Trip Planning will be display only.


The outbound and inbound EFX flows will amended to match the mapping provided.


Scope

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

Functional Description

The EFX message is currently sent when the trip is set to status TENDERED. However the message informing the user that the message has been sent is displayed at the wrong point. This will be changed so that the message only appears when the message has been sent.


The message will only be triggered under the following conditions:-


  • A trip was at a status of planned
  • EFX Carrier assigned
  • EFX Box ticked
  • Pop up payment box populated with rate etc
  • OK button pressed
  • Trip Set to Status Tendered


[[Image:]]


Two fields will be added to the Payment Details window that will allow the user to enter the expiry date and time. The current fields on Trip manipulation / planning will become display only and will pick up the values from the data entered in the Payment details window.


[[Image:]]


The above screen changes will be applied to both the Trip Manipulation screen and the Trip Planning screen.


The outbound file will be changed to match the mapping shown below. Outbound files will be put in the directory /webint/<database>/interface/out to be collected.


Field name
Datatype
Example
RECHEADSTART Text <RECHEADSTART>
MTS_OPERATION Text COPF
MESSAGE_TYPE Text EFX_TRIP_DETAILS
TIMESTAMP Date/Time (DD/MM/CCYY) 25/07/2007 16:29:50
RECHEADEND Text <RECHEADEND>
Details
EFXTRIPDTLSTART Text <EFXTRIPDTLSTART>
Trip Number Text MAN-1234
Owning Depot Text Test Depot
First PK Loc Name Text Test Depot
First PK Loc Post Code Text OX16 3
Pick by Date Date/Time (DD/MM/CCYY) 25/07/2007
Pick by Time Date/Time (HH24:MM) 13:00
First PK Dep Date Date/Time (DD/MM/CCYY) 25/07/2007
First PK Dep Time Date/Time (HH24:MM) 13:54
Sum of delivery DU's on Trip (integer) Integer 12
DU Type from first line of first order Integer CHEP PALLET
Sum of delivery Weight on Trip (integer) Double  
Trip Cost Double 123
Expiry Date Date/Time (DD/MM/CCYY) 25/07/2007
Expiry Time - 2 Hours Date/Time (HH24:MM) 11:54
Trailer Type via decode Text  
Carrier via Decode if not EFX Text CARRIER 01
Temperature Type from first order Text AMBIENT
First DL Loc Post Code Text  
First DL Loc Name Text  
Latest delivery date from order at first DL stop Text  
Latest delivery time from order at first DL stop Text  
Second DL Loc Name Text  
Latest delivery date from order at second DL stop Text  
Latest delivery time from order at second DL stop Text  
Third DL Loc Name Text  
Latest delivery date from order at third DL stop Text  
Latest delivery time from order at third DL stop Text  
Fourth DL Loc Name Text  
Latest delivery date from order at fourth DL stop Text  
Latest delivery time from order at fourth DL stop Text  
Fifth DL Loc Name Text  
Latest delivery date from order at fifth DL stop Text  
Latest delivery time from order at fifth DL stop Text  
Sixth DL Loc Name Text  
Latest delivery date from order at sixth DL stop Text  
Latest delivery time from order at sixth DL stop Text  
Seventh DL Loc Name Text  
Latest delivery date from order at seventh DL stop Text  
Latest delivery time from order at seventh DL stop Text  
Eighth DL Loc Name Text  
Latest delivery date from order at eighth DL stop Text  
Latest delivery time from order at eighth DL stop Text  
Ninth DL Loc Name Text  
Latest delivery date from order at ninth DL stop Text  
Latest delivery time from order at ninth DL stop Text  
Trailer
EOF Text <EOF>


The Inbound flow will be amended to accept CSV files in the below format.


EFX Fields
<RECHEADSTART>
MTS database name
Tripstatus
timstamp
<RECHEADSTART>
<EFXTRIPDTLSTART>
Load Ref
EFX_REF
Owning Depot
<EFXTRIPDTLEND>

The inbound files will be put into the folder /webint/<database>/interface (This will be controlled by a system parameter – See below). A new database job will be created that will handle the inbound files from this directory.


When a file is processed the EFX_REF and owning depot will be updated on the relevant sch_trip record.

As part of the inbound flow 2 new system parameters will be created. These will be ‘EFX_PATH’ and ‘EFX_IDENTIFIER’. EFX_PATH will contain the directory path into which the inbound files should be put. Initially this will be /webint/<database>/interface.


EFX_IDENTIFIER will be the first three characters of the inbound file names. This will be set to ‘EFX*’.


Document History

Version
Date
Status
Reason
Initials
1a
02/06/08
Draft
Initial version
DNG
1
02/12/08
Issued
Reviewed and Issued
MJC

Authorised By

Matt Crisford OBS Development Manager
Suk Sandhu TMSCC MTS Product Manager