Database changes for Commerce inventory service

Introduction

This document describes changes done to the database schema for the inventory service used when extracting inventory data directly from the EPiServer Commerce database. Refer to Inventory requests for information on how to access warehouse inventory information.

Accessing inventory information

Direct database access is usually not a recommended practice. The database schema is considered an internal artifact of the system and database schema changes may occur at any release without affecting the semantic versioning scheme. This means that a breaking change such as changing tables or table names in the database schema will not result in a major version update of EPiServer Commerce.

Instead the recommended approach is to access this information either through the EPiServer Service API, or the inventory API:s.

Database schema changes

The modifications to the databse schema involves migration of information from the old WarehouseInventory table to the new InventoryService table. If you have reporting running against the old WarehouseInventory table, this table will not receive any new or updated information, but the existing information will remain.

Database tables

Below is an overview of the two tables.

Migration script

When migrating data between the systems, the script below will be executed.

insert into [dbo].[InventoryService]
(
[ApplicationId],
[CatalogEntryCode],
[WarehouseCode],
[IsTracked],
[PurchaseAvailableQuantity],
[PreorderAvailableQuantity],
[BackorderAvailableQuantity],
[PurchaseRequestedQuantity],
[PreorderRequestedQuantity],
[BackorderRequestedQuantity],
[PurchaseAvailableUtc],
[PreorderAvailableUtc],
[BackorderAvailableUtc],
[AdditionalQuantity],
[ReorderMinQuantity]
)
select
wi.[ApplicationId],
wi.[CatalogEntryCode],
wi.[WarehouseCode],
cast(case wi.[InventoryStatus] when 1 then 1 else 0 end as bit) as [IsTracked],
wi.[InStockQuantity] - wi.[ReservedQuantity] as [PurchaseAvailableQuantity],
wi.[PreorderQuantity] as [PreorderAvailableQuantity],
wi.[BackorderQuantity] as [BackorderAvailableQuantity],
0 as [PurchaseRequestedQuantity],
0 as [PreorderRequestedQuantity],
0 as [BackorderRequestedQuantity],
ce.[StartDate] as [PurchaseAvailableDate],
case
when wi.[AllowPreorder] = 1 and wi.[PreorderAvailabilityDate] < ce.[StartDate] then wi.[PreorderAvailabilityDate]
else ce.[StartDate]
end as [PreorderAvailableUtc],
wi.[BackorderAvailabilityDate] as [BackorderAvailableUtc],
wi.[ReservedQuantity] as [AdditionalQuantity],
wi.[ReorderMinQuantity]
from [dbo].[WarehouseInventory] wi
join [dbo].[CatalogEntry] ce on wi.[ApplicationId] = ce.[ApplicationId] and wi.[CatalogEntryCode] = ce.[Code]

See also

• Inventory requests

Last updated: Oct 07, 2014