271598
271598 - PA-7XVMF8/ EFX Interface Sub-Con
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
If a Trip is being subcontracted and has not been previously posted on EFX, once a subcontractor (non-own fleet) is allocated and a payment added, when the trip is changed to a status of 'ACCEPTED' the details of the trip and subcontractor details will be interfaced into EFX as an accepted load. Confirmation E-Mail and Fax will be generated automatically from EFX.
This will involve a new trigger in MTS to send a message in the same format as RIO PA-7XVL6X
Solution
Once a trip has been set to Accepted and a payment is created, a trigger will run to send a trip accepted message to EFX. If a sub contractor is added as a carrier, a payment must be created for a trip to be set to Accepted.
The trigger will run under the following conditions
EFX_SEND_FLAG is null CARRIER_ID is not own fleet STATUS is ACCEPTED Payment record exists for Carrier charges between the carrier and the cost centre for the trip.
Currently when a trip is set to TENDERED, this triggers the procedure WRITE_TRIP_DTL_MSG which inserts a record into INT_TRIP_DTL for the trip with a status NEW. A database job will then run at regular intervals to find any NEW records in this table and run the procedure INT_MSG.PROCESS_OUTSTANDING_TRIP_DTL. This procedure selects information from the trip and creates a file to be sent to EFX.
When the STATUS is set to ACCEPTED and the additional conditions as detailed above are met, the procedure will also be triggered.
To process the ‘ACCEPTED’ trips a new cursor will be added which checks the status of the trip. If the status is tendered then the procedure will run as it currently does. If the status is ACCEPTED, the filename will be changed from EFX_TRIP_DETAIL_XXXXXX to EFX_SUB_DETAIL_XXXXXX.
The information within the file will be identical whether the TRIP is TENDERED or ACCPETED.
EFX Requirements EFX will be required to distinguish between the two file names . The data within the files will be identical, but files with the name EFX_SUB_DETAIL should be treated as posted jobs which have already been ACCEPTED and files with the name EFX_TRIP_DETAIL should be treated as jobs available for TENDER.
When the already ACCEPTED trips are entered on EFX, EFX should continue to send out confirmation e-mails and faxes as they would when a TENDERED job is accepted
Scope
This change will be applied to system version 10.5
FUNCTIONAL DESCRIPTION
A trigger already exists in MTS called TIU_TRIP_STATUS on the SCH_TRIP table which when a trip goes to status ‘ACCEPTED’ from either ‘PLANNED’ or ‘TENDERED’ then it writes a record to the MSG_EVENT file.
This trigger is going to be amended so as well as the above it will also send out a message to EFX if all of the appropriate criteria is matched in the same format as that specified under RIO PA-7XVM64.
Firstly it will check that the EFX_SEND_FLAG is NULL. i.e. Trip has not already been sent to EFX.
Next it will check if the Carrier_ID (which must be set and not ‘EFX’) that is assigned to the trip does not belong to its own fleet. i.e. Where the Owning_Depot on the trip is not the same as the Hub_Location (from RES_CARRIER) for the carrier.
Also the process will check that a payment record exists between the carrier and the cost centre for this trip. i.e. An ACC_PAYMENT record exists for the trip (Event_Ref is ‘Schedule-Trip’) with Debit_ACC set to the trips Cost_Centre and a Credit_ACC set to the Carrier_ID.
When all of the above criteria is met then a record is written to the INT_TRIP_DTL table by calling the standard procedure INT_MSG.WRITE_TRIP_DTL_MSG passing in the Schedule Name, Trip ID and Message Type of 'EFX_TRIP_DTL'.
A database jobs runs at regular intervals and calls a procedure called PROCESS_OUTSTANDING_TRIP_DTL in the INT_MSG package which reads these records and if appropriate then this it in turn calls the PROCESS_EFX_TRIP_DTL_MSG procedure in the same package.
This procedure needs changing to check the carrier id of the trip.
If the carrier ID is ‘EFX’ then it is a request in the original format (as specified under call 271600) so will create a file with the name as now. i.e. ‘EFX_TRIP_DETAILS_’<next sequence from seq_dsg_trip_dtl_msg>.
If the carrier ID is not ‘EFX’ then it is a request in the new format so will create a file with the name ‘EFX_SUB_DETAILS_’<next sequence from seq_dsg_trip_dtl_msg>.
ESI will use the file name to determine if we are sending an initial request to be loaded into EFX (as now) or we are sending a new request to load an Accepted Status trip into EFX and they will adjust there output to EFX accordingly.
The data put into the file itself will be identical to the existing code except that the new field of preferred site will be blank.
Also the expiry date on the output file is currently set from SCH_TRIP.TRIP_EXPIRY_TIME but this will be blank for sub-contractor trips. This needs to be confirmed prior to development.
The trip cost is currently set from SCH_TRIP.TRIP_COST which in turn is updated when the cost is entered manually on the EFX payments screen but for sub-contractor trips then this may need to be re-calculated from all of the appropriate ACC_PAYMENT records (excluding the new EFX1,2 and 3) costs (see spec for 271604).
Once loaded, EFX will then use this information to E-Mail/Fax the confirmation to the supplier (Carrier/Sub-Contractor) as normal.
REFERENCES
Not Available
DOCUMENT HISTORY
| Initial version | ||||
| Update following meeting | ||||
| Revised and Issued |
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
