Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0034205Openbravo ERPI. Performancepublic2016-10-13 18:252016-12-16 18:39
ReporterJONHM 
Assigned ToAtulOpenbravo 
PriorityhighSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR17Q1 
Merge Request Status
Review Assigned Toaferraz
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0034205: View c_invoice_candidate_v has performance problem when having lots of unpaid invoices
DescriptionWhen having lots of unpaid invoices, the view c_invoice_candidate_v presents a performance problem. It have a lot of sequential accesses.
Steps To Reproducecheck c_invoice_candidate_v
If we make an explain of the query, we can see a lot of sequential accesses on c_orderline table
Proposed SolutionI also add a patch with some change proposals
Additional Information
TagsPerformance
Relationships
related to defect 00142522.50MP22 closed harikrishnan Invoice Term "Do not invoice" fails when creating Invoice from Shipment 
related to defect 00192783.0MP8 closed jecharri Error with parcial "Goods Shipments" and Invoices 
causes defect 0035341 closed mcobas Wrong orders shown in "Create Invoices From Orders" window 
Attached Filesdiff patch.diff (7,155) 2016-10-14 11:56
https://issues.openbravo.com/file_download.php?file_id=9912&type=bug
Issue History
Date ModifiedUsernameFieldChange
2016-10-13 18:25JONHMNew Issue
2016-10-13 18:25JONHMAssigned To => platform
2016-10-13 18:25JONHMModules => Core
2016-10-13 18:25JONHMResolution time => 1477519200
2016-10-13 18:25JONHMTriggers an Emergency Pack => No
2016-10-14 07:30alostaleTag Attached: Performance
2016-10-14 07:30alostaleAssigned Toplatform => Triage Finance
2016-10-14 11:56JONHMProposed Solution updated
2016-10-14 11:56JONHMFile Added: patch.diff
2016-10-17 11:57aferrazAssigned ToTriage Finance => AtulOpenbravo
2016-10-17 12:20AtulOpenbravoStatusnew => scheduled
2016-10-25 11:08JONHMResolution time1477519200 => 1478127600
2016-10-25 16:48ahernandezgilIssue Monitored: ahernandezgil
2016-10-27 13:15hgbotCheckin
2016-10-27 13:15hgbotNote Added: 0090952
2016-10-27 13:15hgbotStatusscheduled => resolved
2016-10-27 13:15hgbotResolutionopen => fixed
2016-10-27 13:15hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/a9ab872b6deefcd0565250d8bed1402d97f085a2 [^]
2016-10-27 13:25aferrazReview Assigned To => aferraz
2016-10-27 13:25aferrazNote Added: 0090953
2016-10-27 13:25aferrazStatusresolved => closed
2016-10-27 13:25aferrazFixed in Version => 3.0PR17Q1
2016-11-21 11:19hgbotCheckin
2016-11-21 11:19hgbotNote Added: 0091691
2016-11-21 11:20aferrazNote Deleted: 0090953
2016-11-21 11:20aferrazNote Added: 0091692
2016-11-25 15:02JONHMRelationship addedrelated to 0034637
2016-11-25 15:34aferrazRelationship deletedrelated to 0034637
2016-11-25 15:34aferrazRelationship addedcauses 0034637
2016-12-02 09:49aferrazRelationship addedrelated to 0014252
2016-12-02 09:49aferrazRelationship addedrelated to 0019278
2016-12-02 11:20hgbotCheckin
2016-12-02 11:20hgbotNote Added: 0092021
2016-12-02 11:24aferrazNote Added: 0092022
2016-12-16 18:38hudsonbotCheckin
2016-12-16 18:38hudsonbotNote Added: 0092580
2016-12-16 18:38hudsonbotCheckin
2016-12-16 18:38hudsonbotNote Added: 0092646
2016-12-16 18:39hudsonbotCheckin
2016-12-16 18:39hudsonbotNote Added: 0092685
2017-02-23 10:25aferrazRelationship addedrelated to 0035341
2017-02-27 17:18markmm82Relationship deletedrelated to 0035341
2017-02-27 17:18markmm82Relationship addedcauses 0035341

Notes
(0090952)
hgbot   
2016-10-27 13:15   
Repository: erp/devel/pi
Changeset: a9ab872b6deefcd0565250d8bed1402d97f085a2
Author: Atul Gaware <atul.gaware <at> openbravo.com>
Date: Thu Oct 27 12:14:29 2016 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/a9ab872b6deefcd0565250d8bed1402d97f085a2 [^]

Fixes issue 34205: Improve c_invoice_candidate_v view performance

