Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0037664Openbravo ERPI. Performancepublic2018-01-17 18:452018-07-09 11:24
Reportergorkaion 
Assigned Tocollazoandy4 
PriorityurgentSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR18Q2 
Merge Request Status
Review Assigned Todmiguelez
OBNetwork customerOBPS
Web browser
ModulesCore
Support ticket50473
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0037664: Performance issue in Invoice Line drop down of Landed Cost Cost
DescriptionOn a environment with huge number of invoices the drop down to select a invoice line in the Cost tab of Landed Cost window takes too much time to load.

Also the where clause of the selector is wrong. It should filter out also the invoices that have been completely matched in the Matched Amount tab and are not in the Landed Cost Cost tab.

Steps To ReproduceHave a environment with many invoice lines.
Configure the Landed Cost functionality and having some Sales Invoices using the products defined in the Landed Cost Cost.
Create a Landed Cost and Cost.
Display the Invoice Line drop down.
Proposed SolutionThe landed cost type defined in the Landed Cost Cost must have an account or a product defined.
The invoice line field should show invoice lines of that account or product that have not been completely matched or are pending to match.

1. Check the WhereClause of the selector to improve the performance.
Current clause is:
exists (select 1 from LandedCostType lct
        where lct.id = @inpmLcTypeId@
          and (e.product = lct.product or e.account = lct.account))
AND e.lineNetAmount <> (SELECT coalesce(SUM(lcc.amount),0)
                         FROM LandedCostCost lcc WHERE lcc.invoiceLine = e)

2. Consider adding a matchedAmount column in the invoice line or a matched flag when it is completely matched. Perhaps also a option to discard invoices that are not expected to match to any Landed Cost Cost.
3. Modify the selector adding some field to be shown in a popup so it is more easy to filter and search.



Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2018-01-17 18:45gorkaionNew Issue
2018-01-17 18:45gorkaionAssigned To => Triage Finance
2018-01-17 18:45gorkaionOBNetwork customer => Yes
2018-01-17 18:45gorkaionModules => Core
2018-01-17 18:45gorkaionSupport ticket => 50473
2018-01-17 18:45gorkaionResolution time => 1517958000
2018-01-17 18:45gorkaionTriggers an Emergency Pack => No
2018-01-19 14:28dmiguelezAssigned ToTriage Finance => collazoandy4
2018-01-20 01:46collazoandy4Statusnew => scheduled
2018-01-25 15:53collazoandy4Note Added: 0101946
2018-01-30 17:29hgbotCheckin
2018-01-30 17:29hgbotNote Added: 0102069
2018-01-30 17:29hgbotStatusscheduled => resolved
2018-01-30 17:29hgbotResolutionopen => fixed
2018-01-30 17:29hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/e557ef28eb740da3f040c34c1db6fbac5f86a790 [^]
2018-01-30 17:30dmiguelezReview Assigned To => dmiguelez
2018-01-30 17:30dmiguelezNote Added: 0102071
2018-01-30 17:30dmiguelezStatusresolved => closed
2018-01-30 17:30dmiguelezFixed in Version => 3.0PR18Q2
2018-01-30 17:58dmiguelezNote Added: 0102074
2018-01-30 18:11hgbotCheckin
2018-01-30 18:11hgbotNote Added: 0102075
2018-02-06 16:48gorkaionNote Added: 0102222
2018-02-22 18:18hudsonbotCheckin
2018-02-22 18:18hudsonbotNote Added: 0102697
2018-02-22 18:18hudsonbotCheckin
2018-02-22 18:18hudsonbotNote Added: 0102699
2018-07-09 11:24ngarciaSummaryPerfomance issue in Invoice Line drop down of Landed Cost Cost => Performance issue in Invoice Line drop down of Landed Cost Cost

Notes
(0101946)
collazoandy4   
2018-01-25 15:53   
Test Plan
  Go to Product window and create a new record:
    Search Key/Name: P37664
    Product Type: Service
    Create two records in Price tab for Tarifa de Bebidas Alegres and Tarifa de ventas
  Go to G/L Item window and create a new record:
    Name: GL37664
    Enable in Financial Invoices: Y
    Tax Category: IVA Normal
    Create a new record in Account tab:
      General Ledger: F&B España, S.A. US/A/Euro
      Glitem debit acct: 10000 - Capital social
      Glitem credit acct: 10100 - Fondo social
  Go to Landed Cost Type window and create three records:
    1. Name: LCTEmpty
    2. Name: LCTProduct, Product: P37664
    3. Name: LCTAccount, Account: GL37664
  Go to Purchase Invoice and create a new record:
    Business Partner: Bebidas Alegres, S.L.
    Create a new record in Lines tab:
      Product: P37664
      Invoiced Quantity: 10
    Complete the invoice
  Create another record in Header tab:
    Business Partner: Bebidas Alegres, S.L.
    Create a new record in Lines tab:
      Financial Invoice Line: Y
      Account: GL37664
      Invoiced Quantity: 10
      Net Unit Price: 1
    Complete the invoice
  Create two Sales Invoices with the above configuration for Alimentos y Supermercados, S.A bp
  Go to Landed Cost window and create a new record:
    Create a new record in Cost tab:
      Landed Cost Type: LCTProduct
      Select in Invoice line the previous invoice defined with the product P37664
      Check Amount: 70
      Change Landed Cost Type to LCTEmpty and check:
        Empty Invoice Line field
        Amount: 0
      Change Landed Cost Type to LCTAccount and check:
        Select in Invoice line the previous invoice defined with the financial invoice line GL37664
        Amount: 10
