[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Onhand inventory - Committed Inventory

Previously, I asked about determining what quantity is onhand versus what's been requested. All I'm after is the ability to determine what inventory has already been committed by placing a sales order but hasn't been subtracted from onhand inventory count yet because an invoice hasn't been created for the sales order.

All I really want to do is get the committed quantity and subtract that from the onhand quantity to get available quantity. Even if I get a negative number, that's still valid information the client can use to make decisions on how to proceed with fulfilling orders. To do this, I've come up with the following (admittedly clumsy) SQL statement:

SELECT p.partnumber AS item_number,
       p.onhand AS qty_onhand,
         (SELECT SUM(oi.qty)
          FROM orderitems oi LEFT JOIN oe ON oi.trans_id = oe.id
          WHERE oe.closed = false
               AND oi.parts_id = p.id),
       0) AS qty_committed
FROM parts p
WHERE p.id = '22543';

This seems to give me what I want:

1) the part number
2) the onhand quantity
3) the quantity committed to sales orders that have not yet been closed.

My questions:

1) Is there a better way to do this SQL statement?

2) What did I forget to consider? I created about 10 different sales orders and then tracked down what happened to inventory each time I performed an action in the db. The orderitems, oe, and parts tables seemed to be the only ones I needed to involve in what I was trying to accomplish. Is there someplace else that I need to be looking as well?