Example: Inserting and replacing price lists

The Data Load utility provides two modes for loading price lists: insert and replace. Use the insert modes for new price lists. Use the replace mode to update an existing price list.

Do not use the insert mode if a price list with the same price list name, or if a uniqueId exists in database. In the replace mode, if the price list name or the uniqueId in the load file does not exist in database, the price list is not replaced. In insert mode, if a price list with the same price list name or uniqueId exists in database, the record is not inserted. In replace mode, if a price list in the load file does not exist in database, the price list is inserted.

This example uses a CSV file to demonstrate how to insert, replace, or delete your data. We can also create and use an XML formatted file to insert, replace, or delete your data. If you choose to create and use an XML formatted file, ensure that your XML elements use the same names as are used for CSV column names.

CSV file with sample price list data

The example CSV file, PriceListGroup.csv contains sample price list data. The first row in the CSV file can also be column names, which depend on the firstLineIsHeader attribute in the price list data load configuration file. The firstLineIsHeader is an attribute of <_config:DataReader> element.

firstLineIsHeader

true

Indicates that the first line in the CSV file is treated as column name data, and it is not inserted into the database.

false

Indicates that the first line in the CSV file is data to be read and inserted into the database.

Formatted CSV file with column headings

Name	Description	Precedence	Type
Standard Price List	This list is the standard price list.	1	S
Holiday price list	This is the price list for the holiday.	5	C
Legacy system price list	This is the price list that is managed by the legacy system.	2	E
Overstock price list	This is the price list for overstocked items.	1	C

CSV column and XML element definitions

The column order is defined in the price list data load configuration file. See the number attribute in the <_config:Data> element.

UniqueID

(Optional, String) The identifier of the price list. If both the price list name and price list uniqueID are provided in the CSV record, the Data Load utility loads according to price list uniqueID. When there is a data mapping for price list UniqueID in the price list data load configuration file, then the uniqueID is required in the CSV file.

Name

(Required, String) The name of the price list. If both the price list name and price list uniqueID are provided in CSV record, the Data Load utility loads according to price list uniqueID.

Description

(Optional, String) The description of this price list.

Precedence

(Optional, Double) The value that determines which price list takes precedence when more than one price list is effective at a particular time. The system uses the trading position container with the highest precedence value. If this column is null from the input file, then it is set to the default value of 0.0. The value maps to the OFFER.PRECEDENCE database column.

Note: The Precedence value is not used with price rules. For price rules, to include multiple price lists within a price rule, we must use condition branches in the Catalog Filter and Pricing tool. When we use condition branches to add multiple price lists, the conditions determine the priority of the price lists. The precedence value for the price lists is ignored. If you do not use price rules and more than one price list (trading position container) can apply, the system uses the price list with the highest precedence.

Type

(Optional, String) The Trading Position Container type:

S

(Default value) Standard price list, which contains the base prices for the products in the store catalog.

C

Custom price list, which specifies the list of products and their customized prices.

E

External price list.

Values other than S, C, or E are considered custom types. The type is inserted into the table. The mediator does not restrict this value.

Mapping data

The following snippet from the sample configuration file demonstrates how to map each column of data in the source CSV file to a value. Figure 1. Mapping data with uniqueID

<_config:Data>
	<_config:column number="1" name="UniqueId" />
	<_config:column number="2" name="Name" />
	<_config:column number="3" name="Description" />
	<_config:column number="4" name="Precedence" />
	<_config:column number="5" name="Type" />
</_config:Data>

The number attribute in the <_config:column> element, defines the column order in the CSV record file. The name attribute defines the column name used in the <_config:DataMapping> element.

The following snippet maps each value to a business object logical schema path.

<_config:DataMapping>
	<_config:mapping xpath="PriceListIdentifier/UniqueID" value="UniqueId" valueFrom="InputData" />
  <_config:mapping xpath="PriceListIdentifier/ExternalIdentifier/Name" value="Name" valueFrom="InputData" />
  <_config:mapping xpath="Description/value" value="Description" valueFrom="InputData" />
  <_config:mapping xpath="precedence" value="Precedence" valueFrom="InputData" />
  <_config:mapping xpath="type" value="Type" valueFrom="InputData" />
</_config:DataMapping> 

The value attribute in the <_config:DataMapping> element must be consistent with the name attribute of the corresponding item in the <_config:column> element. Both attributes are case-sensitive. Figure 2. Mapping data without a uniqueID

<_config:DataSource>
	<_config:Data>
		<_config:column number="1" name="Name" />
		<_config:column number="2" name="Description" />
		<_config:column number="3" name="Precedence" />
		<_config:column number="4" name="Type" />
		</_config:Data>
</_config:DataSource>

The number attribute in the <_config:column> element, defines the column order in the CSV record file. The name attribute defines the column name used in the <_config:DataMapping> element.

The following snippet maps each value to a business object logical schema path.

<_config:DataMapping>
	<_config:mapping xpath="PriceListIdentifier/ExternalIdentifier/Name" value="Name" valueFrom="InputData" />
	<_config:mapping xpath="Description/value" value="Description" valueFrom="InputData" />
	<_config:mapping xpath="precedence" value="Precedence" valueFrom="InputData" />
	<_config:mapping xpath="type" value="Sequence" valueFrom="InputData" />
</_config:DataMapping>

The value attribute in the <_config:DataMapping> element must be consistent with the name attribute of the corresponding item in the <_config:column> element. Both attributes are case-sensitive.

Business object mediator

The mediator class name is com.ibm.commerce.price.dataload.mediator.PriceRuleMediator. The corresponding logical schema is PriceRule.xsd. The component ID is com.ibm.commerce.price.

Note: When we use a mediator that is provided with WebSphere Commerce with the Data Load utility, the utility assumes that we are loading data for all columns for a business object. To update the data in only specific columns, configure a column exclusion list for the load process. A column exclusion list causes the Data Load utility to ignore specific columns during the load operation. If you do not use a column exclusion list, the utility updates all columns in the row of a database table row when the utility updates the row. If no value is set in the input file, the utility can replace the existing column value with a default value or set the value to be null. See Configure a column exclusion list.