Skip to Content
This question has been flagged
inventoryquerysqlproduct.productodoo12
3 Replies
11733 Rodiniai
Portretas
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.

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

Portretas
Atmesti
Portretas
Jeffry Jesús De La Rosa
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
              """



Portretas
Atmesti
Portretas
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

 

Portretas
Atmesti
Related Posts Replies Rodiniai Veikla
How to solved this SQL error ?? Solved
query sql
Portretas
Portretas
1
rugs. 21
3767
Creating and managing Multi Product Variants numbers ranging to thousands and millions
product.product odoo12
Portretas
0
liep. 19
2748
Extending Kanban View Solved
product.product odoo12
Portretas
1
kov. 19
4710
Unable to assign a company to a newly created product
inventory product.product access_error
Portretas
Portretas
1
rugs. 25
924
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
Portretas
Portretas
1
rugs. 24
4200