Don't miss out Virtual Happy Hour this Friday (April 26).
Try our conversational search powered by Generative AI!
AI OnAI Off
Loading...
ARCHIVED This content is retired and no longer maintained. See the latest version here.
Recommended reading
Database changes for Commerce inventory service
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 the recommended approach is to access this information either through the Episerver Service API, or the inventory APIs.
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.
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. The following image shows the two tables.
Migration script
When you migrate data between the systems, the following script is 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]Do you find this information helpful? Please log in to provide feedback.
Last updated: Oct 12, 2015
Recommended reading
