Samples > Data load utility samples > Examples: Mapping data > Examples: Mapping inventory data
Example: Inserting and replacing ATP inventory
Use the data load utility to load ATP inventory configuration data into the database. The data may 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 may be used to load only ATP inventory configuration data without impact to existing catalog entry data.
Prerequisites
Before running this example, ensure the inventory system is ATP, i.e. 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.
PartNumber ParentPartNumber Type INVQuantityMeasure INVQuantityMultiple TrackInventory BackOrderable ReleaseSeparately Creditable ForceBackorder ReturnNotDesired MinQTYForSplit PickingMethod Discontinued Example-PN-10001 Product C62 2 N N Y N Y Y 10 L Y Example-PN-10002 Example-PN-10001 Item C62 2 N N Y N Y Y 10 L Y
If the catalog entry data haven't been loaded to database, the ATP inventory configuration data should 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 have already existed in database, ATP inventory configuration data in above table may be loaded separately.
CSV file column definitions
- PartNumber
- (Mandatory, 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 mandatory 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
- (Mandatory, string) This field defines the type of catalog entry. The values for Type are "Item", "Product", "Bundle", and "Package". This field is mandatory 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' will be 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 will be used if it is empty in the CSV file.
- TrackInventory
- (String) Controls whether or not inventory is tracked in the RECEIPT table. N = Inventory is not tracked (there are no entries in the RECEIPT table). Y = Inventory is tracked in the RECEIPT table. E = Inventory is tracked externally. Default value 'Y' will be used if it is empty in the CSV file.
- BackOrderable
- (String) This field is used to indicate if the specified items for this Base Item can be backordered. N = Items may not be backordered. Y = Items may be backordered. Default value 'Y' will be used if it is empty in the CSV file.
- ReleaseSeparately
- (String) Controls how Order Items for specified items for this Base Item are released. N = Order Items may be released along with other Order Items. Y = Order Items must be released separately (in their own boxes). Default value 'N' will be used if it is empty in the CSV file.
- Creditable
- (String) Whether the merchant will, without an override, issue a credit for this item. N = Sold as-is. Y = Creditable. The requirement for the return of merchandise is evaluated separately. Default value 'Y' will be used if it is empty in the CSV file.
- ForceBackorder
- (String) Can be used to temporarily suspend allocation of specified items for this Base Item. N = Inventory can be allocated (typical behavior). Y = Inventory cannot be allocated, even if there is enough inventory. Default value 'N' will be used if it is empty in the CSV file.
- ReturnNotDesired
- (String) NULL DEFAULT 'N' Item return not wanted, even if customer is willing or able to return it. For example, perishable food items. N = Request for credit evaluated based on the customers intention to return the item, and customer is required to return merchandise. Y = Request for credit evaluated as if return is expected, but customer is not required to return merchandise. Default value 'N' will be used if it is empty in the CSV file.
- MinQTYForSplit
- (Integer) Used by the default AllocateInventory task command implementation. Order Items will not be automatically split during inventory allocation if the remaining unallocated quantity would be less than the specified minimum quantity. Default value '0' will be used if it is empty in the CSV file.
- PickingMethod
- (String) Determines the sequence in which inventory is picked from the RECEIPT table under this arrangement: F = FIFO (First In First Out) - the least recently received inventory. L = LIFO (Last in First Out) - the most recently received inventory.
- Discontinued
- (String) Y = This item specification has been discontinued. It can be ordered if there is sufficient inventory but it cannot be backordered. N = This item specification is active and may be backordered if out of stock. Default value 'N' will be used if it is empty in the CSV file.
Other optional fields not included in the example are:
- 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 will be 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 will be deleted. The default deleteValue is 1, deleteValue may be configured under <DataMapping> element in BusinessObjectConfigFile.
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" langId="-1" > </_config:BusinessContext>
- storeIdentifier
- (String) The identifier of the store this catalog entry belongs to, such as ConsumerDirect.
- langId
- (Int) The identifier of a language which is used to load data to BASEITEMDSC. Default value is "-1".
Map 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 will be loaded together with catalog entry data in a single source CSV file, the following mapping needs to be incorporated into the configuration file for catalog entry data load. If only ATP inventory configuration data will be loaded, the following mapping information should be define 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: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.
<_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: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.