Example: Insert and replace ATP inventory

We can insert and replace your ATP inventory using the Data Load utility. This example uses a CSV file to demonstrate how to insert and replace your ATP inventory information.

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.

Use the Data Load utility to load ATP inventory configuration data into the database. The data can be inserted or replaced in BASEITEM, BASEITMDSC, STOREITEM, DISTARRANG, ITEMVERSN, ITEMSPC, VERSIONSPC table.

The ATP inventory configuration data are typically loaded together with catalog entry data in a single CSV file for initial loading, because each ATP inventory configuration data is related to a catalog entry record in the database. In this case, CatalogEntryMediator is used to load data.

In another case, catalog entry data are initially loaded by CatalogEntryMediator without ATP inventory configuration data, so default ATP inventory configuration data are loaded in to database. If user wants to modify the ATP inventory configuration data after that, FulfillmentPropertyMediator can be used to load only ATP inventory configuration data without impact to existing catalog entry data.

Prerequisites

Before you run this example, ensure that the inventory system is ATP. For example, check STORE table to make sure INVENTORYSYSTEM=-1

CSV file with sample ATP inventory configuration data

In this example, the data source is a CSV file named ATPconfig.csv. The file contains sample ATP inventory configuration data. Each column is delimited by a comma.

CSV file with sample data

PartNumber	ParentPartNumber	Type	INVQuantityMeasure	INVQuantityMultiple	TrackingInventory	BackOrderable	ReleaseSeparately	Creditable	ForceBackorder	ReturnNotDesired	MinQTYForSplit	PickingMethod	Discontinued	CatEntryStoreIdentifier
Example-PN-10001		Product	C62	2	N	N	Y	N	Y	Y	10	L	Y	10701
Example-PN-10002	Example-PN-10001	Item	C62	2	N	N	Y	N	Y	Y	10	L	Y

Note: If the catalog entry data is not loaded to the database, the ATP inventory configuration data must be loaded together with catalog entry data in a single source CSV file, in such case, this table is part of the CSV file for the catalog entry data load. If the catalog entry data existed in database, ATP inventory configuration data in above table can be loaded separately.

CSV column and XML element definitions

PartNumber

(Required, string) The reference number that identifies the part number of the catalog entry. It uniquely identifies a catalog entry for a particular owner. This field is required and cannot be null.

ParentPartNumber

(String) The part number of a parent product of this item. It is used to retrieve BASEITEM_ID in BASEITEM table, together with the owner id of this item.

Type

(Required, string) This field defines the type of catalog entry. The values for Type are "Item", "Product", "Bundle", and "Package". This field is required and cannot be null.

INVQuantityMeasure

(String) The unit of measure for QUANTITYMULTIPLE. For example, to represent one quarter of an inch, QUANTITYMULTIPLE would be 0.25, and QUANTITYMEASURE would indicate the QTYUNIT that represents inches (normally INH). Default value 'C62' is used if it is empty in the CSV file.

INVQuantityMultiple

(Double) Amounts of this BaseItem are measured in integral units. QUANTITYMULTIPLE, along with QUANTITYMEASURE, indicates how much each integral unit represents. For example, textiles might be measured in integral units each representing one quarter of an inch. Default value 1.0 is used if it is empty in the CSV file.

TrackingInventory

(String) Indicates whether the inventory is tracked. The value can be:

• N = Inventory is not tracked.

• Y = Inventory is tracked.

• E = Inventory is tracked externally.

If you do not specify this value, the default value is Y.

BackOrderable

(String) Indicates whether the item can be back ordered. The value can be:

• N = Items cannot be backordered.

• Y = Items can be backordered.

If you do not specify this value, the default value is Y.

ReleaseSeparately

(String) Indicates whether the order item can be released separately. The value can be:

• N = Order Items can be released along with other Order Items.

• Y = Order Items must be released separately (in their own boxes).

If you do not specify this value, the default value is N.

Creditable

(String) Indicates whether the merchant, without an override, can issue a credit for this item. This value can be:

• N = Sold as-is.

• Y = Creditable.

The requirement for the return of merchandise is evaluated separately. If you do not specify this value, the default value is Y.

ForceBackorder

(String) Indicates whether the inventory can be allocated. Can be used to temporarily suspend allocation of specified items for this Base Item. The value can be:

• N = Inventory can be allocated (typical behavior).

• Y = Inventory cannot be allocated, even if there is enough inventory.

If you do not specify this value, the default value is N.

ReturnNotDesired

(String) Indicates whether the return of the item is wanted, even if the customer is willing or able to return the item. For example, perishable food items. The value can be:

• N = Request for credit is evaluated based on the intention of the customer to return the item. The customer is required to return the merchandise.

• Y = Request for credit is evaluated as if a return is expected. The customer is not required to return the merchandise.

If you do not specify this value, the default value is N.

MinQTYForSplit

(Integer) The minimum deallocated quantity that must remain for splitting order items in inventory allocation. If you do not specify this value, the default value is 0.

PickingMethod

(String) Determines the method in which inventory is picked under this arrangement. The value can be:

• F = FIFO (First In First Out) - the least recently received inventory.

• L = LIFO (Last in First Out) - the most recently received inventory.

Discontinued

(String) Indicates whether the item specified is discontinued. The value can be:

• Y = The item is discontinued. It can be ordered if there is sufficient inventory but it cannot be backordered.

• N = The item is active and can be backordered if out of stock.

CatEntryStoreIdentifier

(String) This identifier is used in an extended sites store. If the CatalogEntry is defined in a catalog entry asset store, this field stands for the identifier of the catalog entry asset store. This identifier is used to retrieve the catalog entryOwner.

(Enterprise) This field is required if we are using an extended sites store.

Other optional fields not included in the example:

