This question has been flagged
3 Replies
7950 Views
inventoryquerysqlproduct.productodoo12
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
Jeffry Jesus 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
              """



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