Fixes Issue 40676. Trunc the movement date in the query filters
authorDavid Miguelez <david.miguelez@openbravo.com>
Thu, 25 Apr 2019 09:54:50 +0200
changeset 36063 b8db7c601aee
parent 36062 516c6b7277a0
Fixes Issue 40676. Trunc the movement date in the query filters
as it can have precision greater than days, but the comparison
is always done at day level
src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql
src/org/openbravo/materialmgmt/GenerateValuedStockAggregated_data.xsql
--- a/src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql	Thu Apr 25 09:53:25 2019 +0200
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql	Thu Apr 25 09:54:50 2019 +0200
@@ -53,8 +53,8 @@
                                  WHERE dateacct < to_date(?)
                                    AND dateacct > to_date(?)
                                  GROUP BY m_transaction_id, c_currency_id) tc ON trx.m_transaction_id = tc.m_transaction_id
-                    WHERE trx.MOVEMENTDATE < to_date(?)
-                    AND trx.MOVEMENTDATE > to_date(?)
+                    WHERE TRUNC(trx.MOVEMENTDATE) < to_date(?)
+                    AND TRUNC(trx.MOVEMENTDATE) > to_date(?)
                     AND trx.TRXPROCESSDATE >= to_timestamp(?, ?)
                     AND trx.ad_org_id IN ('1')
                     AND 0=0
@@ -157,8 +157,8 @@
             JOIN M_PRODUCT p ON trx.m_product_id = p.m_product_id
             JOIN M_TRANSACTION_COST tc ON trx.m_transaction_id = tc.m_transaction_id AND tc.dateacct < to_date(?)
               AND tc.dateacct > to_date(?)
-          WHERE trx.MOVEMENTDATE < to_date(?)
-            AND trx.MOVEMENTDATE > to_date(?)
+          WHERE TRUNC(trx.MOVEMENTDATE) < to_date(?)
+            AND TRUNC(trx.MOVEMENTDATE) > to_date(?)
             AND trx.TRXPROCESSDATE >= to_timestamp(?, ?)
             AND trx.ad_org_id IN ('1')
             AND trx.iscostcalculated = 'Y'
@@ -219,8 +219,8 @@
         JOIN m_transaction trx ON trx.m_product_id = p.m_product_id
         JOIN m_locator l ON trx.m_locator_id = l.m_locator_id
       WHERE trx.iscostcalculated = 'N'
-        AND trx.MOVEMENTDATE < to_date(?)
-        AND trx.MOVEMENTDATE > to_date(?)
+        AND TRUNC(trx.MOVEMENTDATE) < to_date(?)
+        AND TRUNC(trx.MOVEMENTDATE) > to_date(?)
         AND trx.TRXPROCESSDATE >= to_timestamp(?, ?)
         AND  trx.ad_org_id IN ('2')
         AND 2 = 2
@@ -305,8 +305,8 @@
                                  WHERE dateacct < to_date(?)
                                    AND dateacct > to_date(?)
                                  GROUP BY m_transaction_id, c_currency_id) tc ON trx.m_transaction_id = tc.m_transaction_id
-                    WHERE trx.MOVEMENTDATE < to_date(?)
-                    AND trx.MOVEMENTDATE > to_date(?)
+                    WHERE TRUNC(trx.MOVEMENTDATE) < to_date(?)
+                    AND TRUNC(trx.MOVEMENTDATE) > to_date(?)
                     AND trx.TRXPROCESSDATE >= to_timestamp(?, ?)
                     AND trx.ad_org_id IN ('1')
                     AND 0=0
@@ -380,8 +380,8 @@
             JOIN M_PRODUCT p ON trx.m_product_id = p.m_product_id
             JOIN M_TRANSACTION_COST tc ON trx.m_transaction_id = tc.m_transaction_id 
               AND tc.dateacct < to_date(?) AND tc.dateacct > to_date(?)
-          WHERE trx.MOVEMENTDATE < to_date(?)
-            AND trx.MOVEMENTDATE > to_date(?)
+          WHERE TRUNC(trx.MOVEMENTDATE) < to_date(?)
+            AND TRUNC(trx.MOVEMENTDATE) > to_date(?)
             AND trx.TRXPROCESSDATE >= to_timestamp(?, ?)
             AND trx.ad_org_id IN ('1')
             AND trx.iscostcalculated = 'Y'
@@ -423,8 +423,8 @@
         JOIN m_transaction trx ON trx.m_product_id = p.m_product_id
         JOIN m_locator l ON trx.m_locator_id = l.m_locator_id
       WHERE trx.iscostcalculated = 'N'
-        AND trx.MOVEMENTDATE < to_date(?)
-        AND trx.MOVEMENTDATE > to_date(?)
+        AND TRUNC(trx.MOVEMENTDATE) < to_date(?)
+        AND TRUNC(trx.MOVEMENTDATE) > to_date(?)
         AND trx.TRXPROCESSDATE >= to_timestamp(?, ?)
         AND  trx.ad_org_id IN ('2')
         AND 2 = 2
--- a/src/org/openbravo/materialmgmt/GenerateValuedStockAggregated_data.xsql	Thu Apr 25 09:53:25 2019 +0200
+++ b/src/org/openbravo/materialmgmt/GenerateValuedStockAggregated_data.xsql	Thu Apr 25 09:54:50 2019 +0200
@@ -64,7 +64,7 @@
                 SELECT trx.ad_client_id, trx.ad_org_id, trx.createdby, trx.updatedby, trx.m_product_id, trx.m_locator_id, trx.c_uom_id, trx.movementqty,
                   0 AS trxcost
                 FROM m_transaction trx
-                WHERE trx.movementdate <= TO_DATE(?)
+                WHERE TRUNC(trx.movementdate) <= TO_DATE(?)
                 AND 2=2
                 AND 4=4
                 AND 5=5
@@ -79,7 +79,7 @@
                 FROM(SELECT SUM(CASE WHEN t2.movementqty < 0 THEN -cost ELSE cost END) AS trxcost, t2.m_transaction_id, tc2.c_currency_id, COALESCE(dateacct, costdate) AS movementdate
                      FROM m_transaction_cost tc2
                      JOIN m_transaction t2 ON (tc2.m_transaction_id = t2.m_transaction_id)
-                     WHERE dateacct <= TO_DATE(?)
+                     WHERE TRUNC(dateacct) <= TO_DATE(?)
                      AND 3=3
                      AND 6=6
                      AND 7=7
@@ -110,7 +110,7 @@
     <Parameter name="cCurrencyID"/>
     <Parameter name="mCostingRuleID"/>
     <Parameter name="dateTo"/>
-    <Parameter name="startingDate" optional="true" after="AND 2=2"><![CDATA[ AND trx.movementdate > TO_DATE(?) ]]></Parameter>
+    <Parameter name="startingDate" optional="true" after="AND 2=2"><![CDATA[ AND TRUNC(trx.movementdate) > TO_DATE(?) ]]></Parameter>
     <Parameter name="crStartDate" optional="true" after="AND 4=4"><![CDATA[ AND trx.trxprocessdate >= to_timestamp(?, 'DD-MM-YYYY HH24:MI:SS') ]]></Parameter>
     <Parameter name="crEndDate" optional="true" after="AND 5=5"><![CDATA[ AND trx.trxprocessdate <= to_timestamp(?, 'DD-MM-YYYY HH24:MI:SS') ]]></Parameter>
     <Parameter name="adClientId"/>
@@ -118,7 +118,7 @@
     <Parameter name="cCurrencyID"/>
     <Parameter name="legalOrg"/>
     <Parameter name="dateTo"/>
-    <Parameter name="startingDate" optional="true" after="AND 3=3"><![CDATA[ AND dateacct > TO_DATE(?) ]]></Parameter>
+    <Parameter name="startingDate" optional="true" after="AND 3=3"><![CDATA[ AND TRUNC(dateacct) > TO_DATE(?) ]]></Parameter>
     <Parameter name="crStartDate" optional="true" after="AND 6=6"><![CDATA[ AND t2.trxprocessdate >= to_timestamp(?, 'DD-MM-YYYY HH24:MI:SS') ]]></Parameter>
     <Parameter name="crEndDate" optional="true" after="AND 7=7"><![CDATA[ AND t2.trxprocessdate <= to_timestamp(?, 'DD-MM-YYYY HH24:MI:SS') ]]></Parameter>
     <Parameter name="adClientId"/>