Related to Issue 27395. Fix issue when calculating several trx
authorGorka Ion Damián <gorkaion.damian@openbravo.com>
Mon, 05 Jan 2015 12:18:46 +0100
changeset 25726 401f1e9fc80d
parent 25725 87b0a4144cc7
child 25727 33b8eec9e03f
Related to Issue 27395. Fix issue when calculating several trx

When calculating more than one transaction of the same product the first
transaction value amount is not considered.
Add a coalesce on the trigger for the cost amount
Do incremental updates instead of calculating the total amount.
src-db/database/model/functions/M_UPDATE_STOCK_VALUATION.xml
src-db/database/model/triggers/M_TRANSACTION_COST_TRG.xml
--- a/src-db/database/model/functions/M_UPDATE_STOCK_VALUATION.xml	Wed Dec 17 19:06:50 2014 +0100
+++ b/src-db/database/model/functions/M_UPDATE_STOCK_VALUATION.xml	Mon Jan 05 12:18:46 2015 +0100
@@ -82,7 +82,7 @@
   v_stock := v_stock + p_stock;
   v_valuation := v_valuation + p_valuation;
 
-  IF (v_stock= 0) THEN
+  IF (v_stock = 0) THEN
     v_unit_price := 0;
     v_valuation := 0;
   ELSE
@@ -91,13 +91,13 @@
 
   IF (v_count > 0) THEN
     UPDATE m_stock_valuation
-    SET stock = v_stock,
-        stock_valuation = v_valuation,
+    SET stock = stock + p_stock,
+        stock_valuation = stock_valuation + p_valuation,
         unit_price = v_unit_price
-     WHERE ad_client_id = p_client
-       AND ad_org_id = p_org
-       AND m_product_id = p_product
-       AND (v_warehouse IS NULL OR m_warehouse_id = v_warehouse);
+    WHERE ad_client_id = p_client
+      AND ad_org_id = p_org
+      AND m_product_id = p_product
+      AND (v_warehouse IS NULL OR m_warehouse_id = v_warehouse);
   ELSE
     INSERT INTO m_stock_valuation (
         m_stock_valuation_id, ad_client_id, ad_org_id,
--- a/src-db/database/model/triggers/M_TRANSACTION_COST_TRG.xml	Wed Dec 17 19:06:50 2014 +0100
+++ b/src-db/database/model/triggers/M_TRANSACTION_COST_TRG.xml	Mon Jan 05 12:18:46 2015 +0100
@@ -61,7 +61,7 @@
   FROM DUAL
   WHERE EXISTS (SELECT 1 FROM ad_preference
                 WHERE property = 'UnitaryCost'
-                  AND ad_client_id IN (:NEW.ad_client_id, '0')
+                  AND ad_client_id IN (v_client_id, '0')
                   AND TO_CHAR(value) = 'Y');
 
   IF (v_updatestockvaluation > 0) THEN
@@ -77,7 +77,7 @@
         JOIN m_locator l ON t.m_locator_id = l.m_locator_id
       WHERE m_transaction_id = v_transaction_id;
 
-      v_valuation := c_currency_convert_precision(:NEW.cost, v_currency_id, v_currency_to_id, :NEW.costdate, NULL, v_client_id, v_legal_org_id, 'C');
+      v_valuation := c_currency_convert_precision(COALESCE(:NEW.cost, 0), v_currency_id, v_currency_to_id, :NEW.costdate, NULL, v_client_id, v_legal_org_id, 'C');
       v_valuation := v_valuation * v_sign;
       M_UPDATE_STOCK_VALUATION(v_client_id, v_legal_org_id, :NEW.createdby, v_warehouse_id, v_product_id, 0, v_valuation);
     END IF;