duplicate inventory lines produce wrong posted inventories

[Numérigraphe]

When you enter an inventory with several lines containing the same (product,location,prodlot) tuple, the posted inventory is not consistent : all lines are processes independently of each others, but they all relate to the same initial quantity.
As a consequence, differences are found even when the total of all the "duplicate" lines is right. Please see the attached screencast for an illustration of the problem, where I can make the stock wrong twice even though I entered correct quantities, split in 2 lines.

The simplest solution would be to forbid "duplicate" inventory lines with the same (product,location,prodlot) tuple.

Another possible solution would be to let users insert duplicates, but sum them up before computing the difference with the initial stock. This would be interesting as it would allow users to enter products one by one (possibly with a barcode scanner).

Lionel Sausin.

[Rifakat Husen (OpenERP) (rha-openerp)]

Hi Lionel,

Thanks for providing detail over this bug report.

Problem occurs when executing inventory lines one by one and leveling the stock to the quantity provided(14 -> 7),
and it happens two times for both inventory line. When the first one just executes and it creates a draft stock
movement, so it decreases stock levels 2 times by qty 7 and as result it shows 0 qty.

Lionel, don't you think a user has to manage it himself not to enter duplicate lines? or else in which case user enters
identical inventory lines?

However I do agree that we should improve this but that should be done in Trunk, don't you think?(by applying a pythonic constraint)
Awaiting your response.

Regards,
Rifakat

[Numérigraphe (numerigraphe)]

Dear Rifakat Haradwala,

Le 09/07/2013 08:18, Rifakat Haradwala (rha) a écrit :
> Problem occurs when executing inventory lines one by one and leveling the stock to the quantity provided(14 -> 7),
> and it happens two times for both inventory line. When the first one just executes and it creates a draft stock
> movement, so it decreases stock levels 2 times by qty 7 and as result it shows 0 qty.
I'll add that I've seen the problem occur even if the inventory lines
are in 2 distinct inventories, as soon as both inventories have the same
"date" field value.
So "duplicates" mean same product, same lot, same location and same date.
> Lionel, don't you think a user has to manage it himself not to enter duplicate lines?
Alas, currently that's not possible because there is no way to know
whether another inventory line exists for the same
product+location+lot+date.
To explain how difficult this is for complex warehouse, consider the
yearly physical inventory in our small food factory : >6000 inventory
lines, spread in 40 inventories.
I made SQL queries to help users avoid duplicates - but that's not so
fun :)
> or else in which case user enters
> identical inventory lines?
I met several legitimate uses of "duplicates" in inventories:
- users scan products 1 by 1 => many lines with same product and qty =1
- in locations containing several pallets, it's easier to enter 1 line
by pallet.
I don't think they are important use cases, but some users will try
them, so it must be either working or refused by the system.
> However I do agree that we should improve this but that should be done in Trunk, don't you think?(by applying a pythonic constraint)
Consider wrong stock moves are recorded without users knowing it: my
humble opinion is this should be fixed in stable releases, which is why
I forwarded it to OPW.
A constraint on same product+location+lot+date seems easiest and it has
little enough risk for regression in my opinion.

Lionel Sausin.

[Numérigraphe (numerigraphe)]

Dear Rifakat Haradwala,
Yes "date" is a timestamp, but we're used to splitting our yearly
inventory into several sub-inventories, all set to the same timestamp
(for example "2013-07-01 00:00:00").
This way, all the posted moves are written at the same date, so we have
a clean one-shot adjustment in stock levels.
Lionel.

[Numérigraphe (numerigraphe)]

Le 12/07/2013 07:26, Rifakat Haradwala (rha) a écrit :
>
> Hi Lionel,
>
> In your case you might have using your date with timestamp 00:00:00,
> but what about other users?
> Normally no one would ever enter date with timestamp 00:00:00, (...)
>
The mandatory yearly inventory is part of the accounting/fiscal year
closing procedure: it MUST happen at the end of the period, so in all
logic it should be dated right after the last day, at 00:00:00 (local time).
Will all due respect, I think everyone should do it this way.
It's good practice and OpenERP supports it out of the box: it's a good
design except for this bug.
>
> (...)if I create constraint considering this date field then I have an
> impression that the constraint will never get executed as date
> normally will not match.
>
Yes of course: constraints only catch errors!

Anyway, it's would only be a workaround.
Now I think it's better to fix the bug than work around it. Stock Moves
should be created correctly when several inventory lines have the same
date+product+lot+location.
Maybe the problem just to that the Stock Moves dated at the exact same
date of the inventory are excluded from the computation?

> Do you suggest any generic possible solution which can work for any
> user? This looks more like your specific requirement and I can provide
> a patch for you.
>
It's not specific, I use only standard features in a way supported by
the standard: multiple concurrent inventories with precise dates.
Let's call it a corner case.

Sorry I kept you waiting so long. I hope this helps you progress towards
a solution. Thanks for your support.

Lionel.

[Numérigraphe (numerigraphe)]

-------- Message original --------
Sujet: Re: OpenERP Enterprise Edition Bugfix: duplicate inventory lines
produce wrong posted inventories
Date : Tue, 06 Aug 2013 09:11:08 -0000
De : Rifakat Haradwala (rha) <email address hidden>
Répondre à : Followers of
OpenERP-Enterprise-Edition-Bugfix-duplicate-inventory-lines-produce-wrong-posted-inventories
<email address hidden>
Pour : Followers of
OpenERP-Enterprise-Edition-Bugfix-duplicate-inventory-lines-produce-wrong-posted-inventories
<email address hidden>

