- Menu
- Sitemap
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
| Term | Description |
|---|---|
| CSV | Comma-separated values file; stores tabular values in plain text files |
| DO | Data object - used as persistence entities to represent stored values in database |
| EAN | International Article Number (originally, European Article Number) |
| GUI | Graphical User Interface |
| IOM | The abbreviation for Intershop Order Management |
| ISBN | International Standard Book Number |
| ISO | International Organization for Standardization |
| No | Number |
| UK | The United Kingdom of Great Britain and Northern Ireland |
| ZIP | Archive 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.
- 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. - 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.
- 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) | Type | Description | Example |
|---|---|---|---|---|
| 1 | id | Long (bigint) NOT NULL | Unique ID for the new entry. Usually, SELECT nextval('"ImportConfigurationDO_id_seq"') is used. | 1 |
| 2 | availabilityStoredProcedure | Character (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 |
| 4 | copyStoredProcedure <optional> | Character (255) | Stored procedure used for copying files | null |
| 5 | csvDelimiter | Character (255) | Which delimiter is used in the CSV file for column separation | | (Pipe) |
| 6 | csvHeader | Character (1000) | Which headers (columns) will be provided in the CSV file | supplierArticleNo|bakeryArticleNo|shopArticleNo |
| 13 | priceComparisonProviderDefRef <optional> | Integer | Reference to price search provider, e.g., Geizhals Deutschland. Refer "PriceComparisonProviderDefDO" | null |
| 14 | storedProcedureGroup | Character (255) | Stored procedure under the schema 'product' used for processing the information of the placed CSV file | import_standard_abcg_v10import_standard_abcg_v10 |
| 15 | supplierRef | Long (bigint) | Reference to the supplier. Refer to the table oms."SupplierDO". | 20000 |
| 16 | tableNameGroup | Character (255) | Name of the temporary table used for import | Import_Standard_ShopArticleNo_V4 |
| 18 | checkMissingInLastDatapack | Boolean | 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. | true |
| 19 | importDatapackFileTypeDefRef | Integer | Defines the purpose of the CSV file. Refer to "ImportDatapackFileTypeDefDO" table.
| 82 |
| 20 | canDatapackBeEmpty | Boolean (NOT NULL) | Whether data pack can be empty | true |
| 21 | parentImportConfigurationRef | Long (bigint) | Refers to the parent configuration of product."ImportConfigurationDO". Important for zip-imports. | 1 |
| 22 | convertToCSV | Boolean | If the provided file has to be converted into internal CSV file format | false |
| 23 | createContentAssignment | Boolean | Whether the provided supplier product number should be saved to Article2ContentSupplierDO | false |
| 24 | internalMediaPath <optional> | Character (250) | Path for product images (currently, not in use) | |
| 25 | splitCount | Integer | Number of asynchronous processes | 2 |
| 26 | variationProcedure <optional> | Character (250) | Name of the procedure responsible for version recognition | null |
| 27 | identifyOnBakeryArticleRef | Boolean (default: FALSE) | Whether the IOM internal product number should be used during import (used for dynamic data) | false |
| 28 | supplementArticleParent | Boolean | Generate missing parent product (used for dynamic data) | false |
| 29 | assortmentSupplier | Long (bigint) | Reference to the supplier. Refers to the table oms."SupplierDO". | 20000 |
| 30 | updateArticleType | Boolean (NOT NULL - default FALSE) | During basic data import, do not verify product type | false |
| 31 | importBeanDefRef | Integer | Bean used for processing import. Refers to the table "ImportConfigurationBeanDefDO" | 2 |
| 32 | createSupplierAssortmentSystem | Boolean (NOT NULL - default FALSE) | Whether to refer to internal commodity group of supplier, but refer to the existing one (create if non-existent). | false |
| 33 | createManufacturer | Boolean (default FALSE) | Create the manufacturer if not already known | false |
| 34 | financeControllerRef <optional> | Integer | Reference to external finance controller reference for a product (bookkeeping service to manage credits/debits of system) | null |
| 35 | changeIdentificationOfArticle | Boolean | For Basic Data (*_A.csv), for pre-existing articles identified by their "supplier" - "supplier-product-number": For the article ID Import (*_N.csv): allow to modfiy the article mapping (see 5.1.3) | false |
| 36 | bonusPartnerRef <optional> | Integer | Reference 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 Name | Description | 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 |
| 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
| 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 |
| 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 Name | Description | Field-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 Name | Description | Field-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
| Integer |
availabilityInDays | Availability of the product in 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.