Example: Inserting and replacing expected inventory records

Use the Data Load utility to load expected inventory record data into the RA table and RADETAIL table with a single CSV file.

Prerequisites

Before running this example, ensure that the following prerequisites have been fulfilled:

• Make sure that the inventory system is ATP. For example, check STORE table to make sure INVENTORYSYSTEM=-1
• Catalog data, catalog group data, and catalog entry data that is used in this sample must be loaded into the database.
• ATP inventory configuration data is loaded into the database.
• The data of VendorName in the source CSV file exists in the table VENDOR of the database.

CSV file with sample data

In this example the data source is a CSV file named ra_radetail.csv. The file contains sample data of expected inventory record (RA) and expected inventory detail (RADETAIL). Each column is delimited by a comma.

CSV file with sample data

CSV file column definitions

VendorName

(Required, String) The vendor that supplied the inventory. This field is required and cannot be null.

CreateTime

(Required, Timestamp) The date and time this record is created. The format of the input is "yyyy-mm-dd hh:mm:ss.SSSSSS". the length of the fractional seconds must be no more than 6. The format can be customized by setting timestampPattern property in wc-dataload.xml. This field is part of the unique index of RA table. This field is required and cannot be null.

OrderDate

(Required, Timestamp) The date and time this order was created with the vendor. The format is same as specified in CreateTime. This field is required and cannot be null.

DateClosed

(Timestamp) The date and time this record was closed. The format is same as specified in CreateTime.

OpenIndicator

(Boolean) If set to true, the expected inventory might still be received and allocated to back orders. If set to false, no further inventory is received. Expected inventory might not be allocated to back orders.

ExternalId

(String) The external identifier. It is supplied by the vendor and can be used to reference this record.

PartNumber

(Required, string) The part number of this item. It uniquely identifies an item for a particular owner. Together with the owner identifier, it is used to retrieve the primary key of ITEMSPC table, such as ITEMSPC_ID. This field is required and cannot be null if the CatalogEntryUniqueId field is not specified in CSV file.

ExpectedDate

(Timestamp) The date this inventory is expected to be received. The format is same as specified in RACreateTime field.

Quantity

(Integer) Quantity that is ordered from vendor in this expected inventory record.

RADetailComment

(String) The comments. The length of the comment must be no more than 254.

CatEntryStoreIdentifier

(String) This field 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.

This field is required if you are using an extended sites store.

Other optional fields not included in the example are:

StoreIdentifier

(String) The identifier of the store that this item belongs to, such as ConsumerDirect. This column can be added into CSV if the item belongs to a store that is different from the store that is defined in BusinessContext elements of wc-dataload-env.xml. If this column is not provided in the CSV file, the StoreIdentifier in the business context is used.

FulfillmentCenterId

(String) The identifier of the fulfillment center that this item belongs to. This column can be added into CSV if the item belongs to a fulfillment center, which is different from the one defined in BusinessContext property of wc-dataload-env.xml. If this column is not provided in the CSV file, the fulfillmentCenterIdentifier is retrieved by FulfillmentCenterName. If it is not found, the fulfillmentCenterIdentifier in the business context is used. If it is not specified in either CSV file or business context, the default fufillmentCenter of the specified store is used.

FulfillmentCenterName

(String) The name of the fulfillment center that this item belongs to, such as ConsumerDirect Home. This column is used to retrieve fulfillmentCenterIdentifier.

OwnerId

(Long) The identifier of the catalog entryOwner of this item. If it is not specified in the CSV file, the owner of the catalog entry asset store is retrieved. If it is not found, the owner of current store is retrieved. If it is not found either, the ownerId specified in BusinessContext property of wc-dataload-env.xml is used.

CatalogEntryUniqueId

(Long) The identifier of the catalog entry of this item. If this field is not specified in CSV file, the identifier of catalog entry is retrieved by part number and owner of the catalog entry.

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="ConsumerDirect">
<_config:property name="fulfillmentCenterName" value="ConsumerDirect Home"/>
<_config:property name="ownerId" value="7000000000000000102"/>
</_config:BusinessContext>

FulfillmentCenterId

(String) The identifier of the fulfillment center this item belongs to, for example "ConsumerDirect Home". It is used if the identifier cannot be retrieved by FulfillmentCenterId or FulfillmentCenterName specified in CSV file.

FulfillmentCenterName

(String) The name of the fulfillment center this item belongs to, for example "ConsumerDirect Home". It is used to retrieve the fulfillmentCenterID, if fulfillmentCenterID is not retrieved from CSV file or defined in business context.

OwnerId

(Long) The identifier of the owner of this item. It is used if the item owner is not provided in CSV file and cannot be retrieved by specified store identifier.

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.

<_config:DataReader className="com.ibm.commerce.foundation.dataload.datareader.CSVReader" firstLineIsHeader="true">
	<_config:Data>
		<_config:column number="1" name="VendorName" />
		<_config:column number="2" name="CreateTime" />
		<_config:column number="3" name="OrderDate" />
		<_config:column number="4" name="ClosedDate" />
		<_config:column number="5" name="OpenIndicator" />
		<_config:column number="6" name="ExternalId" /> 
		<_config:column number="7" name="PartNumber" />
		<_config:column number="8" name="ExpectedDate" />
		<_config:column number="9" name="Quantity" />
		<_config:column number="10" name="RADetailComment" /> 
		<_config:column number="11" 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 above. Each column in the CSV file must have a mapping to the logical schema path. If the optional fields specified above are present in the CSV file, the mapping for them must be added. The mapping logical schema path is specified in the following table.

<_config:DataMapping>
	<_config:mapping xpath="ExpectedInventoryRecordIdentifier/ExternalIdentifier/VendorName" value="VendorName" valueFrom="InputData" /> 
	<_config:mapping xpath="OrderDate" value="OrderDate" valueFrom="InputData" />
	<_config:mapping xpath="ExpectedInventoryRecordIdentifier/ExternalIdentifier/CreateDate" value="CreateTime" valueFrom="InputData" />
	<_config:mapping xpath="CloseDate" value="ClosedDate" valueFrom="InputData" />
	<_config:mapping xpath="OpenIndicator" value="OpenIndicator" valueFrom="InputData" />
	<_config:mapping xpath="ExternalId" value="ExternalId" valueFrom="InputData" />
	<_config:mapping xpath="ExpectedInventoryDetail[0]/ExpectedInventoryDetailIdentifier/ExternalIdentifier/CatalogEntryIdentifier/ExternalIdentifier/PartNumber" value="PartNumber" valueFrom="InputData" />
	<_config:mapping xpath="ExpectedInventoryDetail[0]/ExpectedInventoryDetailIdentifier/ExternalIdentifier/ExpectedDate" value="ExpectedDate" valueFrom="InputData" />
	<_config:mapping xpath="ExpectedInventoryDetail[0]/QuantityOrdered/value" value="Quantity" valueFrom="InputData" />
	<_config:mapping xpath="ExpectedInventoryDetail[0]/QuantityRemaining/value" value="Quantity" valueFrom="InputData" />
	<_config:mapping xpath="ExpectedInventoryDetail[0]/Comment" value="RADetailComment" valueFrom="InputData" />
	<_config:mapping xpath="InventoryReceiptIdentifier/ExternalIdentifier/CatalogEntryIdentifier/ExternalIdentifier/StoreIdentifier/ExternalIdentifier/NameIdentifier" value="CatEntryStoreIdentifier" />
</_config:DataMapping>

Business object mediator

The mediator class name is com.ibm.commerce.inventory.dataload.mediator.ExpectedInventoryMediator. The corresponding logical schema is ExpectedInventoryRecord.xsd.