266872

266872 - NW-7TBD7M / RPE calc to take acct of new factors

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

FUNCTIONAL OVERVIEW

Client Requirement

Modify RPE calculation in MTS to take into account the location type.

The objective is to convert the volume by Store (location) type into a standard cubic measure to accommodate the different utilisation achieved by each loading method based on the store type.

RPE Calculation to be based on Location Type :- Outlet Store, Loose Store and Std Store

Product Type :- D98, D99, O98, O99, EMPTY

and DU Type :- Boxed or Hanging

It is suggested that Location Type and Product Type are added into the Despatch Unit Types tab of the Resource Maintenance form and subsequently added as factors in the RPE calculation on an Order. Note that it is important that this calculation works when creating Orders from Bookings and Slots.

Solution

The solution will be based on a new table (RES_DU_LOC_TYPE).

The table will be keyed by DU_TYPE and LOC_TYPE and will contain most of the fields from the existing RES_DESPATCH_UNIT_TYPE.

The RESOURCE form (Despatch Units Type tab) will be amended to allow all of the existing values for the DU Type (Volume, Volume Collapsed, Max KG, RPE, and Priority) to be specified at the lower (Loc Type) level.

The RESOURCE form (Despatch Units Type tab) will be modified to include a detail panel for each main DU type. The detail panel will (optionally) allow the DU profile for a particular location type to be defined. This means the current DU type data will act as a default at PRODUCT and DU TYPE, however if a detail entry is input at LOCATION TYPE, the RPE calculation will use these values in preference to the default.

Anywhere that the current MTS system uses the DU Type to get these values (not required if only getting the description or just validating the DU Type itself) then it will additionally check for a lower level record for the location type and use these values in preference (if found) to those on the higher level.

Procedures requiring change are :-

BKG.Auto_Summary

CSV.Write_Var_Analysis_Summ

CSV.Write_Var_Analysis_Store_Dtl

CSV.Write_Var_Analysis_Trip_Dtl

GEN_TI.Generate_TIs

GEN_TI.Generate_CrossDock_TIs

GEN_TI.Create_Booking_TIs

GEN_TI.Auto_Advance

OMS.Validate_Order

RES.Get_Trailer_Du_Cap

RES.Delete_Despatch_Unit_Type

RES.Get_DU_Type_Details

RES.RPE_Equivalent

RES.RPE_to_DU

NB) None of the RES code currently passes the location ID so will need overloading and the code calling these procedures changed to pass the location where required.

Additional change to BOOKING form for Source/Destination selection and drop down lists needs the Branch option to be a case else (of SUPPLIER, RDC, X_DOCK) so that the new Location Types (STD STORE, LOOSE STORE, OUTLET STORE etc) will be picked up when Branch is selected. Note that this change is out of scope of the cost of this RIO and has been agreed as 0.5 days additional charges handled through EARS00027102616

Scope

These changes will be applied to system version 10.6 on MASTST and once approved MASPRD. #

Data

No new data is required in any tables. DU Location data will be entered manually by the user via the new functionality on the RESOURCE form.

FUNCTIONAL DESCRIPTION

RESOURCE Form Chsnges

Below is a mock up of how the new RESOUCRE screen will look with the new DU profile panel at the bottom named ‘DU Location Type’. This new panel will relate to the new table RES_DU_LOC_TYPE (defined in Appendix A). In here users will be able to define DU Information against particular Location Types. If data has been entered in this table, it will be used in the RPE calculation whenever it is called/used. If no data has been entered in this lower level table, then the RPE calculation will default back to the original functionality around data held in the original higher level table (RES_DESPATCH_UNIT_TYPE).

The upper table RES_DESPATCH_UNIT_TYPE will be linked to the lower table RES_DU_LOC_TYPE by DU_TYPE.

PROCESSING AMENDMENTS

Many of the procedures mentioned that require changing to calculate the RPE value on Location Type are around Bookings. In these procedures, 2 locations are passed in; Source and Destination. In calculating the RPE value, it will be the LOC_TYPE of the Destination that is used in the new RPE calculation when querying the table RES_DU_LOC_TYPE. The Destination is used as it will be here, at the store type(LOC_TYPE), that will decide RPE loading capacity.

