Product integration from Norce to ERP

When products are first created in Norce and then later created in the ERP.

Overview

Fig

Event and Query

To get data out from Norce and into the ERP system, you need to listen to changes in Norce Commerce, using Event and lookup the specifics with Query.

For Norce [Storm] clients:

Important information

The most important fiels and their purpose:

Field Entity Description Usage
PartNo ProductSku Norce generates the product identifier that should be used by the ERP system. It is possible to set up rules for the partno generation in the Admin UI When products are created in Norce, use the seed generation preferrably.
Status ProductSku Depict the life of the product. Active (1), Coming (2), Expiring (3), Hidden (4), Inactive (5) New products are usually "coming" until they are ready to be sellable.
Type ProductSku The type of product in Norce. These are defined in Norce, but most are not activated by default for a client. The most common are: Standard (1), Freight (3), ManagedStructure (37), Service (11), Extended (14), ManagedERPPackage (38), see xxx for more information on types Nor all products are created in the ERP, for example ManagedStructure and Extended are commonly left in Norce, see more about Extended here (coming)
Name Product, ProductCulture A short name of the product
EanCode ProductSku Primary GS1/EAN code on the product. There can be more, see postman examples on how to get those
Manufacturer Product Required on all products in Norce, together with ManufacturerPartNo it must be unique in the product catalog
ManufacturerPartNo Product Required on all products in Norce, together with Manufacturer it must be unique in the product catalog
(Supplier)PartNo, SupplierId SupplierSkus If the client has supplier information in Norce, one or more supplier skus can be related to the product. Each have supplier and a supplier PartNo. See more on suppliers here
CategoryId Category Each product should have a primary category. For new products this might be used to mark the product with sufficient information to the ERP so that it will be created correctly. See below
VatCodeId SalesArea For each sales area the product will have a vat rate When vatcodeid is null it is inherited from primary category (or from application)
ParametricId, Values, ListId, MultipleListId ProductParametric, *Multiple Products can have parametrics, use metadata to lookup more information, see below Not all parametrics is needed in the ERP, maybe none. Which ones are different for each client.
FlagId ClientProductFlag Product can have many flags, use metadata to lookup more information, see below Not all flags is needed in the ERP, maybe none. Which ones are different for each client.
An example fetching the important information from Commrce Query
Copy
Copied
https://query.lab.storm.io/2.0/Products/ProductSkus('ThePartNo')?$expand=Product($select=DefaultName,ManufacturerId,ManufacturerPartNo;$expand=Categories($filter=IsPrimary eq true;$select=CategoryId),SupplierSkus($select=PartNo,SupplierId),Parametrics($select=ParametricId,DefaultVarcharValue,DefaultHtmlValue,IntegerValue,DecimalValue,BooleanValue,DateValue,ListId,NormalizedValue),ParametricMultiple($select=ParametricId,MultipleId),Flags($select=FlagId,IsActive)),SalesAreas($select=SalesAreaId,VatCodeId)&$select=PartNo,StatusId,TypeId,EanCode,PriceLists
Copy
Copied
{
    "@odata.context": "https://query.lab.storm.io/Query/2.0/Products/$metadata#ProductSkus(PartNo,StatusId,TypeId,EanCode,PriceLists,Product(DefaultName,ManufacturerId,ManufacturerPartNo,Categories(CategoryId),SupplierSkus(PartNo,SupplierId),Parametrics(ParametricId,DefaultVarcharValue,DefaultHtmlValue,IntegerValue,DecimalValue,BooleanValue,DateValue,ListId,NormalizedValue),ParametricMultiple(ParametricId,MultipleId),Flags(FlagId,IsActive)),SalesAreas(SalesAreaId,VatCodeId))/$entity",
    "PartNo": "PRD0001270",
    "StatusId": 1,
    "TypeId": 1,
    "EanCode": "123123123123123",
    "Product": {
        "ManufacturerId": 1685,
        "ManufacturerPartNo": "T540XP",
        "DefaultName": "T540XP",
        "Categories": [
            {
                "CategoryId": 41722
            }
        ],
        "SupplierSkus": [
            {
                "PartNo": "123456122433",
                "SupplierId": 1545
            },
            {
                "PartNo": "3214325436543",
                "SupplierId": 5246
            }
        ],
        "Parametrics": [
            {
                "ParametricId": 1726,
                "DefaultVarcharValue": null,
                "DefaultHtmlValue": null,
                "IntegerValue": null,
                "DecimalValue": null,
                "BooleanValue": true,
                "DateValue": null,
                "ListId": null,
                "NormalizedValue": 1.000
            },
            {
                "ParametricId": 16057,
                "DefaultVarcharValue": "Husqvarna T540 XP is developed for the exclusive top segment of saws with top handles focusing on professional customers such as arborists, gardeners, gardeners and property caretakers who need excellent performance combined with the best possible ergonom",
                "DefaultHtmlValue": null,
                "IntegerValue": null,
                "DecimalValue": null,
                "BooleanValue": null,
                "DateValue": null,
                "ListId": null,
                "NormalizedValue": null
            },
            {
                "ParametricId": 16061,
                "DefaultVarcharValue": null,
                "DefaultHtmlValue": null,
                "IntegerValue": null,
                "DecimalValue": null,
                "BooleanValue": null,
                "DateValue": null,
                "ListId": null,
                "NormalizedValue": null
            }
        ],
        "ParametricMultiple": [
            {
                "ParametricId": 16061,
                "MultipleId": 44776
            },
            {
                "ParametricId": 16061,
                "MultipleId": 44778
            },
            {
                "ParametricId": 16061,
                "MultipleId": 44780
            }
        ],
        "Flags": [
            {
                "FlagId": 565,
                "IsActive": true
            },
            {
                "FlagId": 915,
                "IsActive": true
            }
        ]
    },
    "SalesAreas": [
        {
            "SalesAreaId": 1,
            "VatCodeId": null
        },
        {
            "SalesAreaId": 2,
            "VatCodeId": null
        }
    ]
}

