292356: Difference between revisions
| Line 38: | Line 38: | ||
EDI jobs will need to be set up in the EDI maintenance screen with the correct parameters and email recipients | EDI jobs will need to be set up in the EDI maintenance screen with the correct parameters and email recipients | ||
[[Image:]] | [[Image:292356_1.png]] | ||
= Functional Description = | = Functional Description = | ||
Revision as of 15:02, 8 May 2012
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.
[[Image:]]
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.
[[Image:]]
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.
[[Image:]]
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
| EST-292356 PM-8LZMTU C-TMS data export | |||
Glossary
| C-TMS | Calidus TMS |
Document History
| Initial version | ||||
| Reviewed | ||||
| Issued | ||||
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
