Creating and Managing Pricing Overrides Outside of ERP Sync

A preview of this functionality is available as of version 4.16. If you are interested in getting access let us know.

Overview

This functionality is part of bundle -Pricing Overrides Configuration

Pricing Overrides synced from ERP systems to the site should not be edited as the next time the record syncs it will likely be reverted to previous state and any manual updates reverted t the synced value.

Pricing managed on the website is the responsibility of the site owner and any assistance requested by manually added/edited pricing overrides will most likely result in a billable engagement if CIMcloud assistance is requested.

Most pricing is applied to products on the website through pricing overrides. If no pricing overrides are found the product will default to the retail_price on the product. When a user first goes to the site the pricing overrides that apply to anonymous users are populated in the sessions_pricing_overrides_map (spom) table. When the user logs in these are replaced with the pricing overrides that apply to the specific user. When interacting with products on the site, the pricing comes from these spom records. This means if a new pricing override is added after the user logged in the override will not apply until their session is refreshed. If a pricing override mapped to the session is updated, the updates will apply as soon as the override is updated and the page is refreshed on the sitefront.

If no pricing overrides apply to a product the price will default from the retail_price on the product (products.retail_price).

On top of pricing determined through the product record and standard pricing overrides the price can also be impacted by Customer Trade Discounts. This comes from a percentage set on the Account record (Typically Sage100). A specific manually created override has to be on the site for this to apply. More details follow.

In the following article database field names are typically in parenthesis. If using spreadsheet uploads to create records it is recommended, as with any new upload, to manually create a record first through the UI to see how things are populated in the database and to verify the records works as intended. Then do a single upload to verify the upload template works before doing a larger number of records.

Pricing Overrides

Overall Type of pricing override settings

Pricing overrides can be set up in three high level ways. The use of these is global for a site and sites cannot mix type of overrides.

1. Standard – (standard for Sage100) This is where the threshold quantities and adjustments are all on one pricing override record
2. Price Breaks Table (used for Acumatica and Sage500) This is where the threshold qty and adjustments are on separate records mapped to the pricing override. Needs a specific global option to be set to be used. Currently managing pricing using the pricing_breaks table is not supported through the site UI.
3. Price Sheets (used for X3, Sage300 in specific use cases) – This uses the single pricing override where different prices are put in different fields on the override and the field used is based on a field on the account. Needs a specific global option to be set to be used.

How Pricing Overrides are Applied to Products

There several fields that control which product(s) a pricing overrides applies to. The first (product_applies_to) defines the level at which the override applies. The standard values used outside of price sheets are

1. All Products/Styles (database value ‘all’) – This will apply to all products
2. Product Class (database value ‘product class’) – This applies to all products with the designated product class as defined below and matching the value on the product_class field on the product record(s)
3. SKU (database value ‘sku’) – This will apply to the product with the sku designated in the field defined below.
4. SKU-Unit of Measure (database value ‘sku-uom’) – THis will apply the override to products matching the sku and uom designed in the fields defined below. For this to function properly the global option, GLOBAL_MULTI_UOM_CURRENCY, needs to be enabled. This is done through the UOM feature in the Advanced UOM bundle

Next the value for this application type needs to be defined (for example if SKU is selected then the value for the specific sku to apply to needs to be set) and there are several fields available based on the application type identified. If All Products/Styles selected then no other information is needed since it will apply to all products. The other fields are:

• Product (database field p_id) – This holds the p_key for the product the override is to apply to is SKU or SKU-Unit of Measure is selected above. If doing imports (including ERP sync) the p_key will be looked up if the sku value is in the field.
• Unit of Measure (database field uom_id) – This holds the uom_key from the unit_of_measure table for the UOM the override is to apply to. If doing imports (including ERP sync) the uom_key will be looked up if the uom value is in the field. This field displays below the product field when SKU – Unit of Measure is selected above. Requires the Advanced UOM bundle.
• Currency (database field cur_id) – This is needed when using the global option for Unit of Measure (GLOBAL_MULTI_UOM_CURRENCY). If doing imports (including ERP sync) the cur_key will be looked up if the currency value is in the field from the currency table. This field displays towards the top of the page when enabled on the page as shown below.
• Product Class (database field product_class) – This field displays if Product Class is selected above. This is a text field and will apply to products with the product_class field on products matching the value entered