Hi Lionel,

I have created a patch which restricts creation of duplicate inventory
line on same date.
In the patch I have checked only Date part, and removed timestamp so for
all the inventory it checks it's date and not the time.

I still face a problem. When inventory is created on different date then
also it creates problem. Because when we create 1st inventory and
confirm it, that time stock move is generated in waiting state and does
not affect stock, so when creating the 2nd inventory it gets the same
stock level and creates duplicate stock move.

Could you please check the patch and let me know if there is any problem?
Please see the video also which I have shared with your email.

Regards,
Rifakat Haradwala,
OpenERP Enterprise Services.

Sent by OpenERP India using OpenERP <https://www.openerp.com/>. Access
your messages and documents through our Customer Portal
<https://accounts.openerp.com?db=openerp#action=login&token=9ppxSDjYL3MvY6ZRg2YS&type=signup&model=project.issue&id=594736>

[Weste (julien-weste)]

Hi,

I had the same problems as Lionel and wrote a function to handle the duplicates. It works only in a unique inventory (it won't do anything if there are duplicates in different inventories, even if they have the same date)
If the function find several lines with same product, lot and location, it deletes those lines and create a new one summing the quantities of the deleted ones.

Here is the code:

# -*- coding: utf-8 -*-

from openerp.osv import fields, osv, orm

class stock_inventory(osv.osv):
    _inherit = "stock.inventory"
    _name = "stock.inventory"

    def action_confirm(self, cr, uid, ids, context=None):
        if context is None:
            context = {}
        inv_line_obj = self.pool.get('stock.inventory.line')
        uom_obj = self.pool.get('product.uom')
        product_obj = self.pool.get('product.product')

        for inv in self.browse(cr, uid, ids, context=context):
            lines = {}
            lines_to_unlink = []
            lines_to_create = {}

            for line in inv.inventory_line_id:
                product_id = line.product_id.id
                location_id = line.location_id.id
                prod_lot_id = line.prod_lot_id.id
                product_qty = line.product_qty
                product_uom = line.product_uom.id

                #copy the inventory.lines line by line in a new dict, merging the duplicates
                if product_id in lines.keys():
                    if location_id in lines[product_id].keys():
                        if prod_lot_id in lines[product_id][location_id].keys():
                            if product_uom in lines[product_id][location_id][prod_lot_id]['qty'].keys():
                                lines[product_id][location_id][prod_lot_id]['qty'][product_uom] += product_qty
                            else:
                                lines[product_id][location_id][prod_lot_id]['qty'][product_uom] = product_qty
                            lines[product_id][location_id][prod_lot_id]['ids'] += [line.id]
                        else:
                            lines[product_id][location_id][prod_lot_id] = {'qty': {product_uom: product_qty}, 'ids': [line.id]}
                    else:
                        lines[product_id][location_id] = {prod_lot_id: {'qty': {product_uom: product_qty}, 'ids': [line.id]}}
                else:
                    lines[product_id]={location_id: {prod_lot_id: {'qty': {product_uom: product_qty}, 'ids': [line.id]}}}

            #browse the new dict to find the duplicates, unlink the old lines and create a unique new one
            for product_id in lines.keys():
                for location_id in lines[product_id].keys():
                    for prod_lot_id in lines[product_id][location_id].keys():
                        if len(lines[product_id][location_id][prod_lot_id]['ids']) > 1:
                            inv_line_obj.unlink(cr, uid,
                                                lines[product_id][location_id][prod_lot_id]['ids'],
                                                context = context)
                            product_uom = product_obj.browse(cr, uid, [product_id], context = context)[0].product...

[Cedric Le Brouster(OpenFire) (cedric-lebrouster)]

Hi,

I had the same problem.
Like Lionel Sausin, I think there are real cases with several entries with same product/location.
The user's warehouse is not as tidy as OpenERP's. There may be products of same kind on different places, and user won't look for the line already entered.

Julien Weste's code seems a good solution, but I'll have 2 things to say :
 - First, although I'm ok with merging stock moves, is it good to merge inventory lines? User might want to check it and not understand.
- Second, there is another problem, and I don't know if it's the right place to discuss it, but we could kill two birds with one stone : currently if we enter an inventory line with uom different from product's default uom, there may result in an incorrect move line.

explanation of the bug :
Let say I have 14 eggs in OpenERP (default product uom)
In annual inventory I find and enter 2 dozens (which may be purchase uom)
Currently, OpenERP rounds 14 eggs to 1 dozen, and generates a +12 eggs stock move, which makes 26 instead of 24

So if we change just a little Julien Weste's code, converting quantities to default product uom in the first loop (when lines dict is filled), this bug would be fixed

Regards,

[Lionel Sausin - Initiatives/Numérigraphe (ls-initiatives)]

To the best of my knowledge, this bug is fixed in the new WMS in v8.0.

[qdp (OpenERP) (qdp)]

ho yes, indeed...

refactoring has good sides sometimes ^^

[Lionel Sausin - Initiatives/Numérigraphe (ls-initiatives)]

Unfortunately we somehow missed Cedric Le Brouster's patch so we can't test it in time four our own inventory.
So as a preventive measure I've pushed a branch that takes the preventive route again, and forbids duplicate lines.