I want to show the qty_available for all of my products with an sql query.
The qty_available seems like a calculated field. I think I need to count the product_qty for all incoming and outgoing stockmoves for each product.
Am I correct? And if so, does anyone already have that query? Seems like a lot of work for something that's probably been written hundreds of times before...
So I'ld be very happy if someone could help me out with this :-)

My query results seemed faulty at first, but now I am suspecting that there is a bug in OERP. If I create a new product and update the stock with 15 units in location 'stock', and then do a second update with 13 units in location 'stock\shelf1', then only 15 units are shown as available qty. But I have 28 available (15 in stock + 13 on shelf 1)
This seems like a bug to me...

Actually there is a method that calculates the qty_available amount. It is not as simple as writing a SQL query. Check the code in the stock module, specially the reports and the product.py module. There you can see how the current stock is calculated.

i just thought i would post this update as it includes the SKU (product_product.default_code) which is important for most users :

///sql starts///

with

uitstock as (
select t.name product, sum(product_qty) sumout, m.product_id, m.product_uom, p.default_code sku
from stock_move m
left join product_product p on m.product_id = p.id
left join product_template t on p.product_tmpl_id = t.id
where m.state like 'done' and m.location_id in
(select id from stock_location where complete_name like '%Stock%')
and m.location_dest_id not in
(select id from stock_location where complete_name like '%Stock%')
group by product_id,product_uom, t.name, p.default_code order by t.name asc )
,

instock as
( select t.standard_price purchaseprice, t.name product, sum(product_qty) sumin, m.product_id, m.product_uom, p.default_code sku
from stock_move m
left join product_product p on m.product_id = p.id
left join product_template t on p.product_tmpl_id = t.id
where m.state like 'done' and m.location_id not in
(select id from stock_location where complete_name like '%Stock%')
and m.location_dest_id in
(select id from stock_location where complete_name like '%Stock%')
group by product_id,product_uom, t.name, t.standard_price, p.default_code order by t.name asc )

with uitstock as
(
select t.name product, sum(product_qty) sumout, m.product_id, m.product_uom
from stock_move m
left join product_product p on m.product_id = p.id
left join product_template t on p.product_tmpl_id = t.id
where m.state like 'done'
and m.location_id in (select id from stock_location where complete_name like '%Stock%')
and m.location_dest_id not in (select id from stock_location where complete_name like '%Stock%')
group by product_id,product_uom, t.name
order by t.name asc
)
,
instock as
(
select t.standard_price purchaseprice, t.name product, sum(product_qty) sumin, m.product_id, m.product_uom
from stock_move m
left join product_product p on m.product_id = p.id
left join product_template t on p.product_tmpl_id = t.id
where m.state like 'done'
and m.location_id not in (select id from stock_location where complete_name like '%Stock%')
and m.location_dest_id in (select id from stock_location where complete_name like '%Stock%')
group by product_id,product_uom, t.name, t.standard_price
order by t.name asc
)
select i.product, sumin-coalesce(sumout,0) AS stock, sumin, sumout, purchaseprice, ((sumin-coalesce(sumout,0)) * purchaseprice) as stockvalue
from uitstock u
full outer join instock i on u.product = i.product

Your Answer

Please try to give a substantial answer. If you wanted to comment on the question or answer, just
use the commenting tool. Please remember that you can always revise your answers
- no need to answer the same question twice. Also, please don't forget to vote
- it really helps to select the best questions and answers!

About This Community

This community is for professionals and enthusiasts of our products and services. Read Guidelines