Odoo Help

Welcome!

This community is for beginners and experts willing to share their Odoo knowledge. It's not a forum to discuss ideas, but a knowledge base of questions and their answers.

0

qty_available current stock query

By
Flyguy
on 4/25/13, 11:23 AM 3,846 views

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...

Flyguy
on 4/30/13, 7:28 AM

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.

Flyguy
on 9/30/13, 9:25 AM
1

Gustavo

--Gustavo--
950
| 2 1 4
Buenos Aires, Argentina
--Gustavo--

Python developer, big fan of sailing

Gustavo
On 4/25/13, 11:47 AM

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.

Flyguy
on 4/25/13, 2:43 PM
0
john
On 9/30/13, 8:38 AM

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///

0
Flyguy
On 4/30/13, 8:10 AM

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
on 4/30/13, 8:11 AM

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 :]

Yao
on 8/15/13, 1:22 AM

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

Question tools

1 follower(s)

Stats

Asked: 4/25/13, 11:23 AM
Seen: 3846 times
Last updated: 3/16/15, 8:10 AM