Skip to Content
This question has been flagged
inventoryquerysqlproduct.productodoo12
3 Replies
8807 Views
Avatar
Kevin Cruz

Odoo Version
 12.0
Context

I need to know how to get these fields, lst_price and standard_price, from product_product model,  i'm developing an inventory report so i need to show this values in my excel table.

The problem

When i try to call lst_price using "pp.lst_price or pp.standard_price" i get the error "the column doen't exist" but i'm on the right place, so i realized something about this two fields are computed fields and they're not stored so they have not a column in the table... 

CODE

query = """select DATE(sm.date) as date, sm.origin as origin, sm.reference as ref, pt.name             as product,\ sm.product_uom_qty as out_qty, pp.id as product_id, pt.list_price             as price, pp.standard_price as cost\
              from stock_move as sm \
              JOIN stock_picking as sp ON sp.id = sm.picking_id \
              JOIN res_partner as rp ON rp.id = sp.partner_id \
              JOIN product_product as pp ON pp.id = sm.product_id \
              JOIN product_template as pt ON pp.product_tmpl_id = pt.id \
              where sm.date >= %s and sm.date <= %s \
              and sm.location_id = %s and sm.product_id in %s \
              and sm.state not in %s and sm.company_id = %s
              """


So i really need help to achieve this.

Avatar
Discard
Avatar
Sudhir Arya (ERP Harbor Consulting Services)
Best Answer

lst_price does not get stored in the database and is a related field of the "list_price". So you cannot use lst_price in the SQL.

Avatar
Discard
Avatar
DESARROLLO INMOBILIARIO MHER SRL
Best Answer

Hi maybe is late but here your solution

query = """select DATE(sm.date) as date, sm.origin as origin, sm.reference as ref, pt.name             as product,\ sm.product_uom_qty as out_qty, pp.id as product_id, pt.list_price             as price, prop.value_float as cost\
              from stock_move as sm \
              JOIN stock_picking as sp ON sp.id = sm.picking_id \
              JOIN res_partner as rp ON rp.id = sp.partner_id \
              JOIN product_product as pp ON pp.id = sm.product_id \
              JOIN product_template as pt ON pp.product_tmpl_id = pt.id \ 
              JOIN ir_property prop on prop.res_id = 'product.product,' || pp.id \
              where sm.date >= %s and sm.date <= %s \
              and sm.location_id = %s and sm.product_id in %s \
              and sm.state not in %s and sm.company_id = %s
              """



Avatar
Discard
Avatar
Ray Carnes (ray)
Best Answer

You can see in the source code that the standard_price field is company_dependent

https://github.com/odoo/odoo/blob/14.0/addons/product/models/product.py#L110

These kinds of fields are stored in the ir.properties model / table.


For more information, see:

https://www.odoo.com/documentation/14.0/howtos/company.html?highlight=company_dependent

https://www.odoo.com/forum/help-1/question/what-is-ir-property-used-for-48299

 

Avatar
Discard
Related Posts Replies Views Activity
How to solved this SQL error ?? Solved
query sql
Avatar
Avatar
1
Sep 21
1796
Creating and managing Multi Product Variants numbers ranging to thousands and millions
product.product odoo12
Avatar
0
Jul 19
1768
Extending Kanban View Solved
product.product odoo12
Avatar
1
Mar 19
3614
Database fetch misses ids ((<NewId origin=56101>,)) and has extra ids ((56101,)), may be caused by a type incoherence in a previous request Solved
inventory product.product v15
Avatar
Avatar
1
Sep 24
2173
How to print tree_view from sql query in odoo 11
query sql tree_view
Avatar
0
Oct 21
1691