---
M src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml
---
(0091691)
hgbot   
2016-11-21 11:19   
Repository: erp/devel/pi
Changeset: a86d3e78d9bc8105a30d3941663d6d6cd5767980
Author: Atul Gaware <atul.gaware <at> openbravo.com>
Date: Sun Nov 13 23:49:39 2016 +0530
URL: http://code.openbravo.com/erp/devel/pi/rev/a86d3e78d9bc8105a30d3941663d6d6cd5767980 [^]

Related to issue 34205: Remove OR clause for invoicerule='N'

Remove OR clause for invoicerule='N' from c_invoice_candidate_v query to avoid orders having invoice rule as Do Not Invoice.

---
M src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml
---
(0091692)
aferraz   
2016-11-21 11:20   
Code review + Testing OK
(0092021)
hgbot   
2016-12-02 11:20   
Repository: erp/devel/pi
Changeset: daca5e5e260e11bebd32898f38321ebd84a0c07d
Author: Atul Gaware <atul.gaware <at> openbravo.com
Date: Fri Dec 02 10:26:25 2016 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/daca5e5e260e11bebd32898f38321ebd84a0c07d [^]

Related to issue 34205: Code review improvements

- Fix parenthesis in C_INVOICE_CANDIDATE_V view.
- Fix CreateFrom_InvoiceData.selectFromShipmentSOTrxCombo query, by retrieving shipments not related to orders or shipments related to orders with invoicerule <> 'N'.
- Remove unnecessary ic.term <> 'N' checks, as C_INVOICE_CANDIDATE_V view will not retrieve orders with invoicerule = 'N' anymore.

---
M src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml
M src/org/openbravo/erpCommon/ad_actionButton/CreateFrom_Invoice_data.xsql
M src/org/openbravo/erpCommon/ad_forms/GenerateInvoicesmanual_data.xsql
---
(0092022)
aferraz   
2016-12-02 11:24   
Test Plan A
- Go to F&B International Group Admin.
- Create a Invoice Schedule with name Daily and frequancy as Daily.
- For business partner "Hoteles Buenas Noches, S.A.", in Customer Tab using invoice term as "Customer Schedule After Delivery" set Invoice Schedule as Daily.
- Keeping Invoice Schedule as Daily set invoice term as blank.
- Create a Order for business partner "Hoteles Buenas Noches, S.A." with Immediate Invoice Term. Add Line/s to it. Complete it.
- Create a Sales Invoice Header. Using Create Lines From, try to select above order.
- Above order appears in the drop down list.

Test Plan B
- Create a Order with invoice term 'Do Not Invoice'. Add line and Complete it.
- Create a Shipment for above order.
- Create a Sales Invoice Header and using Create Lines From, check Shipment Combo.
- Above shipment does not appear in drop down list.

Test Plan C
- Create Sales Order with Invoice Term as "Do Not Invoice". Add line/s to it. Book it.
- Create a Sales invoice Header, using Create Lines From button process check Order combo.
- Above order does not appear in the list.
- Go to Create Invoices From Order process.
- Try to search above order, it wont appear in the grid.
- Create a Shipment for above Order.
- Go to Create Invoices From Order process.
- Try to search above order, it wont appear in the grid.
- Go to Sales Invoice created above, using Create Lines From button process check Order combo.
- Above order does not appear in the list.
- Check Shipment combo, above created shipment does not appear in the list.

Test Plan D
- Create Sales Order's with Invoice Term as "Immediate".
- Go to Create Invoice From Order process.
- Try to search above order, it appears in the grid.
- Create Sales Invoice and Using Create Lines From button process, Check Order Combo.
- Above order appears in the list.

Test Plan E
- Create Sales Order's with Invoice Term as "Customer Schedule After Delivery" or "After Delivery" or "After Order Delivered"
- Create Sales Invoice and Using Create Lines From button process, Check Order Combo.
- Above order does not appears in the list.
- Go to Create Invoices From Orders process.
- Check above order does not appears in the grid.
- Create Shipment for above order.
- Go back to Sales Invoice created and Using Create Lines From button process, Check Order Combo.
- Above order appears in the list.
- Go to Create Invoices From Orders process.
- Check above order appears in the grid.

Test Plan F
- Create a Shipment for any business partner. Add line and process it.
- Create a Invoice Header for same business partner.
- Using Create Lines From button, check Shipment Combo.
- Above Shipment should appear in the list.
(0092580)
hudsonbot   
2016-12-16 18:38   
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/dc8bf00badd0 [^]
Maturity status: Test
(0092646)
hudsonbot   
2016-12-16 18:38   
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/dc8bf00badd0 [^]
Maturity status: Test
(0092685)
hudsonbot   
2016-12-16 18:39   
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/dc8bf00badd0 [^]
Maturity status: Test