Currency field

How Pricing Overrides are Mapped to Who They Apply To

How pricing overrides are mapped to who they apply to is done in the User Profiling section of the pricing override. While there are additional options available, the current supported options are explained here. Other options will be mentioned later in the article.

• All Users (value in database field access_type = ‘all’) – When selected this override will apply to all users logged in and anonymous.
• Accounts (value in database field access_type = ‘restricted’, and the mapped accounts selected) – Pricing overrides are applied to accounts through the user_profile_map table when mapping to accounts; where content_table = ‘pricing_override’, user_table will be “accounts1′ when mapped directly to accounts, content_key_id is the pricing override key, and user_record_id is the key for the table in user_table (so it would be the a_key for the account if the value is accounts1). This is created directly when posting through the UI. If doing an import (including ERP) then need to set access_type as ‘restricted’ and a semicolon delimited list of the account ref_id’s in accounts_vir.
• Price Group (value in database field access_type = ‘restricted’, and the designed price group set in the price_group field) – This ties the pricing override to accounts where the value matches the price_group field on the account matches the price_group field on the pricing override. These are text fields and not looked up. A few notes:
  • Feature setting Price Group for Anonymous Users under feature Product Price Settings is available to set the price group for non-logged in users or any user without a pricing override assigned.
  • In Sage100 you can set pricing against price groups and leave the value blank and it applies against customers where the value is left blank. This is the default on how things are set up. To distinguish between this and accounts on the site that are not in sage when mapping price group values from sage that are blank we actually set the value to the word ‘BLANK’.

Setting Date Ranges for When Pricing Override Applies

Pricing overrides need to be set when they apply. This can either be always or a date range.

• All Dates (sets field when_applies to ‘always’) – applies across all sates
• Only These Dates (sets field when_applies to ‘restricted’) – It applies across the date ranges entered in the following fields. The override will not be applied the the session if expired and if on the session past the expired date it will not apply.
  • Start Date (field start_date) – The start date/time of the override
  • End Date (field end_date) – The end date/time the override applies

Determining Pricing Override Output Price

Calculation Basis

This is the field (calculation_basis) that holds the price that the override is adjusting. If the Calculation Adjustment (explained next) is just setting the actual price this can be anything, but the adjustment in the next step could be an increase or decrease off this value. The possible values are as follows, some may need to be turned on through options to display. The display of the Stage1 and Stage2 values can be impacted by the pricing overrides stage value set in the header. Stage values are explained below in the Determining Pricing Override Order of Application section.

• Retail Price (Database value ‘retail_price’) – This is referencing the retail_price field on the product.
• Suggested Retail Price (Database value ‘sretail_price’) – This is referencing the sretail_price field on the product.
• Cost (Database value ‘cost’) – This is referencing the cost field on the product.
• Stage 1 – (Database value ‘stage1’) – This is referencing the output value from the price determined from stage 1 overrides. This would not be set on a stage 1 override (stages explained below).
• Stage 2 – (Database value ‘stage2’) – This is referencing the output value from the price determined from stage 2 overrides. This would not be set on a stage 1 or 2 override (stages explained below).
• Other fields from product. The following fields can also be referenced and just match the exact same field on the product record – wholesale1, wholesale2, wholesale3, wholesale4

Calculation Adjustment

This determines how the price is calculated based on the value noted previously in calculation_basis and the value(s) following in the adjustment fields. The value is populated in field calculation_adjustment. The available values are:

• Set Actual Product Price  (Database value ‘set_price’) – This sets the price to the value in the adjustment field
• Percent Decrease  (Database value ‘percent_dec’) – This decreases the value from the calculation basis by the percentage in the adjustment field
• Percent Increase  (Database value ‘percent_inc’) – This increases the value from the calculation basis by the the percentage in the adjustment field
• Incremental Fixed Price Decrease  (Database value ‘fixed_price_dec’) – This decreases the price from the calculation basis by the value in the adjustment field
• Incremental Fixed Price Increase  (Database value ‘fixed_price_inc’) – This increases the price from the calculation basis by the value in the adjustment field
• Percentage Gross Margin  (database value ‘percent_gm’) – The output price is (calculation basis)/(1 – Adjustment),  where the calculation basis is typically cost and the adjustment is a percentage.
• Percent Decrease on Session Trade Adjustment (s.trade_adjustment) (Database value ‘trade_percent_dec’) – This decreases the value from the calculation basis by the value from the session trade_adjustment. There will be no value in the adjustment on than override using this setting. The main use case for this is the Sage100 Trade Adjustment where the calculation basis is the output from Stage1.
• Percent Increase on Session Trade Adjustment (s.trade_adjustment) (Database value ‘trade_percent_inc’) – This increases the value from the calculation basis by the value from the session trade_adjustment. There will be no value in the adjustment on than override using this setting.

