292987
[[Image:]]
|
Version :
|
Copyright OBS Logistics © 2011
The information contained herein is the property of OBS Logistics and is supplied without liability for errors or omissions. No part may be reproduced or used except as authorised by contract or other written permission. The copyright and foregoing restriction on reproduction and use extend to all media in which the information may be embodied
Contents
[#__RefHeading__1_1810204259 1. Functional Overview3]
[#__RefHeading__3_1810204259 2. Set-up7]
[#__RefHeading__5_1810204259 3. Functional Description8]
[#__RefHeading__7_1810204259 Appendix A table updates REQUIRED13]
[#__RefHeading__9_1810204259 Appendix B Modules to be changed14]
[#__RefHeading__11_1810204259 Appendix C QUOTE & DoCuMENT History15]= Functional Overview =
Client Requirement
Development of 3 new extracts.
- C-TMS Activity Extract
- C-TMS Delivery Overview
- C-TMS Provider Revenue
Solution
Three new CSV extract reports will be developed to be available to be run from the Exports screen the details of each extract are described below.
C-TMS Activity Extract(Trip Level)
The following fields are required in the extract
| Name | C-TMS field | C-TMS Table |
| TO_PLANNING_REG | PLANNING_REGION | GEO_LOCATION record for the to_loc of the order |
| SCHEDULE DATE | SCHED_NAME | SCH_TRIP |
| TRIP ID | TRIP_ID | SCH_TRIP |
| CARRIER | CARRIER_ID | SCH_TRIP |
| CARRIER_TYPE | CARRIER_TYPE_ID | RES_CARRIER |
| DRIVER ID | FORNAME SURNAME | SCH_TRIP.driver_id join to RES_PERSON.id |
| VEHICLE ID | TRACTOR_ID | SCH_TRIP |
| TRAILER ID | TRAILER_ID | SCH_TRIP |
| TRAILER TYPE | DESCRIPTION | RES_TRAILER_TYPE |
| FROM LOC | LOCATION_ID | SCH_TRIP_STOP (the start location of the trip) |
| FROM TOWN | TOWN | GEO_LOCATION (town for the above location id) |
| FROM POSTCODE | POSTCODE | GEO_LOCATION(postcode for the above location id) |
| TO LOC | LOCATION_ID | SCH_TRIP_STOP(location id of the last delivery on the trip) |
| TO LOC TOWN | TOWN | GEO_LOCATION(town for the above location id) |
| TO LOC POSTCODE | POSTCODE | GEO_LOCATION(postcode for the above location id) |
| FIRST DEL TIME | EARLY_DEL | SCH_ORD(indicates the early del Date/Time of the first order on the trip) |
| SHIPPMENT | ROUTE_CODE | SCH_TRIP |
| TOTAL DEL NOTES | CALCULATED | Total count of orders on the trip |
| COMMENTS | ORDER_COMMENTS | SCH_ORD(taken from the first order on the trip) |
| RPE | CALCULATED | Sum of the SCH_ORD.total_rpe for all orders on the trip |
| REVENUE | CALCULATED | Sum of the SCH_ORD.ord_revenue for all orders on the trip |
| COST | CALCULATED | Sum of the SCH_ORD.ord_cost for all orders on the trip |
| POD RECIEVED | DERIVED | Will only be set if all orders on the trip have the SCH_ORD.POD set |
There will be three parameters available to control the records selected,
- From Schedule
- To Schedule (the schedules will be used to select trips with a schedule name between these 2 values)
- Depot will restrict orders selected by Owning Depot. If the user only has one owning depot the list will default to this else the owning depot can be selected from the list. If no depot is selected all relevant depots will be used
Delivery Overview (Order Level)
The Following fields are required in the extract
| Name | C-TMS field | C-TMS Table |
| TO_PLANNING_REG | PLANNING_REGION | GEO_LOCATION planning region of the to_loc of the order |
| SCHEDULE DATE | SCHED_NAME | SCH_TRIP |
| TRIP ID | TRIP_ID | SCH_TRIP |
| DROP NUMBER | CALCULATED | Taken from trip details so delivery 1 is drop one, delivery 2 is drop 2 etc |
| CARRIER | CARRIER_ID | SCH_TRIP |
| CARRIER_TYPE_ID | CARRIER_TYPE_ID | RES_CARRIER |
| DRIVER ID | FORNAME SURNAME | SCH_TRIP.driver_id join to RES_PERSON.id |
| VEHICLE ID | TRACTOR_ID | SCH_TRIP |
| TRAILER ID | TRAILER_ID | SCH_TRIP |
| TRAILER TYPE | TRAILER_TYPE | RES_TRAILER_TYPE |
| SHIPPMENT | ROUTE_CODE | SCH_TRIP |
| DEL NOTE | EXTERNAL_REF | SCH_ORD |
| DEL TYPE | DELIVERY_TYPE_ID | SCH_ORD |
| FROM LOC | FROM_LOC | SCH_ORD |
| FROM TOWN | TOWN | GEO_LOCATION(town of above location id) |
| FROM POSTCODE | POSTCODE | GEO_LOCATION(postcode of above location id) |
| TO LOC | TO_LOC | SCH_ORD |
| TO LOC TOWN | TOWN | GEO_LOCATION(town of above location id) |
| TO POSTCODE | POSTCODE | GEO_LOCATION(postcode of above location id) |
| FIRST DEL TIME | EARLY_DEL | SCH_ORD |
| MILES | DISTANCE | SCH_ORD(this value may be in km’s and will need to be calculated correctly) |
| WEIGHT | TOTAL_WEIGHT | SCH_ORD |
| RPE | TOTAL_RPE | SCH_ORD |
| ORDER REVENUE | ORD_REVENUE | SCH_ORD |
| TRIP COST | TRIP_COST | SCH_TRIP(for multiple order trips this will only be displayed on the first order) |
| POD CONFIRMED | POD | SCH_ORD |
| MANUAL FINANCE | DERIVED | This field will be set to ‘Y ‘if any manual finance has been associated with this order/trip |
There will be four parameters available to control the records selected,
- From Schedule
- To Schedule (the schedules will be used to select trips with a schedule name between these 2 values)
- Depot will restrict the trips selected by Owning Depot . If the user only has one owning depot the list will default to this else the owning depot can be selected from the list. If no depot is selected all relevant depots will be used
- POD confirmed will restrict orders selected by proof of delivery the value can be Yes/No/Both
Provider Revenue (Carrier Level)
The Following fields are required in the extract
| Name | C-TMS field | C-TMS Table |
| DEPOT | OWING_DEPOT | SCH_TRIP |
| CARRIER ID | CARRIER ID | SCH_TRIP |
| CARRIER_TYPE_ID | CARRIER_TYPE_ID | RES_CARRIER |
| TOTAL TRIPS | CALCULATED | Total trips for the carrier within schedule range |
| TOTAL ORDERS | CALCULATED | Total of orders on the trips |
| TOTAL REVENUE | CALCULATED | Total SCH_ORD.ord_revenue of orders |
| TOTAL COST | CALCULATED | Total SCH_ORD.ord_cost of orders |
| MARGIN | CALCULATED | TOTAL REVENUE – TOTAL COST |
| % MARGIN | CALCULATED | TOTAL REVENUE – TOTAL COST as a % value |
There will be Three parameters available to control the records selected all totals will be produced based on the trip carrier,
- From Schedule
- To Schedule (the schedules will be used to select trips with a schedule name between these 2 values)
- Depot will restrict the trips selected by Owning Depot .If the user only has one owning depot the list will default to this else the owning depot can be selected from the list. If no depot is selected all relevant depots will be used
Scope
This change will be applied to system version 10.7
Set-up
Pre-requisites
None
Menu Structure
Unchanged
Data
New entries will be inserted into the REP_REPORT and REP_REPORT_PARAMS tables to control the reports.
Implementation Advice
A system super user will be required to grant access to the required extracts
[[Image:]]
Functional Description
C-TMS Activity Extract (Trip Level)
An extract will be developed and will be available to be run from the Exports Screen and example of which is shown below
The user will be prompted to enter From Schedule, To Schedule and the Owning Depot.
The following information will be required in the extract.
| Name | C-TMS field | C-TMS Table |
| TO_PLANNING_REG | PLANNING_REGION | GEO_LOCATION for to_loc of the order |
| SCHEDULE DATE | SCHED_NAME | SCH_TRIP |
| TRIP ID | TRIP_ID | SCH_TRIP |
| CARRIER | CARRIER_ID | SCH_TRIP |
| CARRIER_TYPE_ID | CARRIER_TYPE_ID | RES_CARRIER |
| DRIVER ID | FORNAME SURNAME | SCH_TRIP.driver_id join to RES_PERSON.id |
| VEHICLE ID | TRACTOR_ID | SCH_TRIP |
| TRAILER ID | TRAILER_ID | SCH_TRIP |
| TRAILER TYPE | DESCRIPTION | RES_TRAILER_TYPE |
| FROM LOC | LOCATION_ID | SCH_TRIP_STOP (the start location of the trip) |
| FROM TOWN | TOWN | GEO_LOCATION (town for the above location id) |
| FROM POSTCODE | POSTCODE | GEO_LOCATION(postcode for the above location id) |
| TO LOC | LOCATION_ID | SCH_TRIP_STOP(location id of the last delivery on the trip) |
| TO LOC TOWN | TOWN | GEO_LOCATION(town for the above location id) |
| TO LOC POSTCODE | POSTCODE | GEO_LOCATION(postcode for the above location id) |
| FIRST DEL TIME | EARLY_DEL | SCH_ORD(indicates the early del Date/Time of the first order on the trip) |
| SHIPPMENT | ROUTE_CODE | SCH_TRIP |
| TOTAL DEL NOTES | CALCULATED | Total count of orders on the trip |
| COMMENTS | ORDER_COMMENTS | SCH_ORD(taken from the first order on the trip) |
| RPE | CALCULATED | Sum of the SCH_ORD.total_rpe for all orders on the trip |
| REVENUE | CALCULATED | Sum of the SCH_ORD.ord_revenue for all orders on the trip |
| COST | CALCULATED | Sum of the SCH_ORD.ord_cost for all orders on the trip |
| POD RECIEVED | DERIVED | Will only be set if all orders on the trip have the SCH_ORD.POD set |
The extract will be developed into the standard CSV processing package. A new procedure to select the data and a new procedure to write the data to the extract file will be added to the existing program. The schedules and the owning depot parameters will be used to control the information selected. If the user only has one owning depot the list will default to this value. If no owning depot is selected all depots relevant to the user will be used.
Delivery Overview (Order Level)
An extract will be developed and will be available to be run from the Exports Screen and example of which is shown below:
The user will be required to enter the From and To Schedule, the Owning Depot and optionally a Proof of Delivery Y/N/ALL option.
The flowing information is required in the extract
| Name | C-TMS field | C-TMS Table |
| TO_PLANNING_REG | PLANNING_REGION | GEO_LOCATION planning region of to_loc |
| SCHEDULE DATE | SCHED_NAME | SCH_TRIP |
| TRIP ID | TRIP_ID | SCH_TRIP |
| DROP NUMBER | CALCULATED | Taken from trip details so delivery 1 is drop one, delivery 2 is drop 2 etc |
| CARRIER | CARRIER_ID | SCH_TRIP |
| CARRIER_TYPE_ID | CARRIER_TYPE_ID | RES_CARRIER |
| DRIVER ID | FORENAME SURNAME | SCH_TRIP.driver_id join to RES_PERSON.id |
| VEHICLE ID | TRACTOR_ID | SCH_TRIP |
| TRAILER ID | TRAILER_ID | SCH_TRIP |
| TRAILER TYPE | TRAILER_TYPE | RES_TRAILER_TYPE |
| SHIPPMENT | ROUTE_CODE | SCH_TRIP |
| DEL NOTE | EXTERNAL_REF | SCH_ORD |
| DEL TYPE | DELIVERY_TYPE_ID | SCH_ORD |
| FROM LOC | FROM_LOC | SCH_ORD |
| FROM TOWN | TOWN | GEO_LOCATION(town of above location id) |
| FROM POSTCODE | POSTCODE | GEO_LOCATION(postcode of above location id) |
| TO LOC | TO_LOC | SCH_ORD |
| TO LOC TOWN | TOWN | GEO_LOCATION(town of above location id) |
| TO POSTCODE | POSTCODE | GEO_LOCATION(postcode of above location id) |
| FIRST DEL TIME | EARLY_DEL | SCH_ORD |
| MILES | DISTANCE | SCH_ORD(this value may be in km’s and will need to be calculated correctly) |
| WEIGHT | TOTAL_WEIGHT | SCH_ORD |
| RPE | TOTAL_RPE | SCH_ORD |
| ORDER REVENUE | ORD_REVENUE | SCH_ORD |
| TRIP COST | TRIP_COST | SCH_TRIP(for multiple order trips this will only be displayed on the first order) |
| POD CONFIRMED | POD | SCH_ORD |
| MANUAL FINANCE | DERIVED | This field will be set to ‘Y ‘if any manual finance has been associated with this order/trip |
The extract will be developed into the standard CSV processing package. A new procedure to select the data and a new procedure to write the data to the extract file will be added to the existing program. The schedules and the owning depot parameters will be used to control the information selected. If the user only has one owning depot the list will default to this value. If no owning depot is selected all depots relevant to the user will be used. The Proof of delivery parameter will be set to Yes/No or Both this will control records extracted by proof of delivery.
Provider Revenue (Carrier Level)
An extract will be developed and will be available to be run from the Exports Screen and example of which is shown below:
The user will be required to enter the From and To Schedules and the Owning Depot.
The extract will contain the following information:
| Name | C-TMS field | C-TMS Table |
| DEPOT | OWNING_DEPOT | SCH_TRIP |
| CARRIER ID | CARRIER ID | SCH_TRIP |
| CARRIER_TYPE_ID | CARRIER_TYPE_ID | RES_CARRIER |
| TOTAL TRIPS | CALCULATED | Total trips for the carrier within schedule range |
| TOTAL ORDERS | CALCULATED | Total of orders on the trips |
| TOTAL REVENUE | CALCULATED | Total SCH_ORD.ord_revenue of orders |
| TOTAL COST | CALCULATED | Total SCH_ORD.ord_cost of orders |
| MARGIN | CALCULATED | TOTAL REVENUE – TOTAL COST |
| % MARGIN | CALCULATED | TOTAL REVENUE – TOTAL COST as a % value |
The extract will be developed into the standard CSV processing package. A new procedure to select the data and a new procedure to write the data to the extract file will be added to the existing program. The schedules and the owning depot parameters will be used to control the information selected. If the user only has one owning depot the list will default to this value.If no owning depot is selected all depots relevant to the user will be used. All totals produced will be created at carrier level within the specified parameters
Table Updates Required
[[Image:]]
[[Image:]]
Modules to be changed
| DP_CSV5.sql | Package | Add new extracts |
References
| EST-292987 TH-8MLJTQ | |||
Glossary
| C-TMS | Calidus TMS |
Document History
| Initial version | ||||
| Reviewed | ||||
| Issued | ||||
| Revised | ||||
| Revised | ||||
| Reviewed and Issued |
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
