291614

DHL C-TMS

C-TMS Interface Modifications

FUNCTIONAL SPECIFICATION - 10.7

18/11/11 - 2.0
Reference: FS 291614 NW-8L8JRN

FUNCTIONAL OVERVIEW

Client Requirement

Project Rigel - Modifications to XML Order Import (see section 4.1.1 in the System Design Document).

Solution

A new version of the standard XSD will be required to accommodate the new fields being sent into C-TMS.

It is currently assumed that the generic CSV order import will suffice for all of the customers not using the new XML format that requires the additional information for the carrier label production etc.

It is expected that most of the additional fields will be included within the ‘Order Sub Refs’ section of the XML.

These ‘Order Sub Refs’ will be defined on the ‘Decode’ tab in the ‘Import Maintenance’ screen.

The following items are to be included in the ‘Order Sub Refs’:

• Carrier Service Level
• COD Amount
• Currency Code
• Contact Surname
• Special Delivery (i.e. Saturday Delivery)
• Download ID (WISE Flow)
• Batch ID (WISE Flow)
• Message Medium
• SMS Address 1
• SMS Address 2
• SMS Address 3
• SMS Address 4
• Email Address
• Account Number
• Schedule Number
• Contract Number
• Sortation Hub
• Carrier Service Product Line 1
• Carrier Service Product Line 2
• Tracking Reference
• Tracking Number
• Trailer Restrictions
• Delivery Method
• Slot Code
• Key Code
• Pack No
• Packer Name (WISE Flow)
• Current Gazetteer Version
• Shipped Transport Order

A new section will be added to the XSD to allow the input of the additional hazardous information.

The hazardous information will be at DU level so data can be entered for each order line.

The following information will be included in the hazardous information:

• Hazardous Flag
• UN Number
• Hazardous Quantity
• Hazardous Description

Sub-references will be available at the trip and trip stop levels.

The ‘Order Header’ section will include the following customer information:

• Customer ID
• Customer Name

The ‘Order Header Address’ and ‘Stop Detail’ sections will include a new ‘Country Name’ item.

Scope

This change will be applied to system version 10.7.0.

FUNCTIONAL DESCRIPTION

TripOrder XML

The current XML order import is based on the format defined in the TripOrder.xsd (Appendix A for the XSD versions).

The new version 2.23 of the XSD has been created containing the following additional items:

• STOP_ETA_DATE
• STOP_ETA_AT_DATE
• ADDRESS_ORDER_COMMENTS
• ADDRESS_CONTACT_TYPE
• ACTUAL_FOOTPRINT
• HAZARDOUS
• UN_NUMBER
• HAZARDOUS_QTY
• HAZARDOUS_DESCRIPTION
• PALLET_ID

The new version 2.24 of the XSD will be created containing the following additional items:

• ADDRESS_COUNTRY_NAME
• STOP_COUNTRY_NAME
• CUSTOMER_ID
• CUSTOMER_NAME
• DIM_WEIGHT
• ACTUAL_DIM_WEIGHT
• DU_DESCRIPTION
• DU_CATEGORY
• UNIT_TRACKING_REF
• UNIT_TRACKING_NO

‘ADDRESS_COUNTRY_NAME’ and ‘STOP_COUNTRY_NAME’ will store the country name of the address in the ‘ORDER_HEADER_ADDRESS’ and ‘STOP_ADDRESS’ sections respectively.

‘CUSTOMER_ID’ and ‘CUSTOMER_NAME’ will store the customer information of the transport order in the ‘ORDER_HEADER’ section.

‘DIM_WEIGHT’ and ‘ACTUAL_DIM_WEIGHT’ will store the volumetric weight and they will be added to the ‘ORDER_DETAIL’ section.

‘DU_DESCRIPTION’ and ‘DU_CATEGORY’ will store the despatch unit data and they will be added to the ‘ORDER_DETAIL’ section.

‘UNIT_TRACKING_REF’ and ’UNIT_TRACKING_NO’ will store the tracking number data for the despatch unit and they will be added to the ‘ORDER_DETAIL’ section in a new repeating subsection called ‘TRACKING_REFS/TRACKING_REF’.

N.B. Multiple tracking references and number may be included for the quantity of despatch units, therefore, the tracking reference tags will be repeated in the ‘ORDER_DETAIL’ section for the despatch unit.

N.B. The ‘Customer Reference’ of the order will be stored in the <SO_REF> tag and the ‘Delivery Point Reference of the order will be stored in the <PO_REF> tag.

We will need to receive both a delivery type (as now) and an addition service level field that will need to be stored at order level rather than just in sub references to be used in determining the time windows (if not already populated by the external system) using the wholesale template as part of the new scheduling process.

Order Sub Refs Data

Most of the new data to be provided for the Rigel project as part of the XML order import will be passed through the existing optional <ORDER_SUB_REFS> section of the XSD.

The data provided against the <SUB_REF_IDENTIFIER> tag in the XML has to be decoded via the standard import decode table using a ‘Decode Name’ of ‘XML_REFERENCE’.

The data will be maintained on the ‘Import Maintenance’ form as follows:

The following is a list of possible decoded values (which need to be agreed with DHL Link) which can be used for the new data to be provided in the <SUB_REF_IDENTIFIER> tag:

N.B. It will be used to store the ‘Account Number’, ‘Schedule Number’ and ‘Contract Number’ for the customer at the despatching location of the trip for the carrier where these values are obtained at the appropriate combination of carrier/customer/location.

‘Sortation Hub’, ‘Carrier Service Type’, ‘Carrier Service Product Line 1’, ‘Carrier Service Product Line 2’, Customer Code’ and ‘Customer Name’ all refer to the order.

‘Shipment Tracking Reference’ and ‘Shipment Tracking Number’ refer to the shipment tracking data and may be stored here because the ‘Shipment ID’ is stored for each order.

N.B. The OMS reference for each transport order in the shipment will be included in the order sub-references section.

The decoded <SUB_REF_IDENTIFIER> data and its corresponding extracted <SUB_REFERENCE> data will be stored within C-TMS on the table called ‘SCH_ORD_REFERENCE’.

The new data once imported into C-TMS will be visible from the ‘ORDERS’ form on the ‘Add Refs’ (‘Additional References’) tab:

N.B. No code changes are required for this portion of the RIO.

Trip Sub Refs Data

A new section called ‘TRIP_SUB_REFS’ will be added to the new v2.24 of the ‘TripOrder’ XML format in the ‘TRIP_DETAIL’ section.

This section will be included once per trip and will have the same structure as the order sub-references section that exists already.

N.B. It will be used to store the ‘Meter Number’ for the cost centre of the trip for use in the generation of the electronic carrier manifests by DHL Link.

Stops Sub Ref Data

A new section called ‘STOP_SUB_REFS’ will be added to the new v2.24 of the ‘TripOrder’ XML format in the ‘STOP_DETAIL’ section.

This section will be included once per trip stop and will have the same structure as the order sub-references section that exists already.

They will be used by DHL Link in the generation of the electronic carrier manifests

Stop Signature

The signatory of the delivered items would be recorded usually in the ‘SIGNATURE’ or ‘SIGNED_BY’ tags of the ‘STOP_SIGNATURE’ section.

The ‘STOP_SIGNATURE’ section is maintained with the stop number of the trip.

However, for the return of POD by the parcel carrier the population of the data for the standard XML format cannot be fulfilled, therefore, the signatory will be stored as an order sub-reference.

Shipment ID

N.B. The shipment ID will be included in the ‘ORDER_HEADER_TMS/SHIPMENT_ID’ section and not the ‘ORDER_HEADER/TMS_REF’ section as stated in the mapping documents provided by DHL LinK.

Hazardous Data

The new hazardous fields are defined as follows:

The new hazardous fields will be added to the standard XSD at the <ORDER> level in a new section:

The standard XML order import package INT_XML_IN will be changed to extract these new values from their corresponding new tags (if they have been provided in the XML).

The data extracted from these tags will be written to the holding table INT_XML_ORD_DETAILS.

No validation will be performed on these new fields other than to ensure that the hazardous flag is either ‘Y’ or ‘N’ and that hazardous quantity is a valid number.

This holding table is then used to create/update the order lines (and/or order items).

In the case of the Rigel project we are only concerned with order lines so the package will be changed to use the data stored in the holding table in the new fields to populate the corresponding fields on the SCH_ORDER_LINE table when creating or updating records on this table.

N.B. The 4 new hazardous columns will need adding to the ‘INT_XML_ORD_DETAILS’ database table for update on the new ‘HAZARDOUS_ITEMS’ database table for the OMS reference.

