286647

DHL MTS

Tokairo Interface

FUNCTIONAL SPECIFICATION - 10.6

- 1.1

Reference: FS 286647 PM-8ELMYE

Client Requirement

Change Request Summary:

Integration Tokairo.

Change Request Details:

Tokairo debrief data to C-TMS. Tokairo scan recognises main debrief info from the driver trip sheet/delivery note that will be interfaced to C-TMS.

Benefits identified as a result of the change:

Integration to and from Tokairo to C-TMS.

Solution

A new extract will be written that will create files to be sent from C-TMS to Tokairo via a database job and this process will replace the existing one for Openfield. The new process will be performed per customer and this will be reflected in the filename format which will include the customer. The existing process will need to exclude customers by the same method (e.g. a tick box for the Openfield customer group).

The trip ID and the stop number of the loading and unloading activities will be appended to the new outbound file, created via the database job that creates the daily Tokairo extract, for the collection and delivery trips for the order.

The orders will be updated with the information scanned on the proof of delivery document from a file sent by Tokairo via ESI.

The process has been implemented in a different database and will be implemented in the INDTST and INDPRD databases for use by Openfield but the existing process will not be used: the process used by Openfield will be a XML file based on the format described in ‘TripOrder v2.13.xsd’.

The use of the XML file for inbound flows will enable more information to be extracted and updated in C-TMS: the orders and the trips will be updated with the following data:

1. Collection Trip Arrival and Departure Dates and Times
2. Delivery Trip Arrival and Departure Dates and Times
3. Delivered Weight
4. Weighbridge Ticket Number
5. Trip Vehicle
6. Trip Trailer
7. POD Flag

The existing version uploads a file into C-TMS via a Perl script and this functionality will be retained in the database for use by other customers.

A new procedure will be required to perform the update of the data (including the running of the procedure to update the POD name and revalidate the order).

The inbound flow of type ‘TRIP_XML’ will be setup in the ‘Edi’ tab page of the ‘Import’ screen and the filename must be unique for the customer for it to be identified and run the new process for Openfield.

The existing files from Tokairo may be processed as at present because they will not contain data for Openfield.

New scripts, database jobs and directories specific to the databases will need to be created to enable the outbound and inbound files to be processed.

Changes will be made for the file sent from C-TMS to Tokairo via ESI so Tokairo and ESI must be able to identify the source of the file to determine which orders will be processed for which customer (e.g. Openfield on the IND database).

N.B. The development required by ESI and Tokairo for the new flows and filenames is not included in this estimate.

Scope

This change will be applied to system version 10.6

Set-up

Pre-requisites

1. A new column will be added to the ‘ORG_CUSTOMER’ table for the ESI interfaces for Tokairo.

1. A new EDI flow type of ‘POD_XML’ is required.

Menu Structure

‘Unchanged’

Data

1. A new tick box will indicate which customer groups will be included in the new Tokairo extract.

1. A new EDI flow type of ‘POD_XML’ is required to run the process to upload the file from Tokairo to C-TMS.

Functional Description

Customer Groups

A new column called ‘TOKAIRO_GROUP’ will be added to the ‘ORG_CUSTOMER_GROUP’ table.

A tick box called ‘Tokairo Group’ will be added to the ‘Customer Group’ tab page of the ‘Customers’ screen in the ‘CUST_COST’ form to indicate if the customer group will be included in the new Tokairo extract:

If the box is ticked then the column will be stored as ‘Y’ and if it is not ticked then the column will be stored as ‘N’.

See Appendix A for the creation of the new column.

EDI Flow Type

A new EDI flow type of ‘POD_XML’ is required to run the process to upload the file from Tokairo to C-TMS.

See Appendix A for the creation of the EDI flow type.

Extracts to Tokairo (‘CSV’ Package)

A new Tokairo extract will be written for the Openfield customer group so that it may be run via a database job (expected to run daily at ‘00:05’).

This extract will create ‘csv’ files to be sent from C-TMS to Tokairo. The new process will be performed per customer and this will be reflected in the filename format which will include the customer.

The ‘CSV’ package will be changed to include a new procedure and a new function: the procedure will be called ‘P_TOKAIRO_CUST_EXPORT’ and the function will be called ‘F_WRITE_TOKAIRO_CUST_EXPORT’.

Procedure ‘P_TOKAIRO_CUST_EXPORT’ will not need to receive any parameters and will be run via a database job. It will be based on the existing procedure called ‘TOKAIRO_DAILY’ and may be run as an alternative to that procedure or as an accompaniment.

The outbound path will be obtained from the system parameter called ‘TOK_OUTBOUND_PATH’ but the filename will be named to include the customer code to make it unique.

Function ‘F_WRITE_TOKAIRO_CUST_EXPORT’ will receive five parameters from procedure ‘P_ TOKAIRO_CUST_EXPORT’ and it will be based on the existing function ‘WRITE_TOKAIRO_EXPORT’.

Function ‘F_WRITE_TOKAIRO_CUST_EXPORT’ will include an extra four items in the extract:

• Collection Trip ID
• Collection Trip Stop Number
• Delivery Trip ID
• Delivery Trip Stop Number

The ‘Delivery Trip ID’ and ‘Delivery Stop Number’ will be obtained from the unloading activities over the previous day for the customers that have their customer group ticked as a ‘TOKAIRO_GROUP’. Therefore, the tick box will control the query by passing each customer group identified so that separate files are generated with the filename format of ‘TOK_{CUSTOMER_GROUP}_{SYSDATE}.csv’. The customer ID assessed for the customer group will be that of the order (i.e. column ‘CUSTOMER’ on table ‘SCH_ORD’).

The ‘Collection Trip ID’ and the ‘Collection Stop Number’ will be obtained from the loading activity at the ‘From Location’ of the order and it may be assumed that there will be a collection trip for a delivery trip to exist.

If the collection trip and the delivery trip are the same then they will both be displayed.

The items reported will be as follows:

Item	Name	Format
1	Order Customer Name
2	Order Customer ID
3	Delivery Trip Carrier Name
4	Delivery Trip Carrier ID
5	Order External Reference
6	Order Booking Reference
7	Order Delivery Point Reference
8	OMS Reference
9	Order Final/To Location Name
10	Order Final/To Location ID
11	Delivery Trip Stop Arrival Date	DD/MM/YY
12	Order Early Available Date	DD/MM/YY
13	Delivery Trip Owning Depot Name
14	Delivery Trip Owning Depot ID
15	Collection Trip ID
16	Collection Trip Stop Number
17	Delivery Trip ID
18	Delivery Trip Stop Number

The existing procedure and function in the ‘CSV’ package used to send files from C-TMS to Tokairo will be changed to exclude any customers that have their customer group ticked as an ‘TOKAIRO_GROUP’: the function ‘WRITE_TOKAIRO_EXPORT’ will be changed to reference the system parameter and exclude the orders for that customer in the query. If the system parameter does not exist, or does not contain any customers, then the extract will not exclude any customers and will work as at present.

Updates from Tokairo (‘INT_XML_IN’ Package)

The orders will be updated with the information scanned on the proof of delivery document from a file sent by Tokairo via ESI.

The process has been implemented in a different database and will be implemented in the INDTST and INDPRD databases for use by Openfield but the existing process will not be used: the process used by Openfield will be a XML file based on the format described in ‘TripOrder v2.12.xsd’.

The inbound flow of type ‘POD_XML’ will be setup in the ‘Edi’ tab page of the ‘Import’ screen and the filename must be unique for the customer for it to be identified and run the new process for Openfield.

For example:

New scripts, database jobs, system parameters and directories specific to the databases will need to be created to enable the inbound files to be processed.

The database job will be generated when the EDI process is started, for example:

DECLAREg_process_name edi_process_header.process_name%TYPE;BEGINg_process_name := 'POD_OPENFIELD;INT_XML_IN.PROCESS_POD_TRIP_XML_IN(g_process_name);COMMIT;END;

The use of the XML file for inbound flows will enable more information to be extracted and updated in C-TMS: the orders and the trips will be updated with the following data:

