Archives
Contributors
Inventory report / valuation loading time
by Francesco Foresti - 12:51 - 7 Nov 2022
Follow-Ups
-
Re: Inventory report / valuation loading time
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.Mattwith 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.0On 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!
--
Francesco ForestiSicurpharma 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
by Matt - 08:18 - 7 Nov 2022 -
Re: Inventory report / valuation loading time
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!
--
Francesco ForestiSicurpharma 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
by Frederik Kramer - 01:10 - 7 Nov 2022