Fixes bug 29111: Valued Stock report filter properly by Product Category
authorUnai Martirena <unai.martirena@openbravo.com>
Tue, 03 Mar 2015 13:57:53 +0100
changeset 26123 f06d7018a52b
parent 26122 bce56d638278
child 26124 268b0e438f83
Fixes bug 29111: Valued Stock report filter properly by Product Category

A new language parameter was added in the issue that has caused the regression, but in wrong order, causing not to be able to filter by Product Category.
Also a left join was being done instead of inner join, so the query was displaying an wrong extra line with a sum up of all not necessary m_transaction records.
src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql
--- a/src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql	Wed Mar 04 18:06:07 2015 +0100
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql	Tue Mar 03 13:57:53 2015 +0100
@@ -35,7 +35,7 @@
                      END AS AVERAGE_COST,
                SUM(TOTAL_COST) AS TOTAL_COST
         FROM M_TRANSACTION M
-        LEFT JOIN (SELECT M_PRODUCT_CATEGORY.NAME AS CATEGORY_NAME, A.M_PRODUCT_ID, AD_COLUMN_IDENTIFIER (to_char('M_Product'),to_char(M_PRODUCT.M_PRODUCT_ID),to_char(?)) AS PRODUCT_NAME, 
+        INNER JOIN (SELECT M_PRODUCT_CATEGORY.NAME AS CATEGORY_NAME, A.M_PRODUCT_ID, AD_COLUMN_IDENTIFIER (to_char('M_Product'),to_char(M_PRODUCT.M_PRODUCT_ID),to_char(?)) AS PRODUCT_NAME, 
                 C_UOM.NAME AS UOM_NAME, SUM(A.SUMA) AS TOTAL_COST, A.ISCOSTCALCULATED, A.AD_CLIENT_ID, A.C_CURRENCY_ID, A.M_TRANSACTION_ID
              FROM M_PRODUCT_CATEGORY,
              M_TRANSACTION TR 
@@ -74,8 +74,8 @@
       <Parameter name="datePlus"/>
       <Parameter name="datePlus"/>
       <Parameter name="warehouse"/>
+      <Parameter name="categoryProduct" optional="true" after="AND   1 = 1"><![CDATA[ AND M_PRODUCT.M_PRODUCT_CATEGORY_ID= ? ]]></Parameter>
       <Parameter name="adLanguage"/>
-      <Parameter name="categoryProduct" optional="true" after="AND   1 = 1"><![CDATA[ AND M_PRODUCT.M_PRODUCT_CATEGORY_ID= ? ]]></Parameter>
   </SqlMethod>
   <SqlMethod name="set" type="constant" return="multiple">
       <SqlMethodComment></SqlMethodComment>