c2c-oerpscenario team mailing list archive
-
c2c-oerpscenario team
-
Mailing list archive
-
Message #11815
[Bug 701402] [NEW] Purchase Analysis SQL ERROR: numeric field overflow
Public bug reported:
addons/purchase/report/purchase_report.py line74:
cr.execute("""
create or replace view purchase_report as (
select
min(l.id) as id,
s.date_order as date,
to_char(s.date_order, 'YYYY') as name,
to_char(s.date_order, 'MM') as month,
to_char(s.date_order, 'YYYY-MM-DD') as day,
s.state,
s.date_approve,
date_trunc('day',s.minimum_planned_date) as expected_date,
s.partner_address_id,
s.dest_address_id,
s.pricelist_id,
s.validator,
s.warehouse_id as warehouse_id,
s.partner_id as partner_id,
s.create_uid as user_id,
s.company_id as company_id,
l.product_id,
t.categ_id as category_id,
(case when u.uom_type not in ('reference') then
(select id from product_uom where uom_type='reference' and category_id = 1)
else
u.id
end) as product_uom,
s.location_id as location_id,
sum(l.product_qty/u.factor) as quantity,
extract(epoch from age(s.date_approve,s.date_order))/(24*60*60)::decimal(18,2) as delay,
extract(epoch from age(l.date_planned,s.date_order))/(24*60*60)::decimal(18,2) as delay_pass,
count(*) as nbr,
(l.price_unit*l.product_qty*u.factor)::decimal(18,2) as price_total,
avg(100.0 * (l.price_unit*l.product_qty*u.factor) / NULLIF(t.standard_price*l.product_qty*u.factor, 0.0))::decimal(18,2) as negociation,
sum(t.standard_price*l.product_qty*u.factor)::decimal(18,2) as price_standard,
(sum(l.product_qty*l.price_unit)/NULLIF(sum(l.product_qty*u.factor),0.0))::decimal(18,2) as price_average
from purchase_order s
left join purchase_order_line l on (s.id=l.order_id)
left join product_product p on (l.product_id=p.id)
left join product_template t on (p.product_tmpl_id=t.id)
left join product_uom u on (u.id=l.product_uom)
where l.product_id is not null
group by
s.company_id,
s.create_uid,
s.partner_id,
l.product_qty,
u.factor,
s.location_id,
l.price_unit,
s.date_approve,
l.date_planned,
l.product_uom,
date_trunc('day',s.minimum_planned_date),
s.partner_address_id,
s.pricelist_id,
s.validator,
s.dest_address_id,
l.product_id,
t.categ_id,
s.date_order,
to_char(s.date_order, 'YYYY'),
to_char(s.date_order, 'MM'),
to_char(s.date_order, 'YYYY-MM-DD'),
s.state,
s.warehouse_id,
u.uom_type,
u.category_id,
u.id
)
""")
------------------------------------
ERROR: numeric field overflow
DETAIL: A field with precision 16, scale 2 must round to an absolute value less than 10^14.
********** Error **********
ERROR: numeric field overflow
SQL state: 22003
Detail: A field with precision 16, scale 2 must round to an absolute value less than 10^14.
------------------------------------
If i replace in the code : decimal(16,2) --> decimal(18,2)
It's working good.
** Affects: openobject-addons
Importance: Undecided
Status: New
--
You received this bug notification because you are a member of C2C
OERPScenario, which is subscribed to the OpenERP Project Group.
https://bugs.launchpad.net/bugs/701402
Title:
Purchase Analysis SQL ERROR: numeric field overflow
Status in OpenObject Addons Modules:
New
Bug description:
addons/purchase/report/purchase_report.py line74:
cr.execute("""
create or replace view purchase_report as (
select
min(l.id) as id,
s.date_order as date,
to_char(s.date_order, 'YYYY') as name,
to_char(s.date_order, 'MM') as month,
to_char(s.date_order, 'YYYY-MM-DD') as day,
s.state,
s.date_approve,
date_trunc('day',s.minimum_planned_date) as expected_date,
s.partner_address_id,
s.dest_address_id,
s.pricelist_id,
s.validator,
s.warehouse_id as warehouse_id,
s.partner_id as partner_id,
s.create_uid as user_id,
s.company_id as company_id,
l.product_id,
t.categ_id as category_id,
(case when u.uom_type not in ('reference') then
(select id from product_uom where uom_type='reference' and category_id = 1)
else
u.id
end) as product_uom,
s.location_id as location_id,
sum(l.product_qty/u.factor) as quantity,
extract(epoch from age(s.date_approve,s.date_order))/(24*60*60)::decimal(18,2) as delay,
extract(epoch from age(l.date_planned,s.date_order))/(24*60*60)::decimal(18,2) as delay_pass,
count(*) as nbr,
(l.price_unit*l.product_qty*u.factor)::decimal(18,2) as price_total,
avg(100.0 * (l.price_unit*l.product_qty*u.factor) / NULLIF(t.standard_price*l.product_qty*u.factor, 0.0))::decimal(18,2) as negociation,
sum(t.standard_price*l.product_qty*u.factor)::decimal(18,2) as price_standard,
(sum(l.product_qty*l.price_unit)/NULLIF(sum(l.product_qty*u.factor),0.0))::decimal(18,2) as price_average
from purchase_order s
left join purchase_order_line l on (s.id=l.order_id)
left join product_product p on (l.product_id=p.id)
left join product_template t on (p.product_tmpl_id=t.id)
left join product_uom u on (u.id=l.product_uom)
where l.product_id is not null
group by
s.company_id,
s.create_uid,
s.partner_id,
l.product_qty,
u.factor,
s.location_id,
l.price_unit,
s.date_approve,
l.date_planned,
l.product_uom,
date_trunc('day',s.minimum_planned_date),
s.partner_address_id,
s.pricelist_id,
s.validator,
s.dest_address_id,
l.product_id,
t.categ_id,
s.date_order,
to_char(s.date_order, 'YYYY'),
to_char(s.date_order, 'MM'),
to_char(s.date_order, 'YYYY-MM-DD'),
s.state,
s.warehouse_id,
u.uom_type,
u.category_id,
u.id
)
""")
------------------------------------
ERROR: numeric field overflow
DETAIL: A field with precision 16, scale 2 must round to an absolute value less than 10^14.
********** Error **********
ERROR: numeric field overflow
SQL state: 22003
Detail: A field with precision 16, scale 2 must round to an absolute value less than 10^14.
------------------------------------
If i replace in the code : decimal(16,2) --> decimal(18,2)
It's working good.
Follow ups
References
