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 :-)
Odoo is the world's easiest all-in-one management software.
It includes hundreds of business apps:
- CRM
- e-Commerce
- Accounting
- Inventory
- PoS
- Project management
- MRP
This question has been flagged
3
Replies
13206
Views
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.
My advice is... write a Python script for calculating that.
I need the sql query for a Jasper report. I played around with sql a bit today and I'm close to the solution.
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
Any comments on this are welcome :-)
Flyguy the query works great! I also need generate reports with the current stock (qty_available) for the product. I modified mine based on yours. I helps a lot! Just wanna say thank you :]
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 )
select i.product, i.sku, 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
///sql ends///
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...
The reason that the shelves weren't added to the stock qty, was because the shelves weren't linked to the stock location. So it was setup mistake that I made, not a bug.