c2c-oerpscenario team mailing list archive

["Eric Caudal - www.elico-corp.com" <elicoidal@xxxxxxxxxxx>]

Hi Saz,
sorry but I cant find the modification in trunk nor in stable. Can you
explicit it?
Best regards

-- 
Eric

-- 
Invalid Product Margin report
https://bugs.launchpad.net/bugs/582250
You received this bug notification because you are a member of C2C
OERPScenario, which is subscribed to the OpenERP Project Group.

Status in OpenObject Addons Modules: Fix Released

Bug description:
Hi,
When I issue a supplier refund on a product (total invoice = 10 = 1 pce x10 USD), the quantity is computed as a sales increase instead of a purchase decrease in purchase. Average price calculatation seems wrong:

Details of product margin report:
before supplier refund:
avg price : 2.31
invoice : 120
turnover: 277.2

After the supplier refund
avg price : 6.16 (!!! this is (2.31x1 + 10x1)/2 quite illogical
invoice : 121 (why adding a negative purchase to a sale?!)
turnover: 287,2 (why adding a negative purchase to a sale?!)
report seems not very useful.

I assume in this that in_refund and out_refund are respectively a supplier and customer refund. Both appears in positive in database (account_invoice_line), making a negative accounting movement.

According to this, I have made some investigation in coding and as far as my programming skills can help, I have made the following change in product_margin.py:
Change
Code:

            if 'sale_avg_price' in field_names or 'sale_num_invoiced' in field_names or 'turnover' in field_names or 'sale_expected' in field_names:
                invoice_types=['out_invoice','in_refund']

by
Code:

            if 'sale_avg_price' in field_names or 'sale_num_invoiced' in field_names or 'turnover' in field_names or 'sale_expected' in field_names:
                invoice_types=['out_invoice','out_refund']

Change
Code:

            if 'purchase_avg_price' in field_names or 'purchase_num_invoiced' in field_names or 'total_cost' in field_names or 'normal_cost' in field_names:
                invoice_types=['in_invoice','out_refund']

by
Code:

            if 'purchase_avg_price' in field_names or 'purchase_num_invoiced' in field_names or 'total_cost' in field_names or 'normal_cost' in field_names:
                invoice_types=['in_invoice','in_refund']


the biggest change occurs for the following SQL Statement :
Code:

                sql="""
                select
                   avg(l.price_unit) as avg_unit_price,
                   sum(l.quantity) as num_qty,
                   sum(l.quantity * l.price_unit) as total,
                   sum(l.quantity * product.list_price) as sale_expected,
                   sum(l.quantity * product.standard_price) as normal_cost   
                from account_invoice_line l
                left join account_invoice i on (l.invoice_id = i.id)
                left join product_template product on (product.id=l.product_id)               
                where l.product_id = %s and i.state in ('%s') and i.type in ('%s') and i.date_invoice>='%s' and i.date_invoice<='%s'           
                """%(val.id,"','".join(states),"','".join(invoice_types),date_from,date_to)               

that I changed into the following one (correct avg price, shows 999999 when div/0 and correct sens in movements):
Code:

                sql="""
                select
                   (CASE
                        (sum(CASE i.type WHEN 'out_refund' THEN - l.quantity WHEN 'in_refund' THEN - l.quantity ELSE l.quantity END))
                     WHEN 0 THEN 999999
                     ELSE   sum(l.quantity * l.price_unit)
                              /
                            (sum(CASE i.type WHEN 'out_refund' THEN - l.quantity WHEN 'in_refund' THEN - l.quantity ELSE l.quantity END))
                    END) as avg_unit_price,

                   sum(
                        (CASE i.type
                        WHEN 'out_refund' THEN - l.quantity
                        WHEN 'in_refund' THEN - l.quantity
                        ELSE l.quantity
                        END)) as num_qty,
                   
                    sum(
                        (CASE i.type
                        WHEN 'out_refund' THEN - (l.quantity * l.price_unit)
                        WHEN 'in_refund' THEN - (l.quantity * l.price_unit)
                        ELSE (l.quantity * l.price_unit)
                        END)) as total,

                   sum(
                        (CASE i.type
                        WHEN 'out_refund' THEN - (l.quantity)
                        WHEN 'in_refund' THEN - (l.quantity)
                        ELSE (l.quantity)
                        END)* product.list_price) as sale_expected,

                   sum(
                        (CASE i.type
                        WHEN 'out_refund' THEN - l.quantity
                        WHEN 'in_refund' THEN - l.quantity
                        ELSE l.quantity
                        END) * product.standard_price) as normal_cost   

                from account_invoice_line l
                left join account_invoice i on (l.invoice_id = i.id)
                left join product_template product on (product.id=l.product_id)               
                where l.product_id = %s and i.state in ('%s') and i.type in ('%s') and i.date_invoice>='%s' and i.date_invoice<='%s'           
                """%(val.id,"','".join(states),"','".join(invoice_types),date_from,date_to)