Freight Pricing Schedules (in 3.2)

 

Overview 

The sponsoring customer of this feature would like xTuple to provide a means to store freight rate data in a matrix that can be used at the time of order entry to automatically calculate freight charges. The system will allow the creation of freight pricing schedules that work similarly to item price schedules to be used as the basis for freight value calculations on sales orders. Freight schedules will allow the definition of a matrix of pricing, sites, shipping zones, ship vias and freight classes to be created for a price schedule. These freight schedules in turn will be assignable to customers the same way existing price schedules are assigned.

Freight pricing will be available in all editions of xTuple ERP. It will apply to Sales Orders only and not to Purchase or Transfer orders.

Functional Requirements

Freight price schedules will implement the following new functionality:

  • An "Item Weight Unit of Measure" flag will be added to the Unit of Measure maintenance window.
  • The globally defined item weight unit of measure will appear on the Item window where weights are defined.
  • A new concept of Freight Class will be introduced into the system.
  • A new pricing type "Freight" will be available in item price schedules.
  • Freight price schedules will allow two pricing methods:
    • Flat Rate: a flat freight charge based on the weight quantity break point for a freight class/item site group.
    • Per weight unit: a rate charge that increments proportionally to the weight of the items on an order.
  • Freight price schedule items will optionally allow the rate to apply to a specific freight class.
  • Freight price schedule items will optionally allow the rate to apply to a specific ship-from site.
  • Freight price schedule items will optionally allow the rate to apply to a specific ship-to shipping zone.
  • Freight price schedule items will optionally allow the rate to apply to a specific ship via.

New Terms and Definitions

Freight Class

Item Weight Unit of Measure

Pricing Schedules

    • A class designator assigned at the item level which may be used to determine freight charges.
    • A unit of measure that has been globally defined as the UOM used for item weights. Informational only.
    • A schedule of prices that may apply to items or freight calculations. Formerly only applied to items.

Related Existing Functionality

This new functionality will extensively leverage existing Item Pricing functionality. Freight pricing will be added to price schedules, and price schedules assignment methodology will apply to freight pricing as well. Currently price schedules may be created and assigned that overlap each other to allow for promotional pricing. Freight pricing will also inherit this behavior by taking the lowest available freight rate, so freight promotions such as free or reduced shipping rates in a certain time period may be allowed.

The freight value will be populated on shipments and carried through to invoicing exactly as it currently does in xTupleERP version 3.0.1.

Shipping charge types have a flag that determines whether customers may pay freight. If this flag is not true on the shipping charge setting for an order, freight is not editable.

Conflicting Features

It is possible some time in the future that functionality may be added to connect directly to carrier websites to obtain real time freight information, which would be mutually exclusive from this functionality.

User-Level Functionality

Modified and new user interface files used for these mock up screen shots may be found attached to this page.

Changes to the Item, Item Price Schedule and UOM .ui files have already been committed to the SVN repository (revision 3481).

Freight Class Window

A new pair of screens will be created to maintain freight class. The first will be called freightClasses which will be found in Products > Master Information > Freight Classes. This screen will be functionally similar to the class codes screen by presenting a list of class code records and allowing the creation, editing and deleting of them. The second will be the freight class edit window which will look as pictured:

freightClass.png

 

Item Window

The item window will be altered to include freight class as an optionally selectable drop down box as pictured:

item2.png

The freight class combo box will be populated with all available freight classes. The freight class combo box should be disabled for the following item types: Phantom, Planning, Kit and Costing. The report associated with the item window should be altered to include freight class.

Designating the Item Weight Unit of Measure

There should be a means to define what the unit of measure is for item weights so when pricing is factored it is clear whether we are talking about prices per pound per kilogram and so on. To accomplish this a flag will be added to the Unit of Measure maintenance window to designate one unit of measure as the item weight unit of measure.

uom.png