We will not be considering the SCH_ORD_ITEMS table as part of this RIO.

There is another RIO for Unison that covers the consolidation of the orders based on loading package types which will need include the UN Numbers as part of the mapping (possibly consolidated into a comma separated string as part of CL01 record or a totally new section all together) in order to populate the correct fields in the XML by DHL Link.

Time Windows

There are various rules specified in the System Design Document to be used in determining the order time windows when they have not been provided in the XML.

It is assumed that the initial order import will use the code that already exists within the standard package when no time windows have been provided (although the document implies that DHL Link will always be setting dummy default values anyway) so no specific changes are required in for this in the XML order import package for the Rigel project.

The new automatic scheduling process (specified under RIO NW-8KEMH2 290935) will be using these rules to determine how the order will be scheduled and will adjust the time windows on the orders as appropriate.

Inbound Orders

The ‘INT_XML_IN.PROCESS_ORD_XML_IN’ procedure will be changed to process the new tags available in the ‘TripOrder’ XML file for the upload of inbound orders from Unison WMS and WISE WMS.

The ‘Special Instructions’ will store the packing instructions.

The ‘Carrier Instructions ‘will be stored as the ‘Order Comments’.

N.B. The ‘INT_XML_IN’ package will need to be changed to store the sub-references as sub-references and ensure that lane comments are identified and stored as lane comments for the transport order.

Order Processing

CIPD Message

The ‘CIPD’ message from Unison WMS to C-TMS will be processed as described in the functional specification for RIO ‘290934 NW-8KEN3X Parcel Carrier Management’.

CITD Message

The ‘CITD’ message from C-TMS to Unison WMS will be processed by DHL Link from an XML file in the ‘TripOrder’ format as described in the functional specification for RIO ‘290934 NW-8KEN3X Parcel Carrier Management’.

The message will be generated when the status of the trip is set to ‘EN-ROUTE’.

Electronic Messages

The ‘Electronic Manifest’ message from C-TMS to Unison WMS will be processed by DHL Link from an XML file in the ‘TripOrder’ format as described in the functional specification for RIO ‘290934 NW-8KEN3X Parcel Carrier Management’.

The message will be generated when the status of the trip is set to ‘EN-ROUTE’.

N.B. The same file will be used by DHL Link for the production of the CITD message and the electronic manifests as the XML file will be produced by the same trigger point in C-TMS.

The XML tags will be within the <EVENT_DETAIL> section.

A summary of the mapping may be seen below:

N.B. All of the messages between C-TMS and DHL Link will use the ‘TripOrder’ format and DHL link will map the data from the file provided to the type for the carrier.

Return of POD

The carriers will send a POD file to DHL Link to confirm the delivery of the items and DHL Link will send to C-TMS an XML file in the ‘TripOrder’ format.

A new inbound EDI process will be developed and setup in the ‘EDI Maintenance’ screen:

The new flow type of ‘CARPOD_XML’ indicates the new limited data format and will be created for this development as follows:

The filename format is expected to be:

‘CARPOD_{CARRIER_ID}_{SEQUENCE_NUMBER}.XML

‘CARPOD’ indicates a ‘Carrier POD’ message and the sequence number will be generated by DHL Link to form a unique filename.

A check will be performed to ensure that a duplicate filename is not processed: if a duplicate file is received then the status of the interface record will be set to ‘FAILURE’ and the order data will not be updated.

The file is expected to contain the following data in the XML tags for it to be identified and processed correctly:

The order sub-references in section ‘ORDER_SUB_REF’ will be used to store the carrier’s POD information for the despatch unit scanned by the carrier when it is delivered:

Opening and closing tags will be required to enable the ‘ORDER_SUB_REF’ section to be accessed:

• <OBS_XML>
• <EVENT>
• <EVENT_DETAIL>
• <STOPS>
• <STOP>
• <ORDERS>
• <ORDER>
• <ORDER_SUB_REFS>

The order sub-references will be populated because of the limited amount of data available from the carriers: the stop-level information will not be provided and the standard order references will be unknown (e.g. OMS, customer, delivery point or booking references).

It is expected that the carrier will be able to provide the individual despatch unit tracking reference printed on the label, plus the actual delivered quantity, actual stop arrival and/or departure times and a description of a non-conformance.