(0102069)
hgbot   
2018-01-30 17:29   
Repository: erp/devel/pi
Changeset: e557ef28eb740da3f040c34c1db6fbac5f86a790
Author: Armaignac <collazoandy4 <at> gmail.com>
Date: Tue Jan 23 20:42:37 2018 -0500
URL: http://code.openbravo.com/erp/devel/pi/rev/e557ef28eb740da3f040c34c1db6fbac5f86a790 [^]

Fixes issue 37664:Perfomance issue in Invoice Line drop down of Landed Cost Cost

On a environment with huge number of invoices the drop down to select a invoice
line in the Cost tab of Landed Cost window takes too much time to load.

The where clause of the selector was wrong. It should filter out also the
invoices that have been completely matched in the Matched Amount tab and are not
in the Landed Cost Cost tab.

Two new auxiliary input was added to compute the product and account ids defined
in the landed cost type selected also a callout was added to clear the invoice
line field when it's change.

The where clause of the selector was updated to properly take into account the
invoices

---
M src-db/database/model/tables/C_INVOICELINE.xml
M src-db/database/sourcedata/AD_AUXILIARINPUT.xml
M src-db/database/sourcedata/AD_CALLOUT.xml
M src-db/database/sourcedata/AD_COLUMN.xml
M src-db/database/sourcedata/AD_MODEL_OBJECT.xml
M src-db/database/sourcedata/AD_MODEL_OBJECT_MAPPING.xml
M src-db/database/sourcedata/OBUISEL_SELECTOR.xml
M src-db/database/sourcedata/OBUISEL_SELECTOR_FIELD.xml
A src/org/openbravo/erpCommon/ad_callouts/SL_LandedCost_Cost_Type.java
---
(0102071)
dmiguelez   
2018-01-30 17:30   
Code Review + Testing Ok
(0102074)
dmiguelez   
2018-01-30 17:58   
Commit related to Issue:

https://code.openbravo.com/erp/devel/pi/rev/093957bbfa3a [^]
(0102075)
hgbot   
2018-01-30 18:11   
Repository: erp/devel/pi
Changeset: fb5bcddb2101c698f2685ac357da4f4da2cd5eb0
Author: David Miguelez <david.miguelez <at> openbravo.com>
Date: Tue Jan 30 18:11:04 2018 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/fb5bcddb2101c698f2685ac357da4f4da2cd5eb0 [^]

Related to Issue 37664. Product field of selector is flagged as No Centraly
Maintained to avoid the description and help fields to be overwritten.

---
M src-db/database/sourcedata/OBUISEL_SELECTOR_FIELD.xml
---
(0102222)
gorkaion   
2018-02-06 16:48   
There is another changeset related to this issue that has to be applied before fb5bcddb2101c698f2685ac357da4f4da2cd5eb0

URL http://code.openbravo.com/erp/devel/pi/rev/093957bbfa3abdc00b7dba11a90bdf5b0e50586f [^]
changeset: 33232:093957bbfa3abdc00b7dba11a90bdf5b0e50586f
user: David Miguelez <david.miguelez@openbravo.com>
date: Tue Jan 30 17:29:16 2018 +0100
files: src-db/database/sourcedata/AD_REFERENCE.xml src-db/database/sourcedata/OBUISEL_SELECTOR.xml src-db/database/sourcedata/OBUISEL_SELECTOR_FIELD.xml
extra: branch=default
description:
Related to Issue 33231. Code Review changes

* Added another field to PopUp
* Changed order in grid of fields
* Added missing descriptions and help
(0102697)
hudsonbot   
2018-02-22 18:18   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/980a6ad5bbf5 [^]
Maturity status: Test
(0102699)
hudsonbot   
2018-02-22 18:18   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/980a6ad5bbf5 [^]
Maturity status: Test