Front End to Debug Stock Issues

Description (*)

Some kind of front end to the Inventory_reservation table, that shows issues. e.g. reservations where the sum of the qty is positive & reservations that don't have an order in the sales_order table.

Expected behavior (*)

Page in the Magento admin to show all the current rows in the inventory_reservation, the ability to click on each one to be taken to the related order.

The ability to remove incorrect rows from the inventory_reservation table.

Another part of the page to flag up issues, if things don't add up correctly.

Benefits

Easy way to fix Inventory_reservation problems without having to use SQL.

Additional information

We had some issues with the import from Magento 1 to Magento 2, orders that were created in Magento 1 then shipped in Magento 2, the inventory_reservation didn't work as expected. We had to delete the rows from inventory_reservation to sort.

We have also had a problem with a PayPal order, something went wrong, the payment failed but we ended up with records in the inventory_reservation table that didn't have a related order is sales_order table.

Please ignore the bad SQL, but this is what we have been using to find the issues in the table:
Find reservations where reservations are positive:
SELECT sku, sum(quantity) FROM inventory_reservation GROUP by sku having sum(quantity)>0

Find reservations where reservations don’t have a matching order:

SELECT res_orderid
FROM sales_order
RIGHT JOIN
(SELECT DISTINCT(SUBSTRING(metadata, POSITION("object_increment_id" IN metadata)+22, LENGTH(metadata)-POSITION("object_increment_id" IN metadata)-23)) AS res_orderid FROM inventory_reservation) b
ON b.res_orderid = sales_order.increment_id
WHERE sales_order.increment_id IS NULL

Start by reviewing the inventory_reservation and sales_order tables and the two SQL examples in the issue to define the reservation checks. The work is done when a Magento admin page lists reservations, links entries to related orders, identifies the described inconsistencies, and supports removing incorrect rows without SQL.