286733
DHL MTS
Rating and Finance
FUNCTIONAL SPECIFICATION - 10.6
- 0.1
Reference: FS 286733 DK-8EMENH
FUNCTIONAL OVERVIEW
Client Requirement
Capped and exception/redirect load rates, local and regional, spot rates, mileage calculator, post code rate matrix, backfill contracts
Solution
Order Revenue
OPENFIELD will be the only customer created and all revenue will be received by DHL from OPENFIELD.
A new table called POSTCODE_MATRIX will be created and will store collection and delivery postcode districts (out-codes) and the rate for the journey. A base contract will be created using Distance and Weight to rate. The contract will be based on postcode tariffs and will charge a rate per tonne. The postcodes will be postcode out-codes as pairs to describe the geography of the transportation (up to 4 characters each).
If, for example, a record for post code AA99 to postcode BB99 does not exist in the POSTCODE_MATRIX, the base contract will be applied. In any instance where the base contract has been applied, the rate returned from the contract will be used to create a record in the CUSTOMER_MATRIX table. This will be referred to as ‘backfill’ of the POSTCODE_MATRIX.
To retrieve an appropriate distance mileage band from the base contract, a new postcode district to postcode district distance table will be maintained (out-code to out-code) with distance for each record. DHL will provide this data and OBS will generate a one-off script (off-system) to upload the data during implementation of the solution. The assumption is that this data set is represented by circa 7.5 million rows.
A status will be stored against each record in the POSTCODE_MATRIX, N-new, H- historical , A-amended. When a record is created from the contract, the status will be set to N. New import and export CSV procedures will be created to allow users to import new records and amendments into the table and to export the data to EXEL for manipulation. A new screen will be developed to allow users to maintain the postcode matrix data. If a rate is changed via the screen, the status will be updated to A- Amended. Changes uploaded from CSV files will be considered to be fuel increases and so the A- Amend flag will not be set for these changes.
Openfield orders will be rated based on planned weight until the actual weight has been completed by TOKAIRO scan or a manual debrief has been input into C-TMS. Part of the debrief process (from TOKAIRO scan or manual) will include re-rating the orders against ACTUAL weight. Prior to this, C-TMS will rate the orders as forecast revenue on planned weight. CAPPED Tonnage will take precedence over the actual and planned weight; this is discussed in detail in section 3.1.4. of this specification.
A new audit field in the database payment table will be introduced to store how the payment was generated. This field will store the reference from the CUSTOMER_MATRIX or the charge id from the CONTRACT. If the payment is manual, the field will remain null. This will allow users to establish the method by which a revenue payment was generated.
Trip Costs
A journey between two postcodes (or postcode region or planning region) is defined as a lane. A new table will be created, RTE_HAULIER_LANES to store these lane records. The table in some respects will be similar to the POSTCODE_MATRIX and will be used for lookup of carrier costs rather than of customer revenue. The table will store operational and financial information including rate per tonne and carrier preference.
Some carriers, typically where providing a long distance service, will be rated based on a carrier contract. The carrier contract allows for distance bands and cost per weight to be maintained. For these carriers there will be a record in the Carrier Lane Table date but the cost rate per tonne will not be maintained (will be null). The Carrier Lane Table will allow from or to location type to be defined as ‘Distance Greater’ or ‘Distance Less’ and a value in miles entered. (Note Distance Less will be consider less than or equal to). These distance based lane records will be supported by entry of a carrier contract and the lane and contract associated using a tariff id.
Users will also be able to generate a trip cost using ‘SPOT RATE’ payments, which will override the Lane Table and Carrier Contracts.
Selecting Carriers
In the new Carrier Trip Planning screen a ‘Select Carrier’ function will be available. For each lane, there may be several carriers that could be considered to provide the transport service. In the lane table, a preference field and cost will indicate which carrier DHL should prioritise the tendering of the transport.
When using ‘Select Carrier’, a list of all carriers which have a rate available for the lane will be displayed. The list will be ordered by preference and then by cost, (with cost cheapest first) but will also display the carrier preference. In the scenario where the rate in the lane table is null and for distance lane agreements, the C-TMS will find a rate from the carrier’s contract tariff if available. – BS & DK – Sort by Preference then cost – DJM - Ok
The priority, carrier and name, cost, contact details and lane type will be displayed in the list of carriers. The planner will tender the work to the carrier to confirm and book the service. At this stage of the process the planner might agree with the carrier to cap the weight for the service, usually to a full load 29 tonnes. To record the capped weight agreed with the carrier, two input fields will be available in the Carrier Selection screen, Capped Flag and Capped Tonnage.
The capped flag and tonnage will be stored at order header level. The capped flag and tonnage will be displayed in the Finance tab of the C-TMS order screens. Users will be able to amend the Capped Flag and Tonnage field from the order screen finance tab until the trip the order is allocated to is COMFIRMED and COMPLETED. This means the capped arrangement can be agreed and the system updated prior to collection, at collection or at delivery of the product.
When the trip is for one order, the capped weight will be written directly back to the order. When the trip is for combined orders, a pop up window will be generated to allow the respective weight cap of each of the orders to be entered. This window will display a list of order refs and the planned weight and allow input of the respective cap weight for each order. Two new fields will be added to database table SCH_ORD to store the agreed capped tonnage. Cap Load will always be written back to a single order not split between two. Unless the user can select how the weight will be apportioned
When rating orders for revenue and trips for cost, the CAPPED tonnage overrides both planned and actual weight. This will be managed by setting the OPENFIELD customer to rate by ‘CAPPED’ weight (this is a parameter setting). This will be a new way of managing rating based on the existing PLANNED, DESPATCHED or DELIVERED functionality. If the customer is set to rate by CAPPED, the contract quantity will be defined based on CAPPED weight if a capped value exists, then ACTUAL weight if actual weight exists then PLANNED weight. If CAPPED load the actual weight still needs to be stored and sent back in the DESP1/2 file as the actual weight, however all financial transaction are calculated on the CAPPED weigh.
For the DESP1 and DESP2 message to OPENFIELD, the actual weight will be sent although all financial transactions both revenue and cost will be calculated on the CAPPED weight. DESP2 is designed to interface the revenue figure to OPENFIELD once POD is received and this will be calculated on the CAPPED weight input.
To assign a carrier, the planner will highlight the carrier record and SAVE in the Carrier Selection screen. At this stage of the process, a validation check will be performed to ensure the carrier allocation has not exceeded the maximum trips for the schedule day; if this has been exceeded, the user will be warned and asked to select another carrier, otherwise C-TMS will then generate a trip haulage payment record to store the cost of the haulage. This warning will not stop the planner from selecting the carrier; the intention of the warning message is to provide decision support not constrain the planners decision. The carrier and cost rate will be used to populate the payment record with the carrier ACCOUNT_ID and cost AMOUNT. VAT will be automatically applied at the current rate maintained in C-TMS.
(Note that VAT is not used in any Openfield input or output. VAT will be calculated automatically by C-TMS and not suppressed in any way. OBSL will need to ensure revenue and cost amounts are always input, displayed and output as net of VAT values).
Spot Rates and Override Carrier
The Select Carrier Screen will also allow the user to enter a spot rate. A field will allow users to enter a ‘SPOT RATE’ for the trip cost which will override the rate currently available for the selected carrier. If the planner selects SAVE, a new payment record will be created using the selected carrier and the SPOT RATE.
The planner will also have the ability to select a different carrier from those returned in the list. The planner will be prompted for a carrier name and a rate per tonne. The user will be able to select a carrier from a lookup list of all carriers on the system (or a sub-list constrained to his user profile). The carrier and rate will be used to generate a payment record.
Exceptions and Redirects
If the Exception flag is selected on the Order screen, the user will be required to enter a revenue/tonne amount. Once a value has been entered in this field, this will be used to generate the order revenue payment record and any payment records created from the POSTCODE_MATRIX or base revenue contract will be overwritten. The record in the POSTCODE_MATRIX will not be overwritten.
Redirects
The principle assumptions relating to redirects are listed below;
- The original order remains on the system with zero quantity and revenue
- A new order is created to cover the journey from A to C by Openfield and interfaced into C- TMS(see diagram below)
- The new order will be manually planned onto the relevant trip by the planner.
- The original order will remain on the trip with zero weight and quantity.
If an exception has been caused by a re-direct, where the carrier is instructed to deliver the product to an alternative destination, the original order weight from A to B will be set to zero to clear the revenue and the planner will add a non-conformance record. Openfield will send a redirect replacement order A-C and this will be rated using the exception revenue per tonne value.
The new order will be received in the system from OPENFIELD and manually planned on the same trip as the original order.
Auto Planning
When an order is automatically planned as part of the order import, a new payment record for the carrier cost will be created. The cost payment record will be based on the rate obtained from the lane table and the carrier assigned to trip. The payment will be created as part of the automatic planning process.
Scope
This change will be applied to system version 10.6.0 on INDTST and once approved INDPRD.
SET-UP
Pre-Requisites
286734 DK-8EMEWU Trip Planning, this development will create the RTE_HAULIER_LANE table and the new Haulier Trip Planning screen.
Data
- Create new tables POSTCODE_MATRIX, POSTCODE_DISTANCE
- New field RATING_ID added to ACC_PAYMENT
- Three new procedures
CNT.GET_RATE (based on the existing CNT.GET_CHARGES)
RATE.CALC_POSTCODE_RATE
RATE.CALC_LANE_RATES
FUNCTIONAL DESCRIPTION
Order Revenue
Order revenue will be generated when the order record is received into C-TMS by EDI from Openfield. Each order will be validated and part of the validation will be to find the revenue rate and apply to the order to generate a revenue payment record. The revenue will be re-calculated as part of the debrief process (TOKAIRO scan or manual).
Rating
There are two methods for finding an order rate, matching the order to a record in the POSTCODE_MATRIX table or finding a rate from the Openfield base contract. The rate will always be defined as a price per tonne.
The Base Contract will only be used if C-TMS fails to find a rate in the POSTCODE_MATRIX table. To find a rate the system will search the POSTCODE_MATRIX for a record based on postcode outcode (to the first space) for the collection and delivery location addresses.
Example extract from the POSTCODE_MATRIX
If a record is found, the data will be used to create a new revenue payment record. See section 3.1.6 for how the payment record will be generated.
If the selection does not find a record, the base contract will be used. The base contract will be called if there is no relevant record in the POSTCODE_MATRIX, or the rate on the POSTCODE_MATRIX is null. The base contract will be created using the standard CONTRACTS screen on C-TMS and will be based on distance and weight.
The distance used for rating revenue is defined on a district to district basis contractually between DHL and Openfield. C-TMS will calculate order distance using Navteq electronic map for reporting and this will be full postcode to full postcode by the road route. For rating revenue, a new table of outcode to outcode distances will be maintained in C-TMS which defines the specific district to district distances. Once the rating distance is derived from this new table, it will be used to select the appropriate distance band breakpoint from the customer base contract, then calculate the revenue using the weight calculation for that distance band. A new system parameter will be used to define whether the postcode distance (district to district) should be used to derive revenue.
The detail of the Tariff_ID as shown below from the Contracts Screen is expanded using a double click mouse action to display the distance banded breakpoints (Tiers) and a charge calculation expressed in value per weight provided for each band.
Double click row above displays distance band breakpoints and charges in the screen below.
The contract will be created to cover all journeys nationwide and will be based on distance. When rating an order using the contract, the system will calculate the distance between the collection and delivery locations outcodes. This will fall into a distance band. Each band has a charge applied which is based on weight. Weight on Orders in C-TMS is stored in Kgs. To apply a rate by tonne, the charge is set up per 1000 kgs weight.
The revenue payment will be created using the generic contract code in C-TMS. (See 3.1.6) A new field will be added to the ACC_PAYMENT table called RATING_ID and will store the reference from the POSTCODE_MATRIX table or the charge id which generated the revenue payment. If the revenue payment was generated manually, this field will be NULL. This data will provide the user with audit information regarding how the payment was generated.
Contract Backfill
If a rate is found using the base contract, the rate will be added to the POSTCODE_MATRIX using the outcode to outcode postcode pair. If a record already exists for the postcode out-code pair with a null rate, the rate will be updated and the status set to N (new). If no record currently exists, a new record will be created using the postcodes from the order and the status will be set to N (new). Next time an order is created for the same postcode out-codes pair, the rate will be found in POSTCODE_MATRIX table. The data insert will be completed as part of the rating process.
Postcode Distance
A new tab screen will be added to the Accounts Maintenance form called POSTCODE_DISTANCE. The new screen will allow users to search, add and amend records in the POSTCODE_DISTANCE table.
DHL will provide the data to populate this table during implementation. It is understood that circa 7.5 million rows exist. C-TMS will lookup first from and then to, then to and then from to find a distance; in other words the table will be indexed and can be accessed from and to OR to and from.
Revenue Rates Maintenance
A new tab screen will be added to the Accounts Maintenance form called POSTCODE_MATRIX. The new screen will allow users to search, add and amend records in the POSTCODE_MATRIX table. If a rate on an existing record is changed the status will be updated to A (amended).
A new CSV import will be created for uploading POSTCODE_MATRIX records into C-TMS. The records will be displayed in the maintenance screen shown above. The new import will be configured in the Import Maintenance screen, based on a new import type POSTCODE_MATRIX. An example of this standard import configuration screen is shown below;
The import detail will be defined as follows
A procedure to process the POSTCODE_MATRIX import will be added to the IMP package. Part of the process will be to determine if the record is new or an update to an existing record. Table triggers on POSTCODE_MATRIX will update the Created and Updated data as necessary.
A new CSV export will be created to allow users to extract all the records from the POSTCODE_MATRIX table into MS excel for manipulation. The extract will be available to run from the EXPORT screen in C-TMS. The new CSV extract will be developed in the DP_CSV2 package. The user will not be prompted for run-time selection parameters, each extract will be a complete selection of the records on the POSTCODE_MATRIX table.
Rating Quantity
In the customer maintenance screen, there are two fields which are set which determine how revenue is calculated and trip cost apportioned.
The first field ORDER REVENUE CHARGING TYPE determines which quantity is used to determine the CONTRACTUAL quantity value (weight for Openfield), used to generate a revenue payment record. Currently there are four different ways to calculate the CONTRACTUAL value for an order - PLANNED, DESPATCHED, DELIVERED and GREATEST.
For Openfield a new method of calculating the CONTRACTUAL quantity value will be created. The new method will be named CAPPED and will be based on the CAPPED quantity (of weight). If no CAPPED value has been populated, the DELIVERED (actual) quantity will be used. If no actuals have been entered then PLANNED quantity will be used. The new CAPPED quantity will be defined in the T_SCH_ORD_PNL trigger.
The capped quantity is stored on the Order screen in the Finance tab. Planners will manually enter the capped quantity here.Can there be a note to prompt entry in Kilos or can the Capped Load be entered in Tonnes as per the screenshot below for Carrier entry? An agreement to be reached with DHL IT and the Operation as to whether C-TMS will be run in tonnes or in kgs. Either way, all inputs and outputs need to be consistent in the chosen measure.
The second field ALLOCATION method, allows a value to be defined that is used to apportion trip cost to the individual orders. It is assumed that this will be weight for Openfield.
Order Exceptions
A (finance) exception is entered by selecting the Exception flag on the Finance tab of the Order screen. When selected, the user is able to enter a new rate per tonne as an override value.
If the Exception rate/tonne field has been populated, this rate is used to generate the order revenue, rather than the POSTCODE_MATRIX or base contract.
If the exception has been caused by a redirect, the user should add a non-conformance record for a new type ‘REDIRECT’.
Adding a ‘REDIRECT’ non-conformance code will set the quantity, weight, revenue and rate/tonne to 0 on the original order
A replacement order will be received into the C-TMS from Openfield and manually planned to the same trip as the original order. The Carrier planning screen will display a column in the order well so redirect orders are easy to find and see. The planner will then find the order in C-TMS and indicate the order is an Exception, applying the rate/tonne using the order screen finance tab.
Order Revenue Payment Record
Payments will be created for Order Revenue using the existing ACC.Create_Payment procedure, based on the following data:
Trip Costs
There are several ways of generating a trip cost. The cost can be generated using a rate obtained from the RTE_HAULIER_LANES table, a rate obtained from a carrier contract or a spot rate. In all cases the rate will be determined for the carrier selected for a trip.
If a record exists on the RTE_HAULIER_LANES table with a null rate and is a distance lane agreement, the rate should be obtained from the standard contract for the specified carrier and tariff id for the lane. The RTE_HAULIER_LANES table will not be back filled. Unlike revenue calculations (district to district distance), the distance used for costing carrier charges will be full postcode to full postcode by the road route.
Haulier Selection
In the new carrier planning screen the user will highlight any number of orders and select one of the following functionalities:
Selecting any of the above buttons will display a carrier selection screen within the carrier planning screen with a list of suggested carriers that can be assigned to the resulting trip.
The Suggested carriers will presented sorted by priority, then by cost (cheapest to dearest). The history of costs will show the carrier contact information in the same format as the suggested carriers section.
The list of carrier records will be referenced from the carrier lanes data for the order(s) selected in the carrier planning screen. A search of the RTE_HAULIERS_LANES table will select carriers which have an agreed rate for the lane. If the RTE_CARRIERS_LANES table has a record with a null rate and is a regional (distance) lane, the system will find the carrier contract and specific tariff id and return a rate found in the contract.
If Skip Select is chosen, no cost payment record is generated at this stage of the process. This facility allows trips to be created without an assigned carrier and cost.
The data displayed in the Carrier Selection screen will be determined as follows
The records will be selected based on the ROUTE_TYPE, ROUTE_FROM and ROUTE_TO compared to the COLLECTION and DELIVERY locations on the orders selected.
If the orders selected do not match an existing lane record a message will be displayed informing the users ‘The orders selected do not match a Lane’ and the Select Carriers screen will be displayed with no suggested carriers listed. The user will be able to select a carrier manually supported by a lookup list of values.
The planner will contact a carrier and book and confirm the transport. At this stage of the process a CAPPED weight might be agreed, which will be entered on the Carrier Selection screen. (More typically, the decision to cap weight will be made as the collection or the delivery of the product is made rather than at appointing the carrier by a planner).
When the planner selects SAVE, the requested functionality for the action (create trip, combine orders etc) is run to create a trip. When the trip has been created, the highlighted carrier record is used to generate a cost payment record for the trip. The following data will be stored in the cost payment record, the payment will be created using the standard ACC.Create_Payment procedure.
If the carrier selected by the user has exceeded the number of trips in one schedule day, a message on the screen will inform the planner and ask them to confirm that the haulier can still be selected. If confirmed that the carrier should still be used, the carrier will be assigned to the trip, otherwise an alternative carrier from the suggestion list of manual lookup can be selected.
If the capped quantity field has been entered, this will be written to the capped quantity field in the order table for the orders selected in the carrier planning screen. Where more than one order has been selected, the capped quantity will be apportioned to each order.
Eg Capped Qty = 29000
Spot Rate & Override
If the planner needs to override the list of suggested carriers he can select a carrier manually and enter an agreed spot rate cost. When the planner enters a carrier manually rather than selecting from the suggested list, a lookup list of values will be available to select from. The list of values will be based on active carriers. Will the lookup be a type-ahead search / drop-down box or a % search thing? And will this be on the Name of the haulier or the carrier ID? The list of values for the carrier selection will support partial entry with % wildcard and a matching selection list presented.
The planner may need to use one of the selected carriers but overwrite the rate as a one off spot cost. This is achieved by selecting the required carrier record, then entering either a cost/tonne value OR the total cost in header of the screen for the spot rate. The screen will calculate the total from the cost/tonne or the cost/tonne from the total which ever is entered.
When the planner selects the Save button the process will first check if manual spot rate has been entered. This data will be used to generate the cost payment. Otherwise, the data from the selected haulier lane record (or contract tariff) will be used to generate the cost payment.
Payment record for override and spot rates, created using standard ACC.Create_payment procedure.
Auto Planning & Rating
Order Revenue
When the orders are received into C-TMS from Openfield, they will be uploaded and the VALIDATE_ORDER process will be called. Within the VALIDATE_ORDER process the order will be rated, using the POSTCODE_MATRIX or base contract.
A parameter will be created at cost centre level called ‘POSTCODE_LANE_RATE’. If this parameter is set to ‘Y’, the orders will be rated based on the POSTCODE_MATRIX table. If this process returns a null as there is no record on the POSTCODE_MATRIX or the parameter is not set to Y, the existing rating process from contracts will be called.
The additional code to support this function to check the POSTCODE_MATRIX table will be added to a new procedure CALC_POSTCODE_RATE in the RATE package.
Trip Costs
The auto-planning process will schedule the order on a trip and assign a carrier. When this process has completed a trip cost will be generated.
A new procedure called CALC_LANE_RATES will be added to the RATE package. The new procedure will only be called if the value of PROCESS_LANE_RATE is Y. (Before generating a cost payment, the system will check if a cost payment has been automatically generated from the carrier contract. Any cost payment found will be deleted, using the ACC.DELETE_PAYMENT procedure).
CALC_LANE_RATES will search the RTE_HAULIER_LANES table for a match based on the carrier, collection and delivery postcode. A carrier will only be assigned during auto-planning if a matching lane record exists, so a cost rate should always be returned.
The cost rate/tonne will be used with the CONTRACTUAL_QTY to generate a cost. A new cost payment record will be created based on the following data:
REFERENCES
| EST-286733 DK-8EMENH Rating & Finance v1.0 |
DOCUMENT HISTORY
| Initial version | ||||
| Reviewed and Issued | ||||
| Reviewed with DHL and re-issued | ||||
| Reviewed with DHL comments and re-issued |
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
