Hello Eric Caudal,
Would you please update your code as changes you have suggested in your
bug specification have been done in latest version.
Thanks.
** Changed in: openobject-addons
Status: New => Fix Released
--
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)
_______________________________________________
Mailing list: https://launchpad.net/~c2c-oerpscenario
Post to : c2c-oerpscenario@lists.launchpad.net
Unsubscribe : https://launchpad.net/~c2c-oerpscenario
More help : https://help.launchpad.net/ListHelp
