Loading...
Area: Episerver Commerce
Applies to versions: 10 and higher
Other versions:

Database changes for inventory service

Recommendations [hide]

This topic describes changes done to the database schema for the inventory service used when you extract inventory data directly from the Episerver Commerce database. See Inventory requests for information about accessing warehouse inventory information.

Accessing inventory information 

Direct database access usually is not a recommended practice. Instead, you should access this information either through the Episerver Service API or the inventory APIs.

The database schema is considered an internal artifact of the system. 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, does not result in a major version update of Episerver Commerce.

Database schema changes

The modifications to the database schema involve 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 remains.

Commerce 11 and higher

Migration script

When you migrate data between the systems, the following script is executed.

Commerce 11 and higher

insert into [dbo].[InventoryService]
  (
    [CatalogEntryCode],
    [WarehouseCode],
    [IsTracked],
    [PurchaseAvailableQuantity],
    [PreorderAvailableQuantity],
    [BackorderAvailableQuantity],
    [PurchaseRequestedQuantity],
    [PreorderRequestedQuantity],
    [BackorderRequestedQuantity],
    [PurchaseAvailableUtc],
    [PreorderAvailableUtc],
    [BackorderAvailableUtc],
    [AdditionalQuantity],
    [ReorderMinQuantity]
  ) 
select
  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.[CatalogEntryCode] = ce.[Code]
Do you find this information helpful? Please log in to provide feedback.

Last updated: Oct 24, 2016

Recommendations [hide]