Threshold Quantity

This is the value that the override is being applied to. These are the tiers. The adjustment that matches applies to values that match or are above up to the next threshold value. The value is applied based on the value in the threshold basis noted above. The first value should always typically be 1.

The fields are threshold_qty_1 through threshold_qty_25 (threshold_qty_1, threshold_qty_2,… treshold_qty_25) and match up the adjustment fields with the matching numerical value (threshold_qty_1 matches up to adjustment_1, threshold_qty_2 matches to adjustment_2,…)

Adjustment

Where appropriate these fields hold the value used with the Calculation Basis and Calculation Adjustment to determine the output price from the override. There can be multiple values if quantity pricing (tiered pricing) is being used on the override. This is where different quantities being purchased have different prices. For Standard settings this comes from fields on the pricing_override records and for sites where price breaks have been turned on the values come from pricing_breaks table (even if only one level of pricing). Information on using the price breaks table is below.

Pricing_overrides table – This uses the threshold_qty_x and adjustment_x fields in this table. It supports up to 25 levels, so threshold_qty_1, threshold_qty_2,… through threshold_qty_25 and adjustment_1, adjustment_2,…through adjustment_25.

Pricing_breaks table – These match to the pricing override they apply to by the po_id field on in the pricing_breaks table and the quantity goes in the threshold_qty field and the adjustment in the adjustment field. Not currentlysupported through UI

Flat Fee

Note the use of the flat fee section is not currently supported.

Threshold Basis

This is typically the quantity of the product but the value in the threshold_qty fields can also reference other values like the price. This is defined by the value in field qty_threshold_basis. Possible currently supported values arevalues are:

• quantity – This is the quantity of the product(s) in the cart the override is being applied across.

Applying Threshold Qty Across Multiple Products

Pricing overrides can be set so that the threshold_qty can apply across multiple products in the cart. The database field to populate is tally_threshold_totals_by. This section does not display be default on the pricing overrides add/edit page. The options are:

• Per Product (Database field value ‘product’) – This s the default if not populated and the pricing is based only on the individual product line
• Per Class (Database filed value ‘product_class’) – This combines the total qty of products in a given class to determine the threshold quantity for the adjustment
• All Products in the Cart (Database field value ‘cart’) – This uses the total qty of products in the cart to determine the threshold quantity to apply to each product
• Highest Qty in a Class (database field value ???) – Believe this will use the highest qty of a given product in a given class and use that as the threshold quantity for all item in that class

Determining Pricing Override Order of Application

Multiple pricing overrides can be applied to the same product in a given session, so there are fields to determine the order that the overrides apply to determine the winning override or the price builds through multiple overrides properly. Examples would be:

• There is a base price override that sets the price for all users for a product. On top of that a customer gets pricing based on a customer group for some products, and additionally there certain products that have special negotiated pricing. There would be the pricing override applied for the pricing for all customers, then one that gives the pricing from their customer group, and then some products would have an override tied directly to the customer. The final override for the customer specific pricing would need to be the one that applies.
• On top of the above there may also be an adjustment from Sage100 customer discounts or Acumatica Line Discounts that would apply on top of the winning level from the previously determined price.

There are two main fields used for determining the order pricing overrides apply:

• Priority (pos) [any numeric value] – This is a “position” field where the override with the higher position applying over those with lower pos in a given stage.
• Stage (Type) [Database values: ‘stage1’, ‘stage2’, ‘stage3’] – The winning override in each stage is determine based on the pos and then the next stage runs using the pos for the overrides in that stage to determine the order applied within the stage.

