270869
270869 PA-7X9DXS Add Total Trip Cost to Trip Screens
FUNCTIONAL OVERVIEW
Client Requirement
Change Request Summary: Add additional field to show Total Trip Cost and Profit with Red/Green +/- IndicatorAllan Butterworth/St Albans/UK/Exel Change Request Details: In trip planning/ trip manipulation - Trip Detail - add in a Field to show the sum of all order costs and carrier Cost - ie all applicable costs to trip. Add another field to show the result of calculation between Trip Cost and Trip revenue totals - Background RED for negative value, GREEN for positive. - Trip Cost to capture all Trunk and radial costs if cross-Docked. - Mock-up attached Benefits identified as a result of the change: Visibility of profitable planning.
Solution
Currently, if an order is placed on a trip, the order revenue is displayed as Trip revenue. If an order is cross docked and appears on more than trip, the revenue is then applied to multiple trips. Any charges incurred through cross docking are not currently assigned to the relevant trips.
In order to reflect the correct charges and costs on the trip, there are three amounts which will be calculated.
TRIP REVENUE. (from customers)
This will be based on order revenue for all orders which are on the trip where the trip covers the pick up of the order. Any subsequent trips which cover the cross docking of the order will not generate trip revenue from the order.
TRIP REVENUE (from internal charges)
When a trip has been cross docked, the charge against the RDC responsible for the order are also revenue for the RDC which undertakes the cross docking. If a trip covers a trunk journey, then the cost incurred by the trunk journey will appear as revenue on the trip, likewise if a trip covers a RADIAL journey, the cost incurred by the radial journey will appear as REVENUE on the trip.
The SUM of any internal charges revenue will be added to customer revenue to give a combined TRIP REVENUE amount. The combined value will be used to populate the TRIP REVENUE field on the trip screens.
TRIP COSTS (from internal charges)
Internal charges will only be applied to one trip, this trip will be identified as the trip which picks up the order at the start of the journey. This is also the trip that will generate the order revenue from the customer contract, so the trip will reflect the true cost/ revenue of the order. Any trips which do not start at the from_loc of the order will not generate any internal charges. This value will be displayed in a new field on the TRIP screens, above TRIP revenue as requested.
PROFIT-LOSS
The INTERNAL TRIP COST, CARRIER COST, TRIP REVENUE AND INTERNAL REVENUE will be used to determine the PROFIT/LOSS of the trip. This field will be displayed on the 2 trip screens, when the value reflects profit, the field will have a green background and loss will have a red background.
Identifying the Relevant Payments
For the three charges detailed above, we must identify the relevant payment record to calculate the revenue/ cost. All payments related to an order will have the OMS_REF in the event_ref field. All payments cover a to and from location and this information can be used to match the stops on the trip and identify which payment should be applied.
Three new fields will be added to the SCH_TRIP table
- INTERNAL_COST
- INTERNAL_REVENUE
- PROFIT_LOSS
The extra fields will allow the information to be reported in exports. The current population of TRIP REVENUE will be amended to only show ORDER revenue when it is applicable to the trip. As part of this RIO, the GROUP NAME field on the locations screen will be disabled, so the value can only be amended using the tab page from the maintenance menu.
Scope
This change will be applied to system version 10.5.0 on CONTST and once approved CONPRD.
FUNCTIONAL DESCRIPTION
This solution is different to the estimate due to the impact of the CROSS DOCK changes to be developed as part of log 271365 PA-7XQD87 - Internal Charging from Cross dock.
The finance tab on the trip screens (Trip Manipulation and Trip Planning) will be updated to look like the above screen shot. The value of each field will be calculated as detailed below:
Carrier Cost
This will continue to reflect any costs incurred from Carrier Contracts, the functionality to populate this field will not change.
Trip Cost
This is new field and will be used to display internal order charging as a cost to the trip. If an order is cross docked onto the current trip, the trunk and radial charges of the order between the group name of the originating location and the group name of the hub location will be displayed on this trip.
If an order has previously been cross docked on another trip and is cross docked again on this trip, the trunk and radial charges between the group name of the load location and the group name of the hub location will be applied as a cost to this trip.All relevant Trunk and Radial charges for all orders on the trip will be totalled and displayed in this field.
Trip Revenue
If an order is being loaded onto the trip from the original location , the customer revenue is displayed as part of the Trip Revenue. Any subsequent trips which carry the order will not display the order revenue as Trip revenue.
In addition to the revenue from the customer, an order can also generate revenue through internal charges. One trips internal charges are another trips internal revenue. The trip after a cross dock will be assigned the cross dock charges as revenue.
To demonstrate how the charges will be distributed please see the tables below.
ORDER 2938475
Identifying Payments
To allocate costs and revenue to trips, rules must be created to identify the relevant payment records. From the payment records, the amounts will be obtained.
The payment records include a to and from location, for trunk records this will always be the load location to the unload location and for radial records this will always be the unload location to the order destination.
The location values on the payment records will be compared with the load and unload locations on the trip to determine if the payment amount should be applied as cost, revenue or not at all.
Finding Trunk Cost
Oms_ref = ‘xxxxxxxxx’ Payment type = ‘ORDER COST’
LOAD_LOCATION = PAYMENT.FROM_LOC
UNLOAD_LOCATION = PAYMENT.TO_LOC
Finding Radial Cost
Oms_ref = ‘xxxxxxxxx’
Payment type = ‘ORDER COST’
UNLOAD_LOCATION = PAYMENT.FROM_LOC
PAYMENT.TO_LOC = ORDER.TO_LOC
Finding Trunk Revenue
Oms_ref = ‘xxxxxxxxx’
Payment type = ‘ORDER COST’
LOAD_LOCATION = PAYMENT.FROM_LOC
UNLOAD_LOCATION = PAYMENT.TO_LOC
Finding Radial Revenue
Oms_ref = ‘xxxxxxxxx’
Payment type = ‘ORDER COST’
LOAD_LOCATION = PAYMENT.FROM_LOCATION
PAYMENT.TO_LOC = ORDER.TO_LOC
The amount fields will be updated in the post query trigger. A cursor will be created to identify the oms_ref of all orders on the current trip.
4 Cursors will be created for calculating Trunk Charges, Trunk Revenue, Radial Charges and Radial Revenue. Variables will be created to store the values as the orders identified in the first cursor are passed through the 4 charge and revenue cursors.
The final calculation will be to determine the profit/loss of the trip by subtracting the loss from the profit. A positive value will be displayed with a green background, a negative value will be displayed with a red background.
REFERENCES
Not Applicable
DOCUMENT HISTORY
| Initial version |
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
Copyright OBS Logistics © 2010
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
