Contributors mailing list archives

contributors@odoo-community.org

Browse archives

Avatar

Re: Inventory report / valuation loading time

by
Matt
- 07/11/2022 20:10:46
Hi Francesco,

For things like this, I use a SQL query, creating a new model, based on a database view.  Here's a query that works for me in v12.

Matt

with a as (
    select
        pp.id as product_id,
        pc.id as category_id,
        aa.id as account_id
    from product_product pp
    left join product_template pt on pt.id=pp.product_tmpl_id
    left join product_category as pc on pc.id=pt.categ_id
    left join ir_property as ip on ip.res_id='product.category,'||pt.categ_id and ip.name='property_stock_valuation_account_id'
    left join account_account as aa on 'account.account,'||aa.id=ip.value_reference
    where pt.type='product'
    --and pt.active=true
)
select
    pp.default_code,
    pt.name as product_name,
    pc.name as cat_name,
    pt.type as product_type,
    pt.tracking,
    pt.active,
    pt.deprecated,
    uu.name as uom,
    round(mv.avail_qty, 0) as stock_qty,
    av.acct_value,
    round(av.acct_qty, 0),
    av.acct_qty - mv.avail_qty as diff
from a
left join (
    select
        a.product_id,
        sum(aml.balance) as acct_value,
        sum(aml.quantity) as acct_qty
    from a
    left join account_move_line as aml on aml.product_id=a.product_id and aml.account_id=a.account_id
    left join account_move as am on am.id=aml.move_id
    where am.state='posted'
    and aml.date<'2022-01-01'
    group by a.product_id
) as av on av.product_id=a.product_id
left join (
    select
        sm.product_id,
        sum(
            case when (ls.usage<>'internal' and ld.usage='internal') then sm.product_qty
            else -sm.product_qty end
        ) as avail_qty
    from stock_move as sm
    left join stock_location as ls on ls.id=sm.location_id
    left join stock_location as ld on ld.id=sm.location_dest_id
    where sm.state='done'
    and sm.date<'2022-01-01'
    and (
        (ls.usage<>'internal' and ld.usage='internal')
        or
        (ld.usage<>'internal' and ls.usage='internal')
    )
    group by sm.product_id
) as mv on mv.product_id=av.product_id
left join product_product as pp on pp.id=a.product_id
left join product_template as pt on pt.id=pp.product_tmpl_id
left join product_category as pc on pc.id=a.category_id
left join uom_uom as uu on uu.id=pt.uom_id
where pc.name like 'RM - %'
and av.acct_value<>0
and coalesce(mv.avail_qty, 0.0)<>0.0



On Mon, Nov 7, 2022 at 5:12 AM Frederik Kramer <notifications@odoo-community.org> wrote:

Hi Francesco,

that is genuinly one to the more ressource hungry processes in Odoo (and of course all ERP systems). It heavily depends on what report you exactly execute, configuration parameters like (use of serial numbers, concrete valuation method, amount of stock locations to consider) and last but certainly not least the overall amount of stock moves in you database (that are a subset of the concrete report under investigation), version of Odoo that you are using and many more.

As far as i know there is no simple method that helps everywhere (as the problem origins can be vast), but i would recommend to analyze / profile first what actually takes so much time while processing the report in the calculation before taking any measure in whatever direction. Depending on the version of Odoo you are using and the landscape you are running there are different tools to profile. If you tell us the version that you are running, the amount of stock movel lines and stock locations we are speaking about an generally a bit more detail about your case i am sure some more technical people among our community can recommend that to exactly analyze first.

Best Frederik


Am 07.11.22 um 12:51 schrieb Francesco Foresti:
Hi, 

do you have any solutions/suggestions to speed up inventory reports/valuation loading time?

Thanks!

image.png

--

Francesco Foresti
Sicurpharma Srl

_______________________________________________
Mailing-List: https://odoo-community.org/groups/contributors-15
Post to: mailto:contributors@odoo-community.org
Unsubscribe: https://odoo-community.org/groups?unsubscribe

-- 
Dr.-Ing. Frederik Kramer
Geschäftsführer

initOS GmbH
Innungsstraße 7
21244 Buchholz i.d.N.

Phone:  +49 4181 13503-12
Fax:    +49 4181 13503-10
Mobil:  +49 179 3901819

Email: frederik.kramer@initos.com
Web:   www.initos.com

Geschäftsführung:
Dr.-Ing. Frederik Kramer & Dipl.-Ing. (FH) Torsten Francke

Sitz der Gesellschaft: Buchholz i.d.N.
Amtsgericht Tostedt, HRB 205226
Steuer-Nr: 15/200/53247
USt-IdNr.: DE815580155

_______________________________________________
Mailing-List: https://odoo-community.org/groups/contributors-15
Post to: mailto:contributors@odoo-community.org
Unsubscribe: https://odoo-community.org/groups?unsubscribe

Reference