Fixes issue 021199: Module script DeleteInventoryLines is incomplete
authorJavier Etxarri <javier.echarri@openbravo.com>
Wed, 25 Jul 2012 12:27:17 +0200
changeset 17897 9af9c20db94b
parent 17896 da1248116f0e
child 17898 90d2a49c5544
Fixes issue 021199: Module script DeleteInventoryLines is incomplete
src-util/modulescript/build/classes/org/openbravo/modulescript/DeleteInventoryLines.class
src-util/modulescript/build/classes/org/openbravo/modulescript/DeleteInventoryLinesData.class
src-util/modulescript/src/org/openbravo/modulescript/DeleteInventoryLines.java
src-util/modulescript/src/org/openbravo/modulescript/DeleteInventoryLines_data.xsql
Binary file src-util/modulescript/build/classes/org/openbravo/modulescript/DeleteInventoryLines.class has changed
Binary file src-util/modulescript/build/classes/org/openbravo/modulescript/DeleteInventoryLinesData.class has changed
--- a/src-util/modulescript/src/org/openbravo/modulescript/DeleteInventoryLines.java	Wed Jul 18 16:37:40 2012 +0200
+++ b/src-util/modulescript/src/org/openbravo/modulescript/DeleteInventoryLines.java	Wed Jul 25 12:27:17 2012 +0200
@@ -26,6 +26,7 @@
   public void execute() {
     try {
       ConnectionProvider cp = getConnectionProvider();
+      DeleteInventoryLinesData.deleteStockAuxLines(cp);
       DeleteInventoryLinesData.deleteInventoryLines(cp);
     } catch (Exception e) {
       handleError(e);
--- a/src-util/modulescript/src/org/openbravo/modulescript/DeleteInventoryLines_data.xsql	Wed Jul 18 16:37:40 2012 +0200
+++ b/src-util/modulescript/src/org/openbravo/modulescript/DeleteInventoryLines_data.xsql	Wed Jul 25 12:27:17 2012 +0200
@@ -30,15 +30,40 @@
     <SqlMethodComment></SqlMethodComment>
     <Sql><![CDATA[
       DELETE FROM m_storage_detail
-        WHERE m_product_id IN (SELECT p.m_product_id
-                      FROM m_product p JOIN m_attributeset aset ON p.m_attributeset_id = aset.m_attributeset_id
-                      WHERE coalesce(p.attrsetvaluetype, '-') <> 'F'
-                      AND aset.isoneattrsetvalrequired = 'Y')
-        AND COALESCE(m_attributesetinstance_id, '0') = '0'
-        AND QTYONHAND = 0 
-        AND PREQTYONHAND = 0 
-        AND (QTYORDERONHAND=0 OR QTYORDERONHAND IS NULL)
-        AND (PREQTYORDERONHAND=0 OR PREQTYORDERONHAND IS NULL)
+        WHERE m_storage_detail_id IN
+        	(SELECT m_storage_detail_id
+        	FROM M_STORAGE_DETAIL MS
+	        WHERE m_product_id IN (SELECT p.m_product_id
+	                      FROM m_product p JOIN m_attributeset aset ON p.m_attributeset_id = aset.m_attributeset_id
+	                      WHERE coalesce(p.attrsetvaluetype, '-') <> 'F'
+	                      AND aset.isoneattrsetvalrequired = 'Y')
+	        AND COALESCE(m_attributesetinstance_id, '0') = '0'
+	        AND QTYONHAND = 0 
+	        AND PREQTYONHAND = 0 
+	        AND (QTYORDERONHAND=0 OR QTYORDERONHAND IS NULL)
+	        AND (PREQTYORDERONHAND=0 OR PREQTYORDERONHAND IS NULL)
+	        AND NOT EXISTS (SELECT 1 FROM m_stock_aux SA WHERE SA.M_STORAGE_DETAIL_ID = MS.M_STORAGE_DETAIL_ID));
+      ]]>
+    </Sql>
+  </SqlMethod>
+  <SqlMethod name="deleteStockAuxLines" type="preparedStatement" return="rowcount">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+      DELETE FROM m_stock_aux
+        WHERE m_storage_detail_id IN
+        	(SELECT m_storage_detail_id
+        	FROM M_STORAGE_DETAIL MS
+	        WHERE m_product_id IN (SELECT p.m_product_id
+	                      FROM m_product p JOIN m_attributeset aset ON p.m_attributeset_id = aset.m_attributeset_id
+	                      WHERE coalesce(p.attrsetvaluetype, '-') <> 'F'
+	                      AND aset.isoneattrsetvalrequired = 'Y')
+	        AND COALESCE(m_attributesetinstance_id, '0') = '0'
+	        AND QTYONHAND = 0 
+	        AND PREQTYONHAND = 0 
+	        AND (QTYORDERONHAND=0 OR QTYORDERONHAND IS NULL)
+	        AND (PREQTYORDERONHAND=0 OR PREQTYORDERONHAND IS NULL))
+	        AND COALESCE(quantity, 0) = 0
+			AND COALESCE(qtyorder, 0) = 0;
       ]]>
     </Sql>
   </SqlMethod>