In the first example above pricing overrides for customer specific pricing would be set with a higher pos than customer price group pricing and pricing applied to all customers would have the lowest pos. This would all be in the same stage (type).

For the second example the discounts would be set as stage 2 overrides applied to Stage 1 to give the discount on the value determine in the first stage.

There are some additional specificities that will also take president if the pos matches on pricing overrides.

• Warehouse (database field pw_id populated with the warehouse key) – If the warehouse on the pricing override matches the warehouse for the product, the pricing override will take apply over a pricing override at the same pos without a warehouse. For warehouse pricing to work properly the warehouse pricing feature needs to be enabled
• UOM (database field uom_id populated with the UOM key) – If the UOM on the pricing override matches the UOM for the product, the pricing override will take apply over a pricing override at the same pos without a UOM. For UOM pricing to work properly the Advanced UOM feature needs to be enabled.

 

Other Fields/Concepts Used

These are fields you will commonly see set for specific cases

applies_to_all_snapsites – This field needs to be explicitly sett o 1 for the pricing to apply to additional customer sites or snap sites.

proceed_to_next – The system defaults to this being seen as set to 1. If set to 0 the system will stop within the given stage and not process any other overrides.

populate_price_before_adustment – If this is set to 1 when an item is added to cart it will populate specific price fields on the order detail differently to capture the adjustment the override applies to the price input to the override in fields to capture the price before the override and the adjustment/price after the override. This is so the system can display the adjustment “below the line” during checkout and in some cases pass it into the ERP differently. This is used for Sage100 trade discounts and Acumatica Discounts.

only_apply_if_price_is_lower – When this is set to 1 it will only be used if the price output is lower than the price input to the override. This is used with Acumatica Line Discounts where if multiple discounts apply to a product only the one with the lowest price should apply.

Price Sheets

Instead of using threshold_qty and adjustment fields on the pricing override, prices are stored in specific price fields on the override. The price a particular customer gets is from a field on the account matching the specific price field on the override. Tiered pricing does not work with price sheets.

This requires global option GLOBAL_USE_PO_PRICE_SHEETS to be enabled. This must be done by CIMcloud.

You apply to products as you do on other override types and as noted above

Fields on the override:

• Type (Database field value ‘price-sheet’) – This identifies the override as a price sheet
• calculation_basis (Database field value ‘ps-winning’) – This makes the winning price sheet price apply
• calculation_adjustment (Database field value ‘set_price’) – price sheet set price versus doing an adjustment on an existing value
• qty_threshold_basis (Database field value ‘quantity’) – typical value for price sheets
• price_sheet_type (Database field value ‘winning’) –
• price_to_use (Database field value ‘price-field’) –
• cost fields 1-10 – you can use any of these ten fields (cost1, cost2,… cost10) to store prices.
• wholesale fields 1 – 10 – you can use any of these ten fields (wholesale1, wholesale2,… wholesale10) to store prices.
• opt fields – opt fields can also be used to store additional prices if needed.
• The field on the account that holds the price to use is price_sheet_field

So if accounts.price_sheet_field is populated with value ‘wholesale3’ the price in option_override.wholesale3 will be the price for pricing override that applies to the product.

Other type overrides can be applied on top of this to give additional discounts.

Price Groups Overrides

Price Group Overrides are a separate record that can allow pricing for a specific customer and product group to come from an override price group/level  in place of the default price group/level from that customer/account. So a customer/account may get pricing based on Price Group A standard but for a specific product group they may get Price Group C. This set-up is involved and is not currently supported outside the sync from specific ERPs (mainly Sage100) even though is shows available in the menu.

 

Product Promotional Pricing

This where a price/adjustment on the product record takes priority over other pricing. This is not on by default and currently needs a custom feature to enable. This is because it is not compatible with customer trade adjustments in all scenarios to match Sage100. The feature that needs to be enabled is: PO_USE_SALE_PRICE and must be done by CIMcloud.

If enabled and set up to apply to a product this will apply as the last adjustment applied.

The fields on the product used are:

• sale_price_calc_type – Database values:
  • Percent Decrease
  • Fixed Price
• sale_price_basis – Always ‘retail_price’
• sales_price_adj – Database values:
  • SalesPromotionDiscountPercent
  • SalesPromotionPrice
• sale_price_start
• sale_price_end