Wrong dates used when confirming stock inventory

I attach a Video from user poit of view.

I attach video from program point of view.

There is a mistake in the context used to compute the stock (use of 'date' instead of 'to_date').

Hello.

IMHO the solution for this problem is a little more complicated.

I attach a patch with my point of view:

The patch is a mix of your solution Xavier and The solution proposed for our OPW + our concept about HOW should be done an inventory move.

Regards.

Nhomar and Xavier,

Thank you very much for your inputs here.

Lets take an example for the general scenario.

I decide to put my inventory for product "xyz" with qty 10.0 at morning 9 AM and create physical Inventory(draft state),
Then I decide to process it at 11 AM, confirm my inventory and finish it. Related stock moves will be having schedule Date and
Date as 11 AM.

Then in the evening 5 PM, I came to know my mistake and decided to update my inventory with qty 20.0. Related stock moves will be having Date and Schedule Date as evening 5 PM. (Previous stock move will be canceled and that can be used a reference)

As per your opinion, when I update my inventory in the evening and it shows me time of morning(inventory date) then I will be really amused that I am processing my inventory latter in evening then why it s showing me morning time? IMHO it should be the time when we process the inventory and thats what currently happening.

Correct me if I am wrong.
@ Experts, would you please share your view for this point as per our opinion this should not be considered as bug. So we need more clarification with this functional point.

Best regards.

The date when you process the inventory should not matter at all. All the stock moves related to it must have an effective date at the date of the inventory.
You may give the moves a creation date that matches the moment you confirm the inventory, that does not matter. But the stock variation has to happen at exactly the date of the inventory.
For example, our factory has quite a large inventory that takes 3 days to key in. The stock moves must all be dated the date of the inventory, not 3 days later.

