[stock] store price_unit for all stock moves, and use it for suggesting return picking values

[Ferdinand]

In OpenERP 6.0 stock.move has a price_unit field for storing the unit cost price of the products, but this field only has a value for incoming pickings on products with average price (because it is used by the picking wizard in that case).

We should change the stock module to make sure the unit price is always written in that field, also in the other cases (we must also preserve the current behavior for
incoming pickings, which means we should store the value that was set by the user in the picking wizard, not always the current average price.).

Finally, we should use this price_unit column to suggest a cost price when doing a return picking. We should suggest the price_unit value of the stock moves we are
returning, instead of suggesting the current product price, as previously discussed in bug 613286. The tooltip should also be updated, etc.

======= (original description below) =======

to be able to calculate the stock value for every point in time for avg price and real time
obviously for accounting purpose real time we must have
account_balance == stock values
and must be able to proove it

if (pick.type != 'in') and (move.product_id.cost_method == 'average'):

if all stock moves have the price_unit field set:
qty * price_unit= move_value
and sum(+/- move_value)
enables to calculated the total stock value per product in every point of time matching the account balance

a little step further will be to calculated the average price per stock_location and lot

[Ferdinand (office-chricar)]

Nevertheless for accounting purpose it would be much better to store in stock moves the value (qty*price) used for account moves instead as it is always possible to re-calculate the price = value/qty , but not always value= qty*price, because of the rounding and precision issue needed for the price field.

[Amit Parik (amit-parik)]

Hello Dr Ferdinand,

I have checked your issue and patch.

But I am not getting exactly where did you faced the problem because I have tried all scenario with "Average price " and all are working fine.

So would you please provide the steps where you faced the problem.

Thanks and waiting for your reply!

[Ferdinand (office-chricar)]

let's start with a bit of accounting theory

product value = qty*price

simplest case: product_values in all of these records are identical
* PO/SO line == stock_move == invoice_line == account_move

if AVERAGE and REAL TIME price is used in a volatile market the product average price will change with every incoming product move.

to be able to calculate correctly the value of a product at period (month/year) end we MUST store the product value (or it's components qty and price) in every stock move.

this is also necessary to make stock reports and analysis - which must include values - returning meaningful values.

currently only the price of incoming goods is stored - IMHO the "current" price for all moves must be stored to be able to generate reproducable results.

product_product.standard_price * product_product.qty_available gives "only" the correct value for all other point in time it is WRONG!.

the patch adds the current "price_unit" to each stock move which allows to calculate
* stock value = sum(price_unit*qty) for every point in time.

I suggest to store move_value = price_unit * qty for every stock move.
* stock_value = sum(move_value) to avoid rounding differences and differences to account_move_lines.

in "Moves Analysis" there is a column "Total Value" and IMHO this must show correct figures
similar in "Inventory Analysis" grouped by "Date"

in
./report/report_stock_move.py
                        CASE WHEN sp.type in ('in') THEN
                            sum(sm.product_qty * pu.factor) * pt.standard_price
                            ELSE 0.0
                            END AS in_value,

returns wrong and unsusable values .....
it is absulutely necessary to use the price_unit of the move line.

and instead of haveing to calcualte
- sum(sm.product_qty * pu.factor) * move.price_unit
we should have
- sum(move_value)

BTW much if this is available in
http://bazaar.launchpad.net/~c2c/+junk/c2c_austria_dev/files/head:/c2c_stock_accounting/

we really should team up to get a working stock accounting ASAP.

[Olivier Dony (Odoo) (odo-openerp)]

Hello Ferdinand,

This bug report/feature request looks a lot like what was discussed in bug 613286, like a partial duplicate.
After discussing with R&D we think it is a valid addition for 6.1, but simply storing the price_unit all the time without using it does not make a lot of sense.
So we'll at least use it in the way it was meant in bug 613286, and propose this historical unit cost price as the suggested price for return pickings on products with average price.
I'll update the description and title to reflect that.

Thanks for suggesting a patch (to be double-checked by R&D)!

[Olivier Dony (Odoo) (odo-openerp)]

(Confirming and assigning to R&D)

[Ferdinand (office-chricar)]

thanks for clarification, but IMHO the analysis is not complete.

please consider that the way

./report/report_stock_move.py

calculates the product value does not return historical prices.

hence doing the anlaysis every month (for products with average price) it will likely return different values for past month (as it uses the current avg price) . this can't be explained as (useful) feature of Openerp.

hence arguing that the price_unit is not used - although it should be - can't be an argumetn to not store it.

[Rohan Nayani(Open ERP) (ron-tinyerp)]

Hello,

Thanks for Reporting.
It has been fixed in lp:~openerp-dev/openobject-addons/trunk-bug-779145-ron
Revision ID: <email address hidden>
Revision num:4809.

It will be available in trunk soon.

[Ferdinand (office-chricar)]

please could you fix also
./stock/report/report_stock_move.py

to use the new price in move line instaed of product

[Rohan Nayani(Open ERP) (ron-tinyerp)]

Hello Dr. Ferdinand ,

Thanks for suggestion.
It has been fixed with Revision ID: <email address hidden> on same branch,
Revision num:4811.

It will be available in trunk soon.

[Dmitri (7a-support-6r)]

Hello,

Could you merge it with OpenERP 6.0 as well?

Thanks,

[Nhomar - Vauxoo (nhomar)]

Excellent, this feature was REALLY needed by almost everybody in the world.....

And now with this feature, the unique thing is necesary is a good historical analisys as Ferdinand Recomend..

But it is another history. I will check branch.

Regards.

[Fabien (Open ERP) (fp-tinyerp)]

I propose to set this as a wishlist. It's too risky to merge the proposed branch as there is a lot of side effects to handle if we want to store the price_unit in the stock.move:
  - inventories,
  - stock.moves are created way before they are processed (and the cost price can change)

If you base reporting on this, you may have a lot of troubles. I think it's important that reporting remains on standard prices rather than a price on a stock.move.

So, I think it's too risky to merge the proposed branch for now on and we should do a complete project to ensure it's clean.

[Nhomar - Vauxoo (nhomar)]

When Fabien?

This issue has at least 1.5 Years reported, it is a really needs to have
serious accounting implementations running,

I think if Trunk already is exploting for new features that just show "new
toys", this is one of the features that needs to be in every software of
global class as openERP is becoming.

Please go up with this wish list as is, and prepare a verification sprint to
solve all broken parts on addons related to this.

IMHO,

If we do this (we already did in our implementations in V5) the work is not
so big.

Regards.

2011/9/27 Fabien (Open ERP) <email address hidden>

> I propose to set this as a wishlist. It's too risky to merge the proposed
> branch as there is a lot of side effects to handle if we want to store the
> price_unit in the stock.move:
> - inventories,
> - stock.moves are created way before they are processed (and the cost
> price can change)
>
> If you base reporting on this, you may have a lot of troubles. I think it's
> important that reporting remains on standard prices rather than a price on a
> stock.move.
>
> So, I think it's too risky to merge the proposed branch for now on and
> we should do a complete project to ensure it's clean.
>
> ** Changed in: openobject-addons
> Importance: Low => Wishlist
>
> --
> You received this bug notification because you are subscribed to OpenERP
> Project Group.
> https://bugs.launchpad.net/bugs/779145
>
> Title:
> [stock] store price_unit for all stock moves, and use it for
> suggesting return picking values
>
> Status in OpenERP Addons (modules):
> Fix Committed
>
> Bug description:
> In OpenERP 6.0 stock.move has a price_unit field for storing the unit
> cost price of the products, but this field only has a value for
> incoming pickings on products with average price (because it is used
> by the picking wizard in that case).
>
> We should change the stock module to make sure the unit price is always
> written in that field, also in the other cases (we must also preserve the
> current behavior for
> incoming pickings, which means we should store the value that was set by
> the user in the picking wizard, not always the current average price.).
>
> Finally, we should use this price_unit column to suggest a cost price when
> doing a return picking. We should suggest the price_unit value of the stock
> moves we are
> returning, instead of suggesting the current product price, as previously
> discussed in bug 613286. The tooltip should also be updated, etc.
>
> ======= (original description below) =======
>
> to be able to calculate the stock value for every point in time for avg
> price and real time
> obviously for accounting purpose real time we must have
> account_balance == stock values
> and must be able to proove it
>
> if (pick.type != 'in') and (move.product_id.cost_method == 'average'):
>
> if all stock moves have the price_unit field set:
> qty * price_unit= move_value
> and sum(+/- move_value)
> enables to calculated the total stock value per product in every point of
> time matching the account balance
>
> a little step further will be to calculated the average price per
> stock_location and lot
>
> To manage notifications about ...

[Ferdinand (office-chricar)]

Hello
I fully support Nhomar!

Basic features are a MUST.

and not having a value (move_value_cost) in the stock moves makes it almost impossible to reconstruct any evaluations for past dates which is a core function of accounting.
as Nhomar we had to add this functionality too in v5

[Cristian Salamea (ovnicraft)]

On Tue, Sep 27, 2011 at 10:41 AM, Fabien (Open ERP) <email address hidden> wrote:

> I propose to set this as a wishlist. It's too risky to merge the proposed
> branch as there is a lot of side effects to handle if we want to store the
> price_unit in the stock.move:
> - inventories,
> - stock.moves are created way before they are processed (and the cost
> price can change)
>

Hello, in fact is why this features is needed you need to see cost price
variation in stock moves timeline.

>
> If you base reporting on this, you may have a lot of troubles. I think it's
> important that reporting remains on standard prices rather than a price on a
> stock.move.
>

Stock move happens one time, and price what happened need to be stored (not
related).

Please review the importance of this feature in accounting world.
IMHO has not this features is locking 6 months again and
unnecessary efforts.
New features is not only new toys (agree with @nhomar) is about include
features forgotten.

Regards,

>
> So, I think it's too risky to merge the proposed branch for now on and
> we should do a complete project to ensure it's clean.
>
> ** Changed in: openobject-addons
> Importance: Low => Wishlist
>
> --
> You received this bug notification because you are subscribed to OpenERP
> Addons.
> https://bugs.launchpad.net/bugs/779145
>
> Title:
> [stock] store price_unit for all stock moves, and use it for
> suggesting return picking values
>
> Status in OpenERP Addons (modules):
> Fix Committed
>
> Bug description:
> In OpenERP 6.0 stock.move has a price_unit field for storing the unit
> cost price of the products, but this field only has a value for
> incoming pickings on products with average price (because it is used
> by the picking wizard in that case).
>
> We should change the stock module to make sure the unit price is always
> written in that field, also in the other cases (we must also preserve the
> current behavior for
> incoming pickings, which means we should store the value that was set by
> the user in the picking wizard, not always the current average price.).
>
> Finally, we should use this price_unit column to suggest a cost price when
> doing a return picking. We should suggest the price_unit value of the stock
> moves we are
> returning, instead of suggesting the current product price, as previously
> discussed in bug 613286. The tooltip should also be updated, etc.
>
> ======= (original description below) =======
>
> to be able to calculate the stock value for every point in time for avg
> price and real time
> obviously for accounting purpose real time we must have
> account_balance == stock values
> and must be able to proove it
>
> if (pick.type != 'in') and (move.product_id.cost_method == 'average'):
>
> if all stock moves have the price_unit field set:
> qty * price_unit= move_value
> and sum(+/- move_value)
> enables to calculated the total stock value per product in every point of
> time matching the account balance
>
> a little step further will be to calculated the average price per
> stock_location and lot
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/openobject-addons/+bug/779145...

[filsys (office-filsystem)]

"stock.moves are created way before they are processed (and the cost price can change)"
Yes, but cost price is not an element of stock, is an element of accounting (cost) and stock.moves generate accounting moves when is processed (at stock.move.action_done as you know). In V5 and also in V6 we had to put price_unit in all stock moves for a good hystorical preview of stock moves values. Otherwise, sum of the values from accounting will be different from stock move values if standard price from product is changed in time. Catching errors generated by transfers values from stock moves to accounting is a nightmare without price_unit in stock.moves.
Thanks

[Dmitri (7a-support-6r)]

Hello,

price_unit would make a lot of things easier. There are bug reports regarding item return from customer when using AVCO - at the moment, if you process one, it takes current item cost instead of historical and makes records in accounting accordingly. Price_unit could make it work correctly.

I totally agree with others - while "new toys" are nice to have, bug fixes on fundamental features like this are compulsory if OpenERP is to become a global class software.

[Ferdinand (office-chricar)]

I complete agree with filesys (#17)
the "final" unit_price has to be set at the moment when the accounting moves are created -
in real time accounting it's the time of validating the stock move.

nevertheless I would like to see a "value" field which is "qty*unit_price" to avoid this multiplication for every evaluated move.

[Ferdinand (office-chricar)]

Am Dienstag 27 September 2011, 15:41:28 schrieben Sie:
> I propose to set this as a wishlist. It's too risky to merge the proposed
> branch as there is a lot of side effects to handle if we want to store the
> price_unit in the stock.move: - inventories,
> - stock.moves are created way before they are processed (and the cost
> price can change)
>
> If you base reporting on this, you may have a lot of troubles. I think it's
> important that reporting remains on standard prices rather than a price on
> a stock.move.
>
> So, I think it's too risky to merge the proposed branch for now on and
> we should do a complete project to ensure it's clean.
>
> ** Changed in: openobject-addons
> Importance: Low => Wishlist

I do not see much risk to put this field (or move_value) into stock_moves.

the base distribution does not have to use it, but all installations needing
it could easily modify /add the necessary caluclation

--
Best Regards

ChriCar Beteiligungs- und Beratungs- GmbH
http://www.chricar.at/ChriCar/index.html
Dr. Ferdinand Gassauer
Official Tiny Partner

[Ferdinand (office-chricar)]

@Fabien (#13)
I do not see much risk to put this field (or move_value) into stock_moves.

the base distribution does not have to use it, but all installations needing
it could easily modify /add the necessary caluclation

[Ferdinand (office-chricar)]

in respect to #2 it is necessary to enter values without quantity (and price) if the stock value changes (usually in case of devaluation)