271604
271604 - PA-7XVL6X/ EFX Download Additions
Copyright OBS Logistics © 2010
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
Create a new interface from EFX into MTS to update previously posted trips (EFX reference as the key).
The trigger in EFX would be a Site 'Accepting' a load, which would generate a message to be sent to the posting site if MTS enabled.
The Site EFX ID would be applied to the existing Decode table in MTS to identify the accepting site.
This message would amend the Subcontractor in MTS from EFX to the nominated subbie site and amend the payment from EFX to the accepting subbie site.
The status of the trip would finally be changed to 'ACCEPTED'.
Solution
This solution will handle the following messages from EFX
CONFREQ (EFX request has been accepted by EFX) REJREQ (EFX request has been rejected by EFX) ACCEPTED (TRIP has been accepted by someone else) EXPIRED (TRIP has expired on EFX) REJECTED (TRIP is rejected on EFX) CONFCANC (Cancellation has been confirmed by EFX) REJCANC (Cancellation has been rejected by EFX) COMPLETED (Additional Charges entered in EFX)
The READ_EFX_TRIP_INBOUND procedure in INT_MSG will be amended to handle all of the messages which can be received from EFX.
The procedure will read the records in the files received from EFX, extracting the following information:
EFX_STATUS TRIP_ID ACCEPTING_DEPOT EFX_REF EFX_MESSAGE EFX_ADD_CHARGE_1 EFX_ADD_DESC_1 EFX_ADD_AMOUNT_1 EFX_ADD_CHARGE_2 EFX_ADD_DESC_2 EFX_ADD_AMOUNT_2 EFX_ADD_CHARGE_3 EFX_ADD_DESC_3 EFX_ADD_AMOUNT_3
For each record that is successfully extracted from the file a record will be inserted into the table INT_EFX_TRIP_INBOUND with a status of Success. If a record is not successfully extracted, a record will be inserted into the same table with a status FAILURE. Depending on the status that is received with the record, different actions will be carried out including updating the trip and creating messages. The table on the next page documents the actions carried out for each status.
As part of the validation, before any action is taken we will check the following information
The trip exists on the system The trip is at the correct status for the information we have received from EFX The file has not been loaded before.
EFX Changes Required
EFX will be required to send additional status notification when :-
The trip has been successfully posted on EFX, The trip has not been successfully posted on EFX The trip time has EXPIRED A cancellation has been successful on EFX A cancellation has not been successful on EFX Extra payments added in EFX (completed status)
It is assumed that the failure messages will be passed back to MTS where appropriate so the user has visibility of issues..
Scope
This change will be applied to system version 10.5
Data
Add new field EFX_MESSAGE varchar2(1000) to SCH_TRIP.
Add the following new fields to INT_EFX_TRIP_INBOUND :-
EFX_MESSAGE VARCHAR2(1000) EFX_ADD_CHARGE_1 VARCHAR2(20) EFX_ADD_DESC_1 VARCHAR2(100) EFX_ADD_AMOUNT_1 NUMBER(20,2) EFX_ADD_CHARGE_2 VARCHAR2(20) EFX_ADD_DESC_2 VARCHAR2(100) EFX_ADD_AMOUNT_2 NUMBER(20,2) EFX_ADD_CHARGE_3 VARCHAR2(20) EFX_ADD_DESC_3 VARCHAR2(100) EFX_ADD_AMOUNT_3 NUMBER(20,2)
NB) These additional fields will also be added to the end of the existing interface layout (see appendix A for current layout – Is Owning Depot actually Accepting Depot ? If not then also add.).
System Registries
Existing system registries required are :-
'INT_READ_EFX_COMMENTED_OUT' set to ‘Y’ so only updates EFX Ref
'INT_READ_EFX_CURSOR' set to ‘Trip’ so uses Load Ref as Trip_ID not Bill_of_Lading.
FUNCTIONAL DESCRIPTION
Message Processing
A process already exists (INT_MSG.READ_EFX_TRIP_INBOUND) which is called by a database job running the procedure INT_MSG.FILE_HANDLING at regular intervals.
This process (for consumer the system registry 'INT_READ_EFX_COMMENTED_OUT' is currently set to ‘Y’ and 'INT_READ_EFX_CURSOR' is set to ‘TRIP’) so currently only updates the EFX_REF on the associated trip and orders using the provided load reference as the Trip ID.
The process currently only caters for a message type of ‘ACCEPTED’ or ‘REJECTED’ but this will be extended to also cover all of the new possibilities which ESI will now have to provide :-
Each of these message types will have the same validation as now and the additional validation on the carrier, current trip status (i.e. TENDERED) in MTS etc. but each process will be handled slightly differently and will do the following :-
If any of these updates fail then the INT_EFX_TRIP_INBOUND record will be updated with a RECORD_STATUS of ‘FAILURE’ and an appropriate value written to the VALIDATION_ERROR field.
NB) To data will be written to MSG_EVENT table as now with the EVENT_TYPE of ‘MSG_INT_SUB’ as they are not required.
The payments generated for the additional charge entered in EFX will have the following fields set from the values passed in:-
ID will go into Narrative 1 Desc will go into Narrative 2 Amount will go into Amount
Other hard coded values will be :-
Type will be ‘EFX1’, ‘EFX2’ or ‘EFX3’ Status will be ‘A’ (for Actual) Credit Account will be Trips Cost_Centre Debit Account will be the decoded Carrier ID Event Ref will be the Trip ID
Further possible enhancements that need additional discussions include keeping an EFX Status field against the trip so that the user can see if any actions are required by them (i.e. chase up potential carriers to encourage them to accept the trip on EFX).
Also to colour the EFX_Ref depending on its current status to give the user a visual interpretation of the status of the trip with regards to EFX (see appendix B for DHL’s suggested colours).
Trip Forms EFX Tab
A new Tab will be added to the Trip forms which will display all EFX related data such as status (including colouring), any message text passed back from EFX, last message sent/received types and dates.
This tab will also show information entered when posting EFX jobs and will allow Cancellations to be requested.
REFERENCES
Not Available
DOCUMENT HISTORY
| Initial version | ||||
| Changes following an internal meeting | ||||
| Reviewed and Issued |
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
