Ir al contenido
Se marcó esta pregunta
inventoryquerysqlproduct.productodoo12
3 Respuestas
11805 Vistas
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
Descartar
Avatar
Sudhir Arya (ERP Harbor Consulting Services)
Mejor respuesta

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
Descartar
Avatar
Jeffry Jesús De La Rosa
Mejor respuesta

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
Descartar
Avatar
Ray Carnes (ray)
Mejor respuesta

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
Descartar
Publicaciones relacionadas Respuestas Vistas Actividad
How to solved this SQL error ?? Resuelto
query sql
Avatar
Avatar
1
sept 21
3823
Creating and managing Multi Product Variants numbers ranging to thousands and millions
product.product odoo12
Avatar
0
jul 19
2775
Extending Kanban View Resuelto
product.product odoo12
Avatar
1
mar 19
4734
Unable to assign a company to a newly created product
inventory product.product access_error
Avatar
Avatar
1
sept 25
953
Database fetch misses ids ((<NewId origin=56101>,)) and has extra ids ((56101,)), may be caused by a type incoherence in a previous request Resuelto
inventory product.product v15
Avatar
Avatar
1
sept 24
4261