[Reservations]Do not allow to reserve unavailable stock.
authorGorka Ion Damián <gorkaion.damian@openbravo.com>
Tue, 13 Nov 2012 13:47:07 +0100
changeset 18695 f584300636c8
parent 18694 57becadd32b4
child 18696 018782102b24
[Reservations]Do not allow to reserve unavailable stock.
src-db/database/model/functions/M_RESERVATION_POST.xml
--- a/src-db/database/model/functions/M_RESERVATION_POST.xml	Mon Nov 12 18:42:01 2012 +0100
+++ b/src-db/database/model/functions/M_RESERVATION_POST.xml	Tue Nov 13 13:47:07 2012 +0100
@@ -39,6 +39,8 @@
   v_newstatus       VARCHAR2(60);
   v_newaction       VARCHAR2(60);
   v_orderline_id    VARCHAR2(32);
+  v_product_id      VARCHAR2(32);
+  v_uom_id          VARCHAR2(32);
   v_count           NUMBER;
   v_quantity        NUMBER;
   v_reservedqty     NUMBER;
@@ -86,6 +88,7 @@
   */
     DECLARE
       v_sales_order_id        VARCHAR2(32);
+      cur_not_available       RECORD;
       v_linecount             NUMBER;
       v_creservedcount        NUMBER;
       v_preservedcount        NUMBER;
@@ -93,6 +96,33 @@
       IF (v_resstatus != 'DR') THEN
         RAISE_APPLICATION_ERROR(-20000, '@ActionNotSupported@');
       END IF;
+      -- Check that stock is still available
+      FOR cur_not_available IN (
+        SELECT AD_COLUMN_IDENTIFIER('M_Locator', rs.m_locator_id, 'en_US') as locatorName,
+               asi.description AS attributeName
+        FROM m_reservation_stock rs
+            JOIN m_attributesetinstance asi ON COALESCE(rs.m_attributesetinstance_id, '0') = asi.m_attributesetinstance_id
+            LEFT JOIN m_storage_detail sd ON sd.m_product_id = v_product_id
+                                             AND sd.c_uom_id = v_uom_id
+                                             AND sd.m_product_uom_id IS NULL
+                                             AND COALESCE(sd.m_attributesetinstance_id, '0') = COALESCE(rs.m_attributesetinstance_id, '0')
+                                             AND sd.m_locator_id = rs.m_locator_id
+            LEFT JOIN (
+                    SELECT SUM(rs2.quantity - COALESCE(rs2.releasedqty,0)) AS reservedstock, rs2.m_locator_id, COALESCE(rs2.m_attributesetinstance_id,'0') AS m_attributesetinstance_id
+                    FROM m_reservation_stock rs2
+                      JOIN m_reservation r ON rs2.m_reservation_id = r.m_reservation_id
+                    WHERE r.m_product_id = v_product_id
+                      AND r.res_status NOT IN ('CL', 'DR')
+                      AND r.c_uom_id = v_uom_id
+                    GROUP BY rs2.m_locator_id, COALESCE(rs2.m_attributesetinstance_id,'0')
+                  ) other ON rs.m_locator_id = other.m_locator_id
+                             AND COALESCE(rs.m_attributesetinstance_id, '0') = COALESCE(other.m_attributesetinstance_id, '0')
+        WHERE rs.quantity - COALESCE(rs.releasedqty, 0) > COALESCE(sd.qtyonhand, 0) - COALESCE(other.reservedstock, 0)
+          AND rs.m_reservation_id = v_reservation_id
+      ) LOOP
+        RAISE_APPLICATION_ERROR(-20000, '@NotEnoughAvailableStock@ : ' || cur_not_available.locatorName || ' - ' || cur_not_available.attributeName);
+      END LOOP;
+      
       IF (v_orderline_id IS NOT NULL) THEN
         SELECT count(*) INTO v_count
         FROM m_reservation