Ghost Inventory

[jackkrooss]

When you reserve an item it subtracts it from available inventory. If it is subsequently closed without shipping it it is removed from the shipping screen, but the available inventory is not added back in. So there is a descrepency between Inventory (QTY) and Available Inventory (AVAIL). This discrepancy becomes a ghost (GHOST) in the system which can not be removed. If you add to inventory both QTY and AVAIL go up. If you adjust QTY then AVAIL is QTY - GHOST. I can go to the order screen and reopen the CLOSED/RESERVED order (let's not even think about what happens with multiline orders here... aargh) and then ship it to kill the ghost, but then my shipping numbers are off.

The fix here is modify the Close button trigger on the Order Manager screen. Test if the line item being closed is in status RESERVED and add the quantity back into the inventory QTY at the site where it was reserved from. I am concerned that it might not be immediatly obvious which site the reserve is from, although perhaps it is.

[jackkrooss]

The above just fixes the problem. It does not provide any way to remove the GHOST inventory. Perhaps we should make a special button on the Inventory Manager that can force QTY and AVAIL to be equal. That is change AVAIL to QTY.

[ianslai]

I did some preliminary investigation to figure out what's going on with various tables so I could understand how the data works in normal operations, and make sure we cover all the bases in terms of updating the state of the database.

In the Demand Manager, when a line item in an order is reserved:

• The inventory table is updated, incrementing qty_reserved by the appropriate amount for the current location (involocation) and desired product (partnumber)
• The demands table is updated several times for the selected order line (with database ID uid):
  • increment qty_reserved by the appropriate amount
  • update wolocation to be the current workcenter
  • update process_status to PARTIAL RESERVE or RESERVED according to whether the quantity reserved qty_reserved meets the quantity ordered qty
• The transport table gets a new row with status = 'RESERVED' and wo set to the uid for the appropriate order line from demands

There are several things to note here:

• If an order is reserved partially by several different sites, then there's no way to tell how to undo the reservation, because the wolocation gets overwritten. However, the changes in #205 disable partial reservation, so assuming that gets published, we won't have to worry about that.
• A transport request gets added for the line item, which will stick around even after the order is closed. There is no status I can find that indicates that a transport request is abandoned, so I think the safe thing to do here is to leave it alone. Fortunately, the various terminals handle this properly:
  • The Shipping Terminal loads from transport the list of transport requests in the RESERVED status. However, it filters for only OPEN orders as well, so we're safe here. (Apparently when the shipment is ready to go, the transport request enters the READY status.)
  • When the Courier Deliver Manager loads from transport rows, it includes only orders in the OPEN status and filters out rows with status RESERVED, DELIVERED, and ADHOC, so we don't have to worry about transport requests for orders that get closed without delivery.
• We will need to look at each line in the order (as it may have several lines) and perform updates on all that are applicable.

For future reference, list of transport statuses by frequency (as of 9:20pm on 2020-07-16):

CONFIRMED   6
ASSIGNED    13
DELIVERED   11379
RECEIVED    1
RESERVED    239
READY   45
ADHOC   4

[ianslai]

I have what I think is a fix, but need to confirm whether we should do the second step.

Basically, when we close an order, I would update the inventory table for the appropriate locations/items to decrease qty_reserved by the amount in the given order, for lines that are in the RESERVED status if the order isn't yet CLOSED. However, I was thinking we would also update the demands table to clear out the qty_reserved and reset the process_status to BACKLOG, in order to undo the reservation of those lines.

The rationale for the second step is that if we decide we want to make closed orders re-openable, then the order would be in a clean state. But right now we don't allow that (we can only open an order if it's in the CSR-REVIEW state). And this would also erase the evidence that someone had tried to reserve parts of the order.

Personally I think it's cleaner if we do both, but I would welcome input on this matter.

BEGIN;
    UPDATE corps.inventory inv
    SET
        qty_reserved = coalesce(inv.qty_reserved,0) - dem.qty_reserved
    FROM (
        -- Aggregate by site and product, in case there are multiple
        -- order lines for the same pair
        SELECT
            wolocation,
            product,
            SUM(qty_reserved) AS qty_reserved
        FROM corps.demands
        WHERE orderid = $order$
            AND process_status = 'RESERVED'
            AND order_status <> 'CLOSED'
        GROUP BY wolocation, product
    ) dem
    WHERE inv.invlocation = dem.wolocation
        AND inv.partnumber = dem.product;

    UPDATE corps.demands
    SET
        qty_reserved = NULL,
        order_status = 'CLOSED'
        process_status = 'BACKLOG',
    WHERE orderid = $order$
        AND process_status = 'RESERVED'
        AND order_status <> 'CLOSED';
COMMIT;

[ianslai]

We discussed this in Slack and decided it was better for now to skip reverting the changes to the order.

I created a connector called Order - Close Order and Replace Inventory that can be used in the Close button (replacing the call to "CSR - Order Status"). Will resume testing tomorrow.

I have what I think is a fix, but need to confirm whether we should do the second step.

Basically, when we close an order, I would update the inventory table for the appropriate locations/items to decrease qty_reserved by the amount in the given order, for lines that are in the RESERVED status if the order isn't yet CLOSED. However, I was thinking we would also update the demands table to clear out the qty_reserved and reset the process_status to BACKLOG, in order to undo the reservation of those lines.

The rationale for the second step is that if we decide we want to make closed orders re-openable, then the order would be in a clean state. But right now we don't allow that (we can only open an order if it's in the CSR-REVIEW state). And this would also erase the evidence that someone had tried to reserve parts of the order.

Personally I think it's cleaner if we do both, but I would welcome input on this matter.

[ianslai]

Created snapshot of fix. Tested canceling multiple times; after the first Cancel, the inventory is unaffected.

I discovered you couldn't create a transaction block in the connector so I left only the inventory update in there. The actual closing of the order is performed by the pre-existing call to "CSR - Order Status".

[jackkrooss]

This looks great Ian. Thank you. What remains is a strategy for resolving qty_reserved errors that have already been introduced. As we discussed in Slack, I am wondering if qty_reserved can be calculated at any time by looking at all of the RESERVED orders for the current location (involocation) and desired product (partnumber). Perhaps the calculation could run as part of the Demand Manager trigger any time a line item in an order is reserved. That is when an item is reserved the trigger function looks at all the RESERVED orders for that item and that location and computes a qty_reserved and simply replaces the qty_reserved with that value. This function would also run any time an order is Cancelled. There may be problems with this, but I can not think of any offhand.

[jackkrooss]

Function could even run any time a line item is selected from the table....

[jackkrooss]

Testing... created an order for 200 Foo Item 1 and reserved it for Grape Garden:

Now cancelled the order

Reserve is added back:

[jackkrooss]

I am going to publish now. As mentioned above this change does not correct ghost reserves already created, but it will correct the creation of ghost reserves going forward.