You may call this a wishlist item rather than a bug, because the current behavior is somewhat consistent.
But clearly the current behavior is wrong. This (and Bug #588154) make me think the inventory feature needs re-engineering.

Lionel .

HEllo

@Numérigraphe

You and me have a point, but BTW im not agreed about this affirmation

>> You may give the moves a creation date that matches the moment you confirm the inventory, that does not matter.

It do matter, for traceability and partial calculation at time.

I think it is your situation, when you have an ALIVE inventory process, you need count at 10 am for example, you will sell 11 am for example, but you put your inventory 2 days after .... when you confirm the adjustment of inventory move from or to inventory loss needs to be done to the moment that you count (because it is the moment when you make the REAL adjustment, because you COUNT in this exactly moment.)

Even, for accounting reasons, you need to declare increase or losess about your inventory in the moment of the adjustment.
Not in the moment that you load, because you will calculate with bad in and out moves.

This is what is doing my patch.

With my patch if you decide do it for the MOMENTE WHEN you confirm inventory, JUST change date and it complay with ALL requirements discussed on this bug.

Le 31/01/2012 19:15, Nhomar Hernandez (Vauxoo) a écrit :
> HEllo
>>> You may give the moves a creation date that matches the moment you confirm the inventory, that does not matter.
>>>
> It do matter, for traceability and partial calculation at time.
To make it clearer, I was only talking of the field "create_date"
(record creation date timestamped by the ORM), which is not taken into
account for stock computation as far as I know.
The fields "date" and "date expected" must indeed be the date of the
inventory.
Lionel.

Ok Numerigraphe, i have your point and you are right ....

IMHO we need this ASAP.

Regards.

Dear Naresh,
The proposed branches look fine, would you please merge them now?
Lionel.

An automatic test case would be welcome here.
Lionel.

I think Xavier Fernandez's patch should be applied in addition to the fix proposed.
context['date'] seems wrong indeed.
Lionel.

Hello,

I think that there is the same problem for a simple stock move confirmation. There is 3 dates : created_date, date, and expected_date concerning a stock move. But even if you change the created or expected date, the system keeps and uses "date" for all the calculation of stock move. We write down all the accounting from 2010 with lots of products programmed on real-time inventory, and the result is a disaster. This dysfunction is critical...

"<email address hidden>" answer :
"We have investigated your issue and we are in conclusion stage. We regret to say that your issue cannot be solved in 6.1 on SAAS as it is generic to all the clients so we can not fix that on stable 6.1 at SAAS."

I regret too... Do you think that I can expect something for 6.2?

Geraldine

The second problem is still applicable to OpenERP 7.
The second patch should be applicable with some adaptation

I'm reopening this bug for two reasons:
First, the proposed solution was pushed only to v6.1 but not to v6.0, 7.0 or trunk
Second the proposed solution is wrong: the inventory-related stock moves are first created with the wrong date, and then tinkered but any automatic processing (chained move, accounting entry...) is made with the wrong date.

A clean solution is to pass the date as a context entry, and change stock.move.action_done() to take that date instead of the current date.

Lionel Sausin.

OK, I'm proposing a clean branch based on Nhomar's patch and most comments from this bug.
Anyone affected is invited to test the branch, and comment the proposal here: https://code.launchpad.net/~numerigraphe/openobject-addons/trunk-inventory-move-date/+merge/168787

Let's all hope this can finally be fixed.

I pushed a YAML test to prove the fix is correct for v6.0 .
The same sort of test seems to have been written for v6.1, but they were commented out of v7.0 and trunk (Bug #1190203) so I couldn't make sure they catch the bug and prove the solution right.
Lionel Sausin.

This is not fixed yet in the new WMS for v8.

actually the system of inventories, doesn't support at all to make an inventory at a given date.

I mean, if we put this patch that use the inventory date as post date for moves, then it implies we should also support to make an inventory for the beginning of the current month (for example)... but it won't work as the theoretical quantities are computed based on the current stock situation, and you probably expect the adjustments to be made based on stock level for that inventory date instead of current stock level.

Unfortunatelly it's not working that way. Although it may be improved in the future, we can't do it right now as we consider this as a new feature and we're trying to release the new WMS. So i'll set the bug as whishlist won't fix, because i don't want to confuse people about this.

thanks for your understanding
Quentin

Dear Quentin,
That's the conclusion I ha reached too, even though functionally it's a lack.

In this light, I propose to simply drop the "date" field which makes people expect OpenERP to do what it can't.

And then, I suggest we wait for the WMS to stabilize, and later all work together to introduce this feature.
Philippe at Numerigraphe has developed it for another system outside OpenERP using the same principle of "quant", so I'm confident this can be fixed eventually.

This behaviour is contrary to GAAP and should be fixed sooner rather than later.

A simple example of stock being counted on the last day of the financial year, but entered the next day, leads to the stock adjustments and associated journals being posted in the wrong year, which would need an extra reversing journal to be calculated and entered to make the stock value correct in that period.

Other examples where there are stock movements between stock count and stock entry (some as outlined above), all suffer the worse problem of introducing stock quantity errors. This therefore limits the use to preventing an entries between stock take and data entry, which in many businesses is not possible. This one feature would prevent this software being considered by most companies.

Correct treatment would be to calculate the OpenERP stock quantity at the "Creation Date" of the Physical Inventory, and make any adjustment stock movements the difference between the actual count and the calculated quantities, also with the date of the "Creation Date". This will enter the correct stock values in the correct dates and periods.

I'm marking this as opinion for the official addons because obviously openerp for the moment is hard-coded for real-time inventory management only, which is very limiting indeed.
The strangest thing is that was once accepted in the official v6.1 then never ported to later versions - no matter...

I'll try to implement Hugh Turner's suggestion for OCB-addons and see what comes out of it.

I've re-examined the code in v7:
- the quantities are computed for the date specified on the inventory ("to_date=inv.date")
- the moves are written at the date the inventory is confirmed ("now").
So the behavior is indeed not consistent and buggy, so I'm confirming again.
According to Quentin's comments, I think the consistent solution for the standard would be to :
- compute the quantities up to now
- keep the stock moves up dated "now".
Quentin do you agree please ?

This would of course impose real-time inventory management in OpenERP, which is impossible for some businesses like Hugh Turner emphasized.

I am still not comfortable with the idea.
We should be able to post an inventory at the date it has been performed
(inventory date) and calculated based on this date
Eric CAUDAL

Eric Caudal
/CEO/
--
*Elico Corporation, Shanghai branch
/OpenERP Premium Certified Training Partner/ *
Cell: + 86 186 2136 1670
Office: + 86 21 6211 8017/27/37
Skype: elico.corp
<email address hidden> <mailto:<email address hidden>>
http://www.elico-corp.com

Elico Corp
On 03/11/2014 06:31 PM, Lionel Sausin - Numérigraphe wrote:
> I've re-examined the code in v7:
> - the quantities are computed for the date specified on the inventory ("to_date=inv.date")
> - the moves are written at the date the inventory is confirmed ("now").
> So the behavior is indeed not consistent and buggy, so I'm confirming again.
> According to Quentin's comments, I think the consistent solution for the standard would be to :
> - compute the quantities up to now
> - keep the stock moves up dated "now".
> Quentin do you agree please ?
>
> This would of course impose real-time inventory management in OpenERP,
> which is impossible for some businesses like Hugh Turner emphasized.
>
> ** Changed in: ocb-addons
> Status: New => In Progress
>
> ** Changed in: openobject-addons
> Status: Opinion => Confirmed
>

Quentin, you said "you probably expect the adjustments to be made based on stock level for that inventory date instead of current stock level. Unfortunatelly it's not working that way. "

By that, do you mean stock moves with dates in the past will be plain impossible in v8, or rather that it can be done but it's not on the specs sheet?

In the later case, the community and I will be glad to provide a patch - the code looks very modular and easy to hack.
The former case however will force us all to go the real-time inventory route eventually.

Please let me know so I can propose the right kind of fix for v7.0 - either usual dated inventory or forced real-time.

Well, what i meant was "unfortunately, it's not _currently_ working that way and we did't modify it in trunk-wms". Stock moves are done at current time, and to be honest, i don't know exactly how possible it would be to have them done at a past date, as we didn't study this case yet.

I can forecast it would make some troubles with any removal strategy you have: imagine you manage your stock in FIFO, have received lot 1 on the 1st of month, lot 2 on the 2nd of the month, made a transfer of the 5th and now want to encode a transfer on the 4th? what if the stock move with a past date should create a negative quant? what to do in case of real time valuation, if the accounting period is closed? what if the costs were different...

I don't think it's impossible though, but these questions should be answered first.

For the inventories in trunk-wms branch, the problem is also that currently we compute the theoretical _current_ level of stock of products and compare it to what to user encoded. So here we would need to change by computing the stock level at the inventory date (but i would avoid making a huge SQL query on all the stock moves. Maybe a union of quants minus stock move done in between today and the inventory date would preserve the efficiency of the computation).

That was for the next gen of WMS, but it may be much more easier to solve for v7... I may be blind but i don't see any corner case it would imply in v7. The only thing is: it's a new feature and could be refused in the official v7. And i don't want any complain about regression in v8 because we changed it in v7 but not in trunk-wms :-) We need be be consistent

My message was almost posted when i thought: a lot of problem may be avoided if we allow to post inventory at past date ONLY if there isn't new stock move done in between the inventory date and today, for the given filter(s) (locations, products....). Then it's ok to take the current level of stock, we just post the move at a past date and make the valuation at that date too.

Anyway, i'm open to discussion

My past experience showed me that at month end you wait for the
accountant/manager to give the green light in order to start posting new
stock/accounting moves after the stock taking.
This is annoying but somehow unavoidable: I would advocate for a
solution suggested by Quentin where you can post the stock taking in the
past only if no stock move have been posted in between.
That would preserve current logic of OpenERP, make the system clean
avoiding regression and still allow a common practice in
accounting/stock management.

Eric CAUDAL

Eric Caudal

On 03/12/2014 05:26 PM, qdp (OpenERP) wrote:
> Well, what i meant was "unfortunately, it's not _currently_ working that
> way and we did't modify it in trunk-wms". Stock moves are done at
> current time, and to be honest, i don't know exactly how possible it
> would be to have them done at a past date, as we didn't study this case
> yet.
>
> I can forecast it would make some troubles with any removal strategy you
> have: imagine you manage your stock in FIFO, have received lot 1 on the
> 1st of month, lot 2 on the 2nd of the month, made a transfer of the 5th
> and now want to encode a transfer on the 4th? what if the stock move
> with a past date should create a negative quant? what to do in case of
> real time valuation, if the accounting period is closed? what if the
> costs were different...
>
> I don't think it's impossible though, but these questions should be
> answered first.
>
> For the inventories in trunk-wms branch, the problem is also that
> currently we compute the theoretical _current_ level of stock of
> products and compare it to what to user encoded. So here we would need
> to change by computing the stock level at the inventory date (but i
> would avoid making a huge SQL query on all the stock moves. Maybe a
> union of quants minus stock move done in between today and the inventory
> date would preserve the efficiency of the computation).
>
> That was for the next gen of WMS, but it may be much more easier to
> solve for v7... I may be blind but i don't see any corner case it would
> imply in v7. The only thing is: it's a new feature and could be refused
> in the official v7. And i don't want any complain about regression in v8
> because we changed it in v7 but not in trunk-wms :-) We need be be
> consistent
>
> My message was almost posted when i thought: a lot of problem may be
> avoided if we allow to post inventory at past date ONLY if there isn't
> new stock move done in between the inventory date and today, for the
> given filter(s) (locations, products....). Then it's ok to take the
> current level of stock, we just post the move at a past date and make
> the valuation at that date too.
>
> Anyway, i'm open to discussion
>

Thanks Quentin for this honest explanation.
We should not introduce features in v7 that we won't be able to port to v8 so we'll have to live with it for now.

So here's what I propose for the core v8:
- if there are conflicting stock moves (ie. move.date > inventory.date), refuse to confirm the inventory.
- if there are no conflicting moves, compute the stock difference at inventory.date and insert the stock moves at the same date.
The error message MUST give users the list of locations/products/lots with conflicts. Then they can re-count the goods, change the date of the inventory and confirm.
This is probably acceptable by most SME with no-too-complex warehouse structures.

Now, for those with more complex warehouses like ourselves, we have developed additional features to secure the inventory :
- indicate the impacted locations BEFORE we open the inventory
- forbid all stock moves on the impacted locations during an inventory
Our has been in production on v6 for ~1 year and we've just finished porting it to v7: Loïc will be making a merge proposal to OCA's WMS project soon, possibly this afternoon.

So in the end, users get the choice to either allow moves during inventory and let the manager clean up the mess, or enforce data integrity from the get go.

Either way, we have to accept that all stock moves must be entered in the correct sequence, and as close to real-time as possible.

would it be any problem including these 2 enhancements as well in the next WMS:
- indicate the impacted locations BEFORE we open the inventory
- forbid all stock moves on the impacted locations during an inventory

?

Le 12/03/2014 15:47, qdp (OpenERP) a écrit :
> would it be any problem including these 2 enhancements as well in the next WMS:
> - indicate the impacted locations BEFORE we open the inventory
> - forbid all stock moves on the impacted locations during an inventory
>
> ?
>
It would be great relief for everyone on the contrary.
We have implemented this for v6.0/7.0 and used it in production with
good feedback.
Numerigraphe's Loïc is just proposing it for OCA projects right now:
feel free to peek at the code to evaluate the features and see if it
inspires you:
https://code.launchpad.net/~numerigraphe-team/stock-logistic-warehouse/7.0-inventory-location/+merge/210630

Having the inventory locations in the standard would also allow a nice trick that we use on a project outside OpenERP : we can allow stock moves in the past - if there has been an inventory after the move's date, we don't update the quants, else we do.
Of course it may make the average prices slightly wrong, but most other ERPs have the same problem with this.

Ok, following improvements have been merged in trunk-wms (revision 9596.) :
- if there are no conflicting moves, compute the stock difference at inventory.date and insert the stock moves at the same date.
- indicate the conflicting moves BEFORE we start the inventory
- forbid all stock moves on the impacted locations during an inventory (product, owner, lot...)

thanks for your inputs, guys

Quentin, thanks for the quick fix, it looks fine.
The only corner case I can see is:
- enter an inventory with a date in the far future and confirm it
- enter a stock move today
=> the stock level at the date of inventory is wrong.
This is why I suggested Numerigraphe's Philippe's trick of not impacting quants when there is an inventory after the move's date.

Here are bits of documentation that I humbly propose for v8 when this lands into trunk, based upon this discussion.

"""
Near-real-time requirements
OpenERP expects you to manage your inventory in near-real-time.
For this reason, it will not let you enter stock moves and indicate a date in the past, because it would for example introduce errors in the price averages, and make inventory adjustments wrong.
Of course you do not have to enter stock moves in real-time - this typically require sophisticated inventory support hardware like RFID tags.
You must however enter every stock move in the same order as they really happened.
"""

"""
Inventory adjustments
Good practices and accounting rules dictate frequent inventories of important goods, and at least one global inventory control per fiscal year.
It is obvious that in the real world, not a single stock move should be made while the goods are being counted in the warehouse.
To help you with this, OpenERP will let you indicate which location is being controlled, and will forbid all stock move entry while the inventory is marked "in progress" in OpenERP.
When the inventory is done, OpenERP will adjust the stock levels at the date when the inventory was started.
This way, you can easily have a consistent inventory at a predictable date, which is crucial for end-of-year accounting operations.
Even though it may introduce stock moves in the past, this is consistent with the near-real-time requirement stated above.
"""

missing "s": "this typically requireS" - sorry

Quentin, a distinct bug exists for the remaining problem: would you please get in charge of bug #588154 ?
It contains (admitedly hackish) code to work around it in v6/v7, but more importantly a test scenario with a YML file.

well seen Lionel.

I added a check to restrict to set an inventory date in the future. Using this, we are sure that the stock level is correct when starting the inventory.

Thanks also for the doc. Really appreciated!

For the moment trunk-wms will forbid inventories as soon as there quants in any state other than "cancel".
Numerigraphe's just Loïc pointed out that surely it's going to be a problem with goods being reserved by the scheduler!
In a factory for example, mrp will be reserving raw materials every day, no matter if an inventory is expected or not.
So you won't ever be able to conduct an inventory in real life.

Ideally I suppose we should "break" reservations and set the procurement orders back to their initial state, and then recompute the procurements when the inventory is done (but then that's a problem with MTO isn't it?)

Or we could simply ignore reservations and only take the "done" moves into account.

What do you think about that ?

not since revision 9612. It seems you're a bit outdated ^^

In short, we restrict an inventory if there is quant reserved for a future move now.

Sorry I forgot to pull before I checked the code, but glad to see you think faster than us :)
Still, isn't the scheduler going to produce reserved quants every day, or even in real-time with mrp_jit?

