Guide - IOM Product Export (2.1 - 2.2)

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.

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.

Process-Description

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

Every minute checks an OMS internal job the database table product."ArticleExportConfigDO", whether an export must take place. Depending on the configuration, a full- or increment 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 (prodcut." ArticleExportConfigDO"). Every increment will build on the last full export. A full export cannot be reverted, an increment yet. (see Guide - IOM Product Export (from 2.1 to 2.2)#Troubleshooting)

The by the export process generated CSV files 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

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

3.1 ArticleExportConfigDO

Defines in general a product export 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, then 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 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.NULL
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 informations about the supplier(supplierRef, supplierName and supplierrArticleNo)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 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 article 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 at 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

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

It defines products which category of goods to be exported. If you want to export, without having any restrictions on the product categories, then 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

Opportunity 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 varying(50)Manufacturer 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

Opportunity 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

 Opportunity 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 

Opportunity 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

Opportunity 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(255)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

Opportunity to filter products by the supplier specific sales code, like 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

Opportunity 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 column 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

Opportunity to filter products 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

Opportunity 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 make 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 Purchase Price

Opportunity to filter products by their purchase price.

Via the flag "isPurchasePriceRequired" you can filter out products without a purchase price. This flag should be true in case of filtering by a maximum or minimum value of the purchase price. The columns "purchasePriceMax" and "purchasePriceMin" will be combined via OR at the execution.

4.10.1 Elements of ArticleExportFilterPurchasePriceDO

Number#Column Name (Case-Sensitive)TypeDescriptionExample
1idbigintID / primary key1
2isPurchasePriceRequiredbooleanFlag purchase price required (true/false)true
3purchasePriceMax (deprecated since 2.2)numeric(15,5)Filter products by a maximum value of the attribute purchase price    1000
4purchasePriceMin (deprecated since 2.2)numeric(15,5)

Filter products by a minimum value of the attribute purchase price

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

4.10.2 Example ArticleExportFilterPurchasePriceDO

INSERT INTO product."ArticleExportFilterPurchasePriceDO"(
				"id", "isPurchasePriceRequired", "purchasePriceMax", "purchasePriceMin", "articleExportConfigRef")
			VALUES (nextval('product."ArticleExportFilterPurchasePriceDO_id_seq"'), true, 1000, null, 123);

4.11 Filter by Sales Price

Opportunity to filter products by their sales price.

Via the flag "isSalesPriceRequired" you can filter out products without a sales price. This flag should be true in case of filtering by a maximum or minimum value of the sales price. The columns "salesPriceMax" and "salesPriceMin" will be combined via OR at the execution.

4.11.1 Elements of ArticleExportFilterSalesPriceDO

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

Attributes sales price must be available

true
3salesPriceMax (deprecated since 2.2)numeric(15,5)Filter products by a maximum value of the attribute sales price999.99
4salesPriceMin (deprecated since 2.2)numeric(15,5)Filter products by a minimum value of the attribute sales priceNULL
5articleExportConfigRefbigintReference to the export configuration / product."ArticleExportConfigDO"123

4.11.2 Example ArticleExportFilterSalesPriceDO 

INSERT INTO product."ArticleExportFilterSalesPriceDO"(
				"id", "isSalesPriceRequired", "salesPriceMax", "salesPriceMin", "articleExportConfigRef")
			VALUES (nextval('product."ArticleExportFilterSalesPriceDO_id_seq"'), true, 999.99, NULL, 123);

4.12 Filter by Stock Type

Opportunity to filter products by their stock type.

Currently the OMS support 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). Product name is identical to supplier-specific product name.

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

where,

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

Example file name: 10001_XYZ_7.00000_StandardBasicV10_Full_20160419101200.csv

5.1.1 Elements of Basic Data CSV File

The following table illustrates the elements of CSV file:

Column NameDescription

Field-Type

exportVersionVersion of export -  consecutive numberNumeric(15,5)
articleRefOMS internal product IDBigInt
manufacturerIdentifierUnique identification property of manufacturerCharacter(50)
manufacturerName of manufacturerCharacter(50)
manufacturerArticleNoProduct number of manufacturerCharacter(30)
articleNameName of productCharater(250)
EANEAN of productCharacter(20)
ISBNISBN of productCharacter(20)
lengthLength in mmInteger
heightHeight in mmInteger
widthWidth in mmInteger
weightWeight in kgNumeric(15,5)
assortmentNameName of commodity groupCharacter(250)
assortmentIdentifierUnique identification property of commodity groupCharacter(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(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(10)
shopArticleNoProduct number of shopCharacter(30)
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 only at Incremental exports.

Character(10)
editionDepiction of product edition. Book edition of a publishing company.Character(5)
packagingUnitPackaging unitCharacter(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

where,

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

Example file name: 10001_XYZ_7.00000_StandardDynamicDataV10_Full_20160419101200.csv

5.2.1 Elements of Dynamic Data CSV File

The following table illustrates the elements of 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(30)
currencyCurrency according to ISO 4217
Applies to all prices of a line in the file
Character(5)
purchasePrice (deprecated since 2.2)

Purchase price for the shop

Numeric(15,5)
listPrice (deprecated since 2.2)
  • 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)
salesPrice

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
salesPriceOld (deprecated since 2.2)Old sales price of shopNumeric(15,5)
provisionType (deprecated since 2.2)Provision typeCharacter(10)
provisionPercentage (deprecated since 2.2)Percentage of provisionNumeric(15,5)
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(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(50)
customsTariffNo

Customs tariff number

Contained, when configured in shop related export configuration
Character(11)

5.2.2 Example Dynamic Data CSV File

exportVersion|articleRef|shopArticleNo|currency|purchasePrice|listPrice|stockLevel|stockType|availabilityInDays|marketPriceMin|marketPriceMax|marketPriceAverage|salesPrice|basicPrice|basicPriceUnitValue|basicPriceUnit|salesPriceOld|provisionType|provisionPercentage|articleState|supplierName|supplierRef|supplierArticleNo|customsTariffNo
7.00000|675916||EUR|21.17000||35|1|1||||29.99000||||||21.17000|1|4008838000007|1008|75941800|73239300
7.00000|675917||EUR|14.11000||12|1|1||||19.99000||||||14.11000|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 export again. The following steps are to do for all products, that inherit 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 an full export. You can only run a full export again.

  1. 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).
  • Product has no price in product."ArticleSalesPriceDO".
  • Product has no entry in product."ArticleNameDO".
  • 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