If a user attempts to flag a second unit of measure as the item weight unit of measure they will be warned there may only be one item weight unit of measure defined and that the other will be unchecked. If they agree, the other unit of measure will be unchecked, and the current one will become the new weight unit of measure.

The Item window will be modified so that if an item weight unit of measure has been defined, the label on the group box around weights will change from "Weight" to "Weight in [uom name]" as pictured:

item.png

 

Creating Price Schedules

The current menu path of Sales > Item Pricing > Price Schedules will be changed to Sales > Pricing > Prices Schedules to reflect the new broader definition of price schedules. A user will be able to create a freight price schedule item by opening the price schedules per the path listed above, clicking "New" and creating price schedules the usual way. When the freight type is selected the pricing schedule item screen will render as pictured:

itemPricingSchedule.png

The fields are described as follows:

  • Quantity break is the amount of weight grouped by the order ship from site and freight class level at which this price takes effect. Note this is different from item pricing which calculates breaks at the line item level.

  • Price is the freight charge valuation that will be used at this break point.

  • Flat Rate vs. Qty per [Weight UOM] determines whether price is a straight rate or increments per weight unit.

  • From allows the user to select a specific site that this rate is applicable to.

  • To allows the user to select a specific shipping zone this rate is applicable to.

  • Ship Via allows the user to select a specific ship via this rate is applicable to.

  • Freight Classes allows the user to select a specific freight class this rate is applicable to.

Sales Configuration Settings

The changes to the sales order described below to calculate freight could add noticeable processing burden to the sales order window. To prevent this overhead from being applied to all users of xTuple ERP, a check box option should be added to the sales configuration window to "Use calculated freight values by default" which will be unchecked by default in legacy databases. This check box option should be located in a new group box labeled Freight Pricing situated to the left of the Credit Control group box in that window.

Sales Order

There will be a new option on sales orders to have freight value entered manually or calculated automatically. The default behavior on a new sales order will be set according to the metric described above in Sales Configuration settings.

When the sales order is flagged to use automatic freight calculation the freight will be calculated using the data from the Pricing Schedules whenever sales order line items are added, edited, canceled or deleted. If the user manually enters a freight value that is different from the calculated value, the system will prompt the user with the message box: "Manually editing the freight will disable automatic freight re-calculations. Are you sure you want to do this?" If the user chooses to accept, the sales order freight calculation flag will be set to "manual" and no further warnings will be offered, otherwise the freight will revert back to the calculated value.

Users will likely need a way to switch back from manual to calculated freight and troubleshoot freight calculations if they suspect they are inaccurate. Therefore the freight label on Sales Order should become a hyperlink similar to the tax hyperlink that when clicked displays a read-only dialog box with the breakdown used to arrive at the freight value as pictured:

freightDetail.png

At this screen the user can switch between manual and calculated freight options. If manual is selected the list pictured below will be disabled and when the screen is closed the user can edit the freight at will and be assured their changes will not be over-written. If calculated is chosen, the screen will repopulate with the calculated freight detail on the order, which will be passed back to the sales order header when the Freight Breakdown window is closed.

All the above is only true if the shipping charge type is set to allow the customer to pay shipping. If the shipping charge type on the order does not have the customer may pay shipping set to true, then the order will be set to manual freight calculation, and the user will not be allowed to set it to automatic. This will effectively mirror legacy behavior of that functionality.

Report Changes

Existing Reports found in Sales > Item Pricing > Reports should be relabeled with the prefix "Item" so they become "Item Pricing" reports. Two new reports should be included in this menu: Freight Pricing by Customer Type and Freight Pricing by Customer. They will work similarly to their item counterparts, but will present information as pictured:

dspFreightByCustomerType.png

Corresponding printed reports will also need to be created.

Problems and Alternatives

The allowance of overlapping schedules and the general complexity of freight pricing makes this functionality subject to error if not set up correctly. A situation could easily arise where orders are calculating freight wrong without warning due to improper staging of data. Unfortunately, there is no good way to warn a user that freight is calculating wrong as the system can not be self aware of the "correctness" of a given value beyond the data set it has to work with. Therefore it will be incumbent upon users to carefully set up the data, review it, and test to make sure all scenarios are covered properly.

One way to help enforce integrity in specific implementations is to make certain fields mandatory such Shipping Zone on Customer Ship-to records or the Freight Price records. Unfortunately, adding configuration options for all possible local constraints would make the application extremely complex develop, document and maintain. However, local functionality could easily be added with scripts that, for example, require a user to make a field selection that would normally be optional. Likewise if users found that over lapping price schedules for freight were undesirable in their environment, a script could be added to check and warn the user if they were about to create a schedule that overlaps with another. Scripting therefore, seems like the most appropriate answer to cover specific implementation requirements that may arise.

Some users may desire exotic pricing schemes such as free or reduced shipping for orders over a certain value, or freight pricing based on the greater of a minimum volume and weight. Unfortunately the complexity of including matrices that can encompass that level of complexity quickly leads to a point of diminishing returns where the application becomes so complex to set up and maintain only the most advanced users can manage it, while still likely leaving even those advanced users unable to achieve specialized pricing requirements not anticipated by the program. We recommend that users with such highly specialized needs develop or have developed custom business logic using separate schemas, screens and scripts included in the xTuple "Screen Builder" tool set.

Internal Design

The main algorithm will accept parameters including sales order id, order date, site (warehouse) id, ship zone, ship via and freight class, and will return one or many rows of freight detail information describing the charge(s). The returned values are one to many rows because it is possible that a sales order may be created that has items listed shipping from multiple sites or freight classes, which means multiple Freight Schedules may apply. Therefore price break logic will have to be aggregated on a site/schedule/freight class basis that may not correspond directly with either the header or line items. These rows can be used to populate the freight detail window shown above, while a query summing the totals will be used to populate the total freight amount on the sales order.

The algorithm should work like the item price algorithm where it looks for the most specific schedule match, and where there are multiple exact matches, takes the lowest price. For example if there are two schedules that match the warehouse criteria, but one is "All warehouses" (actually null) and the other is a specific warehouse, the specific warehouse should be used. When a schedule is returned that has no specific criteria the site, shipping zone or ship via the word "All" should be substituted as the value (I.e COALESCE(warehouse_code,'All')).

Basic Algorithm

This basic algorithm draws heavily on the logic of the existing itemprice function:

Function: freightbreakdown RETURNS freight data row
DECLARE
  Sales Order Id ALIAS
  Order Date ALIAS
  Warhouse Id ALIAS
  Ship Zone Id ALIAS
  Ship Via ALIAS
  -- Note: We pass in the last 4 keys because user may have
  -- changed the S/O header and not committed to the db yet.

  _found BOOLEAN
BEGIN
  Set _found = false

  --Get a list of aggregated weights from sites and
  --freight classes used on order lines
  SELECT SUM(item weights), warehouse_id, freight_class
  FROM item, itemsite, coitem, etc.
  WHERE (matches line items from this order)
  GROUP BY warehouse_id, freight_class

  Loop through each warehouse/freight_class
    --First Check for a Sale
    --(Yes, someone might want to offer "free shipping" at Christmas!)
      SELECT freight data INTO sale record
      FROM sale and price schedule tables
      WHERE
        weight qty break is greater or equal to weight at this site,
        warehouse direct match or 'All' (NULL),
        shipping zone direct match or 'All' (NULL),
        shipvia direct match or 'All' (NULL),
        freight class direct match or 'All' (NULL),
        sale meets order date time criteria
      ORDER BY more specific matches ascending, lowest price ascending

    --Check for Customer shipto price
      SELECT statement like above, except meeting shipto criteria

      If found, compare first shipto result to sale (if exists),
      return the lower of the two, set _found=true and run to next loop

    --Check for Customer shipto pattern price
      Same as shipto except meeting shipto pattern criteria

    --Check for Customer price
      Same as shipto except meeting customer criteria

    --Check for Customer Type price
      Same as shipto except meeting customer type criteria

    --Check for Customer Pattern price
      Same as shipto except meeting customer pattern criteria

  Next Loop

  If _found is false then return a record that has 'N/A' for schedule
  and zero value for freight.

Schema Changes

freightclass

A new table will be required to store freight classes:

Column Name

Date Type

Comments

freightclass_id

serial

primary key

freightclass_code

text

code

freightclass_descrip

text

description

ipsfreight

A new table will be required to store the freight price schedules:

The new ipsfreight table

Column Name

Data Type

Comments

ipsfreight_id

serial

primary key

ipsfreight_ipshead_id

integer

foreign key to ipshead_id, required

ipsfreight_qtybreak

numeric

store qty break data (required, 0 default)

ipsfreight_price

numeric

store price data (required, 0 default)

ipsfreight_type

char(1)

Store F for flat rate and P for per weight (required)

ipsfreight_warehous_id

integer

From warehouse (site) foregin key

ipsfreight_shipzone_id

integer

To shipping zone foreign key

ipsfreight_freightclass_id

integer

Freight class foregin key

ipsfreight_shipvia

text

Ship via if applicable

Note: Weight UOM will not be stored on this record since it is informational only and has no bearing on any calculations.

cohead

A new boolean field cohead_calcfreight should be added to cohead and set to false by default. This will be used to determine whether freight is calculated automatically or manually by the user at the sales order level. It should not be allowed to be null.

freightdata

The freight detail function above will require a new data type to be defined for returning rows.

The new freightdata type

Column Name

Data Type

Comments

freightdata_ipshead_name

text

Schedule name

freightdata_warehous_code

text

Schedule From site code

freightdata_shipzone_name

text

Schedule To shipping zone

freightdata_shipvia

text

Schedule Ship Via

freightdata_weight

numeric

Total Weight

freightdata_uom

text

Global Item Weight UOM

freightdata_price

numeric

freight price

freightdata_type

text

"Flat" or "Per [weight UOM]"

freightdata_total

numeric

Total freight for Schedule

freightdata_curr_abbr

text

Currency abbreviation

Existing privilege checks for Pricing Schedules will apply to Freight Pricing as well.

The api.salesorder view will need to be altered to include the calculated/manual freight option and it's default settings based on the global metric.

The api.pricingscheduleitem view will need to be updated to include information for freight schedules.

Stored Procedure Changes

The algorithm above describes a new function freightdetail that will be created to calculate freight.

Freight calculation should be exectued by a trigger on the coitem record so that when line items are added via the api.salesitem view and the order header is set for automatic calulation that the sales order header is updated with the new freight calculation.

The api view for pricingscheduleitem is odd compared to most in that it combines multiple tables into one presentation to mirror the way in which the Pricing Schedule Item window can present data for all types. The view is made to work by using a union for the select statement, and special save and delete functions to handle changse to item and product pricing types respectively. Similar functions saveipsfreight and deleteipsfreight will need to be written for this view to accomodate the changes made to the ipsfreight table using this view.

Widget Changes

The xcombobox widget will need to be altered to support a new selectable freightclass type.

QA Considerations

Careful testing of various iterations of freight pricing should be conducted including overlapping schedules to ensure the "right" schedule is being used to calculate freight. In particular, the most specific schedule that matches criteria should be used. If multiple schedules match the criteria, the lowest price should be used. So if, for example,two schedules meet the order/site criteria, but one is for All shipping zones and another is for the specific shipping zone, the specific match should be selected. Several iterations of schedules will need to be made to make sure these rules apply correctly.

Documentation Considerations

Reference documentation will need to be updated, and this feature may warrant a wiki User Topic.

Release Considerations

This feature is targeted for the 3.2 release of xTuple ERP.

AttachmentSize
UI.zip16.59 KB