Introduction

This guide addresses the partners or clients who use Intershop Order Management (IOM) in their system. It demonstrates how products are to be exported from the IOM. Typically such an export is addressed to a shop or a PIM system.

Glossary

Prerequisites

Basic knowledge of CSV and SQL is a prerequisite. Also a good understanding of the IOM Product Import is required.

Concept / Process

An export depends on the scope of the available products for an OMS shop instance. The IOM can export so-called basic data and/or price and availability information.

The output of the export process is a CSV data format. The OMS supports two different kinds of exports: 

• Full (all products)
• Incremental (only changed products since the last export, changes are: updates on existing products, deletion of already exported products, or generally new products)

The basic data CSV file contains information about the main details of a product, like EAN, ISBN, manufacturer etc.

The dynamic data CSV file contains information about prices (purchase, sales, basic, market), the availability etc.

Process Description

An IOM product export is defined and configured by the table product."ArticleExportConfigDO". The product data to be exported must be configured in the table product."ArticleExportSpecificationDO". The data source for the product export is the table product."ArticleInfoArticleAO".

Every few minutes an OMS internal job checks the database table product."ArticleExportConfigDO", in order to identify whether an export must take place. Depending on the configuration, a full or incremental export is performed.

If you want to run a full export immediately, you have to clear (set to null) the field "lastRun" at the related export configuration (product."ArticleExportConfigDO"). Every incremental will build on the last full export. While an incremental export can be reverted, a full export cannot (see Troubleshooting).

The CSV files generated by the export process are usually transmitted to a shop system via FTP. The transfer is not part of this documentation.

Export Configuration

This chapter describes the configuration of the product export.    

An OMS export configuration consists essentially of a certain row at the configuration tables:

• ArticleExportConfigDO
• ArticleExportClassificationSystemDO
• ArticleExportSpecificationDO

Optionally, the export configuration can be enriched by so-called Export Filters (described in a separate chapter below).

ArticleExportConfigDO

This defines a product export in general and contains the basic configurations to execute an article export.

This table serves as the reference table to other export configuration tables.

Elements of ArticleExportConfigDO

Example ArticleExportConfigDO

INSERT INTO product."ArticleExportConfigDO"(
            "id", "isActive", "exportName", 
            "intervalInDaysBasicExportFull", "intervalInDaysPandAExportFull", 
            "intervalInMinutesBasicExportDelta", "intervalInMinutesPandAExportDelta", 
            "languageDefRef", "numberOfArticlesPerFile",
            
            "shopGroupRef", "shopRef", "startTimeExport", 
            ""version", "exportFileNamePrefix",
            
            "exportArticleOnlyWithContent", "basicExportVersion", "pandaExportVersion", 
            "exportWithSupplierInfo", "useOnlyArticleWhiteList", "articleFactAttributeTypeDefRef",
            
            "useAutomaticCreatedFacts", "useMappedSupplierReference", 
            "regardVariationFilter", "isEANRequired", "isShopArticleNoRequired", 
            "expectedRuntime", "exportBeanDefRef", "includeSoldArticles")
    VALUES (
			123, true, 'Prime-Tech', 
            1, 1, 
            100000000, 100000000, 
            1, 32000,
            1, 9012, '2016-05-01 03:00:00', 
            0,  'Prime-Tech',
            true, 0, 0, 
            true, false, 1,
            false, false, 
            false, false, false, 
            120, 20, false);

ArticleExportClassificationSystemDO

Every export is based on a classification (assortment) system, which is used in the product export. This system can be the IOM internal classification system or a shop classification system (if available in the IOM).

Each export configuration needs at least one entry in this table.

Elements of ArticleExportClassificationSystemDO

Example ArticleExportClassificationSystemDO

INSERT INTO product."ArticleExportClassificationSystemDO"(
            "id", "classificationSystemDefRef", "realClassificationSystemDefRef", 
            "articleExportConfigRef", "shopClassificationSystemRef", "supplierClassificationSystemRef")
    VALUES (nextval('product."ArticleExportClassificationSystemDO_id_seq"'), 1, 1, 123, null, null);

ArticleExportSpecificationDO

This can contain a combination of an assortment and a manufacturer (manufacturer is optional and can be NULL), which is used in an (incremental or full) export. An entry in this table is required for each export.

It defines which category of products should be exported. If you want to export without having any restrictions on the product categories, you put an entry with the root element of the IOM classification system (classificationSystemDefRef = 1) or the shop classification system root element.

It is possible to add more than one entry per export configuration, e.g., "Electronics & Computers" and "Toys, Children & Baby".

Elements of ArticleExportSpecificationDO

Example ArticleExportSpecificationDO

INSERT INTO product."ArticleExportSpecificationDO"(
            "id", "assortmentRef", "articleExportConfigRef", "manufacturerRef", 
            "isWhiteList", "assortmentTypeDefRef")
    VALUES (nextval('product."ArticleExportSpecificationDO_id_seq"'), 1, 123, null, true, 1);

Export Filter

The OMS provides the option to define so-called "Export filters" to refine the amount of products to be exported. In general, several export filters can extend an export configuration. The defined filters are sequentially applied to the amount of products to be exported.

The different filtering options are described in the following.

Filter by Manufacturer

It is possible to filter the products to be exported by manufacture article number. To identify the related manufacturer, the column "manufacturerRef" is mandatory.

Each row of such a filter definition represents a product identified by the combination of "manufacturerArticleNo" and "manufacturerRef".  

The "isWhiteList" flag indicates the kind of filtering (black- vs. white-list).

Elements of ArticleExportFilterArticleManufacturerDO

Example ArticleExportFilterArticleManufacturerDO

INSERT INTO product."ArticleExportFilterArticleManufacturerDO"(
			"id", "isWhiteList","manufacturerRef" , "manufacturerArticleNo", "articleExportConfigRef")
		VALUES (nextval('product."ArticleExportFilterArticleManufacturerDO_id_seq"'), true, 100, 'P5800019', 123);

Filter by Classification

This filter enables to filter products according to the relation to a specific classification system element. Such an element belongs inevitably to a classification system.

The "isWhiteList" flag indicates the kind of filtering (black- vs. white-list).

Elements of ArticleExportFilterArticleClassificationDO

Example ArticleExportFilterArticleClassificationDO

INSERT INTO product."ArticleExportFilterArticleClassificationDO"(
				"id", "isWhiteList","articleExportConfigRef","classificationElementRef") 
			VALUES (nextval('product."ArticleExportFilterArticleClassificationDO_id_seq"'), false, 123, 4567);

Filter by Article

There is also the possibility to filter the products to be exported directly by the OMS internal product id. You can configure lists of products to be black- or white-listed.

The "isWhiteList" flag indicates the kind of filtering (black- vs. white-list).

Elements of ArticleExportFilterArticleDO

Example ArticleExportFilterArticleDO

INSERT INTO product."ArticleExportFilterArticleDO"(
				"id", "isWhiteList","artcielRef", "articleExportConfigRef") 
			VALUES (nextval('product."ArticleExportFilterArticleDO_id_seq"'), false, 123456, 123);

Filter by EAN 

This filter can be used to filter the products to be exported by EAN. You can configure lists of products to be black- or white-listed.

The "isWhiteList" flag indicates the kind of filtering (black- vs. white-list).

Elements of ArticleExportFilterArticleEanDO

Example ArticleExportFilterArticleEanDO

INSERT INTO product."ArticleExportFilterArticleEanDO"(
				"id", "isWhiteList", "ean", "articleExportConfigRef")
			VALUES (nextval('product."ArticleExportFilterArticleEanDO_id_seq"'), false, 4007249787569, 123);

Filter by ISBN

This enables to filter the products to be exported by ISBN. You can configure lists of products to be black- or white-listed.

The "isWhiteList" flag indicates the kind of filtering (black- vs. white-list).

Elements of ArticleExportFilterArticleIsbnDO

Example ArticleExportFilterArticleIsbnDO

INSERT INTO product."ArticleExportFilterArticleIsbnDO"(
				"id", "isWhiteList", "isbn", "articleExportConfigRef")
			VALUES (nextval('product."ArticleExportFilterArticleIsbnDO_id_seq"'), false, '978-3-86680-192-9', 123);

Filter by Supplier Sales Code

Products can be filtered by the supplier specific sales code, e.g., a sales model etc.

The "isWhiteList" flag indicates the kind of filtering (black- vs. white-list).

Elements of ArticleExportFilterArticleSupplierSalesCodeDO

Example ArticleExportFilterArticleSupplierSalesCodeDO

