Manually Updating Product Inventory Levels

Overview

Hotcakes Commerce allows you to easily manage inventory for standard scenarios.  However, in some instances, the inventory levels may appear to be invalid.  This is especially apparent if you see Quantity Available levels that appear to be way off, such as having negative values (e.g., -12).  This generally occurs due to one of the following:

• The store was developed and tested thoroughly, then deployed into production without resetting the inventory levels.  For example, testing various payment and shipping method scenarios without properly deleting the orders and/or using the inventory replenishment features.
• The daily store administration customer service workflow is not resetting the inventory levels when editing or deleting an order.

Requirements

The following prerequisites will be necessary to accomplish the goals of this article:

• Have a superuser account
• Determine whether or not you wish to track and manage inventory
• Familiar with and/or knowledgeable in the SQL Server database language T-SQL

Getting Started

There are no prerequisites to continuing with this article.

Manually Updating Products

In order to update the inventory levels, first, log in using a superuser account.  Next, go to the SQL Console using the control panel or persona bar.

Next, paste in the example SQL code and update it to match your intended inventory levels and product ID (also known as product BVIN).  

DECLARE @ProductBvin NVARCHAR(150), @OnHand INT, @Reserved INT;

SET @ProductBvin = '';
SET @OnHand = ;
SET @Reserved = ;

SELECT pi.* 
FROM {databaseOwner}[{objectQualifier}hcc_ProductInventory] pi
WHERE pi.[ProductBvin] = CAST(@ProductBvin AS UNIQUEIDENTIFIER);

UPDATE {databaseOwner}[{objectQualifier}hcc_ProductInventory]
SET
	[QuantityOnHand] = @OnHand,
    [QuantityReserved] = @Reserved
WHERE [ProductBvin] = CAST(@ProductBvin AS UNIQUEIDENTIFIER);

SELECT pi.* 
FROM {databaseOwner}[{objectQualifier}hcc_ProductInventory] pi
WHERE pi.[ProductBvin] = CAST(@ProductBvin AS UNIQUEIDENTIFIER);

Variable	Description
ProductBvin	You can find this value by navigating to the product detail editing view.  The URL will look like the example below.  You'll want to use your respective ID value of the product highlighted in the query string. https://example.com/DesktopModules/Hotcakes/Core/Admin/Catalog/Products_Edit.aspx?id=c1192659-09f0-4d7d-8ba3-4c28c4defa0c
OnHand	This should be the quantity of the product you have ready to be sold, minus the amount that is on-hand for orders that haven't yet been fulfilled.
Reserved	This quantity should reflect the number of products you have on-hand for orders that are currently being fulfilled.  For example, if there is one order of 5 of the product, then this value should be 5.

 

There will be two resulting queries displayed once you run the updated query.  This first one will reflect the values in the database before the updates are made.  The second one will show you the results of the updates.  This will help you resolve any mistakes that may have been made, if necessary.

Special Note:
You may be wondering about the QuantityAvailableForSale column you may have seen in the database.  You should not update this value for any reason, as it's automatically calculated on your behalf.

The video below shows you how to use the SQL Console tool: