291805
DHL C-TMS
Mods to the sales rating engine
FUNCTIONAL SPECIFICATION - 10.7
4/11/11 - 2.0
Reference: 291805 TH-8LFCT5
Client Requirement
Modification to the sales rating engine to calculate revenue on trips not orders including second drop and vehicle type.
Requirements gathering.
development
testing support
implementation to live.
Solution
There is existing functionality that calculates the revenue for an order based on the distance between stops, the vehicle type and the quantity.
A new process will be written that will be called when the trip is validated. A change will be added to the TRM package to call this new process and calculate the revenue for the orders during the validate trip process.
The new process will calculate the revenue for each stop on the trip using :-
- The vehicle type of the trip
- The distance between the SU stop and the current stop.
The process will loop through all of the orders on the trip providing revenue for each stop. Based on this the maximum revenue will be found. The additional revenue per delivery stop will then be calculated from the tariff. This will give the total revenue for the trip.
Scope
This change will be applied to system version 10.7.0
Set-up
Pre-requisites
None
Menu Structure
Unchanged
Data
Alter table SCH_ORD
Add column RATED_FROM_TRIP varchar2(1);
Implementation Advice
A super user will be responsible for setting the values of 2 new cost centre parameters called ‘TRM_MAX_STOP_REVENUE’ and TRM_MAX_STOP_COST’’ to ‘Y’. This can be completed in the System Parameter maintenance screen.
Functional Description
Gypsum Orders and Trips will be rated C-TMS. To allow the rating to be completed based on existing rating cards several functional changes will be required as described below.
Order Revenue
Order revenue will be calculated based on the delivery location of the order and the trailer type and number of stops on the trip.
Tier Units
Currently the system allows revenue to be generated based on Weight, Distance, RPE, pallets and DU Types. Stops are available as a Tier Unit but functionality has only been added for rating trip cost by the number of stops. A new tier unit will be created call Add_Stops.
Functionality will be added to allow orders to be rated based on the number of additional stops on the trip the order has been scheduled on.
The existing procedure in the rating engine which determines order quantities for each tier type will be amended to calculate quantities for the new ‘Add_Stop’ tier type. The count of stops will be based on the number of additional delivery stops, i.e any stop which performs an unload activity Trip Start (SU) and Trip End (CL) stops will not be included in revenue rating, however if a CL is delivering an order it will be included as an additional stop. Additional stops will be defined as the number of deliveries in addition to the delivery which has generated the maximum revenue. Collections will not be counted as an additional stop. A collection will be identified as an unload at an RDC, where the RDC is the to location on the order.
Trailer Types
The system currently has functionality which allows charges to be applied based on the trailer type assigned to the trip. This can be applied to generating order revenue, however if all charges are based on a trailer type, the order will not rate until it has been scheduled and a relevant trailer type has been assigned.
Customer Contracts
It is expected that customer contracts will be set up as displayed in the screen shot below:
The tier unit will be displayed as Add_Stops. Each tariff will have a charge per stop and a fixed charge which will be based on a specific trailer type. The standard journey tab will be used to set the ‘from’ and ‘to’ locations which may be defined as between any of the following planning region, postal region, postcode or country.
A tariff will be required for each standard journey.
Maximum Stop Revenue
As the order revenue generation relies on the number of stops and the trailer type, orders will not be rated for revenue until they are scheduled onto a trip. As part of the current functionality, each order on the trip will be rated from the relevant customer contract and will be assigned revenue.
A new procedure (Max_Trip_Revenue) will be added to the RATE package which will perform the following functionality:
- Compare the revenue (excluding additional stop charges) at each delivery stop
- Identify the maximum revenue amount , excluding the stop charges
- Assigning the maximum revenue amount to the order rated highest and a single stop charge to an order at each delivery stop (where applicable)
- Set new flag ‘RATED_FROM_TRIP’ to ‘Y’ against each order on the trip.
Based on the value of a new cost centre parameter ‘TRM_MAX_STOP_REVENUE’
The new procedure will be called at the following events:
- Trip status change from ACCEPTED onwards
- Order Scheduled onto Trip ( not at PLANNED status)
- Order Unscheduled from Trip ( not at PLANNED status)
Below is a worked example of an order and trip and the revenue generated:
TRIP MAN-00001234
| Order | To_loc | Weight | Revenue | Apportioned Rev |
| 10112 | IP11 9DQ | 12kg | 320.00 | 320.00 |
| 10113 | C06 1EN | 5kg | 150.00 | 30.00 |
| 10114 | C06 1EN | 7kg | 150.00 | 0.00 |
| 10112 | PK CO6 | 0.00 | 0.00 |
Would this apply if these were collections? Ie collect on 2 different stops. Yogesh
Collections will not incur any charges. The standard journey for the collection should be missing from the contracts ensuring collections are not rated. An unload at an RDC will not be included as an additional stop.
In the example above the contract has been set up to charge 30.00 per additional stop. The last order 10114 receives no revenue as the additional stop revenue has already been allocated to order 10113
A new field will be added to the order table called RATED_FROM_TRIP. When the revenue has been overwritten by an apportioned value calculated from the trip, this flag will be set to Y. When an order is validated it is automatically re rated. An order is validated as part of the SAVE process on the orders form. If this flag is set to Y and the SAVE button has been pressed, the order will not be re rated. This will ensure that when an order is rated based on the trip, the revenue is not overwritten by the existing contract functionality.
Carrier Cost
The “RPL Rate” matrix will be used as the basis for the carrier rating. Each carrier will have their own rating card defined in the contract screen. The carrier assigned to a trip will determine which contract is used to rate the trip cost.
Similarly to the customer revenue, the cost will be rated based on the trailer type, the number of additional stops and the maximum cost at stop level. The existing functionality which determines the quantity for each tier type when rating a trip will be changed to calculate the quantity for the new tier type Add_Stops. The system currently allows Carrier costs to be rated based on number of stops and the trailer type assigned.
The carrier contracts will be set up to rate based on the number of additional_stops. Fixed charges will be added for each trailer type. Carrier cost will always be generated from the cost per stop * additional delivery stops plus the relevant fixed cost based on the trailer type. A tariff will be generated per standard journey, which may be defined by planning region, postal region, country, postcode or location.
Maximum Stop Cost
To rate the trip, each journey within the trip will be rated from the contract. A journey will be defined as the start up location to a delivery location. Each stop will be rated based on the FIXED COST for the journey and the number of stops on the trip.
As the system identifies each journey and passes the information to the contracts module, the cost returned will be compared to previous costs returned for the trip, only the greatest cost will be retained by the system.
When all of the stops have been rated, the maximum cost will be identified and applied as the cost of the trip.
EXAMPLE TRIP: MAN-00001234 has 4 delivery locations
| From Loc | To Loc | Stops cost | Fixed Cost | Journey Cost |
| BARROW | B36 | 90 | 195.90 | 285.90 |
| BARROW | B37 | 90 | 220.69 | 310.69 |
| BARROW | B77 | 90 | 168.15 | 258.15 |
| BARROW | B61 | 90 | 244.81 | 334.81 |
The trip cost will be set as 334.81 as this is the maximum cost from the journeys on the trip. This cost includes the additional stop charges (3 stops @ 30.00).
A new procedure will be created in the Rating engine. The new procedure will be run based on the value of a new cost centre parameter ‘TRM_MAX_STOP_COST’. The new procedure will rate the stops individually and identify the maximum cost to apply to the trip.
Similarly to the order revenue process, the new cost procedure will be run as part of the following events:
- Trip status change from ACCEPTED onwards
- Order Scheduled onto Trip ( not at PLANNED status)
- Order Unscheduled from Trip ( not at PLANNED status)
All Trip costs will be available for users to view in the Finances tab on the trip screen.
The Reporting RIO (292987 TH-8MLJTQ) will cover the reporting of calculated costs and revenue as defined by the Functional Specification.
Table Updates Required
Alter table SCH_ORD
Add column RATED_FROM_TRIP varchar2(1);
Modules to be changed
| Module Name | Module Type | Notes |
| RATE.sql | Rating Package | 2 new procedures |
| ORDERS.fmb | Orders screen | Validation changes |
| TRM.sql | Trip Manipulation package | Calls to new procedures |
| CNT.sql | Contracts module | Overload GET_CHARGES |
References
| EST- 291805 TH-8LFCT5 Mods to the sales rating engine v1.0 | |||
Glossary
| C-TMS | Calidus TMS |
Document History
| Initial version | ||||
| Reviewed | ||||
| Revised following review | ||||
| Reviewed and Issued | ||||
| Revised & Issued Draft | ||||
| Re-issued after revisions |
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
