[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Onhand inventory - Committed Inventory
- Subject: Onhand inventory - Committed Inventory
- From: Charley Tiggs <..hidden..>
- Date: Tue, 21 Nov 2006 00:03:09 -0600
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,
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.
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?