To retrieve the LOC_TYPE of a location the following code will be used within each procedure listed that needs amending, passing in the relevant LOCATION_ID and retrieving the returned value into variable v_loc_type for later use:

CURSOR c_loc_type (cp_location geo_location.location_id%TYPE) IS

SELECT depot

FROM GEO_LOCATION

WHERE location_id = cp_location;

v_loc_type geo_loc_type.loc_type%TYPE;

Where a cursor appears in a procedure to retrieve values from the RES_DESPATCH_UNIT_TYPE table, a new cursor will also be included to retrieve values from the new RES_DU_LOC_TYPE table. This new cursor will require the LOC_TYPE retrieved from the cursor above to be passed in as well as the DU_TYPE that was originally passed in. An example of this is shown below: CURSOR c_du_loc_type( cp_DU_type res_despatch_unit_type.du_type%TYPE,

cp_loc_type geo_loc_type.loc_type%TYPE ) IS

SELECT volume, max_kg, RPE

FROM RES_DU_Loc_Type

WHERE DU_Type = cp_DU_type

AND Loc_Type = cp_loc_type;

cr_du_loc_type c_du_loc_type%ROWTYPE;

Basically, all the changes in each procedure are around the following:

If a procedure makes reference to values held in table RES_DESPATCH_UNIT_TYPE which are used later in calculations, the procedure will be amended to also query the same values on new table RES_DU_LOC_TYPE for the location type passed in.

If any related records for the location type and DU type passed in are retrieved from lower level table RES_DU_LOC_TYPE these will be used in all subsequent calculations in that procedure. However, if when queried, no records are retrieved from the lower level table, the code in the procedure will default back to the original code and use values held in table RES_DESPATCH_UNIT_TYPE in all calculations as normal.

Therefore, if a client does not want to make use of this functionality, they will not have to set up any data in table RES_DU_LOC_TYPE via the new (lower) panel on the RESOURCE form. This will therefore mean that all the procedures listed will process as normal.

Calls to RES Functions From Other Areas in MTS

The following list of functions in the RES package need to be overloaded to pass in an extra parameter. This extra parameter will be location_id. When passed in, the location_id will be used to find the location_type of the passed in parameter. This location type will be used to first query table RES_DU_LOC_TYPE for required DU values, otherwise default back to the original code and query values held in table RES_DESPATCH_UNIT_TYPE. As these functions are being overloaded and now require an extra parameter, all calls to these procedures will have to be updated to pass in the location_id, therefore passing in the correct number of parameters. OBS had to query all code in packages, forms and reports to check where each of the overloaded RES functions are called from. Below lists where each is called from:

RES.Get_Trailer_DU_Cap not called from any package or form

RES.Get_DU_Type_Details is called from:

OMS.Validate_Orders

ORDTEMPL.fmb 4 times

RES.RPE_Equivalent is called from:

GEN_TI.Generate_TIs 3 times.

GEN_TI.Generate_CrossDock_TIs 4 times.

DRIVER_ITINERARIES.rep

HAULIER_CONFIRM_FAX.rep

INBOUND_SCHEDULE.rep

LOC_DEL_PRE_WARNING.rep

SAR_INBOUND_SCHEDULE.rep (may not need amending as SAR specific)

TRIP_SUMMARY.rep

UNPLANNED_PRODUCT.rep

RES.RPE_to_DU is called from:

CSV.Scheduled_Trips_CS

LB_ORDER.fmb

OBS will therefore have to amend all the above procedures / forms / reports to also pass in the extra location_id parameter when a call is made to that particular RES function.

REFERENCES

Ref No	Document Title & ID	Version	Date
1	EST-266872 NW-7TBD7M Slots by Loc Type v1.0.doc	1.0	07/07/09

DOCUMENT HISTORY

Version	Date	Status	Reason	Initials
0.1	10/07/09	Draft	Initial version	LAD
1.0	13/07/09	Issue	Reviewed and Issued	DJM

AUTHORISED BY

Matt Crisford	Development Manager
Suk Sandhu	TMSCC MTS Product Manager

Back to CONPRD Release - 24/02/2010