Where "stock.reorder_level < (Re-Order Level)" for Location X.

Tom, I guess it is a date issue, what I need to add is Quantity On Hand, for today, stock.reorder_level < moves.qty; needs to be for todays date, for that stock_id.  Something like the function get_qoh_on_date.

Sorry I don't know much about this...  what do ya think?

AM

  I'm not totally sure I follow.  Could you clarify your needs?

Tom

I need to add, where stock.reorder_level >= moves.qty; (location,qty) I need to sort be Location Qty, not all location's qty's,  did I say that right?

Thanks for all the help from the SQL Dept.

Alvin

  I'm a little rusty on my joins and I don't particularly use the Items and Inventory module, but is this something like what you're looking for?  (I'm assuming those Xs are actually referring to different values, that is, X and Y.)

select distinct codes.stock_id, codes.description
  from 0_item_codes as codes
       left join 0_loc_stock as stock on (codes.stock_id = stock.stock_id)
       left join 0_purch_data as purch on (stock.stock_id = purch.stock_id)
       left join 0_stock_moves as moves on (purch.stock_id = moves.stock_id)
where stock.loc_code LIKE 'X' and
       purch.supplier_id = Y and
       stock.reorder_level >= moves.qty;

~Tom

I know that the PO system is being worked on and will get better with time, but if I could just do a query on the database for now it would be a big help.

I would like to display columns 0_item_codes.stock_id, 0_item_codes.description,  and maybe more later.

Where
0_loc_stock.loc_code =X and
0_purch_data.supplier_id =X and
0_loc_stock.reorder_level is => 0_stock_moves.qty

Something like that...

If anyone out there can help with this or has put a query together for PO's it would be a great help?

Thanks