Modifying the Order Number Sequence in Optimizely B2B Commerce

Introduction

At some point during the life of your Optimizely B2B web solution, you may find yourself in need of updating the next Order Number to be used when an order is placed. One case where this might be needed is if you are migrating your site’s database from one environment to another. This can cause desynchronization between the site’s current OrderNumber and the WebOrderNumber in your ERP. The website may appear to be functional, but there’s going to be some headaches when the ERP tries to process a duplicate order number that has already been submitted.

How does Opti B2B increment order numbers?  

Optimizely B2B’s database uses an auto-incrementing identity column, SeqOrderNumber, in the dbo.SeqOrderNumber table to tell the website which order number to use at the point of order creation. 

Updating an identity column isn't as straight forward as updating standard data points in SQL because well, in short, they aren’t meant to be updated. To change this value, we will need to give ourselves permission to insert into this column, insert a new record with the updated value, then remove the old record, and then remove the overridden permission.  

Note: If you need assistance running this or would like to use an integration job that XCentium has developed to update your Order Number sequence without any development involved, please reach out! 

-- Temporarily set identity_insert to ON 
SET IDENTITY_INSERT SeqOrderNumber ON 
GO 

-- Declare and set the new order number value 
DECLARE @NewOrderNumber INT 
SET @NewOrderNumber = 4500 

-- Check if the new number isn't the current number 
IF NOT EXISTS (select 1 from SeqOrderNumber where SeqOrderNumber = @NewOrderNumber) 
BEGIN 

-- Insert a new record for the Order Number 
INSERT INTO SeqOrderNumber(SeqOrderNumber, Prefix) VALUES(@NewOrderNumber, [ORD]) 

-- Delete the previously entered Order Number 
DELETE FROM SeqOrderNumber WHERE SeqOrderNumber != @NewOrderNumber 
END 
  
-- Finally set the identity_insert back to OFF 
SET IDENTITY_INSERT SeqOrderNumber OFF 
GO 

In some cases, you may also need to update the next Customer Number that the site uses upon customer creation. The Customer Number sequence is stored in the same way as the order number, so we can adjust the above script to also work with the SeqCustomerNumber table: 

-- Temporarily set identity_insert to ON 
SET IDENTITY_INSERT SeqCustomerNumber ON 
GO  

-- Declare and set the new order number value 
DECLARE @NewCustomerNumber INT 
SET @NewCustomerNumber = 4500 

-- Check if the new number isn't the current number 
IF NOT EXISTS (select 1 from SeqCustomerNumber where SeqCustomerNumber = @NewCustomerNumber) 
BEGIN  

-- Insert a new record for the Order Number 
INSERT INTO SeqCustomerNumber(SeqOrderNumber, Prefix) VALUES(@NewCustomerNumber,'CUS') 

-- Delete the previously entered Order Number 
DELETE FROM SeqCustomerNumber WHERE SeqCustomerNumber != @NewCustomerNumber 
END 

-- Finally set the identity_insert back to OFF 
SET IDENTITY_INSERT SeqCustomerNumber OFF 
GO 

Another table used for storing sequential numbers used by the platform is SeqContentKey, which assigns unique numbers to content items created via the CMS. It is very unlikely that this number would need to be updated unless you are manually migrating content from one database into another. If you did need to update it, the same type of SQL script could likely be used to alter its identity column.

If you have any questions, please feel free to reach out at kevin.marks@xcentium.com