Re: Onhand inventory - Committed Inventory

["Chris Travers" <..hidden..>]

Hi Charley,

You may want to play around with the Parts Requirements report to see
if that gives you most of the information you need also.  This may
make your life a little easier.

On 11/20/06, Charley Tiggs <..hidden..> wrote:
> 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?

No, that seems fine.  You may find it helpful, however, to either:
1)  Wrap it in a view so you can pull it out using common spreadsheets
if you need to or
2)  Wrap it in a stored procedure so you can more easily call it.

In both cases you are storing the SQL code in the db which makes for a
bit better manageability, but it can make for a bit of fun when the
schema upgrades...
> 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?
Not unless you have to track shipping, and transfers between
warehouses (doesn't sound like you do).  Your method will work fine as
long as shipped but non-invoiced goods can simply be listed as
"committed."

Best WIshes,
Chris Travers