Entering a Price SQL

You can calculate stock prices using a SQL statement. You can enter a price SQL statement when:

Setting up Sell Prices in the Exo Business Configurator.

Creating a price change or a scheduled price change.

Use the information below to construct a simple SQL formula in the Price SQL field. Advanced users you can use any valid SQL formula with any field in the STOCK_ITEM table, including Extra Fields.)

Fieldnames

Commonly used fieldnames are:

SELLPRICE1 ... SELLPRICE10 - these ten fields correspond to the ten price names set up in Exo Business Configurator under Admin > Stock > Price Names.
AVECOST
STDCOST

Variables

You can use these variables when configuring Sell Prices:

@EXCHRATE - the exchange rate used with the currency selected for the price.
@TAXRATE - the tax rate entered for the Sales GST property on the Details 2 tab.

Examples

Price Scenario	Price SQL
Calculate a price by factoring another price	SELPRICE2 * 1.3
Calculate foreign price from local cost	SELLPRICE2 * @EXCHRATE
Calculate GST inclusive price from GST exclusive cost	STDCOST * ((100+@TAXRATE)/100)
Calculate GST exclusive price from GST inclusive price	SELLPRICE2 * ((100-@TAXRATE)/100)

Use these variables when creating price changes:

@NEWPRICE
@CURRENCY - the current exchange rate for the nominated currency

Examples

Price Scenario	Price SQL
Price should equal Average Cost Plus 30% Mark-up	AVECOST/0.70
Price should equal standard cost Plus 30 Margin	STDCOST*1.30
Price should equal Retail price (Sellprice2) less 10%	SELLPRICE2*0.9
Price Should equal Sellprice1 in this price name's Foreign Currency	SELLPRICE1/@CURRENCY
Price should equal Average cost + 33% rounded down and then rounded back up to just under the next dollar — for example, $5.99.	FLOOR (AVECOST*1.33)+0.99