I made merge proposals again for affected stable releases and OCB 7.0. They fix the move date part of the problem; the inventory-location part will be fixed in an OCA module we're working on, to be published next wee if all goes well.

"to be published next weeK". Silly keyboard.

Le 09/04/2014 15:39, De Paoli Quentin a écrit :
> (...)i don't know if you noticed but we decided to use the inventory
> post date for the move dates because that's reflect more the reality.
> There will be a way to force the accounting period for the valuation
> entries.
>
So, that means you've re-introduced Bug #915568 "Wrong dates used when
confirming stock inventory"?

This bug is not only about accounting entries, it's about WMS too:
- posting the stock moves later is sure to put someone in fiscal trouble
because the accounting and stock levels do not match at the end of the
fiscal year - some tax controllers are very picky about this
- it's so much more convenient for WMS managers to have the adjusted
quantities at the date of the inventory - this date at least they remember.

Of course... if that's your final call, so be it! Just update mark
"won't fix" andI'll move on to making a community addon that suits the
affected user's needs.
In that case it'll be great help if you arrange the code in trunk-wms to
allow this easily (like delegating the date evaluation to a method we
can surcharge, or maybe making it context-sensitive).

may be this is helpful
http://bazaar.launchpad.net/~camptocamp/c2c-rd-addons/7.0/files/head:/stock_product_zero/

well indeed, this bug is either half fixed either half not-fixed, depending on the way you look at it.

Anyway, if you think "won't fix" is a status reflecting more the reality then let's g for it.

For your easiness to make a module to change this behavior, i'm wondering: could you just overwrite post_inventory() of stock.inventory and make a write on stock move to change their 'date' field (after it has been set to done)? The only "issue" i oversee is that the accounting entries will have the date set to the day you pushed the button, which can't be seen anymore on the stock moves, but you can still force the period or make a write on the journal items too... well i think it should work. What do you think?