Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0041848Openbravo ERP09. Financial managementpublic2019-09-18 15:012019-09-23 14:02
Reporterngarcia 
Assigned Tomarkmm82 
PriorityimmediateSeverityminorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR19Q4 
Merge Request Status
Review Assigned ToSandrahuguet
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date2019-05-03
Regression introduced in release3.0PR19Q3
Regression introduced by commithttps://code.openbravo.com/erp/devel/pi/rev/54f5cc848bee48edf56131bff6b0265e17a75a99 [^]
Triggers an Emergency PackNo
Summary0041848: Cannot post a document if it has a customized SQL description that returns more than one record
DescriptionCannot post a document if it has a customized SQL description that returns more than one record
Steps To ReproduceInstall the 18Q3.5 version
As System Admin role:
   Create a new template for exporting the changes made to core
   Set as Displayed the SQL Description field of Active Tables tab of General Ledger Configuration window
   Export the changes to the template

As group admin role:
   Set the following SQL Description to the Invoice table of 'F&B España, S.A. US/A/Euro' configuration:

select 'Order Document Number: ' || o.documentno || ' ,'
from c_order o, c_invoice i
where i.c_order_id = o.c_order_id
and i.c_invoice_id = @RecordId@
UNION
select 'Invoice Document Number: ' || documentno || ' '
from c_invoice
where c_invoice_id = @RecordId@

   Create a Sales Order:
      Document Type: POS Order
   Add a product with stock and complete it
   Go to Sales Invoice window and post the created invoice
   Check its description. It concatenates the result of the two queries

Upgrade the instance to a version which includes the fix of 40523 issue (18Q4.4, 19Q.1 or higher)

As group admin role:
   Go to Sales Invoice window and unpost the created invoice
   Try to post it and check it finishes with the following error:
      Process failed during execution
   The log shows:
org.openbravo.erpCommon.ad_forms.AcctServer - AcctServer - postCommitjavax.servlet.ServletException: @CODE=@query did not return a unique result: 2
         
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
depends on backport 00418493.0PR19Q3.1 closed markmm82 Cannot post a document if it has a customized SQL description that returns more than one record 
depends on backport 00418503.0PR19Q2.3 closed markmm82 Cannot post a document if it has a customized SQL description that returns more than one record 
caused by defect 0040523 closed nonofrancisco use bind-parameters in FactLine 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2019-09-18 15:01ngarciaNew Issue
2019-09-18 15:01ngarciaAssigned To => Triage Finance
2019-09-18 15:01ngarciaModules => Core
2019-09-18 15:01ngarciaTriggers an Emergency Pack => No
2019-09-18 15:03ngarciaRelationship addedcaused by 0040582
2019-09-18 15:03ngarciaIssue Monitored: networkb
2019-09-18 15:03ngarciaRelationship addedcaused by 0040523
2019-09-18 15:03ngarciaRelationship deletedcaused by 0040582
2019-09-18 15:15SandrahuguetAssigned ToTriage Finance => markmm82
2019-09-18 15:38SandrahuguetRegression date => 2019-05-03
2019-09-18 15:38SandrahuguetRegression introduced in release => 3.0PR19Q3
2019-09-18 15:38SandrahuguetRegression introduced by commit => https://code.openbravo.com/erp/devel/pi/rev/54f5cc848bee48edf56131bff6b0265e17a75a99 [^]
2019-09-18 15:39SandrahuguetStatusnew => scheduled
2019-09-19 02:15markmm82Note Added: 0114661
2019-09-19 02:17markmm82Note Added: 0114662
2019-09-19 02:21markmm82Note Added: 0114663
2019-09-19 18:26hgbotCheckin
2019-09-19 18:26hgbotNote Added: 0114672
2019-09-19 18:27SandrahuguetStatusscheduled => resolved
2019-09-19 18:27SandrahuguetFixed in Version => 3.0PR19Q4
2019-09-19 18:27SandrahuguetFixed in SCM revision => https://code.openbravo.com/erp/devel/pi/rev/ce83eaa46dd9c067b547a70c3e5ffe6962765c62 [^]
2019-09-19 18:27SandrahuguetResolutionopen => fixed
2019-09-19 18:27SandrahuguetReview Assigned To => Sandrahuguet
2019-09-19 18:27SandrahuguetNote Added: 0114675
2019-09-19 18:27SandrahuguetStatusresolved => closed
2019-09-23 14:02hudsonbotCheckin
2019-09-23 14:02hudsonbotNote Added: 0114728

Notes
(0114661)
markmm82   
2019-09-19 02:15   
Test Plan 1 (Original issue 40523)

As System Administrator
  Open Widows Tabs and Field window
  Filter by "General Ledger Configuration"
  Select Active Tables tab
  Set Displayed = true for field "SQL Description"
  Rebuild the system

As F&B Group Admin mode
  Select General Ledger "F&B International Group US/A/US Dollar"
  Move to Active Table tab
  Select "Invoice" table
  Write this query in SQL Description field
   "select o.name from c_invoice i inner join ad_org o on i.ad_org_id = o.ad_org_id and i.c_invoice_id = @RecordId@ and i.c_invoice_id <> @Line@"

  Create a Sales Invoice
  Complete the document
  Post the document
  Verify the operation completes successfully.
(0114662)
markmm82   
2019-09-19 02:17   
Test Plan 2 (Original issue 40523)
Extended test plan:

As System Administrator
  Open Widows Tabs and Field window
  Filter by "General Ledger Configuration"
  Select Active Tables tab
  Set Displayed = true for field "SQL Description"
  Rebuild the system

As F&B Group Admin mode
  Select General Ledger "F&B España, S.A. US/A/Euro"
  Move to Active Table tab
  Select "Invoice" table
  Write this query in SQL Description field:
    select 'RecordId: ' || @RecordId@ || ', Line: ' || @Line@ from dual

  Create a Sales Invoice for España Norte
  Complete the document
  Post the document
  Verify the operation completes successfully
  Inside the Journal Entries Report for "F&B España, S.A. US/A/Euro", press the Detail link.
  Verify the description field looks like (the IDs might change):
    43000 Clientes (euros) a corto plazo 185.13 RecordId: 3232199ED4824EE3A07BCC1E580ABFE7, Line: 5B172283D5474E7384BE6FA4EE1B521B
    47700 Hacienda Pública IVA repercutido 32.13 RecordId: 3232199ED4824EE3A07BCC1E580ABFE7, Line: NULL
    70000 Ventas de mercaderías 153.00 RecordId: 3232199ED4824EE3A07BCC1E580ABFE7, Line: 92488D61F72E4E288C8382FA5E537DEE


As F&B Group Admin mode
  Select General Ledger "F&B España, S.A. US/A/Euro"
  Move to Active Table tab
  Select "Invoice" table
  Write this query in SQL Description field:
    select @Line@ from dual
  (Note there is no @RecordId@ this time).

  Create a Sales Invoice for España Norte
  Complete the document
  Post the document.
  Verify the description field looks like (the IDs might change):
    43000 Clientes (euros) a corto plazo 185.13 5B172283D5474E7384BE6FA4EE1B521B
    47700 Hacienda Pública IVA repercutido 32.13
    70000 Ventas de mercaderías 153.00 92488D61F72E4E288C8382FA5E537DEE
(0114663)
markmm82   
2019-09-19 02:21   
Test Plan 3: Testing when description SQL returns more than one record
  Install the 18Q3.5 version
  As System Admin role:
   Create a new template for exporting the changes made to core
   Set as Displayed the SQL Description field of Active Tables tab of General Ledger Configuration window
   Export the changes to the template

  As group admin role:
   Set the following SQL Description to the Invoice table of 'F&B España, S.A. US/A/Euro' configuration:

   select 'Order Document Number: ' || o.documentno || ' ,'
   from c_order o, c_invoice i
   where i.c_order_id = o.c_order_id
     and i.c_invoice_id = @RecordId@
   UNION
     select 'Invoice Document Number: ' || documentno || ' '
     from c_invoice
     where c_invoice_id = @RecordId@

   Create a Sales Order:
      Document Type: POS Order
   Add a product with stock and complete it
   Go to Sales Invoice window and post the created invoice
   Check its description. It concatenates the result of the two queries

   Upgrade the instance to a version which includes the fix of 40523 issue (18Q4.4, 19Q.1 or higher)

As group admin role:
   Go to Sales Invoice window and unpost the created invoice.
   Check the process ends successfully.
   Open Link Details and check its description concatenates the result of the two queries, first the invoice number and next the order document number.
(0114672)
hgbot   
2019-09-19 18:26   
Repository: erp/devel/pi
Changeset: ce83eaa46dd9c067b547a70c3e5ffe6962765c62
Author: Mark Molina <mark.molina <at> doceleguas.com>
Date: Wed Sep 18 20:01:20 2019 -0300
URL: http://code.openbravo.com/erp/devel/pi/rev/ce83eaa46dd9c067b547a70c3e5ffe6962765c62 [^]

Fix for issue 41848: Cannot post a document if it has customized SQL description
that returns more than one record.

When the customized SQL description returned more than one record, an exception
was thrown because the query was executed expecting an unique result.

Now is executed with getResultList and all the resultant descriptions are append
to the description.

---
M src/org/openbravo/erpCommon/ad_forms/FactLine.java
---
(0114675)
Sandrahuguet   
2019-09-19 18:27   
code review + testing ok
(0114728)
hudsonbot   
2019-09-23 14:02   
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/1a0e045ce34f [^]
Maturity status: Test