Fixes issue 31517: Empty matched document in Match Statement
authorAlvaro Ferraz <alvaro.ferraz@openbravo.com>
Tue, 17 Nov 2015 18:13:59 +0100
changeset 28107 d816f0fd4dd9
parent 28106 3af928301873
child 28108 6abe3000db92
Fixes issue 31517: Empty matched document in Match Statement

The problem was that two new columns were added in aprm_matchstatement hql query in the middle of the select clause, while they were added as two new columns at the end of ad_column. As the columns order was not the same between the hql query and the ad_column and some columns did not have an alias in hql query and some others did not have an entity alias in ad_column, some column values were lost.
To fix it, we added an alias to all columns in aprm_matchstatement hql query (matchedDocument had no alias) and an entity alias to all ad_columns in aprm_matchstatement table (Transaction, bslUpdated and matchedDocument columns had no entity alias and Affinity column had a wrong entity alias).
With this, all columns of aprm_matchstatement will be correctly filled with their values in Match Statement grid.
If new columns are added to the hql query, they can go in any order in the select clause and in the ad_column, but they will need to have both an alias in the hql query and an entity alias in the ad_column, following this documentation: http://wiki.openbravo.com/wiki/How_to_create_a_HQL_Based_Table#Column_Definition
modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_COLUMN.xml
modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TABLE.xml
--- a/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_COLUMN.xml	Sun Nov 15 12:50:48 2015 +0100
+++ b/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_COLUMN.xml	Tue Nov 17 18:13:59 2015 +0100
@@ -4051,6 +4051,7 @@
 <!--7A86A285DAD44ECBAB04B052BEF6F76D-->  <VALIDATEONNEW><![CDATA[Y]]></VALIDATEONNEW>
 <!--7A86A285DAD44ECBAB04B052BEF6F76D-->  <IMAGESIZEVALUESACTION><![CDATA[N]]></IMAGESIZEVALUESACTION>
 <!--7A86A285DAD44ECBAB04B052BEF6F76D-->  <ISUSEDSEQUENCE><![CDATA[N]]></ISUSEDSEQUENCE>
+<!--7A86A285DAD44ECBAB04B052BEF6F76D-->  <ENTITY_ALIAS><![CDATA[fat]]></ENTITY_ALIAS>
 <!--7A86A285DAD44ECBAB04B052BEF6F76D--></AD_COLUMN>
 
 <!--7C947F020859452883746030218158E0--><AD_COLUMN>
@@ -8733,6 +8734,7 @@
 <!--A7389A5DCC6D4D33B9ED1785EC5BA3A0-->  <VALIDATEONNEW><![CDATA[Y]]></VALIDATEONNEW>
 <!--A7389A5DCC6D4D33B9ED1785EC5BA3A0-->  <IMAGESIZEVALUESACTION><![CDATA[N]]></IMAGESIZEVALUESACTION>
 <!--A7389A5DCC6D4D33B9ED1785EC5BA3A0-->  <ISUSEDSEQUENCE><![CDATA[N]]></ISUSEDSEQUENCE>
+<!--A7389A5DCC6D4D33B9ED1785EC5BA3A0-->  <ENTITY_ALIAS><![CDATA[bsl.updated]]></ENTITY_ALIAS>
 <!--A7389A5DCC6D4D33B9ED1785EC5BA3A0--></AD_COLUMN>
 
 <!--A7AA3F7ABB27453795B97A175662F61A--><AD_COLUMN>
@@ -9111,6 +9113,7 @@
 <!--B15C117EB160403FABAC34188650DAC6-->  <VALIDATEONNEW><![CDATA[Y]]></VALIDATEONNEW>
 <!--B15C117EB160403FABAC34188650DAC6-->  <IMAGESIZEVALUESACTION><![CDATA[N]]></IMAGESIZEVALUESACTION>
 <!--B15C117EB160403FABAC34188650DAC6-->  <ISUSEDSEQUENCE><![CDATA[N]]></ISUSEDSEQUENCE>
+<!--B15C117EB160403FABAC34188650DAC6-->  <ENTITY_ALIAS><![CDATA[bsl.matchedDocument]]></ENTITY_ALIAS>
 <!--B15C117EB160403FABAC34188650DAC6--></AD_COLUMN>
 
 <!--B3645A0D0F674CD3BC3FCB6225C4E1A6--><AD_COLUMN>
@@ -11148,7 +11151,7 @@
 <!--FE24DEE7950D47A68BD883DF1F7BA506-->  <VALIDATEONNEW><![CDATA[Y]]></VALIDATEONNEW>
 <!--FE24DEE7950D47A68BD883DF1F7BA506-->  <IMAGESIZEVALUESACTION><![CDATA[N]]></IMAGESIZEVALUESACTION>
 <!--FE24DEE7950D47A68BD883DF1F7BA506-->  <ISUSEDSEQUENCE><![CDATA[N]]></ISUSEDSEQUENCE>
-<!--FE24DEE7950D47A68BD883DF1F7BA506-->  <ENTITY_ALIAS><![CDATA[affinity]]></ENTITY_ALIAS>
+<!--FE24DEE7950D47A68BD883DF1F7BA506-->  <ENTITY_ALIAS><![CDATA[bsl.matchingtype]]></ENTITY_ALIAS>
 <!--FE24DEE7950D47A68BD883DF1F7BA506--></AD_COLUMN>
 
 <!--FEB149D92A0E4F9E9532F0F78A16B0A8--><AD_COLUMN>
--- a/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TABLE.xml	Sun Nov 15 12:50:48 2015 +0100
+++ b/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TABLE.xml	Tue Nov 17 18:13:59 2015 +0100
@@ -208,7 +208,7 @@
 <!--BC21981DCF0846338D631887BEDFE7FA-->  <HQLQUERY><![CDATA[select bsl.transactionDate as banklineDate, case when bp is null then bsl.bpartnername  else bp.name end AS businessPartner, bsl.bpartnername AS Bpartnername, bsl.referenceNo as referenceNo, bsl.cramount - bsl.dramount as amount, bsl.id as match, bsl.matchingtype as affinity, 
 case when fat is null then false else true end AS cleared,
 bsl.matchingtype as matchingtype, bsl.id as id, 
-fat.transactionDate as transactionDate, transactionBP.name as transactionBPName, fat.depositAmount - fat.paymentAmount as transactionAmount, bsl.lineNo as lineNo, fat as fin_finacc_transaction_id, bsl.description as description, fat.description as fatDescription, case when bsl.cramount - bsl.dramount >0 then 'D' else 'P' end as type, bsl.matchedDocument, transactionGLItem.name as transactionGLItemName, glitem.name as glitemName, bsl.updated as bslUpdated
+fat.transactionDate as transactionDate, transactionBP.name as transactionBPName, fat.depositAmount - fat.paymentAmount as transactionAmount, bsl.lineNo as lineNo, fat as fin_finacc_transaction_id, bsl.description as description, fat.description as fatDescription, case when bsl.cramount - bsl.dramount >0 then 'D' else 'P' end as type, bsl.matchedDocument as matchedDocument, transactionGLItem.name as transactionGLItemName, glitem.name as glitemName, bsl.updated as bslUpdated
  @selectClause@
 from FIN_BankStatementLine as bsl
  join bsl.bankStatement as bs