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

E-MAIL

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
E-MAIL	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

Ref No	Document Title & ID	Version	Date
1	EST-291375 – MS-8KNHTD EFX	1.0	23/09/11

Glossary

Term or Acronym	Meaning
C-TMS	Calidus TMS

Document History

Version	Date	Status	Reason	Initials
0.1	07/10/11	Draft	Initial version	CAK
0.2	10/10/11	Draft	Reviewed	MJC
0.3	11/10/11	Draft	Revised	CAK
1.0	11/10/11	Issue	Reviewed and Issued	MJC
1.1	21/10/11	Draft	Revised after review by S Allen	CAK
2.0	21/10/11	Draft	Reviewed and issued by email, left in draft form for ease of review, to be cleared before posting to RIO	MJC
2.1	24/10/11	Draft	Revised	CAK
3.0	24/10/11	Issue	Reviewed and Issued	MJC

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager