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

Ref No	Document Title & ID	Version	Date
	EST 275183 PA-83EFTE Profit Colours on Trip Finances Tabs. v1.0.doc		19/03/10

Document History

Version	Date	Status	Reason	Initials
0.1		Draft	Initial version	SW
1.0		Issue	Reviewed & Issued	MJC

Authorised By

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager