Section 11
Price Lookup Table

The normal quantity based pricing rule ( Q) assumes the unit price is the same per item regardless of the number of items being paid for ... for example, copies might be set using the Q rule at 25 cents each, regardless of how many were being paid for.

This table is used to store information about prices for specific Trancode. If the fee_rule_code in the trancodes table is set to one of the key values in this table, then the user will skip over the unit fee field and enter the quantity. Based the value entered, it will lookup the price in this table finding the unit price that falls between low_range_value and high_range_value. This is used in cases where the price per level varies (upward or downward) is not based on a linear formula (e.g., 25 cents per sheet for copies), but in a non-linear manner, such as 25 cents if you sell 1-10, 20 cents (for 1 to 20), and 10 cents each for 1 to 9999 copies.

An example of what such table entries would look like is shown here:
Because rounding errors can occur, the program will do the math ( unit fee * quantity) and put the amount, rounded to 2 decimal places in the unit_fee field, then store the quantity in a special field in the details table, and lastly, adjust the quantity on the entry screen to one. The COMMENT field show the user-entered-qty.

1. FEE_RULE_CODE
   
   This is a code you assign for your various price schemes. Each set of records associated with a particular pricing scheme must be given the same FEE_RULE_CODE. This code is then placed on the particular TRANCODE table record in the FEE_RULE_CODE field, which is how the program knows to use the rules defined in the PRICE_LOOKUPS table.
   
   
2. LOW_RANGE_VALUE and HIGH_RANGE_VALUE
   
   These values represent the range of quantities RASWIN will use to match your entered quantity. For example, if you have a value of 1 in this field, and 10 in the HIGH_RANGE_VALUE field in one of your records, and you enter a value of 5, the RATE associated with this record will be used as the unit fee amount for that line item.
   
   Once this is done, the program will automatically adjust the quantity value to 1, so that when the unit fee is multiplied by the quantity, the resulting price will be what you intended. This must be done because some pricing schemes are such that rounding errors can occur if the system attempts to determine a unit price by dividing the RATE by the quantity.
   
   There is no way around this issue.
   
   
3. RATE
   
   This value is the price for the item, based on the quantity entered and matched to the values in the LOW_RANGE_VALUE and HIGH_RANGE_VALUE fields. See the above comments regarding how the price is actually calculated.
   
   

Note: If you have a complicated pricing structure, Quadrant can assist you in setting up the data needed to properly price the item, regardless of how many steps there are in your setup.

This can involve, in some cases, setting up hundreds or in very rare cases, even thousands of pricing records in the PRICE LOOKUP table. We have some utility programs and techniques for creating these records very quickly using Excel and some small programs we have created specifically for this purpose.

Please do not spend a lot of time entering these records manually unless you really like to type! We can save you hours of effort if you let us know what your pricing scheme is on a particular item. In operation, there is no performance penalty in having a large number of records in the table, as the table fields are indexed and accessing a scheme with 10000 records takes no more time than accessing one with only 100.

By using the PRICE LOOKUP table we are able to avoid having to include custom code in RASWIN that is unique to a particular client. We have done this in the past, but it can create issues when the price rules change, as the hard-coded formulas then require program adjustments, whereas the PRICE LOOKUP table can be changed to accommodate just about any pricing scheme your city or county council may dream up.

In addition the price rules table, we sometimes use stored procedures to determine prices so that the underlying code of the RASWIN program does not have to be changed when the price rules change as long as the factors that are fed into the pricing formula, such as a quantity (typically # of copies, # of employees, gross revenue, square footage, number of plumbing fixtures, etc.).