Onhand inventory - Committed Inventory

[Charley Tiggs <..hidden..>]

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,
       COALESCE(
         (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?

Charley