[Reservations]Fix issue in trigger.
authorGorka Ion Damián <gorkaion.damian@openbravo.com>
Fri, 26 Oct 2012 11:09:13 +0200
changeset 18668 fdde0ba22069
parent 18667 5442589decf3
child 18669 cd873d384798
[Reservations]Fix issue in trigger.
src-db/database/model/triggers/M_TRANSACTION_TRG2.xml
--- a/src-db/database/model/triggers/M_TRANSACTION_TRG2.xml	Fri Oct 26 09:17:27 2012 +0200
+++ b/src-db/database/model/triggers/M_TRANSACTION_TRG2.xml	Fri Oct 26 11:09:13 2012 +0200
@@ -21,31 +21,43 @@
 ************************************************************************/
   v_onhand      NUMBER;
   v_reserved    NUMBER;
+  v_count       NUMBER;
 
 BEGIN
   IF (AD_isTriggerEnabled() = 'N') THEN RETURN;
   END IF;
 
   IF (INSERTING) THEN
-    IF (:NEW.movementqty < 0) THEN
+    IF (:NEW.movementqty < 0 AND :NEW.m_product_uom_id IS NULL) THEN
       -- Check reservations. Reserved stock must be released before inserting the transacion.
-      SELECT COALESCE(sum(rs.quantity -rs.releasedqty), 0), sd.qtyonhand
-        INTO v_reserved, v_onhand
-      FROM m_reservation_stock rs
-          JOIN m_reservation r ON rs.m_reservation_id = r.m_reservation_id
-          JOIN m_storage_detail sd ON r.m_product_id = sd.m_product_id
-                                      AND r.c_uom_id = sd.c_uom_id
-                                      AND COALESCE(rs.m_attributesetinstance_id, '0') = sd.m_attributesetinstance_id
-                                      AND rs.m_locator_id = sd.m_locator_id
-                                      AND sd.m_product_uom_id IS NULL
-      WHERE res_status <> 'CL'
-        AND r.m_product_id = :NEW.m_product_id
-        AND r.c_uom_id = :NEW.c_uom_id
-        AND rs.m_attributesetinstance_id = :NEW.m_attributesetinstance_id
-        AND rs.m_locator_id = :NEW.m_locator_id
-      GROUP BY sd.qtyonhand, sd.m_storage_detail_id;
-      IF (v_onhand - v_reserved < ABS(:NEW.movementqty)) THEN
-        RAISE_APPLICATION_ERROR(-20000, '@CannotTakeOutReservedStock@');
+      SELECT count(1) INTO v_count
+      FROM dual
+      WHERE EXISTS (SELECT 1
+                    FROM m_reservation r
+                        JOIN m_reservation_stock rs ON r.m_reservation_id = rs.m_reservation_id
+                    WHERE r.m_product_id = :NEW.m_product_id
+                      AND r.c_uom_id = :NEW.c_uom_id
+                      AND COALESCE(rs.m_attributesetinstance_id, '0') = :NEW.m_attributesetinstance_id
+                      AND rs.m_locator_id = :NEW.m_locator_id);
+      IF (v_count > 1) THEN
+        SELECT COALESCE(sum(rs.quantity - COALESCE(rs.releasedqty,0)), 0), sd.qtyonhand
+          INTO v_reserved, v_onhand
+        FROM m_reservation_stock rs
+            JOIN m_reservation r ON rs.m_reservation_id = r.m_reservation_id
+            JOIN m_storage_detail sd ON r.m_product_id = sd.m_product_id
+                                        AND r.c_uom_id = sd.c_uom_id
+                                        AND COALESCE(rs.m_attributesetinstance_id, '0') = COALESCE(sd.m_attributesetinstance_id, '0')
+                                        AND rs.m_locator_id = sd.m_locator_id
+                                        AND sd.m_product_uom_id IS NULL
+        WHERE res_status <> 'CL'
+          AND r.m_product_id = :NEW.m_product_id
+          AND r.c_uom_id = :NEW.c_uom_id
+          AND COALESCE(rs.m_attributesetinstance_id, '0') = :NEW.m_attributesetinstance_id
+          AND rs.m_locator_id = :NEW.m_locator_id
+        GROUP BY sd.qtyonhand, sd.m_storage_detail_id;
+        IF (v_onhand - v_reserved < ABS(:NEW.movementqty)) THEN
+          RAISE_APPLICATION_ERROR(-20000, '@CannotTakeOutReservedStock@');
+        END IF;
       END IF;
     END IF;
   END IF;