Database changes for inventory service

Access inventory information

Direct database access is not recommended. Access this information through the Optimizely 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 Commerce Connect.

See Inventory requests for information about warehouse inventory access.

Database schema changes

The modifications to the database schema involve migration of information to the new InventoryService table from the old WarehouseInventory 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. Compare the column lists to map new fields to their old equivalents.

Migration script

When you migrate data between the systems, the following script runs.

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]