Example: Deleting price lists

The Data Load utility provides two modes for deleting price lists defined in a load file: delete and replace. Use the delete mode to delete price lists if the mode is set to "Delete" in the data load configuration file. Use the replace mode to delete price lists if the mode is set to "Replace" in the data load configuration file.

In replace mode, if the value of the "Delete" column in the load file is equal to the "deleteValue" defined in <_config_Mapping> element in the data load configuration file then the delete action is performed. You can delete multiple price lists at the same time. You can also insert price lists, replace price lists, and delete price lists at the same time.

This example uses a CSV file to demonstrate how to insert, replace, or delete your data. You 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.

Note: Do not delete the default price list for a store. If you do delete the default price list, the CATGRPTPC and STORETPC database tables must be updated with a new default price list for the store. Update these tables with the tradeposcn_ID of the new default price list. If a default price list is not set for the store, errors that are associated with the content in these tables can occur in Management Center and the storefront.

CSV file with sample price list data to be deleted

To delete a price list, you must add a delete column to your CSV file. In this example, the CSV file contains sample price list data. Some price lists are deleted, and others are inserted or replaced. The action depends on the state of the delete flag.

PriceListGroupDelete.csv

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 Delete
Standard Price List The standard price list. 1 S -
Holiday price list The price list for the holiday. 5 C y
Legacy system price list The price list that is managed by the legacy system. 2 E -
Overstock price list The price list for overstocked items. 1 C y
Behavior
  1. Delete price list with price list unique ID

    No exception is thrown in this scenario.

  2. Delete price list with price list name

    An exception is thrown if the price list does not exist in database and the error message, ID was not resolved, is displayed. The price list unique ID cannot be resolved when the price list name and unique ID are the primary key that identifies a price list.

    If the price list exists in the database, the price list is deleted successfully.

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. The uniqueID is required when there is a data mapping for price list UniqueID in the price list data load configuration 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, if you want to include multiple price lists within a price rule, you must use condition branches in the Catalog Filter and Pricing tool. When you 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.
Delete
(Optional, String) Indicates whether to delete this price list. The deleteValue, y in this example, is the string that indicates that you want to delete that price list.

Mapping data

The following snippet maps each column of data in the source CSV file to a value.

	<_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:column number="5" name="Delete" />
	</_config:Data>

The number attribute in the <_config:column> element, defines the column order in the CSV record file. The name attribute defines the name of the column that is 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="Type" valueFrom="InputData" />
	<_config:mapping xpath="" value="Delete" valueFrom="InputData" deleteValue="y" />
</_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 you use a mediator that is provided with WebSphere Commerce with the Data Load utility, the utility assumes that you are loading data for all columns for a business object. If you want 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. For more information, see Configuring a column exclusion list.

The following example shows how to configure the BusinessObjectMediator in the price list data load configuration file. The configuration is to set whether the Data Load utility marks a price list for deletion, or deletes the list from the database.

<_config:BusinessObjectMediator className="com.ibm.commerce.price.dataload.mediator.PriceListMediator"> 
<_config:property name="markPriceListForDelete" value="false" /> 
</_config:BusinessObjectMediator>
markPriceListForDelete
true
The price list is marked for delete. To delete the records, use the Database Cleanup utility.
false
The price list is deleted from the database. All related records are cascade deleted.