The transport order/shipment, trip and delivery stop will need to be obtained from the tracking reference provided: this assessment may be performed using the order tracking information that is stored against the OMS reference for each tracking reference generated during the production of the labels. The ‘SCH_ORD_TRACKING’ database table contains this information and a ‘REF_TYPE’ of ‘D’ indicates a despatch unit.

In the future it may be possible for the carrier to return more information; therefore, the ‘TripOrder’ format may be used more fully.

The XML tags will be validated and mapped to the following C-TMS database items:

A new database package called ‘INT_XML_CARPOD’ will be developed to process the inbound XML file in the new format.

A database job will be created via the ‘Start’ button and it will call the new procedure ‘INT_XML_CARPOD.PROCESS_CARPOD_XML_IN’ to process the file.

The procedure will update the database tables as follows:

A unique reason code of ‘CPOD’ (i.e. ‘Carrier POD’) will be created for the non-conformance descriptions advised by the carrier with a usage type of ‘PO_REASON’.

The ‘SCH_ORD_NON_CONFORM’ database table is maintained for the order at the trip stop and will contain the following data:

It is expected that there can be multiple tracking references returned but only single values for the stop times, non-conformance description and signatory: this is because multiple tracking references can be scanned at the delivery location but the other information is specific to the trip stop itself.

It is expected that a file will contain all of the despatch units delivered, but if despatch units are sent in different files then the first stop time, non-conformance description and signatory will be retained from the first file; only the delivered quantity will be updated for subsequent files processed for tracking references found for the same transport order/shipment.

N.B. The ‘POD’ flag of the transport order/shipment will not be updated to ‘Y’ because this process is not an official POD confirmation process.

INT_XML_IN Package

The ‘INT_XML_IN’ package will be changed to process the new inbound XML order files.

INT_XML_OUT Package

The ‘INT_XML_OUT2’ package will be changed to generate the new outbound XML files.

A change will be required to ensure that the order sub-references are used correctly for the client: a new cost-centre level system parameter called ‘USE_XML_ORDER_SUB_REFS’ will be created and if it is set to ‘Y’ then the order sub-references will be included in the TripOrder XML file and they will be obtained from the ‘SCH_ORD_REFERENCE’ database table.

If it is not set to ‘Y’ then the order sub-reference section will continue to use the existing functionality to display the lane comments

IMP Package

The ‘IMP’ package will be changed to process the new inbound XML order file from Unison.

The file can contain the mandated carrier so a ‘CARRIER_ID’ field type will be required.

REFERENCES

Ref No	Document Title & ID	Version	Date
1	EST-291614 NW-8L8JRN C-TMS Interface Modifications v1.0.doc	1.0	22/09/11
2	FS-290934 NW-8KEMRU Parcel Carrier Management v3.0.doc	3.0	17/11/11
3	FS-291211 NW-8KND7U Modifications to Order Entry v2.0.doc	2.0	17/11/11
4	MSDV08Q410.CTMS_DHLTD_Manifest.xls	1.0	17/11/11
5	MSDV08Q410.CTMS_Yodel_Manifest.xls	1.0	17/11/11
6	MSDV09Q411.CTMS_Movianto_Manifest.xls	1.0	17/11/11
7	MSDV09Q411.CTMS_ParcelForce_Manifest.xls	1.0	17/11/11
8	MSDV09Q411.CTMS_PolarSpeed_Manifest.xls	1.0	17/11/11
9	MSDV08Q410.Unison_CIOC_CTMS.xls	1.0	17/11/11
10	MSDV07Q408.CTMS_Unison_LOAD.xls	1.0	17/11/11
11	MSDV08Q410.CTMS_Unison_CITD.xls	1.0	17/11/11
12	MSDV08Q410.Baxter_IFTMIN_CTMS.xls	1.0	17/11/11
13	MSDV08Q410.Baxter_DESADV_CTMS.xls	1.0	17/11/11
14	MSDV08Q410.CTMS_Baxter_IFCSUM.xls	1.0	17/11/11

DOCUMENT HISTORY

Version	Date	Status	Reason	Initials
0.1	28/09/11	Draft	Initial version	DJM
0.1	28/09/11	Draft	Review of the initial draft	PJH
1.1	26/10/11	Draft	Inclusion of new interface messages	PDR
1.2	14/11/11	Draft	Notes added following meetings	DRM
1.3	17/11/11	Draft	Updated after meeting with client on 15/11.	PDR
2.0	18/11/11	Issued	Issued to client	PJH

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager