[Reservations]Fixes in trigger and procedure.
authorGorka Ion Damián <gorkaion.damian@openbravo.com>
Tue, 23 Oct 2012 10:39:46 +0200
changeset 18656 bbe2cd86c639
parent 18655 67a495fee94a
child 18657 12768892fc35
[Reservations]Fixes in trigger and procedure.
src-db/database/model/functions/M_RESERVATION_CONSUMPTION.xml
src-db/database/model/triggers/M_RESERVATION_STOCK_TRG.xml
--- a/src-db/database/model/functions/M_RESERVATION_CONSUMPTION.xml	Tue Oct 23 10:05:10 2012 +0200
+++ b/src-db/database/model/functions/M_RESERVATION_CONSUMPTION.xml	Tue Oct 23 10:39:46 2012 +0200
@@ -66,6 +66,19 @@
   cur_reserved_stock       RECORD;
   cur_not_allocated_stock  RECORD;
 BEGIN
+  SELECT r.m_product_id, r.c_uom_id, COALESCE(r.reservedqty, 0), COALESCE(r.releasedqty, 0), r.quantity - COALESCE(r.reservedqty, 0),
+      COALESCE(SUM(alloc.quantity - COALESCE(alloc.releasedqty, 0)), 0),
+      r.m_warehouse_id, r.m_locator_id, r.m_attributesetinstance_id
+    INTO v_product_id, v_uom_id, v_this_reservedqty, v_this_releasedqty, v_this_notreserved,
+      v_this_allocated,
+      v_dim_warehouse_id, v_dim_locator_id, v_dim_asi_id
+  FROM m_reservation r
+      LEFT JOIN m_reservation_stock alloc ON r.m_reservation_id = alloc.m_reservation_id AND alloc.isallocated = 'Y'
+  WHERE r.m_reservation_id = p_reservation_id
+  GROUP BY r.m_product_id, r.c_uom_id, r.reservedqty, r.releasedqty, r.quantity,
+      r.m_warehouse_id, r.m_locator_id, r.m_attributesetinstance_id;
+  v_this_noalloc := v_this_reservedqty - v_this_allocated;
+
   SELECT sd.qtyonhand - COALESCE(rs.reservedstock, 0), sd.m_storage_detail_id, loc.m_warehouse_id
     INTO v_notreservedstock, v_storage_detail_id, v_warehouse_id
   FROM m_storage_detail sd
@@ -84,16 +97,6 @@
     AND COALESCE(sd.m_attributesetinstance_id, '0') = COALESCE(p_attributesetinstance_id, '0')
     AND sd.m_locator_id = p_locator_id;
 
-  SELECT r.m_product_id, r.c_uom_id, COALESCE(r.reservedqty, 0)y, COALESCE(r.releasedqty, 0), r.quantity - COALESCE(r.reservedqty, 0),
-      COALESCE(SUM(alloc.quantity - COALESCE(alloc.releasedqty, 0)), 0),
-      r.m_warehouse_id, r.m_locator_id, r.m_attributesetinstance_id
-    INTO v_product_id, v_uom_id, v_this_reservedqty, v_this_releasedqty, v_this_notreserved,
-      v_this_allocated,
-      v_dim_warehouse_id, v_dim_locator_id, v_dim_asi_id
-  FROM m_reservation r
-      LEFT JOIN m_reservation_stock alloc ON r.m_reservation_id = alloc.m_reservation_id AND alloc.isallocated = 'Y'
-  WHERE r.m_reservation_id = p_reservation_id;
-  v_this_noalloc := v_this_reservedqty - v_this_allocated;
 
   IF (COALESCE(v_dim_warehouse_id, v_warehouse_id) != v_warehouse_id
       OR COALESCE(v_dim_locator_id, p_locator_id) != p_locator_id
@@ -112,7 +115,7 @@
       SELECT quantity - releasedqty AS reservedqty, m_reservation_stock_id, isallocated
       FROM m_reservation_stock
       WHERE m_locator_id = p_locator_id
-        AND m_attributesetinstance_id = p_attributesetinstance_id
+        AND COALESCE(m_attributesetinstance_id, '0') = COALESCE(p_attributesetinstance_id, '0')
       ORDER BY CASE isallocated WHEN 'Y' THEN 0 ELSE 1 END
   ) LOOP
     v_qtytorelease := LEAST(cur_reserved_stock.reservedqty, v_pendingtorelease);
--- a/src-db/database/model/triggers/M_RESERVATION_STOCK_TRG.xml	Tue Oct 23 10:05:10 2012 +0200
+++ b/src-db/database/model/triggers/M_RESERVATION_STOCK_TRG.xml	Tue Oct 23 10:39:46 2012 +0200
@@ -28,14 +28,14 @@
   END IF;
 
   IF (INSERTING OR UPDATING) THEN
-    v_reservedqtydiff := :NEW.quantity;
-    v_releasedqtydiff := :NEW.releasedqty;
+    v_reservedqtydiff := COALESCE(:NEW.quantity, 0);
+    v_releasedqtydiff := COALESCE(:NEW.releasedqty, 0);
     v_reservation_id := :NEW.m_reservation_id;
   END IF;
 
   IF (UPDATING OR DELETING) THEN
-    v_reservedqtydiff := -:OLD.quantity;
-    v_releasedqtydiff := -:OLD.releasedqty;
+    v_reservedqtydiff := v_reservedqtydiff - COALESCE(:OLD.quantity, 0);
+    v_releasedqtydiff := v_releasedqtydiff - COALESCE(:OLD.releasedqty, 0);
     v_reservation_id := :OLD.m_reservation_id;
   END IF;