Purchase Analysis SQL ERROR: numeric field overflow

Bug #701402 reported by Jacara
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Odoo Addons (MOVED TO GITHUB)
Confirmed
Wishlist
OpenERP R&D Addons Team 2

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.

Revision history for this message
Amit Parik (amit-parik) wrote :

Hello,

This type of error generate when the purchase order's total exceeds the 16 digit amount.

Thanks.

Changed in openobject-addons:
assignee: nobody → OpenERP R&D Addons Team 2 (openerp-dev-addons2)
importance: Undecided → Wishlist
status: New → Confirmed
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.