Optional metadata

To get more information on entities referred only with an id in query, you need to to extra lookups. Make sure to lookup many at a time, instead of doing lookups for each product fetch. Put the response in your memory cache instead.

Category

Additional informmation on the category could be information needed to create the product correctly in the ERP. Fetch the DefaultName, code and id and other defaultvalues, if they are nul on the ProductSku or Product level.

Field Entity Description
Id Category You get the Id from the ProductSku lookup above
Code Category
DefaultName Category
BusinessArea Category Use this field if you need to mark the category with information that is needed when creating the New Product in the ERP.
ParentCategoryId CategoryHierarchy If you need to look up the categorytree to a level above
Hierarchy CategoryHierarchy The category hierarchy level (1 is first level 2 and up is the lower levels)

TODO: Add Parametric Group to query

An example fetching category information from Commerce Query
Copy
Copied
https://query.lab.storm.io/2.0/Application/Categories?$select=Id,Code,DefaultName

https://query.lab.storm.io/2.0/Application/CategoryHierarchy
Copy
Copied
{
    "@odata.context": "https://query.lab.storm.io/Query/2.0/Application/$metadata#Categories(Id,Code,DefaultName)",
    "value": [
        {
            "Id": 2192,
            "Code": null,
            "DefaultName": "Bild"
        },
        {
            "Id": 2193,
            "Code": null,
            "DefaultName": "TV"
        },
        {
            "Id": 2194,
            "Code": null,
            "DefaultName": "LCD"
        },
        {
            "Id": 2195,
            "Code": null,
            "DefaultName": "LED"
        },
        {
            "Id": 2196,
            "Code": null,
            "DefaultName": "Tillbehör"
        },
        {
            "Id": 2197,
            "Code": null,
            "DefaultName": "Digital-TV"
        },
        ...
    ]
}
Copy
Copied
{
    "@odata.context": "https://query.lab.storm.io/Query/2.0/Application/$metadata#CategoryHierarchy",
    "value": [
        {
            "CategoryId": 2192,
            "ParentCategoryId": 2192,
            "Hierarchy": 1
        },
        {
            "CategoryId": 2193,
            "ParentCategoryId": 2192,
            "Hierarchy": 1
        },
        {
            "CategoryId": 2193,
            "ParentCategoryId": 2193,
            "Hierarchy": 2
        },
        {
            "CategoryId": 2202,
            "ParentCategoryId": 2192,
            "Hierarchy": 1
        },
        {
            "CategoryId": 2202,
            "ParentCategoryId": 2200,
            "Hierarchy": 2
        },
        {
            "CategoryId": 2202,
            "ParentCategoryId": 2202,
            "Hierarchy": 3
        },
        ...
    ]
}

Flags

Depending on the setup for the client, flags might contain important extra information needed on the product in the ERP.

The reason to lookup metadata on flags is to get Code or Group information, to be able to decide which flags that are interesting for the ERP.

Field Entity Description
Id ProductFlags
Code ProductFlags
TypeId ProductFlags Product flag (1) or Variant flag (2)
DefaultName ProductFlags
(Group)Id ProductFlagGroup
(Group)DefaultName ProductFlagGroup

TODO: Add group code to query

An example fetching flag information from Commrce Query
Copy
Copied
https://query.lab.storm.io/2.0/Application/ProductFlags?$select=Id,TypeId,DefaultName,Code&$expand=Group($select=Id,Code,DefaultName)
Copy
Copied
{
    "@odata.context": "https://query.lab.storm.io/Query/2.0/Application/$metadata#ProductFlags(Id,TypeId,GroupId,DefaultName,DefaultDescription,IsManuallyEditable,Code,Cultures(CultureCode,Name,Description),Group(Id,DefaultName,DefaultDescription,Cultures(CultureCode,Name,Description)))",
    "value": [
        {
            "Id": 42,
            "TypeId": 1,
            "GroupId": 9,
            "DefaultName": "Utförsäljning",
            "DefaultDescription": null,
            "IsManuallyEditable": true,
            "Code": "sale",
            "Group": {
                "Id": 9,
                "DefaultName": "Manual flags",
                "DefaultDescription": "Manually defined product flags",
            }
        }
    ]
}

Parametrics

Depending on the setup for the client, flags might contain important extra information needed on the product in the ERP.

The reason to lookup metadata on parametrics is to get Code and list metadata information, to be able to decide which parametrics that are interesting for the ERP.

Field Entity Description
Id Parametric
Code Parametric
DefaultName Parametric
(List)Id ParametricList
(List)Code ParametricList
DefaultVarcharValue ParametricList
(Multiple)Id ParametricMultiple
(Multiple)Code ParametricMultiple
DefaultValue ParametricMultiple
An example fetching parametric information from Commerce Query API
Copy
Copied
https://query.lab.storm.io/2.0/Application/Parametrics?$select=Id,DefaultName,Code

https://query.lab.storm.io/2.0/Application/ParametricListValues?$select=Id,ParametricId,DefaultVarcharValue,Code

https://query.lab.storm.io/2.0/Application/ParametricMultipleValues?$select=Id,ParametricId,DefaultValue,Code
Copy
Copied
{
    "@odata.context": "https://query.lab.storm.io/Query/2.0/Application/$metadata#Parametrics(Id,DefaultName,Code)",
    "value": [
        {
            "Id": 1684,
            "DefaultName": "Anitamodell",
            "Code": null
        },
        {
            "Id": 1714,
            "DefaultName": "Fjärrkontroll",
            "Code": "fjarrkontroll"
        },
        ...
    ]
}
Copy
Copied
{
    "@odata.context": "https://query.lab.storm.io/Query/2.0/Application/$metadata#ParametricListValues(Id,ParametricId,DefaultVarcharValue,Code)",
    "value": [
        {
            "Id": 2145,
            "ParametricId": 1668,
            "DefaultVarcharValue": "4:3",
            "Code": "4-3"
        },
        {
            "Id": 2146,
            "ParametricId": 1668,
            "DefaultVarcharValue": "16:9",
            "Code": "16-9"
        }
        ...
    ]
}
Copy
Copied
{
    "@odata.context": "https://query.lab.storm.io/Query/2.0/Application/$metadata#ParametricMultipleValues(Id,ParametricId,DefaultValue,Code)",
    "value": [
        {
            "Id": 1505,
            "ParametricId": 1671,
            "DefaultValue": "DVB-T",
            "Code": null
        },
        {
            "Id": 1506,
            "ParametricId": 1671,
            "DefaultValue": "DVB-C",
            "Code": null
        },
        {
            "Id": 1507,
            "ParametricId": 1671,
            "DefaultValue": "DVB-C",
            "Code": null
        },
        ...
    ]
}

Build in attributes in Norce

There are many other fields in norce that can be of use, depending on the case. Here is an overview:

Field Entity Description
IntegrationPartNo / DefaultIntegrationPartNo ProductSku / Category An optional identifier, use this if the ERP should has mapped several products in Norce to one integration product in the ERP
CommodityCode / DefaultCommodityCode ProductSku / Category
Grossweight / DefaultGrossWeight ProductSku / Category
UnitOfMeasurementId / DefaultUnitOfMeasurementId ProductSku / Category Pieces (1), Hours (2), Kilogram (3), Litres (4), Pairs (5), Square meters (6), Meters (7)
UnitOfMeasurementCount ProductSku Number of items in a sku
IsDropShipOnly / DefaultIsDropShipOnly ProductSku / Category
LogisticWidth ProductSku
LogisticHeight ProductSku
LogisticDepth ProductSku
IsDangerousGoods Product Marks if the product is classified as dangerous goods and can only be shipped through specific delivery methods
Code Product Variant identifier, all product entities with same code are groups as variants to each other

Sales areas

Finding the Vatcode for a product is resolved from the settings on the Sales Area entities. If the vatcode is null on one level, check the next, follow the priority order below.

  1. ProductSkuSalesAreas (expands from ProductSku, see above) returns vatcodes set up on the product level.
  2. CategorySalesAreas (expands from Category, see above)
  3. SalesAreas (its own endpoint), this entity has all default information for the Sales areas, currencyid and VatCodeId.

Additionally, lookup ApplicationSalesArea to get primary sales area for the application, and lookup ClientSalesArea to get the external Code, if needed.

Field Entity Description
SalesAreaId or Id ProductSkuSalesArea / CategorySalesArea / SalesArea Product and Category returns all sales areas that are set up for the client, but VatCode can be null.
DefaultName SalesArea A vat code region in clear text.
DefaultCurrencyId SalesArea Norce configured default currency, lookup currencies separately if needed
DefaultVatCodeId SalesArea Norce configured default vatcode for the specific sales area
IsPrimary ApplicationSalesArea One sales area only can be default for an application
Code ClientSalesArea The configured code if needed
Fetching sales area information from Norce Commerce Query
Copy
Copied
https://query.lab.storm.io/2.0/Core/SalesAreas?$select=Id,DefaultName,DefaultCurrencyId,DefaultVatCodeId

https://query.lab.storm.io/2.0/Application/ApplicationSalesAreas?$ApplicationId,SalesAreaId,IsPrimary,IsActive

https://query.lab.storm.io/2.0/Application/SalesAreas?$select=SalesAreaId,IsPrimary,Code

https://query.lab.storm.io/2.0/Core/Currencies

Other core entities

Fetch manufacturers and suppliers to lookup the codes, which is usually the identifiers recognized by other systems like the ERP.

Field Entity Description
ManufacturerId ClientManufacturer
ManufacturerCode ClientManufacturer
(Manufacturer)Name ClientManufacturer
SupplierId ClientSupplier
SupplierCode ClientSupplier
(Supplier)Name ClientSupplier
Fetching parametric information from Commerce Query API
Copy
Copied
https://query.lab.storm.io/2.0/Application/ClientManufacturers?$select=ManufacturerId,ManufacturerCode,Name

https://query.lab.storm.io/2.0/Application/ClientSuppliers?$select=SupplierId,SupplierCode,Name

Event settings

The primary event type needed is SkuChangedNotification, which listens to changes to anything regarding the products. There is quite a lot of settings that can be user, read more about what they mean on the page about event settings. Here is an example with explanations.

Entity On Insert On Update On Delete Filter
tCategorySalesArea No Yes (vatcodeid) No Filter on relevant sales areas for the ERP (commonly only one)
tClientProductSku Yes Yes (choose only the fields the erp needs updates on) No Filter on erp-enables product types only (usually 'Standard' only)
tClientProductSkuSalesArea Yes Yes (vatcodeid) No Filter on relevant sales areas for the ERP (commonly only one)
tProduct Yes Yes (choose only the fields the erp needs updates on) No n/a
tProductFlag (*) Yes Yes (flagId, isactive) No Filter on only the flags that the erp is interested in
tProductParametric (*) Yes Yes (only the types of values needed) Yes n/a
tProductParametricMultiple (*) Yes (only of multple type is needed) n/a Yes n/a

(*) Flags and paramteric is only required if the client has som specific information on them that is needed in the ERP.

Note, onhand and price is discussed on other pages, but could also be added here to the same event settings.

Settings in the Admin UI

Event settings 1

Event settings 2

Further reading