INSERT INTO product."ArticleExportFilterArticleSupplierSalesCodeDO"(
				"id", "isWhiteList", "supplierSalesCode", "articleExportConfigRef")
			VALUES (nextval('product."ArticleExportFilterArticleSupplierSalesCodeDO_id_seq"'), false, 'wholesale', 123);

Filter by Availability

It is also possible to filter products by their availability in days.

To get a valid "filter by availability" configuration, one of the two columns ("availabilityInDaysMax", "availabilityInDaysMin") must be defined. These columns are combined via OR at the execution.

Elements of ArticleExportFilterAvailabilityDO

Example ArticleExportFilterAvailabilityDO

INSERT INTO product."ArticleExportFilterAvailabilityDO"(
				"id", "availabilityInDaysMax", "availabilityInDaysMin", "articleExportConfigRef")
			VALUES (nextval('product."ArticleExportFilterAvailabilityDO_id_seq"'), 5, null, 123);

Filter by Classification System

Products can also be filtered according to the membership in a classification system.

Elements of ArticleExportFilterClassificationSystemDO

Example ArticleExportFilterClassificationSystemDO

INSERT INTO product."ArticleExportFilterClassificationSystemDO"(
				"id", "classificationSystemDefRef", "articleExportConfigRef")
			VALUES (nextval('product."ArticleExportFilterClassificationSystemDO_id_seq"'), 2, 123);

Filter by Dimension

This allows to filter the products to be exported by the existence of the attribute weight and/or size.

• To filter out products without the attributes height, width or length, set the flag "isDimensionRequired" true.
• To filter out products without the attribute weight, set the flag "isWeightRequired" true.

The OMS does not distinguish between NULL and 0.

A filter with both flags set to false makes no sense.

Elements of ArticleExportFilterDimensionDO

Example ArticleExportFilterDimensionDO

INSERT INTO product."ArticleExportFilterDimensionDO"(
				"id", "isDimensionRequired", "isWeightRequired", "articleExportConfigRef")
			VALUES (nextval('product."ArticleExportFilterDimensionDO_id_seq"'), true, false, 123);

Filter by Cost Price

There is also an option to filter products by their cost price.

You can filter out products without a cost price via the flag "isCostPriceRequired". This flag should be true in case of filtering by a maximum or minimum value of the cost price. The columns "costPriceMax" and "costPriceMin" will be combined via OR at the execution.

Elements of ArticleExportFilterCostPriceDO

Example ArticleExportFilterCostPriceDO

INSERT INTO product."ArticleExportFilterCostPriceDO"(
				"id", "isCostPriceRequired", "costPriceMax", "costPriceMin", "articleExportConfigRef")
			VALUES (nextval('product."ArticleExportFilterCostPriceDO_id_seq"'), true, 1000, null, 123);

Filter by List Price

Products can be filtered by their list price.

You can filter out products without a list price via the flag "isListPriceRequired". This flag should be true in case of filtering by a maximum or minimum value of the list price. The columns "listPriceMax" and "listPriceMin" will be combined via OR at the execution.

Elements of ArticleExportFilterSalesPriceDO

Example ArticleExportFilterSalesPriceDO 

INSERT INTO product."ArticleExportFilterListPriceDO"(
				"id", "isListPriceRequired", "listPriceMax", "listPriceMin", "articleExportConfigRef")
			VALUES (nextval('product."ArticleExportFilterListPriceDO_id_seq"'), true, 999.99, NULL, 123);

Filter by Stock Type

There is also the opportunity to filter products by their stock type.

Currently the OMS supports the following stock types:

• 1 (for shared inventory for all shops that access this product)
• 2 (for exclusive inventory for a certain shop)

The "isWhiteList" flag indicates the kind of filtering (black- vs. white-list). The column „stockTypeDefRef“ is mandatory to build a valid filter.

Elements of  ArticleExportFilterStockTypeDO

Example ArticleExportFilterStockTypeDO

INSERT INTO product."ArticleExportFilterStockTypeDO"(
				"id", "endTime", "startTime", "stockTypeDefRef", "isWhiteList", "articleExportConfigRef")
			VALUES (nextval('"ArticleExportFilterStockTypeDO_id_seq"'),'02:30:00', '02:00:00', 1, true, 123);

Filter by Supplier

Opportunity to filter products from a certain supplier.

The "isWhiteList" flag indicates the kind of filtering (black- vs. white-list).

Elements of   ArticleExportFilterSupplierDO

Example ArticleExportFilterSupplierDO

INSERT INTO product."ArticleExportFilterSupplierDO"(
				"id", "isWhiteList", "supplierRef", "articleExportConfigRef")
		VALUES (nextval('"ArticleExportFilterSupplierDO_id_seq"'), true, 1086, 123);

CSV Files

Basic Data

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

The name of the file is <ShopID>_<ExportName><ExportVersion>_StandardBasicV10_<ExportType>_<Timestamp>.csv, containing:

• ShopID is the ID of the shop from the table oms."ShopDO".
• ExportName is the value from the export configuration column "exportName".
• ExportVersion is the version of export, like a consecutive number (refer to 'exportVersion' in the table below).
• ExportType is Full or Incremental.
• Timestamp is YYYYMMDDHHMMSS.
  

So, an example file name could look as follows: 10001_XYZ_7.00000_StandardBasicV10_Full_20160419101200.csv.

Elements of Basic Data CSV File

The following table illustrates the elements of the CSV file:

Example Basic Data CSV File

exportVersion|articleRef|manufacturerIdentifier|manufacturer|manufacturerArticleNo|articleName|EAN|ISBN|length|height|width|weight|assortmentName|assortmentIdentifier|deliveryForm|articleFormDefRef|salesCode|articleTypeDefRef|parentArticleRef|taxType|shopArticleNo|state|edition|packagingUnit|packagingUnitValue
7.00000|675916|PrimeTech|PrimeTech|75941800|PrimeTech Abdeckplatte Edelstahl, 2er Set|4008838006313||||||Herdabdeckungen|100817|10|1||1||1|3029312|NEW|||
7.00000|675917|PrimeTech|PrimeTech|7672800|PrimeTech Wachsame Eule|4008838173046||||||Gartendekoration & Licht|100846|10|1||1||1|6472389|NEW|||

Dynamic Data

Dynamic data contains shop-specific product details such as price, availability, stock information, and currency.

The name of the file is <ShopID>_<ExportName>_<ExportVersion>_StandardDynamicDataV10_<ExportType>_<Timestamp>.csv, containing:

• ShopID is the ID of the shop from the table oms."ShopDO".
• ExportName is the value from the export configuration column "exportName".
• ExportVersion is the version of export, like a consecutive number (refer to 'exportVersion' in the table below).
• ExportType is Full or Incremental.
• Timestamp is YYYYMMDDHHMMSS.
  

So, an example file name could look as follows: 10001_XYZ_7.00000_StandardDynamicDataV10_Full_20160419101200.csv.

Elements of Dynamic Data CSV File

The following table illustrates the elements of the CSV file:

Example Dynamic Data CSV File

exportVersion|articleRef|shopArticleNo|currency|costPrice|itemPrice|stockLevel|stockType|availabilityInDays|marketPriceMin|marketPriceMax|marketPriceAverage|listPrice|basicPrice|basicPriceUnitValue|basicPriceUnit|articleState|supplierName|supplierRef|supplierArticleNo|customsTariffNo
7.00000|675916||EUR|21.17000||35|1|1||||29.99000||||1|4008838000007|1008|75941800|73239300
7.00000|675917||EUR|14.11000||12|1|1||||19.99000||||1|4008838000007|1008|7672800|39264000

Troubleshooting

Revert an Increment Product Export

It is possible to revert an increment product export. This means, all products of the last increment can be exported again. The following steps are to be done for all products that are included in the last increment export.

1. Set "lastExportDate" in product."ExportArticleDO" to a timestamp before the last increment export has been performed.
2. Set "modificationDate" in product."ArticleInfoArticleAO" to a timestamp before the last increment export has been performed.

Revert a Full Product Export

It is not possible to revert a full export. You can only run a full export again. To do so, set "lastRun", "startTimeControlApp" and "startTimeMDB" in product."ArticleExportConfigDO" NULL.

Missing Products in Export File

• Check settings / configuration at product."ArticleExportConfigDO".
• Check the last product import (results and errors).
• The product has no price in product."ArticleSalesPriceDO".
• The product has no entry in product."ArticleNameDO".
• The product belongs to an assortment which has no tax assignment.
• The OMS shop instance has no entry in oms."Shop2TaxTypeDefDO".
• A variation product / article has more than one variation identifier, but the product import contains only one.