252347

252347 PA-7DGPFT Add Trip Revenue to Trip Finance

Copyright OBS Logistics © 2008 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

FUNCTIONAL OVERVIEW

Client Requirement

The Finance Tab in Trip Manipulation and Trip planning to show Trip revenue and trip cost (subbed)

Currently 2 Fields visible on Finance Tab of a Trip:

1: Currently Operational- Trip Cost (Payments) - Populated by Carrier related Contract or Manual input of agreed Subcontractor Rates - Sum of PAYMENTS v2.23 Trip Based

2: Revenue - Currently un-used (Greyed-out)- Develop to show sum of all Revenues from all the Orders on the Trip. See Attachment Change Label to REVENUE Sum of PAYMENTS v2.23 (Order Based) for all Orders allocated to the trip

More financial awareness when a Trip is being planned.

Solution

Amend screens Trip Manipulation (TRIPSUM) and Trip Planning (TRIP_PLAN) to include the following changes:

Label currently ‘Cost’ changed to display ‘Trip Cost’. Label currently ‘Revenue’ changed to display ‘Trip Revenue’ Current Greyed out area will be used to display the Revenue of all Orders on the current Trip. This will display in format 123.45 but will remain greyed out along with the current greyed out ‘Payments’ button, so it can not be edited by the user, as it will only be used for financial awareness.

Package ACC.sql will need to be updated as calculating the Trip Revenue requires a call to it when the Finance tab is selected. A new function will be created that will sum all Revenue payments for all Orders on a particular trip. The value will be taken from field ORD_REVENUE in table SCH_ORD. The value held in this field corresponds to the value held in the ‘Revenue’ field on the ‘Finance’ tab within the ORDERS screen.

Scope

This change will be applied to system version 10.6 on CONTST and once approved CONPRD. This form will be applicable to all MTS environments, as the nature of this change does not impact on the current functionality of the form. This field will only be viewed, and can not be edited.

SET-UP

Pre-requisites

None

Menu Structure

Unchanged

Data

Unchanged

FUNCTIONAL DESCRIPTION

New Functionality

The sample screenshot below displays the TRIPSUM screen with the new functionality included - to sum all order payments on the one trip, then display in the greyed out field - for financial awareness. Trip MAN-00665530 on Sched 080428 is the provided example (from COLV):

IMAGE

Also notice, the change to the text labels, ‘Trip Cost’ and ‘Trip Revenue’.

From the screen shots below, note 3 orders that are on this Trip. Summing all Order Revenues will calculate an overall total 300.94, same figure as displayed above.

IMAGE

IMAGE

IMAGE

New Function

The following Function ‘F_GET_TRIP_REVENUE’ will be used in forms TRIP_PLAN and TRIPSUM. It will be passed in a Trip ID (e.g. MAN-00012345) where it will sum field ORD_REVENUE from all Orders on the current trip.

FUNCTION F_GET_TRIP_REVENUE(i_trip_id IN VARCHAR2) RETURN NUMBER IS

v_trip_revenue SCH_ORD.ORD_REVENUE%TYPE;

CURSOR c_trip_revenue( cp_trip_id VARCHAR2 ) IS SELECT SUM(SO.ORDER_REVENUE) FROM SCH_ORD SO, SCH_HAULAGE_ACTIVITY SHA, SCH_TRIP_STOP STS WHERE SO.OMS_REF = SHA.OMS_REF AND SHA.STOP_ID = STS.STOP_ID AND SHA.ACTIVITY_NAME = ‘Load’ AND STS.TRIP_ID = cp_trip_id;

BEGIN

 		OPEN c_trip_revenue ( i_trip_id );
   	FETCH c_trip_revenue INTO v_trip_revenue;
   	CLOSE c_trip_revenue;
   	
   	RETURN (v_trip_revenue);

EXCEPTION WHEN OTHERS THEN RETURN (NULL);

END;

Trigger Updates

General structure the code will take for displaying Trip Revenue functionality in: • POST-QUERY trigger on the TRIP_DTL tabs in TRIP_PLAN and TRIPSUM

TRIP_DTL.TRIP_REVENUE := F_GET_TRIP_REVENUE( :TRIP_DTL.trip_id );

Trigger Amendments

Currently, there is a WHEN-BUTTON-PRESSED trigger on button ‘Apply’ next to MTM Cost in the Finance tab. It includes the code below, which returns values into TRIP_REVENUE, TRIP_COST and ALLOCATABLE_COST:

TRIP_DTL.Trip_Revenue  := ACC.Get_Event_Revenue( :TRIP_DTL.SCHED_NAME || '-' || :TRIP_DTL.TRIP_ID, :TRIP_DTL.Cost_centre, 'DUE' );

TRIP_DTL.Trip_Cost  := ACC.Get_Event_Cost ( :TRIP_DTL.SCHED_NAME || '-' || :TRIP_DTL.TRIP_ID, :TRIP_DTL.Cost_centre, 'DUE' );

TRIP_DTL.Allocable_Cost := ACC.Get_Allocable_Event_Cost ( :TRIP_DTL.SCHED_NAME || '-' || :TRIP_DTL.TRIP_ID, :TRIP_DTL.Cost_centre );

The code to return a value for TRIP_DTL.Trip_Revenue will be removed.

Reasoning behind this, is, after investigation into the procedure it calls in ACC, it will always return the value 0.00. This figure is always returned because the calculation is summing all the order revenues on the trip from table ACC_PAYMENT, where the field EVENT_REF is equal to :TRIP_DTL.SCHED_NAME || '-' || :TRIP_DTL.TRIP_ID (e.g. 080104-MAN-00012345). An EVENT_REF in this format is only stored for Trip Payments. All Order Payments store an OMS Ref in the EVENT_REF field.

This code will be removed. If the new functionality displays 123.45 as the Trip Revenue, and then the user clicks the ‘Apply’ MTM Cost button, the Trip Revenue of 123.45 will be over-written with 0.00.

APPENDIX A TABLE UPDATES REQUIRED

No table changes are required for this development.

APPENDIX B QUOTE & DOCUMENT HISTORY

IMAGE

References

IMAGE

Glossary

IMAGE

Document History

IMAGE

Authorised By

Dave Meir	Development Manager
Suk Sandhu	TMSCC MTS Product Manager