Item	Table
1	Collection Trip Arrival Dates and Times
2	Collection Trip Departure Dates and Times
3	Delivery Trip Arrival Dates and Times
4	Delivery Trip Departure Dates and Times
5	Delivered Weight
6	Weighbridge Ticket Number
7	Trip Vehicle
8	Trip Trailer
9	POD Flag

A new procedure will be required to perform the update of the data (including the running of the procedure to update the POD name and revalidate the order).

The new procedure will be called ‘PROCESS_POD_TRIP_XML_IN’ and will be in the ‘INT_XML_IN’ package.

The XML data will be stored on the ‘INT_XML_TRIP_STOP‘, ‘INT_XML_ORD_HEADER’ and ‘INT_XML_ORD_DETAILS’ tables.

The data will be obtained from the following XML tags:

Item	Tag
1	STOP_ACTUAL_ARRIVAL_DATE
2	STOP_ACTUAL_DEPARTURE_DATE
3	STOP_ACTUAL_ARRIVAL_DATE
4	STOP_ACTUAL_DEPARTURE_DATE
5	ACTUAL_WEIGHT
6	SUB_REFERENCE
7	TRACTOR
8	TRIP_TRAILER_ID
9	N/A

The ‘SUB_REFERENCE’ tab will be extracted as described in functional specification ‘FS-286642 PM-8ELM6Z Integration Orders v1.0.doc’ and it will be a value for the decode name ‘ORDER_SUB_REFS’ and type ‘ORDER’.

The standard validation for a trip stop dates and times will be included, for example:

• Actual Depart Time is before the Actual Arrive Time
• Actual Arrive Time is after the Actual Depart Time on the stop
• Actual Depart Time is before the Actual Arrive Time on the stop

Other validation will be performed as described below:

• The weight is numeric and is not negative
• The tractor exists
• The trailer exists

After the file received has been validated (for the trip and orders) it will update the following data:

Item	Table	Column
1	SCH_TRIP_STOP	ACTUAL_ARRIVE
2	SCH_TRIP_STOP	ACTUAL_DEPART
3	SCH_TRIP_STOP	ACTUAL_ARRIVE
4	SCH_TRIP_STOP	ACTUAL_DEPART
5	SCH_ORD	ACTUAL_WEIGHT
6	SCH_ORD_REFERENCE	Decoded Value
7	SCH_TRIP	TRACTOR_ID
8	SCH_TRIP	TRAILER_ID
9	SCH_ORD	POD

This procedure will also updated the POD name and revalidate the order as per the Perl script ‘pod_upload.pl’. This script runs the ’READ_POD_INT_FILE’ procedure in the ‘INT_MSG’ package and the code to update and validate the order will need to be included in the new procedure: this will involve updating the item ‘SCH_ORD.POD’ to ‘Y’ and running the function ‘OMS.APPLY_POD’ if the XML extract has been successful. If the update is not successful then it will need to report the failed upload for the order.

The existing version uploads a file into C-TMS via a Perl script and this functionality will be retained in the database for use by other customers.

The existing files from Tokairo may be processed as at present because they will not contain data for Openfield.

Table Updates Required

The new tick box and EDI flow type may be created by the following scripts:

ALTER ORG_CUSTOMER_GROUP ADD TOKAIRO_GROUP VARCHAR2(1); / INSERT INTO EDI_FLOW_TYPES (FLOW_TYPE,COMMAND_SQL,TRIGGER_TYPE) VALUES ('POD_XML','INT_XML_IN.PROCESS_POD_TRIP_XML_IN',); /

References

Ref No	Document Title & ID	Version	Date
1	EST-286647 PM-8ELMYE Tokairo Interface v1.0.doc	1.0	18/03/11

Glossary

Term or Acronym	Meaning

Document History

Version	Date	Status	Reason	Initials
0.1	24/03/11	Draft	Initial version	PDR
1.0	28/03/11	Issue	Reviewed and Issued	MJC
1.1	29/03/11	Draft	Included collection trip ID and stop number to send to Tokairo.	PDR

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager