Guide - IOM Product Import 2.2

1 Introduction

This guide is addressed to partners and clients who use Intershop Order Management (IOM) in their system. It demonstrates how products are to be imported into the IOM. Basic knowledge of CSV and SQL is a prerequisite.

1.1 Glossary

TermDescription
CSVComma-separated values file; stores tabular values in plain text files
DOData object - used as persistence entities to represent stored values in database
EAN

International Article Number (originally, European Article Number)

GUIGraphical User Interface
IOMThe abbreviation for Intershop Order Management
ISBNInternational Standard Book Number
ISOInternational Organization for Standardization
NoNumber
UKThe United Kingdom of Great Britain and Northern Ireland
ZIPArchive file format that supports lossless data compression. It contains one or more files or directories that may have been compressed.

2 Prerequisites

As the product(s) are imported for a particular supplier-shop combination with specific properties (explained in detail in section CSV/SQL configurations) such as currency, language, country, and tax details, it is very important that the supplier and the shop are already properly configured. However, this configuration is out-of-scope of this documentation.

3 Concept

Whilst an order is sent to IOM from a shop, it is validated on the basis of pre-configured rules and business processes. One of the very important rules is that the ordered product is known to IOM, i.e., it exists in its database:

  • For the particular shop from where the order is sent - and -
  • One or more suppliers are able to deliver the ordered/reserved product.
    A product should also be configured for shop and supplier combinations, i.e., a shop might be connected or mapped to many suppliers. For example, supplier A delivers the electronic items to the shop, whilst supplier B is responsible for delivering cosmetic and fashion accessories.

Note

Before importing products, there are many things that need to be configured, such as shop, supplier, invoicing processes, payment methods, etc.

Although a product can be imported into IOM using many shop-supplier-combinations, in this document only the standard approach is addressed.

Note

The steps must be repeated for each supplier that delivers products to the customer on behalf of a shop.

  1. IOM is informed about the essential and basic non-supplier-specific data of a product, such as product name, manufacturer name, manufacturer product ID, weight, length, etc.
    It is also mentioned that this product is currently being imported for a particular supplier-shop combination.
  2. IOM is informed about a product ID mapping for shop and supplier, i.e., how the supplier product ID is mapped to the shop product ID.
  3. IOM is informed about the supplier-shop-specific information.
    For instance, the very product is offered by many suppliers with different prices, currencies, availability information, etc.

Note

The above-mentioned details are imported as CSV files into IOM using a pre-configured directory, whose default value is /opt/oms/importarticle/in. However, this can be modified using the property is.oms.dir.var in $JBOSS_HOME/standalone/config/standalone.xml.

4 Description ImportConfigurationDO

The following table illustrates the columns of the table product."ImportConfigurationDO", which will be used in the following sub-sections:

Number #Column Name (Case-Sensitive)TypeDescriptionExample
1idLong (bigint) NOT NULLUnique ID for the new entry. Usually, SELECT nextval('"ImportConfigurationDO_id_seq"') is used.1
2availabilityStoredProcedureCharacter (255)

Applicable only to dynamic data. Name of procedure to be called during price and availability mapping.

For example, a stored procedure exists to adjust negative stock counts to zero.

For example, a stored procedure exists that does nothing, i.e., simply import what is provided in the CSV file.

process_availability_mapping_do_nothing
4copyStoredProcedure <optional>Character (255)Stored procedure used for copying filesnull
5csvDelimiterCharacter (255)Which delimiter is used in the CSV file for column separation| (Pipe)
6csvHeaderCharacter (1000)Which headers (columns) will be provided in the CSV file
supplierArticleNo|bakeryArticleNo|shopArticleNo
13priceComparisonProviderDefRef <optional>IntegerReference to price search provider, e.g., Geizhals Deutschland. Refer "PriceComparisonProviderDefDO"null
14storedProcedureGroupCharacter (255)Stored procedure under the schema 'product' used for processing the information of the placed CSV fileimport_standard_abcg_v10
import_standard_abcg_v10
15supplierRefLong (bigint)Reference to the supplier. Refer to the table oms."SupplierDO".20000
16tableNameGroupCharacter (255)Name of the temporary table used for importImport_Standard_ShopArticleNo_V4
18checkMissingInLastDatapackBoolean

Applicable for full product import.

True: In the consequent imports, provide details for the products which have updates.

False: In the consequent imports, do not provide details for the discontinued products.
I.e., even if no updates exist, still all the products' details have to be imported.

true
19importDatapackFileTypeDefRefInteger

Defines the purpose of the CSV file. Refer to "ImportDatapackFileTypeDefDO" table.

  • 1 means, basic data import
  • 39 means, mapping of supplier product number and shop product number
  • 13 means, mapping of price and availability (i.e., dynamic data)
82
20canDatapackBeEmptyBoolean (NOT NULL)Whether data pack can be emptytrue
21parentImportConfigurationRefLong (bigint)Refers to the parent configuration of product."ImportConfigurationDO". Important for zip-imports.1
22convertToCSVBooleanIf the provided file has to be converted into internal CSV file formatfalse
23createContentAssignmentBooleanWhether the provided supplier product number should be saved to Article2ContentSupplierDOfalse
24internalMediaPath <optional>Character (250)Path for product images (currently, not in use) 
25splitCountIntegerNumber of asynchronous processes2
26variationProcedure <optional>Character (250)Name of the procedure responsible for version recognitionnull
27identifyOnBakeryArticleRefBoolean (default: FALSE)Whether the IOM internal product number should be used during import (used for dynamic data)false
28supplementArticleParentBooleanGenerate missing parent product (used for dynamic data)false
29assortmentSupplierLong (bigint)Reference to the supplier. Refers to the table oms."SupplierDO".20000
30updateArticleTypeBoolean (NOT NULL - default FALSE)During basic data import, do not verify product typefalse
31importBeanDefRefIntegerBean used for processing import. Refers to the table "ImportConfigurationBeanDefDO"2
32createSupplierAssortmentSystemBoolean (NOT NULL - default FALSE)

Whether to refer to internal commodity group of supplier, but refer to the existing one (create if non-existent).
Only applicable to basic data.

false
33createManufacturerBoolean (default FALSE)Create the manufacturer if not already knownfalse
34financeControllerRef <optional>IntegerReference to external finance controller reference for a product
(bookkeeping service to manage credits/debits of system)
null
35changeIdentificationOfArticleBoolean

 For Basic Data (*_A.csv), for pre-existing articles identified by their "supplier" - "supplier-product-number":
  When true, additional article identifiers like the "ean" and "isbn" will get updated with th possible new values from the import file.

For the article ID Import (*_N.csv):  allow to modfiy the article mapping  (see 5.1.3)

false
36bonusPartnerRef <optional>IntegerReference to bonus point partner (e.g., Payback)null

Before placing the CSV files, IOM has to be informed about which combinations are imported for the supplier-shop configuration. It is also specified which columns will be made available for each combination.

Note

Refer to section General Configuration of the document Guide - Generic Interfaces for External Accounting Systems to know where to execute the following SQL queries.

5 Import Zip Version

Note

The name of the file should conform to <ShopID>_<SupplierID>_<Date>_ABCGN.zip

where,

  • <ShopID> is ID of the shop from the table oms."ShopDO"
  • <SupplierID> is ID of the supplier from the table oms."SupplierDO"
  • <Date> is YYYYMMDDHHMMSS

The zip must contain the following CSV files.

  • <ShopID>_<SupplierID>_<Date>_A.csv
  • <ShopID>_<SupplierID>_<Date>_BCG.csv
  • <ShopID>_<SupplierID>_<Date>_N.csv

Example Name: 10000_10002_20160224165437_ABCGN.zip

  • 10000_10002_20160224165437_A.csv
  • 10000_10002_20160224165437_BCG.csv
  • 10000_10002_20160224165437_N.csv

5.1 SQL Configuration

5.1.1 Parent Import Configuration

To be able to import products, a certain structure of import data packs needs to be agreed between the supplier, the shop and the IOM.

The parent import configuration provides the child configuration (i.e., the following configurations) and mainly specifies the Java class to use, which extracts the ZIP files and processes the contained data files.

The SQL procedure given below is an example. However, most of its values have to be specified exactly as given below. Exceptions are the columns "supplierRef" and all Boolean-columns, which can be set according to your project´s needs.

The columns "financeControllerRef" and "bonusPartnerRef" can be ignored.

INSERT INTO product."ImportConfigurationDO"(
	id,
	"availabilityStoredProcedure",
	"copyStoredProcedure",
	"csvDelimiter",
	"csvHeader",
	"priceComparisonProviderDefRef",
	"storedProcedureGroup",
	"supplierRef",
	"tableNameGroup",
	"checkMissingInLastDatapack",
	"importDatapackFileTypeDefRef",
	"canDatapackBeEmpty",
	"parentImportConfigurationRef",
	"convertToCSV",
	"createContentAssignment",
	"internalMediaPath",
	"splitCount",
	"variationProcedure",
	"identifyOnBakeryArticleRef",
	"supplementArticleParent",
	"assortmentSupplier",
	"updateArticleType",
	"importBeanDefRef",
	"createSupplierAssortmentSystem",
	"createManufacturer",
	"financeControllerRef",
	"changeIdentificationOfArticle",
	"bonusPartnerRef")
	SELECT
	1,
	'process_availability_mapping_do_nothing' ,
	'',
	'''|''',
	'',
	null,
	'import_standard_abcg_v10',
	sup.id,
	'',
	FALSE,
	NULL,
	TRUE,
	null,
	TRUE,
	FALSE,
	'',
	2,
	'',
	FALSE,
	TRUE,
	20000,
	FALSE,
	82,
	FALSE,
	TRUE,
	null,
	null,
	null
from oms."SupplierDO" sup where "internalSupplierName" IN ('simwarehouse_north');

5.1.2 Basic Data

Basic data are used to create the products in the IOM DB. They can contain both, non-supplier specific and supplier specific product information. They are delivered in one specific CSV file named <ShopID>_<SupplierID>_<Date>_A.csv.

The SQL below creates an import configuration entry to import the basic data.

The SQL procedure given below is an example. However, most of its values have to be specified exactly as given below. Exceptions are the columns "supplierRef" and all Boolean-columns, which can be set according to your project´s needs.

The columns "financeControllerRef" and "bonusPartnerRef" can be ignored.

 INSERT INTO product."ImportConfigurationDO"(
	id,
	"availabilityStoredProcedure",
	"copyStoredProcedure",
	"csvDelimiter",
	"csvHeader",
	"priceComparisonProviderDefRef",
	"storedProcedureGroup",
	"supplierRef",
	"tableNameGroup",
	"checkMissingInLastDatapack",
	"importDatapackFileTypeDefRef",
	"canDatapackBeEmpty",
	"parentImportConfigurationRef",
	"convertToCSV",
	"createContentAssignment",
	"internalMediaPath",
	"splitCount",
	"variationProcedure",
	"identifyOnBakeryArticleRef",
	"supplementArticleParent",
	"assortmentSupplier",
	"updateArticleType",
	"importBeanDefRef",
	"createSupplierAssortmentSystem",
	"createManufacturer",
	"financeControllerRef",
	"changeIdentificationOfArticle",
	"bonusPartnerRef")
	SELECT
	A_FILE,
	null ,
	'',
	'''|''',
	'supplierArticleNo|manufacturer|manufacturerArticleNo|ISBN|EAN|articleName|length|height|width|weight|assortmentName|assortmentIdentifier|deliveryForm|customsTariffNo|parentSupplierArticleNo|articleForm|immaterialUid|articleLanguage|supplierSalesCode|supplierArticleIdentifier|articleType|edition|packagingUnit|packagingUnitValue',
	null,
	'',
	sup.id,
	'Import_Standard_Basic_V10',
	FALSE,
	1,
	TRUE,
	1,
	FALSE,
	FALSE,
	'',
	2,
	'',
	FALSE,
	TRUE,
	20000,
	FALSE,
	null,
	FALSE,
	TRUE,
	null,
	null,
	null
from oms."SupplierDO" sup where "internalSupplierName" IN ('simwarehouse_north');

5.1.3 Mapping of Supplier-Shop Product ID

In this import file, the "bakeryArticleNo" corresponds to the internal article id by the IOM.

The SQL below creates import configuration entries for a list of supplier to import the mapping of supplier product IDs and shop product IDs.

This SQL is an example. However, most of its values have to be specified exactly as given. Exceptions are the columns "supplierRef" and all Boolean-columns, which can be set according to your project´s needs.

The columns "financeControllerRef" and "bonusPartnerRef" can be ignored.

boolean changeIdentificationOfArticle:

(When the import file contains values for the bakeryArticleNo )
To ensure consistency, the mapping "bakeryArticleNo" - "shopArticleNo" must be unique per shop.
Changing this mapping afterwards may result in severe business problems.
e.g.: shop article references appearing in order confirmations and bills will not match the articles visible within the shop anymore. Or worse: the article reference may change on documents generated for a given order at different time

To prevent such situations, a modification of this mapping is disallowed per default, but you can allow it while setting "changeIdentificationOfArticle" to true and "splitcount" to 1.
There is still one limitation: the key pair "bakeryArticleNo" - "shopArticleNo" must still be unique once the whole file has been processed.
(setting "splitcount" to 1 is required  as the uniqueness check is performed on a transaction basis, and could hence fail when processing the import in multiple chunks)

To help with troubleshooting, such mapping changes are logged internally within the postgres table product."Article2ShopDO_update_log".

 

INSERT INTO product."ImportConfigurationDO"(
	id,
	"availabilityStoredProcedure",
	"copyStoredProcedure",
	"csvDelimiter",
	"csvHeader",
	"priceComparisonProviderDefRef",
	"storedProcedureGroup",
	"supplierRef",
	"tableNameGroup",
	"checkMissingInLastDatapack",
	"importDatapackFileTypeDefRef",
	"canDatapackBeEmpty",
	"parentImportConfigurationRef",
	"convertToCSV",
	"createContentAssignment",
	"internalMediaPath",
	"splitCount",
	"variationProcedure",
	"identifyOnBakeryArticleRef",
	"supplementArticleParent",
	"assortmentSupplier",
	"updateArticleType",
	"importBeanDefRef",
	"createSupplierAssortmentSystem",
	"createManufacturer",
	"financeControllerRef",
	"changeIdentificationOfArticle",
	"bonusPartnerRef")
	SELECT
	N_FILE, 
	null ,
	'',
	'''|''',
	'supplierArticleNo|bakeryArticleNo|shopArticleNo',
	null,
	'import_standard_n_v4',
	sup.id,
	'Import_Standard_ShopArticleNo_V4',
	FALSE,
	39,
	FALSE,
	null,
	FALSE,
	FALSE,
	null,
	3,
	null,
	FALSE,
	FALSE,
	1,
	FALSE,
	null,
	FALSE,
	FALSE,
	null,
	null,
	null
from oms."SupplierDO" sup where "internalSupplierName" IN ('simwarehouse_north');

5.1.4 Dynamic Data

The SQL below creates an import configuration entry to import the dynamic product data (e.g., prices, availability, etc.). The dynamic data are supplier-shop specific.

The SQL procedure given below is an example. However, most of its values have to be specified exactly as given below. Exceptions are the columns "supplierRef" and all Boolean-columns, which can be set according to your project´s needs.

The columns "financeControllerRef" and "bonusPartnerRef" can be ignored.

 INSERT INTO product."ImportConfigurationDO"(
	id,
	"availabilityStoredProcedure",
	"copyStoredProcedure",
	"csvDelimiter",
	"csvHeader",
	"priceComparisonProviderDefRef",
	"storedProcedureGroup",
	"supplierRef",
	"tableNameGroup",
	"checkMissingInLastDatapack",
	"importDatapackFileTypeDefRef",
	"canDatapackBeEmpty",
	"parentImportConfigurationRef",
	"convertToCSV",
	"createContentAssignment",
	"internalMediaPath",
	"splitCount",
	"variationProcedure",
	"identifyOnBakeryArticleRef",
	"supplementArticleParent",
	"assortmentSupplier",
	"updateArticleType",
	"importBeanDefRef",
	"createSupplierAssortmentSystem",
	"createManufacturer",
	"financeControllerRef",
	"changeIdentificationOfArticle",
	"bonusPartnerRef")
	SELECT
	BCG_FILE,
	'process_availability_mapping_do_nothing',
	'',
	'''|''',
	'supplierArticleNo|currency|purchasePrice|listPrice|stockLevel|stockType|availabilityInDays|salesPrice|basicPrice|basicPriceUnitValue|basicPriceUnit|salesPriceOld|provisionType|provisionPercentage|taxType|isBestseller',
	null,
	'import_standard_bcg_v3',
	sup.id,
	'Import_Standard_Dynamic_V7',
	TRUE,
	13,
	TRUE,
	PARENTID,
	FALSE,
	FALSE,
	'',
	6,
	'',
	FALSE,
	TRUE,
	20000,
	FALSE,
	null,
	FALSE,
	FALSE,
	null,
	null,
	null
from oms."SupplierDO" sup where "internalSupplierName" IN ('simwarehouse_north');

5.1.5 SupplierClassificationSystemDO

According to the table product."ClassificationSystemDefDO", supplier-specific classification such as assortment, Christmas specials, or bargains are specified here.

--creating supplier classification system
insert into "SupplierClassificationSystemDO"("id", "isActive", "classificationSystemDefRef", "supplierClassificationSystemName", "supplierRef")
	values(nextval('"SupplierClassificationSystemDO_id_seq"'), true, 1, 'assortment_supplier_20000', 20000);

5.1.6 SupplierClassificationElementDO

SupplierClassificationElementDO is used for categorization or classification of products, e.g., product catalogs such as Electronics, Fashion, etc. It is possible to create child classifications as well, e.g., under 'Electronics', 'Phones' can be created.

Reference the above SQL in the supplier classification elements such as catalogs.

--creating supplier classification element in relation to the supplier classification system above
insert into "SupplierClassificationElementDO"("id", "supplierClassificationElementIdentifier", "supplierClassificationElementName", "parentElementRef", "supplierClassificationSystemRef")
    values (nextval('"SupplierClassificationElementDO_id_seq"'), 'foo', 'foobar', null, currval('"SupplierClassificationSystemDO_id_seq"'));

5.1.7 ClassificationElementDO

According to the table product."ClassificationSystemDefDO", classification such as assortment, size, and color are specified here.

--creating classification system in relation to the existing IOM classification system with key 1
insert into "ClassificationElementDO"("id", "classificationSystemDefRef", "description", "elementName", "parentElementRef")
   values (nextval('"ClassificationElementDO_id_seq"'), 1, 'a description', 'dummy_supplier_20000', null);

5.1.8 ClassificationElement2SupplierClassificationElementDO

ClassificationElement2SupplierClassificationElementDO is used for a mapping between ClassificationElementDO and SupplierClassificationElementDO.

--creating relation supplier classification element to IOM classification element
insert into "ClassificationElement2SupplierClassificationElementDO"("id", "classificationElementRef", "supplierClassificationElementRef")
	values(nextval('"ClassificationElement2SupplierClassificationElementDO_id_seq"'), currval('"ClassificationElementDO_id_seq"'), currval('"SupplierClassificationElementDO_id_seq"'));

5.2 CSV Files

5.2.1 Basic Data

The basic data file contains the common and yet very essential product information, such as dimensions and identification elements (e.g., EAN, ISBN, and manufacturer product ID). Product name is identical to supplier-specific product name.

The name of the file should conform to <ShopID>_<SupplierID>_<Date>_A.csv

where,

  • <ShopID> is ID of the shop from the table oms."ShopDO"
  • <SupplierID> is ID of the supplier from the table oms."SupplierDO"
  • <Date> is YYYYMMDDHHMMSS

Example Name: 10000_10002_20160224165437_A.csv

5.2.1.1 Elements of a CSV File

The following table illustrates the elements of CSV file:

Column NameDescription

Field-Type

supplierArticleNo

Product number of supplier

Character(30)

manufacturer

Name of manufacturer

Character(50)

manufacturerArticleNo

Product number of manufacturer

Character(30)

ISBN <optional>

ISBN of product

Character(20)

EAN <optional>

EAN of product

Character(20)

articleName

Name of product

Character(250)

length <optional>

Length in mm

Integer

height <optional>

Height in mm

Integer

width <optional>

Width in mm

Integer

weight <optional>

Weight in kg

Numeric(15,5)

assortmentName <optional>

Name of commodity group

Character(250)

assortmentIdentifier

Unique identification property of commodity group

Character(250)

deliveryForm

  • 10 (for standard carrier)
  • 20 (for truckage company)
  • 30 (for non-tangible, e.g., downloads)

Character(10)

customsTariffNo <optional>

Customs tariff number

Character(11)

parentSupplierArticleNo <optional>

Supplier product number of main product, e.g., a shoe-model is available in many sizes and colors.

The basic data is imported as parent product (refer 'articleType' = 2) which has no meaning on its own.

In this case, the custom properties such as size and color are imported as child products (refer 'articleType' = 3), and while importing these, the parent product is referred.

Character(30)

articleForm

Product type

  1. Physical product
  2. Non-tangible product

Integer

immaterialUid <optional>

Unique ID of non-tangible product, if required during ordering process

Character(70)

articleLanguage

Codes for the representation of names of languages according to ISO639-2

Character(3)

supplierSalesCode <optional>

Filter criteria

Character(255)

supplierArticleIdentifier <optional>

Additional ID of supplier product type

Character(30)

articleType

  1. Standard
  2. Parent product variation
  3. Child product variation
  4. Bundle product of supplier

Integer

edition <optional>

Depiction of product edition. Book edition of a publishing company.

Character(10)

packagingUnit <optional>

Packaging unit

Character(10)

packagingUnitValue <optional>

Packaging unit value

Numeric(15,5)

5.2.1.2 Example CSV File

Example name of CSV file: 10000_10002_20160224165437_A.csv conforming to <ShopID>_<SupplierID>_<Date>_A.csv (mentioned above).

supplierArticleNo|manufacturer|manufacturerArticleNo|ISBN|EAN|articleName|length|height|width|weight|assortmentName|assortmentIdentifier|deliveryForm|customsTariffNo|parentSupplierArticleNo|articleForm|immaterialUid|articleLanguage|supplierSalesCode|supplierArticleIdentifier|articleType|edition|packagingUnit|packagingUnitValue
107420|HP|107420|||HP 122 LaserJet Printing Supplies 122A Black LaserJet Toner Cartridge|||||Dummy|Dummy||||||EN|||1|||
4010799|DELL|4010799|||DELL Me 800 Full-HD 1000GB|||||Laptops|Laptops||||||EN-GB|||1|||

5.2.2 Mapping of Supplier-Shop Product ID

As mentioned earlier, a product can be delivered to a customer by different suppliers (not at once though) on behalf of the shop where the purchase has been made, without the customer ever realizing the presence or significance of the supplier.

The problem with multiple suppliers is that, there could be conflicting product IDs. For instance, according to a Nokia retailer, product ID ‘1’ is a Lumia 925 smartphone, whereas a Sony retailer has an Xperia smartphone with the same product ID ‘1’.

In this case, where various suppliers are providing different products with the same product ID, the shop is not able to resolve the ID conflicts without mapping. Hence, whilst importing, the supplier has to specify the shop product ID for all their to-be-imported products.

The name of the file should conform to <ShopID>_<SupplierID>_<Date>_N.csv

where,

  • <ShopID> is ID of the shop from the table oms."ShopDO"
  • <SupplierID> is ID of the supplier from the table oms."SupplierDO"
  • <Date> is YYYYMMDDHHMMSS

Example name: 10000_10002_20160224165437_N.csv

5.2.2.1 Elements of CSV File

The following table illustrates the elements of CSV file.

Column NameDescriptionField-Type

supplierArticleNo

Product number of supplier

Character(30)

bakeryArticleNo

Product number of IOM

Character(30)

shopArticleNo

Product number of shop

Character(30)

Note

One CSV file is imported where supplierArticleNo and shopArticleNo are given. (bakeryArticleNo not filled)

You can fill the column "bakeryArticleNo" when this ID is also used by the shop.

 This spare a mapping on the IOM side but imply to export/import the product from the IOM to the shop.

 (In this case, the *_N.csv file must be imported when the products are already within the IOM)

 

5.2.2.2 Example CSV File

Although the shop product ID can be different to the supplier product ID, in this example both are the same. Example name of CSV file: 10000_10002_20160224165437_N.csv conforming to <ShopID>_<SupplierID>_<Date>_N.csv (mentioned above).

supplierArticleNo|bakeryArticleNo|shopArticleNo
107420||107420
4010799||4010799

5.2.3 Dynamic Data

Whilst importing products, the IOM has to consider supplier-shop-specific product details such as price, availability, stock information, and currency.

For instance, supplier A is delivering Nokia Lumia smartphones for British/UK customers (currency: Pound Sterling) with a slightly higher price but with lower delivery time, whereas supplier B delivers the same smartphones for German customers (currency: Euro) with a lower price but with increased delivery time.

The name of the file should conform to <ShopID>_<SupplierID>_<Date>_BCG.csv

where,

  • <ShopID> is ID of the shop from the table oms."ShopDO"
  • <SupplierID> is ID of the supplier from the table oms."SupplierDO"
  • <Date> is YYYYMMDDHHMMSS

Example name: 10000_10002_20160224165437_BCG.csv

5.2.3.1 Elements of CSV File

The following table illustrates the elements of CSV file:

Column NameDescriptionField-Type

supplierArticleNo

Product number of supplier

Character(30)

currency

Currency according to ISO-4217

Character(5)

purchasePrice (deprecated)

Purchase price

Numeric(15,5)

listPrice <optional> (deprecated)

Recommended Retail Price (RRP)

Numeric(15,5)

stockLevel

Inventory/items available

Integer

stockType

Type of inventory

  • 1 = Applicable for all shops that access this inventory/stock
  • 2 = For reservation and applicable for the configured shop

Integer

availabilityInDays

Availability of the product in days

  • -1 = not available
  • 0 = immediately available
  • x = (positive integer) after import, available in ‘x’ days

Integer

salesPrice <optional> (deprecated)

Selling price

Numeric(15,5)

basicPrice <optional>

Basic price based on a unit (see 'basicPriceUnitValue')

Numeric(15,5)

basicPriceUnitValue <optional>

Unit value on which basic price applies to

(piece - 1, milligram - 2, gram - 3, kilogram - 4, liter - 5, milliliter - 6, meter - 7, centimeter - 8, millimeter - 9, square-meter - 10)

Integer

basicPriceUnit <optional>

Unit for the value of basic price

Character (10)

salesPriceOld <optional> (deprecated)

Old sales price of shop

Numeric(15,5)

provisionType <optional> (deprecated)

Provision type

Character(10)

provisionPercentage <optional> (deprecated)

Percentage of provision

Numeric(15,5)

taxType

Tax type (e.g., no tax, normal tax, full tax)

Character(30)

isBestseller <optional> (deprecated)

Is the product a best seller (e.g., applicable for books)

Boolean (‘TRUE’ or ‘FALSE’)

5.2.3.2 Example CSV File

Example name of CSV file: 10000_10002_20160224165437_BCG.csv conforming to <ShopID>_<SupplierID>_<Date>_BCG.csv (mentioned above).

supplierArticleNo|currency|purchasePrice|listPrice|stockLevel|stockType|availabilityInDays|salesPrice|basicPrice|basicPriceUnitValue|basicPriceUnit|salesPriceOld|provisionType|provisionPercentage|taxType|isBestseller
107420|USD|90.95||500|1|0|94.95|||||||FullTax|
6946439|USD|19.95||500|1|0|21.95|||||||FullTax|

6 Import Without Zip File

6.1 SQL Configuration

6.1.1 Basic Data

For description see section Basic Data.

In this scenario each CSV file is imported individually and there is no parent import configuration. Therefore each CSV file needs to contain the information, which Java class is used to process the delivered data.

 INSERT INTO product."ImportConfigurationDO"(
    id,
    "availabilityStoredProcedure",
    "copyStoredProcedure",
    "csvDelimiter",
    "csvHeader",
    "priceComparisonProviderDefRef",
    "storedProcedureGroup",
    "supplierRef",
    "tableNameGroup",
    "checkMissingInLastDatapack",
    "importDatapackFileTypeDefRef",
    "canDatapackBeEmpty",
    "parentImportConfigurationRef",
    "convertToCSV",
    "createContentAssignment",
    "internalMediaPath",
    "splitCount",
    "variationProcedure",
    "identifyOnBakeryArticleRef",
    "supplementArticleParent",
    "assortmentSupplier",
    "updateArticleType",
    "importBeanDefRef",
    "createSupplierAssortmentSystem",
    "createManufacturer",
    "financeControllerRef",
    "changeIdentificationOfArticle",
    "bonusPartnerRef")
    SELECT nextval('"ImportConfigurationDO_id_seq"'),
    null ,
    '',
    '''|''', 
'supplierArticleNo|manufacturer|manufacturerArticleNo|ISBN|EAN|articleName|length|height|width|weight|assortmentName|assortmentIdentifier|deliveryForm|customsTariffNo|parentSupplierArticleNo|articleForm|immaterialUid|articleLanguage|supplierSalesCode|supplierArticleIdentifier|articleType|edition|packagingUnit|packagingUnitValue',     null, 
    'import_standard_a_v10', 
    10000, -- NOTE: enter/modify supplier ID from oms."SupplierDO"
    'Import_Standard_Basic_V10', 
    FALSE, 
    1, -- refer "ImportDatapackFileTypeDefDO" table -> 1 means basic data import
    TRUE, 
    null, 
    FALSE,
    FALSE,
    '', 
    2, 
    '', 
    FALSE, 
    TRUE, 
    10000, -- NOTE: enter/modify supplier ID from oms."SupplierDO"
    FALSE, 
    null, 
    TRUE,  
    TRUE, 
    null, 
    null, 
    null); 

6.1.2 Mapping of Supplier-Shop Product ID

For description see section Mapping of Supplier-Shop Product ID.

 INSERT INTO product."ImportConfigurationDO"(
    id,
    "availabilityStoredProcedure",
    "copyStoredProcedure",
    "csvDelimiter",
    "csvHeader",
    "priceComparisonProviderDefRef",
    "storedProcedureGroup",
    "supplierRef",
    "tableNameGroup",
    "checkMissingInLastDatapack",
    "importDatapackFileTypeDefRef",
    "canDatapackBeEmpty",
    "parentImportConfigurationRef",
    "convertToCSV",
    "createContentAssignment",
    "internalMediaPath",
    "splitCount",
    "variationProcedure",
    "identifyOnBakeryArticleRef",
    "supplementArticleParent",
    "assortmentSupplier",
    "updateArticleType",
    "importBeanDefRef",
    "createSupplierAssortmentSystem",
    "createManufacturer",
    "financeControllerRef",
    "changeIdentificationOfArticle",
    "bonusPartnerRef")
    SELECT nextval('"ImportConfigurationDO_id_seq"'), 
    null ,
    '',
    '''|''',
    'supplierArticleNo|bakeryArticleNo|shopArticleNo',
    null,
    'import_standard_n_v4',
    10000, -- Note: enter supplier ID from oms."SupplierDO"
    'Import_Standard_ShopArticleNo_V4',
    FALSE,
    39, -- refer "ImportDatapackFileTypeDefDO" -> meaning shop<->supplier product number mapping
    FALSE,
    null,
    FALSE,
    FALSE,
    null,
    3, -- no. of asynchronous processes
    null,
    FALSE,
    FALSE,
    10000, -- Note: enter supplier ID from oms."SupplierDO"
    FALSE,
    null,
    FALSE,
    FALSE,
    null,
    null,
    null);

6.1.3 Dynamic Data

For description see section Mapping of Supplier-Shop Product ID.

 INSERT INTO product."ImportConfigurationDO"(
    id,
    "availabilityStoredProcedure",
    "copyStoredProcedure",
    "csvDelimiter",
    "csvHeader",
    "priceComparisonProviderDefRef",
    "storedProcedureGroup",
    "supplierRef",
    "tableNameGroup",
    "checkMissingInLastDatapack",
    "importDatapackFileTypeDefRef",
    "canDatapackBeEmpty",
    "parentImportConfigurationRef",
    "convertToCSV",
    "createContentAssignment",
    "internalMediaPath",
    "splitCount",
    "variationProcedure",
    "identifyOnBakeryArticleRef",
    "supplementArticleParent",
    "assortmentSupplier",
    "updateArticleType",
    "importBeanDefRef",
    "createSupplierAssortmentSystem",
    "createManufacturer",
    "financeControllerRef",
    "changeIdentificationOfArticle",
    "bonusPartnerRef")
    SELECT nextval('"ImportConfigurationDO_id_seq"'),
    'process_availability_mapping_do_nothing',
    '',
    '''|''',    'supplierArticleNo|currency|purchasePrice|listPrice|stockLevel|stockType|availabilityInDays|salesPrice|basicPrice|basicPriceUnitValue|basicPriceUnit|salesPriceOld|provisionType|provisionPercentage|taxType|isBestseller',
    null,
    'import_standard_bcg_v3',
    10000, -- Note: enter supplier ID from oms."SupplierDO"
    'Import_Standard_Dynamic_V7',
    TRUE,
    13, -- mapping of price and availability (i.e., dynamic data) -> refer "ImportDatapackFileTypeDefDO"
    TRUE,
    null,
    FALSE,
    FALSE,
    '',
    6, -- no. of asynchronous processes
    '',
    FALSE,
    TRUE,
    10000, -- Note: enter supplier ID from oms."SupplierDO"
    FALSE,
    null,
    FALSE,
    FALSE,
    null,
    null,
    null);

6.1.4 SupplierClassificationSystemDO

According to the table product."ClassificationSystemDefDO", supplier-specific classifications, such as assortment, Christmas specials, or bargains are specified here.

INSERT INTO "SupplierClassificationSystemDO"("id", "isActive", "classificationSystemDefRef", "supplierClassificationSystemName", "supplierRef")
    VALUES (nextval('"SupplierClassificationSystemDO_id_seq"'), true, 1, 'Sports in Motion Assortment System', 10000 -- Note: enter supplier ID from oms."SupplierDO");

Note

The ID of the SupplierClassificationSystemDO-record inserted above is referenced in the following SQL statement. Hence, note and reuse the ID.

6.1.5 SupplierClassificationElementDO

SupplierClassificationElementDO is used for categorization or classification of products, e.g., product catalogs such as Electronics, Fashion, etc. It is possible to create child classifications as well, e.g., under 'Electronics', 'Phones' can be created.

Reference the above SQL in the supplier classification elements such as catalogs.

-- create a product catalog called 'Electronics'
INSERT INTO "SupplierClassificationElementDO"("id", "supplierClassificationElementIdentifier", "supplierClassificationElementName", "parentElementRef", "supplierClassificationSystemRef")
    VALUES (nextval('"SupplierClassificationElementDO_id_seq"'), 'Electronics', 'Electronics', null, currval('"SupplierClassificationSystemDO_id_seq"') -- resulting ID of above-entered SQL);

-- create a sub-catalog called 'Phones' under the above-created catalog 'Electronics'
INSERT INTO "SupplierClassificationElementDO"("id", "supplierClassificationElementIdentifier", "supplierClassificationElementName", "parentElementRef", "supplierClassificationSystemRef")
    VALUES (nextval('"SupplierClassificationElementDO_id_seq"'), 'Phones', 'Phones', currval('"SupplierClassificationElementDO_id_seq"'), currval('"SupplierClassificationSystemDO_id_seq"')

Note

The product details are imported into IOM as CSV files using a pre-configured directory, whose default value is /opt/oms/importarticle/in. However, this can be modified using the property is.oms.dir.var in $JBOSS_HOME/standalone/config/standalone.xml.

6.2 CSV Files

The CSV file structures are the same like above, see section CSV Files | Basic Data.

7 Scheduled Job

The job CheckForDatapackFiles automatically runs every minute (i.e., no additional configuration is needed). It picks up the CSV files placed in the directory, processes the information, and for each product makes an entry in the table product."ArticleDO".

Note

The product details are imported into IOM as CSV files using a pre-configured directory, whose default value is /opt/oms/importarticle/in. However, this can be modified using the property is.oms.dir.var in $JBOSS_HOME/standalone/config/standalone.xml.

8 Troubleshooting

There is no GUI where the users can check the progress and result of the import process, at the moment.

Hence, it is recommended to check the log files located in $JBOSS_HOME/standalone/log/bakery.log and $JBOSS_HOME/standalone/log/server.log.

Exit code 0 means that the import process was successful. If there are any exceptions thrown or any other exit code is mentioned, the import was not successful.

Disclaimer

The information provided in the Knowledge Base may not be applicable to all systems and situations. Intershop Communications will not be liable to any party for any direct or indirect damages resulting from the use of the Customer Support section of the Intershop Corporate Web site, including, without limitation, any lost profits, business interruption, loss of programs or other data on your information handling system.

Customer Support
Knowledge Base
Product Resources
Support Tickets