275183
DHL MTS
Profit Colours on Trip Finance Tab
FUNCTIONAL SPECIFICATION - 10.5
10/09/2010 - 1.0
Reference: FS 275183 PA-83EFTE
Client Requirement
| Change Request Summary: |
| Enhancement to RIO PA-7X9DXSAllan Butterworth/St Albans/UK/Exel |
| Change Request Details: |
| RIO PA-7X9DXS specified a 0% - 3% profit margin for AMBER status between Trip Cost and trip Revenue when a trip is subcontracted - The upper % should be variable and set by site or Group name, in Location details and then applied to Trips created by the respective site or Group name. |
| Benefits identified as a result of the change: |
| Visibility of contibution from each subcontracted trip |
Solution
A new field will be added to the table OMS_SCHED_GROUP called PROFIT_MARGIN. The field will be maintainable from the BUSINESS DATA MAINTENANCE screen, allowing users to assign a profit margin % to each GROUP_NAME.
In the Trip screens, the profit_loss field is currently displayed with a red or green background to indicate profit or loss. This value will be investigated further to identify the profit margin. To calculate PROFIT_MARGIN we will divide PROFIT_LOSS by REVENUE and times by 100 to return a percentage.
The owning depot of the trip will be assigned a GROUP_NAME and this will be used to determine the PROFIT_MARGIN for the trip.
PROFIT_MARGIN will only be calculated for trips which have made a profit.
If the PROFIT_MARGIN for the GROUP is 3% and the value of ( profit_loss/revenue *100) is between 0 and 3, the profit_loss field will be displayed with an amber background.
If the value of ( PROFIT/REVENUE *100) > 3 then PROFIT_LOSS field will be displayed with a green Background.
Scope
This change will be applied to system version 10.6.0 on CONTST and once approved CONPRD.
Set-up
Pre-requisites
None
Menu Structure
Unchanged
Data
Create a new field in the OMS_SCHED_GROUP table, PROFIT_MARGIN. This will hold a numeric value against each GROUP_NAME. The data will be populated by the users.
Functional Description
On the finance tab of the trip screens, the cost and revenue of the trip are displayed. A new field has been added recently, which displays the profit/ loss of the trip. This calculated by the difference between the carrier-cost and trip-cost and trip-revenue.
A profit is indicated with a green background a loss with a red background. This formatting will be changed to indicate how the GROUP is performing. The group is identified as the GROUP which the OWNING_DEPOT is assigned to. Each GROUP will be assigned a PROFT_MARGIN value which indicates the percentage above which a profit is being made.
To find the % of profit, the profit/loss value is divided by the Trip revenue. In the above screen shot , we will say the GROUP is EXELBAWT which has a PROFT_MARGIN of 5%. To calculate the profit_margin of the trip :
872.89 / 948.88 = 91% profit. This trip has clearly exceeded the GROUPS profit margin, so the profit/loss field will remain with a green background.
If the profit/loss value had been between 0 and 47.44 (5%), the field would be displayed with an amber background as the proft/loss is within the Profit margin for the group.
Any loss will be displayed with a red background regardless of the percentage of loss.
The colour will be set in the forms, using the post_query trigger of the SCH_TRIP_FINANCE data block.
Each time the query is run, the profit/loss will be calculated and the profit margin compared with the GROUP value to determine the fields background colour.
| RED | Any Loss |
| AMBER | > 0 % and <= PROFT MARGIN % for GROUP |
| GREEN | > PROFIT_MARGIN % for GROUP |
TABLE UPDATES REQUIRED
Alter table OMS_SCHED_GROUP
Add column PROFIT_MARGIN NUMBER;
Data will be entered into the new field via the Business Data Maintenance screen as displayed above.
References
| EST 275183 PA-83EFTE Profit Colours on Trip Finances Tabs. v1.0.doc | |||
Document History
| Initial version | ||||
| Reviewed & Issued | ||||
Authorised By
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
