Entering a Price SQL

Stock prices can be calculated using a simple SQL statement, which gives you considerable flexibility in defining your new price. Price SQL statements can be entered in the following situations:

When setting up Sell Prices in the Exo Business Configurator.
When creating a price change or a scheduled price change.

Use the information below to construct a simple SQL formula in the Price SQL field. (For advanced users, you can use any valid SQL formula with any field present 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 Config at Admin > Stock > Price Names.
AVECOST
STDCOST

Variables

Additional useful variables, prefixed with an @ symbol, are available for use in calculations.

The following variables are available when configuring Sell Prices:

@EXCHRATE - the exchange rate used with respect to 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)

The following variables are available when creating price changes:

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

Examples

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