Knowledgebase

Trading Software > Help Desk > Knowledgebase

Data Import: split pricing (re) processing

Solution

2018-01-31: Created: Version 2.02.##.1011

2020-02-29: [Logical test] and [Ignore] filter options when working with Split Price Import Clusters

Importing Split pricing data into the BMS can be achieved with a few tweaks to the standard Inventory Import process.

You will need to:

1) Setup Split Pricing Product Groups and Price Groups

This is achieved in Snapshot Reporting -> Show Edit -> Customer Specific Pricing

2) Prepare your incoming data. Your incoming data should include:

i) Price band Columns: Columns containing the uplift over cost for a SKU in a given price band. The column names will be mapped as part of the split pricing column mapping process. You may choose to use the following Price Banding: BAND1|;|BAND2|;|BAND3|;|BAND4|;|BAND5

ii) An flag to indicate which SKU's can be split: For instance, you may be selling cases of wine, and packs of toilet rolls. You are prepared to split a case of wine, but not a pack of toilet rolls. You may include a colums with a YES / NO value in it

iii) Product Grouping: Include a column that indicates which product group a SKU belongs to. A SKU can only occupy 1 product group. The BMS maintains product group membership as a SKU attribute.

3) Column Mapping

You will have to set up a column map for split pricing that builds a relationship between the data you present to the BMS (source data) and the data passed into the BMS schema. Column mapping for split pricing is set up in the [Stock Import File Data List] UI, which is accessed via the

Stock Data Importer -> Data to Import menu.

To create or edit column mapping values, use

Configuration menu -> Manage Column Maps

Example of Split Pricing Column Mapping: Attached -> SplitPrice_ColMapExample.htm.pdf

2020-02-19: [Logical test] and [Ignore] filter options when working with Split Price Import Clusters

It may be necessary to ignore one or more matching cluster during an import. If we can logically determine which clusters to ignore, we can use the Logical_test or Ignore filter options to limit what data is processed into the BMS.

Logical Test:

Defaults to TRUE. Fails safe to TRUE.

Create a test something like [Column Name] = [VALUE].

You can use the following operators in your logical test: =|<=|>=|<|>
Column Name must evaluate to a existing column in your import data
Value can be anything you like but will be converted to a number for all operators other than '='

The system will collect a value from the specified [column name] column and evaluate it against your [VALUE]

Ignore:

Defaults to FALSE.Fails safe to FALSE

This is a simple evaluation that uses a [column name] and tests its value to see if it evaluates to TRUE

4) Import your split pricing data

This is achieved via the [Stock Import File Data List] UI: Import Other menu -> Import Split Pricing

5) Review your imported split price data before creating BMS data.

Check Data -> Review -> Review Split Pricing

The BMS will generate a table of Batch Specific Split Price data that will be / has been used to generate BMS Split Price Records.

PRODMAP: This is the map value provided in your source batch data

QTY SPLIT: This is the split quantity value collected during the IMPORT process drawing. You define where this data is collected from as part of your Import Column Mapping process

SUPREF: what you call your product in your source data

PRODGROUP: The PRODUCT GROUP this SKU maps to. If you see <<UNMATCHED>> you should review your ProdGroup mapping under the Import UI Check Data menu.

PRICE BANDS: A column named to include all price band names, showing the appropriate percentage of value. Semi Colon separated.

Now that your split pricing data has been imported into the BMS transition tables, we can consider creating SKUs in the BMS with related Split Pricing records. For this process to work, and before we can process new data into the BMS we must adhere to some rules.

RULES:

--------

i)INCOMING Cost (COST) always = case cost Excl VAT

ii) INCOMING Qty (PER) always = case qty

iii)INCOMING PackSize will be reset to = 1

iv)INCOMING [TRADE] always = [case cost] x (1 + worst price group) Excl VAT

-------------------------------------------

It may be necessary to perform some pre-processing logic to your incoming data. This can be done using a custom import routine. These routines are specific to the import you are performing, and are stored in the

Stock Data Importer: Create New Stock Records menu -> Custom Import Routines -> Manage Custom Routines

Creating Split Pricing Records in the BMS, linked to existing SKU's is achieved using

Stock Data Importer: Create New Stock Records menu -> Reprocess -> Reprocess Split Pricing

(See developer notes)

Note:

1) If you want to create more than 1 split per SKU you can do so by creating multiple [Batch Column Map Clusters] in your Split Pricing Column Map interface. Each cluster can draw on a discreet set of source columns allowing you to draw on more than 1 Split Quantity Source column.

Once your data is imported, you can edit SKU Splits as required.

Developer Notes

Creating Split Pricing Records

Stock Data Importer: Create New Stock Records menu -> Reprocess -> Reprocess Split Pricing

Split Pricing records are created using the contents of [a_ibs_pricesplits] which may represent the entire contents of [BatchTableName] or a subset (determined by the application of a custom import script). The steps below describe how the contents of [a_ibs_pricesplits] translates into BMS Split Pricing records.

If [a_ibs_pricesplits] is a subset of [BatchTableName] then it is necessary to apply step 5 below as part of any script that removes records from [a_ibs_pricesplits]

Remove records from [t_pricesplits] because there is no index that can control changes to QtySplit

Then remove orphan records from [t_pricesplits_s_price]

Populate t_price_splits with records from `a_ibs_pricesplits`

that are related to the Batch being processed

Populate `t_pricesplits_s_price` with PriceSplit / ProdGroup / PriceGroup combinations

This table carries uplift values to be applied when generating Split Prices

Add Split Price Records for items that have already been processed with a qty value > 1

but this time as singles.

This will allow Split Pricing to return s_price values for a quantity of 1

This statement draws the uplift value from a_ibs_pricesplits.SINGLE_RATE

Remove any t_pricesplits_s_price records that would return a ZERO value

Remove any orphan t_pricesplits_s_price