285382

DHL C-MTS

Tiered rates to support Dunelm Multi-Rates

FUNCTIONAL SPECIFICATION - 10.6

- 1.0

Reference: FS 285332 RB-8D9KNR

Client Requirement

To provide flexibility to rate charges against standard, tier'd or fixed amounts by volume.

The business requires the ability to input rates using a mixture of tier'd parameters.Provide functionality that will allow the following for tier'd rates regardless of DU type, for example:qty rate1 £102-4 £95+ £8It is assumed that standard and volume related rates is existing within the application.

Solution

The current multi-rate solution was developed specifically for Pallet rates only. The functionality currently is limited to charging an additional pallet rate after the first pallet for a PO. One of the current limitations is that all Pallet rates have to be configured in the multi-rate format even though not multi-rate agreements. i.e. first at £29 and subsequent at £29.

The contract screen and the rating engine will be changed to allow multi-rate to be set up in a new tiered structure in a generic way at charge level in the Tariff screen so that multi-rate can be configured for any DU type. The rate screen will be modified to allow new values to be configured from the units drop down list on the charges panel. This will allow rates to be configured as per the following examples;

First Pallet £29 – subsequent pallets £27.55

Tier 1 to 52 Pallets

Charge 29.00 per 1 PALLET=1

Charge 27.55 per 1 PALLET>1

First CTN £10, second to fourth £9, fifth+ £8

Tier 1 to 1000 CTN

Charge £10.00 per 1 CTN=1

Charge £ 9.00 per 1 CTN>1<5

Charge £ 8.00 per 1 CTN>4

The change means introducing additional static values into the unit drop down for du type and > and < and = combinations. This will be configured using a new button to allow records to be created in a new pop-up panel, one for each value DU and range and numerical quantities required. Note that the format will be restricted to known DU values concatenated with = x or > x or > x and < x. No spaces will be allowed and no combination of signs, for example >= or <= will not be supported.

Note that the new calculation method is designed to be maintained with (usually) a single Tier at the Tier ID level of the Tariff Detail screen.

For reference, the screen below shows the current Tariff Details screen. This screen will be modified to allow the tiered values (like PALLET>3) to be created from a new button. The Tiered values will then be available in the Units drop down list. A new button called Units Names will be included to allow the user to create charge tier values to appear in the Units drop down list.

The C-TMS rating engine will be modified to process the tiered rates as defined above using DU and signs to define a list of accumulating revenues or costs to apply to the order.

This new functionality will allow the description of charges displayed in the payments screen, the Description field, to continue to be generated to define the method of calculation of each revenue or cost.

Scope

This change will be applied to system version 10.7.

Set-up

Pre-requisites

Rates will be reconfigured to take advantage of the revised tiered charge mechanism.

New database table to store the detailed tiered calculation results per charge tier and DU will be implemented and only populated for DU charge method configured against the customer record.

System Registries

No new system registries required to support this development and implementation.

Functional Description

Contract Maintenance

The latest proposal (as opposed to the original solution specified in the solution section above and estimate document) is to introduce 2 new columns to the Charges section of the Contract maintenance form from the CNT_CHARGE table :-

MULTI_FROM NUMBER(3) MULTI_TO NUMBER(3)

They will be added between the Units and the Charge Type columns with the labels ‘From’ and ‘To’ with the other columns narrowed as necessary to fit them in.

The 2 new fields will only be allowed to be entered/changed if the Units field is set to one of the Dunelm recognised DU Types configured i.e. ‘CTN’, ’BAG’, ’ROLL’, ’OHPAL’, ’OSPAL’, ’OCTN’, ’TOTE’ or ‘PALLET’.

Other units values like fixed, weight, stops and distance will still be available and the respective functionality relating to these charge unit definitions will remain unchanged.

This means that the DU specific qty tiers (from and to) will be available to be used with fixed charges, for example.

Package Changes

The procedure ‘Set_Order_Line_Qty_Params’ in the RATE package will be changed to remove the specific code around the ‘PALLET’ Du type that is currently used to set the quantity to 0.5.

The procedure ‘Apply_Charges’ in the CNT package will be changed for the calculation of the ‘v_charge_factor’ within the get charges loop.

If none of the new fields are populated then the code will remain unchanged and leave the charge factor as the original quantity from the appropriate charge tab as now (or 1 for ‘Fixed’ charges).

1. If Quantity >= ‘To’ Then

1. Charge Factor set to ‘To’ – ‘From’ + 1

1. Elsif Quantity >= ‘From’

1. Charge Factor set to Quantity – ‘From’ + 1

1. Else

1. Zero

Example (as per client requirement section):-

Contract Tier Charge setup as :-

' 001 10.00 1 CTN 1 1 002 9.00 1 CTN 2 4 003 8.00 1 CTN 5 9999 i.e. The first carton will be charged at 10.00, second, third and forth cartons with be charged at 9.00 and any more cartons will be charged at 8.00 Example calculation table to illustrate charging method; Quantity Charge 001 Charge 002 Charge 003 Total Charge 0 0 * 10 = 0 0 * 9 = 0 0 * 8 = 0 0 + 0 + 0 = 0 1 1 * 10 = 10 0 * 9 = 0 0 * 8 = 0 10 + 0 + 0 = 10 2 1 * 10 = 10 1 * 9 = 9 0 * 8 = 0 10 + 9 + 0 = 19 3 1 * 10 = 10 2 * 9 = 18 0 * 8 = 0 10 + 18 + 0 = 28 4 1 * 10 = 10 3 * 9 = 27 0 * 8 = 0 0 + 27 + 0 = 37 5 1 * 10 = 10 3 * 9 = 27 1 * 8 = 8 10 + 27 + 8 = 45 6 1 * 10 = 10 3 * 9 = 27 2 * 8 = 16 10 + 27 + 16 = 53 The payments description text will be modified to reflect the elements of the charge calculation and the overall order charge resulting value. Currently, the first pallet is described as fixed and this will be modified with the revised solution to read like pallet 1 at x. The rating functionality that allows multi DU type orders to be rated will be retained. This means considering rates for each of the DU types confirmed by the supplier on a single PO consignment. In other words, if an order is some pallets and some cartons the rating method will calculate for each and then accumulate the total charges as a consolidated payment record. A new database table will be introduced to save the detail transactions / audit trail of each of the charge lines within a DU in order that this information can be easily printed within the detail invoice charge backing sheet. The existing effective date functionality will be retained so that a revised charge methods and values can be introduced to replace existing rate data. Effective dates can be maintained at tier and at charge level within the contract data. Where rates are agreed for DU types that are not tiered a simple single charge line in the rate will be entered that relates from 1 to a maximum value using the new from and to fields respectively.

Implementation and Transition

It is assumed that all the current rates will be replaced to reflect the revised tiered data structure utilising the new from and to fields.

Once the rates are re-configured, OBS will run SQL utilities to re-rate all PO orders not yet invoiced.

References

ID Value Per Units From To

WOODS

Ref No	Document Title & ID	Version	Date

Document History

Version	Date	Status	Reason	Initials
0.1	26/01/11	Draft	Initial version	DRM
0.2	28/01/11	Draft	Change to use From and To	DRM
1.0	31/1/11	Issue	Reviewed and Issued	DJM

Authorised By

Dave Meir	Development Manager
Peter Greer	TMSCC MTS Product Mgr