CatalogEntryUniqueId

(Long) The identifier of the catalog entry. It is used to retrieve a record in CATENTRY table, then BASEITEM_ID, ITEMSPC_ID, and CATENTTYPE_ID can be got from the record. If this field is not specified in CSV file, PartNumber is used to retrieve these IDs.

ParentCatalogEntryUniqueId

(Long) The identifier of the parent catalog entry. It is used to retrieve the BASEITEM_ID corresponding to the parent catalog entry. If this field is not specified in CSV file, BASEITEM_ID is retrieved by ParentPartNumber.

Delete

(String) Indicates whether to delete this record. This field takes effect when dataLoadMode is "replace". If the value of this field equals to deleteValue, the corresponding record is deleted. The default deleteValue is 1, and the deleteValue can be configured under the <DataMapping> element in the BusinessObjectConfigFile file.

Business context data

The following code snippet from the wc-dataload-env.xml configuration file provides the business context data necessary for loading the data:

<_config:BusinessContext storeIdentifier="AuroraESite" langId="-1" >
</_config:BusinessContext>

storeIdentifier

(String) The identifier of the store this catalog entry belongs to.

langId

(Int) The identifier of a language, which is used to load data to BASEITEMDSC. Default value is "-1".

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. If the ATP inventory configuration data is loaded together with catalog entry data in a single source CSV file, the following mapping must be incorporated into the configuration file. If only ATP inventory configuration data is loaded, the following mapping information must be defined in an individual configuration file.

<_config:DataReader className="com.ibm.commerce.foundation.dataload.datareader.CSVReader" firstLineIsHeader="true">
               <_config:Data>
             <_config:column number="1" name="PartNumber" />
                     <_config:column number="2" name="ParentPartNumber" />
                     <_config:column number="3" name="Type" />
                     <_config:column number="4" name="INVQuantityMeasure" />
                     <_config:column number="5" name="INVQuantityMultiple" />
                     <_config:column number="6" name="TrackingInventory" />
                     <_config:column number="7" name="BackOrderable" />
                     <_config:column number="8" name="ReleaseSeparately" />
                     <_config:column number="9" name="Creditable" />
                     <_config:column number="10" name="ForceBackorder" />
                     <_config:column number="11" name="ReturnNotDesired" />
                     <_config:column number="12" name="MinQTYForSplit" />
                     <_config:column number="13" name="PickingMethod" />
                     <_config:column number="14" name="Discontinued" />
                     <_config:column number="15" name="CatEntryStoreIdentifier" />
               </_config:Data>
</_config:DataReader >

The following snippet from the sample configuration file demonstrates how to map each column of the data in the CSV file to a business object logical schema path. The attribute 'value' represents the name of a column of the CSV file, which is defined in the configuration snippet. Each column in the CSV file must have a mapping to the logical schema path.

<_config:DataMapping>
       <_config:mapping xpath="CatalogEntryIdentifier/ExternalIdentifier/PartNumber" value="PartNumber" valueFrom="InputData"/>
       <_config:mapping xpath="ParentCatalogEntryIdentifier/ExternalIdentifier/PartNumber" value="ParentPartNumber" valueFrom="InputData"/>
       <_config:mapping xpath="catalogEntryTypeCode" value="Type" valueFrom="InputData"/>       
       <_config:mapping xpath='FulfillmentProperties/UserData/UserDataField[0]/quantityMeasure' value="INVQuantityMeasure" valueFrom="InputData"/>
       <_config:mapping xpath='FulfillmentProperties/UserData/UserDataField[0]/quantityMultiple' value="INVQuantityMultiple" valueFrom="InputData"/>
       <_config:mapping xpath='FulfillmentProperties/UserData/UserDataField[0]/trackingInventory' value="TrackingInventory" valueFrom="InputData"/>
       <_config:mapping xpath='FulfillmentProperties/UserData/UserDataField[0]/backOrderable' value="BackOrderable" valueFrom="InputData"/>
       <_config:mapping xpath='FulfillmentProperties/UserData/UserDataField[0]/releaseSeparately' value="ReleaseSeparately" valueFrom="InputData"/>
       <_config:mapping xpath='FulfillmentProperties/UserData/UserDataField[0]/creditable' value="Creditable" valueFrom="InputData"/>
       <_config:mapping xpath='FulfillmentProperties/UserData/UserDataField[0]/forceBackorder' value="ForceBackorder" valueFrom="InputData"/>
       <_config:mapping xpath='FulfillmentProperties/UserData/UserDataField[0]/returnNotDesired' value="ReturnNotDesired" valueFrom="InputData"/>
       <_config:mapping xpath='FulfillmentProperties/UserData/UserDataField[0]/minQTYForSplit' value="MinQTYForSplit" valueFrom="InputData"/>
       <_config:mapping xpath='FulfillmentProperties/UserData/UserDataField[0]/pickingMethod' value="PickingMethod" valueFrom="InputData"/>
       <_config:mapping xpath='FulfillmentProperties/UserData/UserDataField[0]/discontinued' value="Discontinued" valueFrom="InputData"/>
			<_config:mapping xpath="InventoryReceiptIdentifier/ExternalIdentifier/CatalogEntryIdentifier/ExternalIdentifier/StoreIdentifier/ExternalIdentifier/NameIdentifier" value="CatEntryStoreIdentifier" />
  
</_config:DataMapping>

Business object mediator

If the ATP inventory configuration data in this sample are loaded together with catalog entry data, the mediator class name is com.ibm.commerce.catalog.dataload.mediator.CatalogEntryMediator. If the ATP inventory configuration data are loaded separately, the mediator class name is com.ibm.commerce.catalog.dataload.mediator.FulfillmentPropertyMediator. The corresponding logical schema is CatalogEntry.xsd.

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.