[Reservations]Avoid consuming on hold reservations.
authorGorka Ion Damián <gorkaion.damian@openbravo.com>
Thu, 25 Oct 2012 11:35:33 +0200
changeset 18661 2d0858b58e59
parent 18660 64b27bbfdd72
child 18662 c05cf8173234
[Reservations]Avoid consuming on hold reservations.
src-db/database/model/functions/M_RESERVATION_CONSUMPTION.xml
--- a/src-db/database/model/functions/M_RESERVATION_CONSUMPTION.xml	Thu Oct 25 11:34:12 2012 +0200
+++ b/src-db/database/model/functions/M_RESERVATION_CONSUMPTION.xml	Thu Oct 25 11:35:33 2012 +0200
@@ -48,6 +48,7 @@
   v_dim_warehouse_id    VARCHAR2(32);
   v_dim_locator_id      VARCHAR2(32);
   v_dim_asi_id          VARCHAR2(32);
+  v_res_status          M_RESERVATION.RES_STATUS%TYPE;
   v_this_notreserved    NUMBER;
   v_this_reservedqty    NUMBER;
   v_this_releasedqty    NUMBER;
@@ -68,10 +69,10 @@
 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
+      r.m_warehouse_id, r.m_locator_id, r.m_attributesetinstance_id, res_status
     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
+      v_dim_warehouse_id, v_dim_locator_id, v_dim_asi_id, v_res_status
   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
@@ -79,6 +80,10 @@
       r.m_warehouse_id, r.m_locator_id, r.m_attributesetinstance_id;
   v_this_noalloc := v_this_reservedqty - v_this_allocated;
 
+  IF (v_res_status = 'HO') THEN
+    RAISE_APPLICATION_ERROR(-20000, '@CannotConsumeHoldReservation@');
+  END IF;
+
   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
@@ -88,6 +93,7 @@
         FROM m_reservation_stock rs
           JOIN m_reservation r ON rs.m_reservation_id = r.m_reservation_id
         WHERE r.m_product_id = v_product_id
+          AND r.res_status NOT IN ('CL')
           AND r.c_uom_id = v_uom_id
           AND rs.m_locator_id = p_locator_id
           AND COALESCE(rs.m_attributesetinstance_id, '0') = COALESCE(p_attributesetinstance_id, '0')
@@ -203,6 +209,7 @@
         AND rs.m_locator_id = p_locator_id
         AND COALESCE(rs.m_attributesetinstance_id, '0') = COALESCE(p_attributesetinstance_id, '0')
         AND rs.isallocated = 'N'
+        AND r.res_status NOT IN ('HO', 'CL')
         AND rs.quantity != rs.releasedqty
   ) LOOP
     v_qtytorelease := LEAST(v_pendingtorelease, cur_not_allocated_stock.quantity - cur_not_allocated_stock.releasedqty);