291375
DHL C-TMS
EFX
FUNCTIONAL SPECIFICATION - 10.7
24/10/11 - 3.0
Reference: FS 291375 - MS8KNHTD
Client Requirement
Consultation on message format, mapping through DHL Link and implementation support. Ensure messages are at trip level not order level. Differentiate internal v external so notice-board or preferred carrier as appropriate. Possible requirement to store EFX site number against the C-TMS customer account.
Solution
The existing XML process to send a file between C-TMS and EFX will be used by Network Rail. The message flows supported are pre-advise from C-TMS to EFX, EFX number from EFX to C-TMS, Debrief update including final cost from C-TMS to EFX, Status change from C-TMS to EFX to confirm debrief. All message flows are written to the EFX XML schema format and are transported between the two applications via DHL LINK. DHL Link do no transformation of the data but do validate against the EFX schema.
The integration flows are implemented on the Industrial database and existing system parameters will be utilised for Network Rail. The system Parameter EFX_XML_SYSTEM. will be changed from a system parameter to a cost centre parameter to allow the system to distinguish between NETWORK RAIL and other EFX users
The existing flag EFX group held at Customer Group level will be set for the relevant Network Rail customer groups and the Customer group name will be added to the filename formats to send to EFX.
EFX_CTMS_TRIP_{CUSTOMER_GROUP}_{sequence_number}.xml
A new field will be added to the Customer table, ORG_CUSTOMER called EFX site and this will be sent in the site field when sending messages to EFX. This will rely on all orders scheduled on a trip being for the same customer group. This is to differentiate the different accounts Heavy and Ad-hoc and non_Heavy.
Decodes will be set up to translate carriers and Network rail sites For EFX.
Where Network Rail have chosen to sub contract a trip to another DHL fleet a preferred carrier will be sent to EFX in the carrier field.
The EFX message will be sent at Trip level The message content will be changed to reflect the required trip details. A new COST_CENTRE parameter will be created called EFX_XML_TYPE and this will be set to ORD or TRIP. This new parameter will be used to control which data format is sent to EFX for Network Rail this parameter will be set to TRIP
OUTBOUND Brief and Debrief Message
SYSTEM
SITE
REFERENCE
CREATION DATE
CUSTOMER
DEADLINE
LOAD LOCATION NAME
POSTCODE
START
FINISH
DELIVERY LOCATION NAME
POSTCODE
START
PRODUCT INFO
PALLET LIFTS
WEIGHT
UOM
PALLET TYPE
NET PRICE
CONTACT NAME
PHONE
FAX
SITE
OTHER
EFX VENDOR
TRIP RATE
KEY LATER
OUTBOUND Debrief Status Message
SYSTEM
SITE
REFERENCE
CREATION DATE
MESSAGE TYPE
EFX REF
CUSTOMER
SERVICE LEVEL
DEBREIF DATE
INBOUND EFX – This will retain the existing XML format.
SYSTEM
SITE
REFERENCE
CREATION DATE
EFX REF
CUSTOMER
STATUS
The cancellation message sent to EFX will be amended to send the EFX site attached to the customer. This will be controlled by the cost centre parameter EFX_XML_SYSTEM.
The trigger points for sending an EFX message will be based on the existing trigger points
The example files included below illustrate how the message content will be created;
The brief message sends the pre-advice to EFX from C-TMS
[[Image:]]
EFX responds with the EFX number
[[Image:]]
Note the Brief message format is sent again to EFX from C-TMS once the trip is debriefed and the final cost is established.
Finally the debrief format is send from C-TMS to change the status in EFX
[[Image:]]
Scope
This change will be applied to system version 10.7
Set-up
Pre-requisites
None
Menu Structure
Unchanged
Data
A new cost centre EFX_XML_SYSTEM parameter will be created for the Network Rail cost centre.
A new cost centre based system parameter EFX_XML_TYPE will be created. And set to TRIP for Network Rail.
A column EFX_SITE will be added to the ORG_CUSTOMER table.
Decode values will be created to translate carriers and network rail sites.
Implementation Advice
A system super user will be required to ensure that the system parameters are correctly set.
[[Image:]]
Functional Description
EFX Trip Messages
The existing XML process used to send a file between C-TMS and EFX will be used by Network Rail. A new cost centre based parameter EFX_XML_SITE will be created for Network Rail.
A new EFX_XML_SITE cost centre base parameter will be created for Network Rail which will be then used to determine how messages are processed. EFX messages are generated when the status of a trip is changed to ACCEPTED or CONFIRMED and when a trip is debriefed. When a message is to be created a record is created in the trip detail table INT_TRIP_DTL for processing by the outbound message creation routine.
A new cost centre parameter EFX_XML_TYPE will be created this will determine if the EFX messages generated should be at trip or order level, For Network Rail this will be set to Trip level.
Messages are retrieved and processed using the INT_MSG package this package will be amended to find the value of the EFX_XML_TYPE parameter if this parameter is set to ‘TRIP’ new functionality will be called, for any other value the existing processes will be followed.
Processing is based on all customers on the trip belonging to the same customer group as the group name is included in the file names to differentiate between the different accounts Heavy, Non Heavy or Ad Hoc.
A new function will obtain the information required to format the EFX message in line with the EFX schema.
The customer group will be obtained for the order and this value will then be used to build the file name e.g. “EFX-CTMS-TRIP-customer group-YYYYMMDD HH24MISSFF2”
The site number should be obtained form the ORG_CUSTOMER table column EFX_SITE for the relevant customer.
It is expected that decode values will be created and used to set up the carriers and Network rail sites to be used by DHL Superusers.
If the trip has been sub contracted to another DHL fleet or a specific subcontractor the preferred carrier will be sent in the carrier field.
Due to limitations within EFX all trips which contain multiple orders should only contain the location /product details of the first order the customer references of all other orders should be strung together and added into the <OTHER> tag field and example is included below
[[Image:]]
The fields required in the message are detailed below
| XML Tag | Value |
| SYSTEM | VALUE OF PARAMETER EFX_XML_SITE |
| SITE | ORG_CUSTOMER.EFX_STIE |
| REFERENCE | FILENAME-TRIP_ID |
| CREATION_DATE | SYSDATE(correctly formatted) |
| CUSTOMER | SCH_ORD.CUSTOMER |
| DEADLINE | SCHED_NAME |
| LOAD LOCATION NAME | SCH_HAULAGE_ACTIVITY.LOCATION_ID |
| POSTCODE | GEO_LOCATION.POSTCODE |
| START | SCH_HAULAGE_ACTIVITY.ARRIVE |
| FINISH | SCH_HAULAGE_ACTIVITY.DEPART |
| DELIVERY LOCATION NAME | SCH_HAULAGE_ACTIVITY.LOCATION_ID |
| POSTCODE | GEO_LOCATION.POSTCODE |
| START | SCH_HAULAGE_ACTIVITY.ARRIVE |
| PRODUCT INFO | DERIVED |
| PALLET LIFTS | 1 |
| WEIGHT | SCH_ORD.WEIGHT(total of all orders) |
| UOM | VALUE OF PARAMETER EFX_XML_UOM |
| PALLET TYPE | PRODUCT_TYPE |
| NET PRICE | SCH_ORD.ORD_COST(total of all orders) |
| CONTACT NAME | RES_CARRIER.CONTACT |
| PHONE | RES_CARRIER.PHONE |
| FAX | RES_CARRIER.FAX |
| RES_CARRIER.EMAIL | |
| SITE | ORG_CUSTOMER.EFX_SITE |
| OTHER | DERIVED |
| EFX VENDOR | DERIVED FROM DECODE |
| TRIP RATE | SCH_ORD.ORD_COST(total of all orders) |
| KEY LATER | N |
EFX Debrief Messages
When creating messages for debrief a new function (INT_XML_EFX.process_trip.efx_deb) will be created. The new function will obtain the information required to format the EFX message. The customer group will be obtained for the order and this value will then be used to build the file name e.g. “EFX-CTMS-DEBRIEF-customer group-YYYYMMDD HH24MISSFF2”. The site number will be obtained form the ORG_CUSTOMER table column EFX_SITE for the relevant customer.
Processing is based on all customers on the trip belonging to the same customer group as the group name is included in the file names to differentiate between the different accounts Heavy, Non Heavy or Ad Hoc.
The fields required in the message are detailed below
| XML Tag | Value |
| SYSTEM | VALUE OF PARAMETER EFX_XML_SITE |
| SITE | ORG_CUSTOMER.EFX_SITE |
| REFERENCE | FILENAME-TRIP_ID |
| CREATION DATE | SYSDATE |
| MESSAGE TYPE | Debrief |
| EFX REF | SCH_TRIP.EFX REF |
| CUSTOMER | SCH_ORD.CUSTOMER |
| SERVICE LEVEL | 3 |
| DEBRIEF DATE | SYSDATE |
EFX Inbound Message
The format of the inbound message is unchanged. The existing inbound process will be amended to check the EFX_XML_TYPE parameter if this parameter is set at TRIP the EFX number should be used to update the trip, for all other values the order should be updated as in the current process.
EFX Cancellation Message
The current EFX cancellation message will be changed to check the EFX_XML_TYPE parameter if this parameter is set at TRIP the site should be obtained from the ORG_CUSTOMER.EFX_SITE value
Table Updates Required
[[Image:]] [[Image:]]
Modules to be changed
| Module Name | Module Type | Notes |
| INT_MSG.sql | Package | Additional Functionality |
| INT_XML_EFX.sql | Package | Additional Functionality |
References
| EST-291375 – MS-8KNHTD EFX | |||
Glossary
| C-TMS | Calidus TMS |
Document History
| Initial version | ||||
| Reviewed | ||||
| Revised | ||||
| Reviewed and Issued | ||||
| Revised after review by S Allen | ||||
| Reviewed and issued by email, left in draft form for ease of review, to be cleared before posting to RIO | ||||
| Revised | ||||
| Reviewed and Issued |
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
