Guide - IOM Product Export

1 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.

Note

The export will be placed under the pre-configured directory BASEPATH/importarticle/export. The BASEPATH is defined by the OMS property IS_OMS_DIR_VAR.

The CSV files generated during processing are initially stored under the directory BASEPATH/importarticle/temp before they will be moved into the export directory.

1.1 Glossary

TermDescription
BasicBasic data
CSVComma-Separated Values
DOData Object - used as persistence entities to represent stored values in database
EAN

International Article Number (originally, European Article Number)

GUIGraphical User Interface
ISBNInternational Standard Book Number
ISOInternational Organization for Standardization
IOMThe abbreviation for Intershop Order Management
NoNumber
PandA

Price and Availability

PIMProduct Information Management
OMSAbbreviation for Order Management System, the technical name of IOM
UKThe United Kingdom of Great Britain and Northern Ireland

1.2 Prerequisites

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

2 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.

2.1 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.

Note

Since IOM 2.14.0.0
This job has been moved to the schedules (configurable within the database table "ScheduleDO", schedule with the  key"ARTICLE_EXPORT" ). Also see Guide - IOM 2.14 Migration of Setup.


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.

3 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).

3.1 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.

3.1.1 Elements of ArticleExportConfigDO

NumberColumn Name (Case-Sensitive)

Type

DescriptionExample
1

id

bigint NOT NULLID / primary key 1
2isActiveboolean NOT NULLActive if truetrue
5exportNamecharacter varying(255) NOT NULLName of export, it is a part of export filenamemyexport
7intervalInDaysBasicExportFullinteger NOT NULLInterval in days when the basic export should run
(depends on startTimeExport and lastRun)
1
8intervalInDaysPandAExportFullinteger NOT NULLInterval in days when the price and availability export should run
(depends on startTimeExport and lastRun)
1
9intervalInMinutesBasicExportDeltainteger NOT NULLInterval in minutes when the delta basic export should run
(depends on startTimeExport and lastRun)
100000000
10intervalInMinutesPandAExportDeltainteger NOT NULLInterval in minutes when the delta price and availability export should run
(depends on startTimeExport and lastRun)
100000000
11languageDefRefinteger

Exports are not related to any language. It is the language of the content.

If you want to export all languages, you have to set LanguageDefDO.NO_LANGUAGE.

1
12lastRuntimestamp without time zone

Set by the export; If it is NULL, it will start next time.

Note

DO NOT CONFIGURE!

NULL
13numberOfArticlesPerFileintegerMax number of article per file; If no max number is required, leave it empty.NULL
15shopGroupRefbigint NOT NULLAttribute is not in use, set to default = 11
16shopRefbigint NOT NULLExport configuration belongs to an OMS shop instance - reference to ID of oms."ShopDO"9012
17startTimeControlApptimestamp without time zone

Set by the export; Set by the OMS Control-Application

Note

DO NOT CONFIGURE!

NULL
18startTimeExporttimestamp without time zone NOT NULLTimestamp indicates when the export was completed last time.'2019-04-02 12:00:00+02'
19startTimeMDBtimestamp without time zone

Set by the export; Set by the OMS Impex-Application

Note

DO NOT CONFIGURE!

NULL
20versionintegerVersion of this configuration1
22contentSupplierRefbigintAttribute is not in use, set to NULLNULL
23exportFileNamePrefixcharacter varying(50)Prefix of the exported filenamePrimeTech
24exportArticleOnlyWithContentbooleanAttribute is not in use, set to default = falsefalse
25basicExportVersionnumeric(15,5)

Counter of Basic-Export (position before decimal point is counter for full- and position after decimal point counter for the Delta-Export)

1.00000
26pandaExportVersionnumeric(15,5)

Counter of PandA-Export (position before decimal point is counter for full- and position after decimal point counter for the Delta-Export)

1.00000
27exportWithSupplierInfobooleanAdded to the PandA-Export information about the supplier (supplierRef, supplierName and supplierArticleNo)false
28useOnlyArticleWhiteListbooleanOnly articles from tables ArticleExportFilter<name> withe column "isWhiteList" = true will be exportedfalse
29articleFactAttributeTypeDefRefintegerWhat Fact-Attributes should be exported (Supplier = 1 or OMS = 2 or Internal = 3)1
30useAutomaticCreatedFactsbooleanShould the automatic created facts also be exported?false
31useMappedSupplierReferencebooleanUse the mapped suppliername, or the Intershop-OMS used onefalse
32regardVariationFilterboolean DEFAULT falseUse variation filterfalse
33isEANRequiredboolean DEFAULT falseIs the EAN required for products?false
34isShopArticleNoRequiredbooleanIs the shopArticleNo required (otherwise the OMS Article number)?true
35expectedRuntimeintegerExpected runtime of the export in seconds (only for admins required)120
36exportBeanDefRefinteger NOT NULLInternal reference to the ArticleExportConfigBeanDefDO (which bean is used for the export) - fix value = 2222
37includeSoldArticlesbooleanShould sold articles be a part of the export?false
38variationSystemTypeDefRefinteger NOT NULL DEFAULT 10Which variationSystem should be used? It is referenced to product."VariationSystemTypeDefDO".20
39threadCountintegerMax threads used on database for the export1

3.1.2 Example ArticleExportConfigDO

INSERT INTO product."ArticleExportConfigDO"(
            "id", "isActive", "exportName", 
            "intervalInDaysBasicExportFull", "intervalInDaysPandAExportFull", 
            "intervalInMinutesBasicExportDelta", "intervalInMinutesPandAExportDelta", 
            "languageDefRef", "lastRun", "numberOfArticlesPerFile",
            
            "shopGroupRef", "shopRef", "startTimeControlApp", "startTimeExport", 
            "startTimeMDB", "version", "contentSupplierRef", "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, null, 32000,
            1, 9012, null, '2016-05-01 03:00:00', 
            null, 0, 1082, 'Prime-Tech',
            true, 0, 0, 
            true, false, 1,
            false, false, 
            false, false, false, 
            120, 20, false);

3.2 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.

3.2.1 Elements of ArticleExportClassificationSystemDO

Number#Column Name (Case-Sensitive)

Type

DescriptionExample
1

id

bigintID / primary key 1
2classificationSystemDefRefintegerSet if the OMS Classification System (ClassificationSystemDefDO) is used1
3realClassificationSystemDefRefintegerReal Classification System. If the OMS System is used, then it is the same (ClassificationSystemDefDO).
Use the reference (ID) of the related ShopClassificationSystemDO / SupplierClassificationSystemDO if a ShopClassificationSystem or SupplierClassificationSystem is used.
1
4articleExportConfigRefbigint

Reference to the export configuration / product."ArticleExportConfigDO"

123
5shopClassificationSystemRefbigintReference to product."ShopClassificationSystemDO"NULL
6supplierClassificationSystemRefbigintReference to product."SupplierClassificationSystemDO"NULL

3.2.2 Example ArticleExportClassificationSystemDO

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

3.3 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".

3.3.1 Elements of ArticleExportSpecificationDO

Number#Column Name (Case-Sensitive)

Type

DescriptionExample
1idbigint NOT NULLID / primary key123
2assortmentRefbigint NOT NULLReference to the assortment either the OMS one or shop/supplier88
3articleExportConfigRefbigintReference to the export configuration / product."ArticleExportConfigDO"123
4manufacturerRefbigint

Reference to the product."ManufacturerDO"

null
5isWhiteListbooleanFlag indicating whether the filter works as a white or blacklist  true
6assortmentTypeDefRefinteger NOT NULL

Reference to the product."AssortmentTypeDefDO"

  • 1 (supplier assortment)
  • 2 (for OMS internal assortment)
  • 3 (for shop assortment)
1

3.3.2 Example ArticleExportSpecificationDO

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

4 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.

4.1 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).

4.1.1 Elements of ArticleExportFilterArticleManufacturerDO

Number#Column Name (Case-Sensitive)

Type

DescriptionExample
1idbigintID / primary key  1
2isWhiteListbooleanFlag indicating whether the filter works as a white or blacklist  true
3manufacturerRefbigintReference to the product."ManufacturerDO"100
4manufacturerArticleNocharacter varyingManufacturer article number to be used for filtering

P5800019

5articleExportConfigRefbigintReference to the export configuration / product."ArticleExportConfigDO"123

4.1.2 Example ArticleExportFilterArticleManufacturerDO

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

4.2 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).

4.2.1 Elements of ArticleExportFilterArticleClassificationDO

Number#Column Name (Case-Sensitive)

Type

DescriptionExample
1idbigintID / primary key  1
2isWhiteListbooleanFlag indicating whether the filter works as a white or blacklist  false
3articleExportConfigRefbigint

Reference to the export configuration / product."ArticleExportConfigDO"

123
4classificationElementRefbigintOMS internal ID of classification element to be used for filtering -
Reference to product."ClassificationElementDO"
587

4.2.2 Example ArticleExportFilterArticleClassificationDO

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

4.3 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).

4.3.1 Elements of ArticleExportFilterArticleDO

Number#Column Name (Case-Sensitive)

Type

DescriptionExample
1idbigintID / primary key1
2isWhiteListbooleanFlag indicating whether the filter works as a white or blacklist  false
3articleRefbigintReference to product."ArticleDO" - OMS internal ID of product123456
4articleExportConfigRefbigintReference to the export configuration / product."ArticleExportConfigDO"123

4.3.2 Example ArticleExportFilterArticleDO

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

4.4 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).

4.4.1 Elements of ArticleExportFilterArticleEanDO

Number#Column Name (Case-Sensitive)

Type

DescriptionExample
1idbigintID / primary key1
2isWhiteListbooleanFlag indicating whether the filter works as a white or blacklist  false
3eanbigintEAN4007249787569
4articleExportConfigRefbigintReference to the export configuration / product."ArticleExportConfigDO"123

4.4.2 Example ArticleExportFilterArticleEanDO

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

4.5 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).

4.5.1 Elements of ArticleExportFilterArticleIsbnDO

Number#Column Name (Case-Sensitive)

Type

DescriptionExample
1idbigintID / primary key1
2isWhiteListbooleanFlag indicating whether the filter works as a white or blacklist  false
3isbncharacter varying(20)ISBN

978-3-86680-192-9

4articleExportConfigRefbigintReference to the export configuration / product."ArticleExportConfigDO"123

4.5.2 Example ArticleExportFilterArticleIsbnDO

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

4.6 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).

4.6.1 Elements of ArticleExportFilterArticleSupplierSalesCodeDO

Number#Column Name (Case-Sensitive)

Type

DescriptionExample
1idbigintID / primary key1
2isWhiteListbooleanFlag indicating whether the filter works as a white or blacklist  false
3supplierSalesCodecharacter varying(255)Supplier specific sales code (e.g., sales model)

wholesale

4articleExportConfigRefbigintReference to the export configuration / product."ArticleExportConfigDO"123

4.6.2 Example ArticleExportFilterArticleSupplierSalesCodeDO

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

4.7 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.

4.7.1 Elements of ArticleExportFilterAvailabilityDO

Number#Column Name (Case-Sensitive)

Type

DescriptionExample
1idbigintID / primary key1
2availabilityInDaysMaxintegerMaximum availability in days5
3availabilityInDaysMinintegerMinimum availability in days

null

4articleExportConfigRefbigintReference to the export configuration / product."ArticleExportConfigDO"123

4.7.2 Example ArticleExportFilterAvailabilityDO

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

4.8 Filter by Classification System

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

4.8.1 Elements of ArticleExportFilterClassificationSystemDO

Number#Column Name (Case-Sensitive)

Type

DescriptionExample
1idbigintID / primary key1
2classificationSystemDefRefbigint

OMS internal ID of classification system to be used for filtering -
Reference to the product."ClassificationSystemDefDO"

2
3articleExportConfigRefbigintReference to the export configuration / product."ArticleExportConfigDO"123

4.8.2 Example ArticleExportFilterClassificationSystemDO

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

4.9 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.

4.9.1 Elements of ArticleExportFilterDimensionDO

Number#Column Name (Case-Sensitive)

Type

DescriptionExample
1idbigintID / primary key1
2isDimensionRequiredbooleanDimension (attributes height, width, and length of a product) must be availabletrue
3isWeightRequiredbooleanWeight (attribute weight of a product) must be availablefalse
4articleExportConfigRefbigintReference to the export configuration / product."ArticleExportConfigDO"123

4.9.2 Example ArticleExportFilterDimensionDO

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

4.10 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.

4.10.1 Elements of ArticleExportFilterCostPriceDO

Number#Column Name (Case-Sensitive)TypeDescriptionExample
1idbigintID / primary key1
2isCostPriceRequiredbooleanFlag cost price required (true/false)true
3costPriceMaxnumeric(15,5)Filter products by a maximum value of the attribute cost price    1000
4costPriceMinnumeric(15,5)

Filter products by a minimum value of the attribute cost price

null
5articleExportConfigRefbigintReference to the export configuration / product."ArticleExportConfigDO"123

4.10.2 Example ArticleExportFilterCostPriceDO

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

4.11 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.

4.11.1 Elements of ArticleExportFilterSalesPriceDO

Number#Column Name (Case-Sensitive)TypeDescriptionExample
1idbigintID / primary key1
2isListPriceRequiredboolean

Attributes list price must be available

true
3listPriceMaxnumeric(15,5)Filter products by a maximum value of the attribute list price999.99
4listPriceMinnumeric(15,5)Filter products by a minimum value of the attribute list priceNULL
5articleExportConfigRefbigintReference to the export configuration / product."ArticleExportConfigDO"123

4.11.2 Example ArticleExportFilterSalesPriceDO 

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

4.12 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.

4.12.1 Elements of  ArticleExportFilterStockTypeDO

Number#Column Name (Case-Sensitive)TypeDescriptionExample
1idbigintID / primary key1
2endTimetime without time zoneEnd time (to) - End time of validity of the filter02:30:00
3startTimetime without time zoneStart time (from) - Start time of validity of the filter02:00:00
4stockTypeDefRefinteger

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

Reference to product."StockTypeDefDO"

1
5isWhiteListbooleanFlag indicating whether the filter works as a white or blacklist  true
6articleExportConfigRefbigintReference to the export configuration / product."ArticleExportConfigDO"123

4.12.2 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);

4.13 Filter by Supplier

Opportunity to filter products from a certain supplier.

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

4.13.1 Elements of   ArticleExportFilterSupplierDO

Number#Column Name (Case-Sensitive)TypeDescriptionExample
1idbigintID / primary key1
2isWhiteListbooleanFlag indicating whether the filter works as a white or blacklist  true
3supplierRefbigintOMS internal id of supplier to be filtered by -
Reference to oms."SupplierDO"
1086
4articleExportConfigRefbigintReference to the export configuration / product."ArticleExportConfigDO"123

4.13.2 Example ArticleExportFilterSupplierDO

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

5 CSV Files

5.1 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.

5.1.1 Elements of Basic Data CSV File

The following table illustrates the elements of the CSV file:

Column NameDescription

Field-Type

exportVersionVersion of export -  consecutive numberNumeric(15,5)
articleRefOMS internal product IDBigInt
manufacturerIdentifierUnique identification property of manufacturercharacter varying(50)
manufacturerName of manufacturerCharacter(50)
manufacturerArticleNoProduct number of manufacturerCharacter
articleNameName of productCharater(250)
EANEAN of productcharacter varying(20)
ISBNISBN of productcharacter varying(20)
lengthLength in mmInteger
heightHeight in mmInteger
widthWidth in mmInteger
weightWeight in kgNumeric(15,5)
assortmentNameName of commodity groupcharacter varying(250)
assortmentIdentifierUnique identification property of commodity groupcharacter varying(250)
deliveryForm
  • 10 (for Standard)
  • 20 (for Freight forwarding)
  • 30 (for Non-tangible, e.g., downloads)
Integer
articleFormDefRef
  • 1 (for Physical product)
  • 2 (for Non-tangible product)
Integer
salesCode

Supplier specific sales code - If available, it can be used as filter criteria

character varying(20)
articleTypeDefRef
  • 1 (for Standard product)
  • 2 (for Parent / Master of a product variation)
  • 3 (for Child of a product variation)
  • 4 (for Bundle product of supplier)
Integer
parentArticleRef

Reference to OMS internal product ID of the parent product (master product) of a child product (variation of a master product),
e.g., a shoe-model is available in many sizes and colors.

The referenced product has 'articleTypeDefRef' = 2.
All products with a 'parentArticleRef' have 'articleTypeDefRef' = 3.

BigInt
taxTypeTax type (e.g., no tax, normal tax, full tax)character varying(10)
shopArticleNoProduct number of shopcharacter varying
state

Status of product

  • new ( for a newly exported product )
  • update (for products that changed since the last export )
  • delete (for products that have been deleted - in the next export these products will no longer be included)

Note

In case of full exports, the state of all items is new. Update and delete is relevant at Incremental exports only.

character varying(10)
editionDepiction of product edition, book edition of a publishing companycharacter varying(5)
packagingUnitPackaging unitcharacter varying(10)
packagingUnitValuePackaging unit valueNumeric(15,5)

5.1.2 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|||

5.2 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.

5.2.1 Elements of Dynamic Data CSV File

The following table illustrates the elements of the CSV file:

Column NameDescriptionField-Type
exportVersionVersion of export -  consecutive numberNumeric(15,5)
articleRefOMS internal product IDBigint
shopArticleNo

Product number of shop if present at OMS

Character varying
currencyCurrency according to ISO 4217
Applies to all prices of a line in the file
Character varying(5)
costPrice

Cost price for the shop

Numeric(15,5)
itemPrice
  • Manufacturer's Suggested Retail Price (MSRP)
  • Recommended Retail Price (RRP)
  • Maximum Retail Price (MRP)
Numeric(15,5)
stockLevelInventory / items availableInteger
stockType

Type of inventory

  • 1 - for shared inventory for all OMS shops that access this product
    (default)
  • 2 - for exclusive inventory for a certain 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
marketPriceMin

Lowest market price for the product

Numeric(15,5)
marketPriceMax

Highest market price for the product

Numeric(15,5)
marketPriceAverageAverage market price for the productNumeric(15,5)
listPrice

Selling price for the product

Numeric(15,5)
basicPriceBasic price based on a unit (see 'basicPriceUnitValue')Numeric(15,5)
basicPriceUnitValue

Unit value on which basic price applies to

Integer
basicPriceUnit

Unit for the value of basic price

  • 1 - for piece
  • 2 - for milligram
  • 3 - for gram
  • 4 - for kilogram
  • 5 - for liter
  • 6 - for milliliter
  • 7 - for meter
  • 8 - for centimeter
  • 9 - for milliliter
  • 10 - for square-meter
Integer
articleState

Status of the availability of the product :

  • 1 - Available
  • 2 - Not available
  • 3 - EOL (end of life)
Integer
supplierName

Name of supplier at shop system
Contained when configured in shop related export configuration

character varying(250)
supplierRef

OMS internal supplier ID
Contained when configured in shop related export configuration

Bigint
supplierArticleNo

Product number of supplier

Contained when configured in shop related export configuration
character varying
customsTariffNo

Customs tariff number

Contained when configured in shop related export configuration
character varying(11)

5.2.2 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

6 Troubleshooting

6.1 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.

6.2 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.

6.3 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.

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