292356

DHL C-TMS

C-TMS Data Export

FUNCTIONAL SPECIFICATION - 10.7

17/10/2011 - 1.0
Reference: 292356 - PM8LZMTU

Client Requirement

C-TMS data export to feed into MIS (MS Access).

Solution

To facilitate off-line statistics and KPI reporting, C-TMS will be developed to provide a specific data export for Project Gamma. The data export will be uploaded into MS Access and DHL will develop an MIS solution around this data source.

The file will be created automatically by a C-TMS batch job that will be configured to run as required either daily or weekly.

The batch job will interrogate the transport data in C-TMS and create a tab delimited output file with .xls filename extension. The file will be saved in the exports archive folder and will be configured to be emailed to a list of email recipients.

Data will be selected for all trips created within the previous x days, x being a parameter that will be set into the configuration of the batch job.

The filename created will also be a parameter that will be configured into the batch job.

Note that the output can be configured to run from many different batch jobs each with different parameters. This means that for example an output for the last day could be generated daily as batch job A and then an output for the last week could be generated weekly as batch job B.

The output data will include trip, trip stop, order and order detail information. For each order, the trip and stop level data will be repeated in the output data file. The data will be sequenced by schedule date, owning depot then route code.

Scope

This change will be applied to system version 10.7

Set-up

Pre-requisites

None

Menu Structure

Unchanged

Data

Unchanged

Implementation Advice

EDI jobs will need to be set up in the EDI maintenance screen with the correct parameters and email recipients

Functional Description

Data Export

A new data extract “TRIP_AND_ORDER_EXT” will be developed.

The extract will be run from the EDI maintenance screen and example of which is displayed below.

The interval length value can be used to control the frequency of the extract. The extract will require parameters to control the file name and the date range. An example of parameters is shown below.

The parameters can then be used to control the data extracted. Any number of jobs can be set up in this way and the parameter information varied for each job. The email recipients of the job can be set up by clicking the Output button and entering the details as shown below.

Multiple email addresses can be stored against a job.

The extract will interrogate the C-TMS data and extract the following information

Export Field	C-TMS field
Cost Centre	SCH_TRIP.cost_centre
Schedule Date	SCH_TRIP.sched_name
Owning Depot	SCH_TRIP.owning_depot
Route Code	SCH_TRIP.route_code
Tractor	SCH_TRIP.tractor_id
Trailer	SCH_TRIP.trailer_id
Driver	RES_PERSON.forname surname or SCH_TRIP.external_driver depending on driver type
Vehicle Type	RES_TRAILER_CARRIER_GROUP.name
Driver Type	RES_PERSON.agency or SCH_TRIP.external driver columns
Start ODO	SCH_TRIP.odo_start
End ODO	SCH_TRIP.odo_end
Planned Km’s	SCH_TRIP.distance
Actual Km’s	Derived using SCH_TRIP_STOP values
Planned Hours	Derived using SCH_TRIP_STOP values
Actual Hours	Derived using SCH_TRIP_STOP values
Trailer Type	RES_TRAILER_TYPE.trailer_type
Trailer Capacity	RES_TRAILER_TYPE.max_rpe
Trip ID	SCH_TRIP.trip_id
Trip Status	SCH_TRIP.trip_status
Stop Identifier	SCH_TRIP_STOP.stop_type
Stop ID	SCH_TRIP_STOP.stop_id
Stop Sequence	SCH_TRIP_STOP.stop_no
Stop Location ID	SCH_TRIP_STOP.location_id
Stop Location Name	GEO_LOCATION.location_name
Stop Location Town	GEO_LOCATION.town
Stop Location Postcode	GEO_LOCATION.postcode
Stop Planned Arrival Date & Time	SCH_TRIP_STOP.arrive
Stop Planned Departure Date & Time	SCH_TRIP_STOP.depart
Stop Actual Arrival Date & Time	SCH_TRIP_STOP.actual_arrive
Stop Actual Departure Date & Time	SCH_TRIP_STOP.actual_depart
Stop PID	New field
Planned Km’s from prev stop	SCH_TRIP_STOP.distance_from_prev_stop
Actual Km’s from prev stop	SCH_TRIP_STOP.distance_from_prev_stop
Activity Load/Unload	SCH_HAULAGE_ACTIVITY.activity_name
Initial Load Collection	‘Y’ or ‘N’ Derived from trip data
Final Unload Delivery	‘Y’ or ‘N’ Derived from trip data
Order Transaction Date	SCH_ORD.created_date
SO Ref	SCH_ORD.external_ref
PO Ref	SCH_ORD.
Order Status	SCH_ORD.status
Early Avail Date & Time	SCH_ORD.early_avail
Late Avail Date & Time	SCH_ORD.late_avail
Early Delivery Date & Time	SCH_ORD.early_del
Late Delivery Date & Time	SCH_ORD.late_del
Address Location ID from	SCH_ORD.from_loc
Address Location Name From	GEO_LOCATION.location_name
Address Location Town From	GEO_LOCATION.town
Address Location Postcode From	GEO_LOCATION.postcode
Address Location Id to	SCH_ORD.to_loc
Address Location Name to	GEO_LOCATION.location_name
Address Location Town to	GEO_LOCATION.town
Address Location Postcode to	GEO_LOCATION.postcode
Item identifier	SCH_ORD_ITEMS.item_identifier
Item description	SCH_ORD_ITEMS.item_description
Stack Factor	SCH_ORD_ITEMS.stack
Item Factor	SCH_ORD_ITEMS.item_factor
Weight	SCH_ORD_ITEMS.weight
Ordered item Qty	SCH_ORD_ITEMS.qty_ordered
Ordered Qty Stacks	SCH_ORD_ITEMS.stack
Ordered Qty baskets	Calculated field derived from qty_ordered
Ordered Qty Loose units	Calculated field derived from qty_ordered
Actual Qty Units	SCH_ORD_ITEMS.qty_delivered
Actual Qty Stacks	SCH_ORD_ITEMS.stack
Actual Qty Baskets	Calculated field derived from qty_delivered
Actual Qty Loose units	Calculated field derived from qty_delivered
Reason Code	SCH_ORD_ITEMS_REASONS.reason_code
Reason Description	SCH_REASON_CODE.description
Reason Comment	SCH_ORD_ITEMS_REASONS.reason_comments

The values held within the syssched_from and syssched_to parameters will be used to control the trips extracted. The output data will include trip, trip stop order and order detail information. For each order the data will be repeated in the output file. The data will be sequenced by schedule date, owning depot and then route code. The file will be created tab delimited, the filename will be controlled by the input parameter and will have an “.xls” file extension.

The file will be saved in the location specified in the delivery folder of the EDI job. The file should then be emailed to all of the specified email recipients of the job.

Table Updates Required

None

Modules to be changed

Module Name	Module Type	Notes
DP_CSV5.sql	Package	Add new functionality

References

Ref No	Document Title & ID	Version	Date
1	EST-292356 PM-8LZMTU C-TMS data export	1.0	12/10/11

Glossary

Term or Acronym	Meaning
C-TMS	Calidus TMS

Document History

Version	Date	Status	Reason	Initials
0.1	14/10/11	Draft	Initial version	CAK
0.2	17/10/11	Draft	Reviewed	MJC
1.0	17/10/11	Issue	Issued	MJC

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager