12. Stock Discrepancies

Description

This report is available from Stores > Configuration > Inventory Management > Stock Discrepancies by clicking on the Display button.

It can be updated :

  • Using the Update Report button at the top right corner, for small products catalog.

  • Running the following command line, for large products catalog :

    bin/magento bms_advancedstock:check_stock_discrepencies
    

If some errors are displayed once report has been updated, you can try to fix them automatically :

  • Using the Fix errors button, for small products catalog.

  • Adding the --fix option to the previous command line, for large products catalog :

    bin/magento bms_advancedstock:check_stock_discrepencies --fix
    

Warning

If you only run the command line :

bin/magento bms_advancedstock:check_stock_discrepencies --fix

Errors will be fixed, but the report will not be updated and still display them. To update it please run the command line again.

Note

You can execute/fix each probe of the stock discrepencies separately.

For it, add an --analyser PROBE_CODE instruction at the end of the basic command line, as following :

bin/magento bms_advancedstock:check_stock_discrepencies --fix --analyser PROBE_CODE

Where PROBE_CODE has to be replaced by the code of the probe you want to execute/fix.

For reference, here are each probe code :

Probe Name Probe Code
Missing Warehouse items missing_warehouse_items
Missing Stock for website (table cataloginventory_stock) missing_stock
Unconsistant Stock for website (table cataloginventory_stock) unconsistant_stock
Missing Stock items (table cataloginventory_stock_item) missing_stock_items
Warehouse items with negative physical quantity wrong_warehouse_item_quantity
Stock items with negative physical quantity wrong_stock_item_quantity
Quantity in stock item negative_stock
Quantity to ship in warehouse item doesn’t match to the pending orders wrong_quantity_to_ship
Unconsistant reserved quantity at warehouse item level unconsistant_reserved_quantity
Missing extended information for sales order item missing_extended_sales_flat_order_items
Wrong extended information for sales order item wrong_extended_sales_flat_order_items
Stock items with quantity NULL (not zero) stock_item_with_null_quantity
Products with a quantity sellable but out of stock products_not_sellable
Products without stock, no backorder, but sellable products_sellable_that_should_not

Note

If you get a “memory exhausted” error executing the stock discrepencies command line, like :

Fatal error: Allowed memory size of XXX bytes exhausted (tried to allocate XXX bytes)

You can solve this problem setting a “-d memory_limit” instruction at the start of the php command line :

php -d memory_limit=2G  bin/magento bms_advancedstock:check_stock_discrepencies --fix

If the problem persists, please increase the value from “2G” to an higher one, until it’s solved.

Probes

Here is described the role of each probe of the stock discrepencies report.

Missing Warehouse items

Every products should have an entry in the table bms_advancedstock_warehouse_item for each ERP warehouses.

If some entries are missing, the Fix errors button will try to recreate them automatically.

Missing Stock for website (table cataloginventory_stock)

The cataloginventory_stock table should have 1 row per Magento website, and an aditional one for “Admin” (corresponding to Magento Admin Panel data).

Note

Websites list is available from Magento menu Stores > All Stores

Let’s take the following example :

_images/stores_list.png

As we can see, there are 2 different websites available in Magento.

Following this example, the cataloginventory_stock table should look like it :

_images/stores_list_database.png
  • We find back Admin row at the top of the table, with a stock_id equals to 1, and a website_id equals to 0.
  • Then, we find one row per Magento website. in our example there were 2 different websites in Magento, so we find 2 lines in the cataloginventory_stock table.

Unconsistant Stock for website (table cataloginventory_stock)

As explained in the “Missing Stock for website” section, the cataloginventory_stock table should have 1 row per Magento website.

It can happen, after a Magento 1 data import for example, to have some “website_id” added to this table without even existing in Magento 2 stores list (Stores > All Stores), due to the fact websites were existing in Magento 1 but have not been created yet in Magento 2.

These rows have to be removed from this table to prevent any problem in Magento.

Warning

The stock discrepencies “Fix” button will not correct this kind of error itself, a manual database intervention will be required.

Missing Stock items (table cataloginventory_stock_item)

Every products should have an entry in the table cataloginventory_stock_item for each Magento website.

If some entries are missing, the “Fix errors” button will try to recreate them automatically.

Warehouse items with negative physical quantity

This probe will detect if some of your products have a negative quantity in the table bms_advancedstock_warehouse_item.

The “Fix errors” button will update these entries to a 0 quantity value.

Stock items with negative physical quantity

This probe will detect if some of your products have a negative quantity in the table cataloginventory_stock_item.

The “Fix errors” button will update these entries to a 0 quantity value.

Quantity in stock item

As a reminder, in ERP Magento 2 version, the cataloginventory_stock_item table contains 1 row per website for each product.

We will store in these rows the sum of warehouses available qty (the ones allowed for sales), for each website.

As example, it can happens that some third party modules will update qty values in the cataloginventory_stock_item table directly, making it impossible for ERP to detect these changes.

Result will be a difference between the sum of warehouses available qty (stored in the bms_advancedstock_warehouse_item table) and the qty value stored in cataloginventory_stock_item table.

This probe will list these stock differences, and fix it by calculating again the sum of warehouses available qty (the ones allowed for sales), then copying this value in the cataloginventory_stock_item table.

Quantity stored in warehouse item doesn’t match to the stock movements

This probe will compare, for each product and by warehouse, the “wi_physical_quantity” value stored in bms_advancedstock_warehouse_item table with the sum of stock movements, calculated using column “sm_qty” of bms_advancedstock_stock_movement table.

Quantity to ship in warehouse item doesn’t match to the pending orders

As a reminder, for each pending order (depending of Pending orders configuration), ERP will assign a “Preparation warehouse” to each ordered item.

This probe will compare, for each product and by warehouse, the sum of ordered qty with the “qty_to_ship” value, sotred in bms_advancedstock_warehouse_item table.

Unconsistant reserved quantity at warehouse item level

Available soon.

Missing extended information for sales order item

Available soon.

Wrong extended information for sales order item

Available soon.

Stock items with quantity NULL (not zero)

This probe will detect if some entries of the table cataloginventory_stock_item have been created with a NULL quantity.

The “Fix errors” button will replace NULL by 0.

Products with a quantity sellable but out of stock

This probe will detect all products having a qty greater than their Out-of-Stock Threshold, but being out of stock.

Note

The Out-of-Stock Threshold can be defined for each product from its page, opening the “Advanced Inventory” pop-up :

_images/advanced_inventory_popup.png

Products without stock, no backorder, but sellable

This probe will detect all products having 0 qty, no backorders, but being in stock.