Community mailing list archives

community@mail.odoo.com

RE: stock.history total quantity and stock.quant total doesn't match

by
dudart
- 04/04/2016 05:28:30

Stock_history is a select from stock.quant and stanck.move which change quantity in (plus or minus) quantity

 

I have a customer which use transit location for internal transfer

For stock valuation, I changed query in stock_valuation_history.py by this one :

 

CREATE OR REPLACE VIEW stock_history AS (

          SELECT MIN(id) as id,move_id,company_id,

                 product_id,product_categ_id,product_weight,

                 sum(quantity)*product_weight as product_weight_total,

                 sum(quantity)*price_unit_on_quant as inventory_value,

                 SUM(quantity) as quantity,date, price_unit_on_quant,source

          FROM ((SELECT stock_move.id::text || '-' || quant.id::text AS id,

                        quant.id                  AS quant_id,

                        stock_move.id             AS move_id,

                        dest_location.id          AS location_id,

                        dest_location.company_id  AS company_id,

                        stock_move.product_id     AS product_id,

                        product_template.categ_id AS product_categ_id,

                        product_template.weight   AS product_weight,

                        quant.qty                 AS quantity,

                        stock_move.date           AS date,

                        quant.cost                AS price_unit_on_quant,

                        stock_move.origin         AS source

                  FROM      stock_quant as quant, stock_quant_move_rel, stock_move

                 LEFT JOIN stock_location dest_location   ON stock_move.location_dest_id = dest_location.id

                 LEFT JOIN stock_location source_location ON stock_move.location_id = source_location.id

                 LEFT JOIN product_product                ON product_product.id = stock_move.product_id

                 LEFT JOIN product_template               ON product_template.id = product_product.product_tmpl_id

                 WHERE quant.qty>0

                 AND  stock_move.state = 'done'

                 AND dest_location.usage in ('internal')

                 AND stock_quant_move_rel.quant_id = quant.id

                 AND stock_quant_move_rel.move_id = stock_move.id

                 AND (   (source_location.company_id is null and dest_location.company_id is not null)

                      or (source_location.company_id is not null and dest_location.company_id is null)

                      or source_location.company_id != dest_location.company_id))

            UNION

                (SELECT '-' || stock_move.id::text || '-' || quant.id::text AS id,

                        quant.id                   AS quant_id,

                        stock_move.id              AS move_id,

                        source_location.id         AS location_id,

                        source_location.company_id AS company_id,

                        stock_move.product_id      AS product_id,

                        product_template.categ_id  AS product_categ_id,

                        product_template.weight    AS product_weight,

                        -quant.qty                 AS quantity,

                        stock_move.date            AS date,

                        quant.cost                 AS price_unit_on_quant,

                        stock_move.origin          AS source

                  FROM      stock_quant as quant, stock_quant_move_rel, stock_move

                  LEFT JOIN stock_location source_location ON stock_move.location_id = source_location.id

                  LEFT JOIN stock_location dest_location   ON stock_move.location_dest_id = dest_location.id

                  LEFT JOIN product_product                ON product_product.id = stock_move.product_id

                  LEFT JOIN product_template               ON product_template.id = product_product.product_tmpl_id

                  WHERE quant.qty>0

                  AND   stock_move.state = 'done'

                  AND source_location.usage in ('internal')

                  AND stock_quant_move_rel.quant_id = quant.id

                  AND stock_quant_move_rel.move_id = stock_move.id

                  AND (   (dest_location.company_id is null and source_location.company_id is not null)

                       or (dest_location.company_id is not null and source_location.company_id is null)

                       or dest_location.company_id != source_location.company_id)

                )) AS foo

                GROUP BY move_id, company_id, product_id, product_categ_id,

                         product_weight,date, price_unit_on_quant,source

                )""")

 

 

 

De : aasim ansari [mailto:aasim333@gmail.com]
Envoyé : lundi 4 avril 2016 09:18
À : Community
Objet : stock.history total quantity and stock.quant total doesn't match

 

Hello,

 

Based on my research, I feel output of the following 2 queries should be the same-

SELECT SUM(qty) FROM stock_quant WHERE product_id=%s AND location_id=%s GROUP BY product_id

 

SELECT SUM(quantity) FROM stock_history h WHERE h.product_id=%s AND h.location_id=%s GROUP BY h.product_id

 

However, for some products it is not. If I have missed anything do let me know.

 

I am developing comprehensive Daily Sales Report module and would like community's input in finding the best way out.

 

Thanks.

--

Aasim Ahmed Ansari

Cell: +91 9619 424 386

_______________________________________________
Mailing-List: https://www.odoo.com/groups/community-59
Post to: mailto:community@mail.odoo.com
Unsubscribe: https://www.odoo.com/groups?unsubscribe