[Reservations]Implementation of procedures and several fixes.
authorGorka Ion Damián <gorkaion.damian@openbravo.com>
Mon, 22 Oct 2012 18:36:20 +0200
changeset 18652 649b5e295f8c
parent 18651 66c2483f9825
child 18653 101a5161866d
[Reservations]Implementation of procedures and several fixes.
Procedures to manage reservations have been implemented:
- M_Reservation_Consumption
- M_Reserve_Stock_Manual
- M_Reserva_Stock_Auto
- ...
Trigger to keep updated quantities on reservation header added.
Several bug fixes and other minor enhancements.
src-db/database/model/functions/M_ADD_RESERVED_STOCK.xml
src-db/database/model/functions/M_CREATE_RESERVE_FROM_SOL.xml
src-db/database/model/functions/M_CREATE_RESERVE_STOCK.xml
src-db/database/model/functions/M_INOUT_POST.xml
src-db/database/model/functions/M_RESERVATION_CONSUMPTION.xml
src-db/database/model/functions/M_RESERVATION_POST.xml
src-db/database/model/functions/M_RESERVE_STOCK_AUTO.xml
src-db/database/model/functions/M_RESERVE_STOCK_MANUAL.xml
src-db/database/model/tables/M_RESERVATION.xml
src-db/database/model/tables/M_RESERVATION_STOCK.xml
src-db/database/model/triggers/M_RESERVATION_STOCK_TRG.xml
src-db/database/sourcedata/AD_COLUMN.xml
src-db/database/sourcedata/AD_ELEMENT.xml
src-db/database/sourcedata/AD_FIELD.xml
src/org/openbravo/materialmgmt/CSResponse.java
src/org/openbravo/materialmgmt/ReservationUtils.java
src/org/openbravo/materialmgmt/ReservationUtils_data.xsql
--- a/src-db/database/model/functions/M_ADD_RESERVED_STOCK.xml	Fri Oct 19 11:49:54 2012 +0200
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,93 +0,0 @@
-<?xml version="1.0"?>
-  <database name="FUNCTION M_ADD_RESERVED_STOCK">
-    <function name="M_ADD_RESERVED_STOCK" type="VARCHAR">
-      <parameter name="p_reservation_id" type="VARCHAR" mode="in">
-        <default/>
-      </parameter>
-      <parameter name="p_type_id" type="VARCHAR" mode="in">
-        <default/>
-      </parameter>
-      <parameter name="p_stock_id" type="VARCHAR" mode="in">
-        <default/>
-      </parameter>
-      <parameter name="p_qty" type="NUMERIC" mode="in">
-        <default/>
-      </parameter>
-      <parameter name="p_user_id" type="VARCHAR" mode="in">
-        <default/>
-      </parameter>
-      <body><![CDATA[/*************************************************************************
-* The contents of this file are subject to the Openbravo  Public  License
-* Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
-* Version 1.1  with a permitted attribution clause; you may not  use this
-* file except in compliance with the License. You  may  obtain  a copy of
-* the License at http://www.openbravo.com/legal/license.html
-* Software distributed under the License  is  distributed  on  an "AS IS"
-* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
-* License for the specific  language  governing  rights  and  limitations
-* under the License.
-* The Original Code is Openbravo ERP.
-* The Initial Developer of the Original Code is Openbravo SLU
-* All portions are Copyright (C) 2012 Openbravo SLU
-* All Rights Reserved.
-* Contributor(s):  ______________________________________.
-************************************************************************/
-/*************
-* Function to reserve given stock or purchase order line.
-* Available types are:
-*   - 'SD': p_stock_id represents a m_storage_detail_id reserves stock in the warehouse.
-*   - 'PO': p_stock_id represents a c_orderline_id of a pending to receipt purchase order line.
-**************/
-  v_reservation_stock_ID    VARCHAR2(32) := get_uuid();
-  v_client_id               VARCHAR2(32);
-  v_org_id                  VARCHAR2(32);
-  v_locator_id              VARCHAR2(32);
-  v_asi_id                  VARCHAR2(32);
-  v_poline_id               VARCHAR2(32);
-  v_count                   NUMBER;
-BEGIN
-  SELECT ad_client_id, ad_org_id
-    INTO v_client_id, v_org_id
-  FROM m_reservation
-  WHERE m_reservation_id = p_reservation_id;
-  IF (p_type_id = 'SD') THEN
-    SELECT count(*) INTO v_count
-    FROM m_storage_detail sd
-      JOIN m_locator loc ON sd.m_locator_id = loc.m_locator_id
-      JOIN m_reservation res ON res.m_reservation_id = p_reservation_id
-    WHERE sd.m_storage_detail_id = p_stock_id
-      AND sd.m_product_id = res.m_product_id
-      AND sd.m_attributesetinstance_id = COALESCE(res.m_attributesetinstance_id, sd.m_attributesetinstance_id)
-      AND sd.m_locator_id = COALESCE(res.m_locator_id, sd.m_locator_id)
-      AND loc.m_warehouse_id = COALESCE(res.m_warehouse_id, loc.m_warehouse_id);
-    IF (v_count = 0) THEN
-      RAISE_APPLICATION_ERROR(-20000, '@GivenStorageDetailDoesNotMatchReservationRequirements@');
-    END IF;
-
-    SELECT m_locator_id, m_attributesetinstance_id
-      INTO v_locator_id, v_asi_id
-    FROM m_storage_detail
-    WHERE m_storage_detail_id = p_stock_id;
-  ELSIF (p_type_id = 'PO') THEN
-    v_poline_id := p_stock_id;
-  ELSE
-    RAISE_APPLICATION_ERROR(-20000, '@UnsupportedReservationType@');
-  END IF;
-
-  INSERT INTO m_reservation_stock(
-    m_reservation_stock_id, ad_client_id, ad_org_id, isactive,
-    created, createdby, updated, updatedby,
-    m_reservation_id, m_attributesetinstance_id, m_locator_id, c_orderline_id,
-    quantity, pendingqty
-  ) VALUES (
-    v_reservation_stock_id, v_client_id, v_org_id, 'Y',
-    now(), p_user_id, now(), p_user_id,
-    p_reservation_id, v_asi_id, v_locator_id, v_poline_id,
-    p_qty, p_qty
-  );
-
-
-  RETURN v_reservation_stock_id;
-END M_ADD_RESERVED_STOCK]]></body>
-    </function>
-  </database>
--- a/src-db/database/model/functions/M_CREATE_RESERVE_FROM_SOL.xml	Fri Oct 19 11:49:54 2012 +0200
+++ b/src-db/database/model/functions/M_CREATE_RESERVE_FROM_SOL.xml	Mon Oct 22 18:36:20 2012 +0200
@@ -1,6 +1,6 @@
 <?xml version="1.0"?>
   <database name="FUNCTION M_CREATE_RESERVE_FROM_SOL">
-    <function name="M_CREATE_RESERVE_FROM_SOL" type="VARCHAR">
+    <function name="M_CREATE_RESERVE_FROM_SOL" type="NULL">
       <parameter name="p_orderline_id" type="VARCHAR" mode="in">
         <default/>
       </parameter>
@@ -10,6 +10,9 @@
       <parameter name="p_user_id" type="VARCHAR" mode="in">
         <default/>
       </parameter>
+      <parameter name="p_reservation_id" type="VARCHAR" mode="out">
+        <default/>
+      </parameter>
       <body><![CDATA[/*************************************************************************
 * The contents of this file are subject to the Openbravo  Public  License
 * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
@@ -27,7 +30,7 @@
 * Contributor(s):  ______________________________________.
 ************************************************************************/
 /*************
-* Function to create reservations for the given Sales Order Line.
+* Procedure to create reservations for the given Sales Order Line.
 **************/
   v_product_id        VARCHAR2(32);
   v_uom_id            VARCHAR2(32);
@@ -59,13 +62,13 @@
     m_reservation_id, ad_client_id, ad_org_id, isactive,
     created, createdby, updated, updatedby,
     c_orderline_id,
-    m_product_id, c_uom_id, quantity, reservedqty, pendingqty,
+    m_product_id, c_uom_id, quantity, reservedqty, releasedqty,
     res_status, res_process
   ) VALUES (
     v_reservation_id, v_client_id, v_org_id, 'Y',
     now(), p_user_id, now(), p_user_id,
     p_orderline_id,
-    v_product_id, v_uom_id, v_qtyordered, 0, v_qtyordered - v_qtydelivered,
+    v_product_id, v_uom_id, v_qtyordered, 0, v_qtydelivered,
     'DR', 'CO'
   );
 
@@ -73,7 +76,8 @@
     M_RESERVATION_POST(null, v_reservation_id, 'PR', p_user_id);
   END IF;
 
-  RETURN v_reservation_id;
+  p_reservation_id := v_reservation_id;
+RETURN;
 END M_CREATE_RESERVE_FROM_SOL]]></body>
     </function>
   </database>
--- a/src-db/database/model/functions/M_CREATE_RESERVE_STOCK.xml	Fri Oct 19 11:49:54 2012 +0200
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,127 +0,0 @@
-<?xml version="1.0"?>
-  <database name="FUNCTION M_CREATE_RESERVE_STOCK">
-    <function name="M_CREATE_RESERVE_STOCK" type="VARCHAR">
-      <parameter name="p_reservation_id" type="VARCHAR" mode="in">
-        <default/>
-      </parameter>
-      <parameter name="p_user_id" type="VARCHAR" mode="in">
-        <default/>
-      </parameter>
-      <body><![CDATA[/*************************************************************************
-* The contents of this file are subject to the Openbravo  Public  License
-* Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
-* Version 1.1  with a permitted attribution clause; you may not  use this
-* file except in compliance with the License. You  may  obtain  a copy of
-* the License at http://www.openbravo.com/legal/license.html
-* Software distributed under the License  is  distributed  on  an "AS IS"
-* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
-* License for the specific  language  governing  rights  and  limitations
-* under the License.
-* The Original Code is Openbravo ERP.
-* The Initial Developer of the Original Code is Openbravo SLU
-* All portions are Copyright (C) 2012 Openbravo SLU
-* All Rights Reserved.
-* Contributor(s):  ______________________________________.
-************************************************************************/
-/*************
-* Function to reserve stock using the get stock function.
-**************/
-  v_product_id                  VARCHAR2(32);
-  v_uom_id                      VARCHAR2(32);
-  v_asi_id                      VARCHAR2(32);
-  v_locator_id                  VARCHAR2(32);
-  v_warehouse_id                VARCHAR2(32);
-  v_client_id                   VARCHAR2(32);
-  v_org_id                      VARCHAR2(32);
-  v_soline_id                   VARCHAR2(32);
-  v_qty                         NUMBER;
-  v_pendingtoreserveqty         NUMBER;
-  v_pendingqty                  NUMBER;
-  v_dummy                     VARCHAR2(2000) := '';
-  v_pinstance_id                VARCHAR2(32) := get_uuid();
-  
-
-  TYPE RECORD IS REF CURSOR;
-  cur_stock                     RECORD;
-
-BEGIN
-  SELECT COALESCE(pendingqty, quantity) - COALESCE(reservedqty,0),
-         m_product_id, c_uom_id, m_attributesetinstance_id, m_locator_id, m_warehouse_id,
-         ad_client_id, ad_org_id, c_orderline_id
-    INTO v_pendingtoreserveqty,
-         v_product_id, v_uom_id, v_asi_id, v_locator_id, v_warehouse_id,
-         v_client_id, v_org_id, v_soline_id
-  FROM m_reservation
-  WHERE m_reservation_id = p_reservation_id;
-
-  IF (v_pendingtoreserveqty <= 0) THEN
-    RETURN '@NothingToReserve@';
-  END IF;
-
-  -- Call M_GET_STOCK
-  DECLARE
-    v_pinstance_result AD_PInstance.result%TYPE;
-    v_pinstance_msg AD_PInstance.errormsg%TYPE;
-    v_warehouse_rule_id VARCHAR2(32);
-  BEGIN
-    INSERT INTO ad_pinstance (
-        ad_pinstance_id, ad_process_id, record_id, isactive, 
-        ad_user_id, ad_client_id, ad_org_id, created, createdby,
-        updated, updatedby
-    ) VALUES (
-        v_pinstance_id, 'FF80818132C964E30132C9747257002E', p_reservation_id, 'Y',
-        p_User_ID, v_client_id, v_Org_ID, now(), p_user_ID,
-        now(), p_user_ID
-    );
-
-    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '10', 'AD_Client_ID', v_client_id, null, null, null, null, null);
-    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '20', 'AD_Org_ID', v_org_id, null, null, null, null, null);
-    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '30', 'M_Product_ID', v_product_id, null, null, null, null, null);
-    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '40', 'C_Uom_ID', v_uom_id, null, null, null, null, null);
-    --AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '50', 'M_Product_Uom_ID', v_Product_UOM_old, null, null, null, null, null);
-    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '60', 'M_Warehouse_ID', v_warehouse_id, null, null, null, null, null);
-    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '70', 'M_Locator_ID', v_locator_id, null, null, null, null, null);
-    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '80', 'M_AttributesetInstance_ID', v_asi_id, null, null, null, null, null);
-    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '90', 'Quantity', null, null, v_pendingtoreserveqty, null, null, null);
-    /*
-    * ADD PROPER PROCESS ID!
-    */
-    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '100', 'ProcessID', '---', null, null, null, null, null);
-
-    IF (v_soline_id IS NOT NULL) THEN
-      SELECT m_warehouse_rule_id INTO v_warehouse_rule_id
-      FROM c_orderline
-      WHERE c_orderline_id = v_soline_id;
-      AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '110', 'M_Warehouse_Rule_ID', v_warehouse_rule_id, null, null, null, null, null);
-    END IF;
-
-    M_GET_STOCK(v_pinstance_ID, 'N');
-    -- Check result
-    SELECT result, errormsg
-      INTO v_pinstance_result, v_pinstance_msg
-    FROM ad_pinstance
-    WHERE ad_pinstance_id = v_pinstance_id;
-    IF (v_pinstance_result = 0) THEN
-      -- Error on m_get_stock
-      RAISE_APPLICATION_ERROR(-20000, v_pinstance_msg);
-    END IF;
-  END; -- End Call M_GET_STOCK
-  
-  v_pendingqty := v_pendingtoreserveqty;
-  FOR cur_stock IN (
-    SELECT *
-    FROM m_stock_proposed
-    WHERE ad_pinstance_id = v_pinstance_id
-  ) LOOP
-    v_qty := LEAST(cur_stock.quantity, v_pendingqty);
-    v_dummy := M_ADD_RESERVED_STOCK(p_reservation_id, 'SD', cur_stock.m_storage_detail_id, v_qty, p_user_id);
-    v_pendingqty := v_pendingqty - v_qty;
-    IF (v_pendingqty <= 0) THEN
-      RETURN '@ReservationFullyReserved@';
-    END IF;
-  END LOOP;
-
-  RETURN '@ReservationPartiallyReserved@';
-END M_CREATE_RESERVE_STOCK]]></body>
-    </function>
-  </database>
--- a/src-db/database/model/functions/M_INOUT_POST.xml	Fri Oct 19 11:49:54 2012 +0200
+++ b/src-db/database/model/functions/M_INOUT_POST.xml	Mon Oct 22 18:36:20 2012 +0200
@@ -45,6 +45,7 @@
   -- Logistice
   v_ResultStr VARCHAR2(2000):='';
   v_Message VARCHAR2(2000):='';
+  v_Message_aux VARCHAR2(2000):='';
   v_Record_ID VARCHAR2(32);
   v_User VARCHAR2(32);
   v_PUser VARCHAR2(32);
@@ -92,6 +93,7 @@
     FINISH_PROCESS BOOLEAN:=false;
     v_Aux NUMBER;
     v_ProductName M_Product.name%TYPE;
+    v_reservation_id    VARCHAR2(32);
   BEGIN
     IF(p_PInstance_ID IS NOT NULL) THEN
       --  Update AD_PInstance
@@ -332,6 +334,20 @@
               AND ProductType='I';
             -- Create Transaction for stocked product
             IF(Cur_InOutLine.M_Product_ID IS NOT NULL AND v_IsStocked=1 AND Cur_InOutLine.IsDescription <> 'Y') THEN
+              IF (cur_inout.issotrx = 'Y' AND cur_inoutline.c_orderline_id IS NOT NULL AND v_qty < 0 AND cur_inoutline.canceled_inoutline_id IS NULL) THEN
+                -- Manage reservations.
+                SELECT count(*), max(m_reservation_id)
+                  INTO v_aux, v_reservation_id
+                FROM m_reservation
+                WHERE c_orderline_id = cur_inoutline.c_orderline_id
+                  AND res_status != 'CL';
+                IF (v_aux > 1) THEN
+                  RAISE_APPLICATION_ERROR(-20000, '@SOLineWithMoreThanOneOpenReservation@');
+                ELSIF (v_aux = 1) THEN
+                  M_RESERVATION_CONSUMPTION(v_reservation_id, cur_inoutline.m_locator_id, cur_inoutline.m_attributesetinstance_id, cur_inoutline.movementqty, v_user, v_result, v_message);
+                END IF;
+              END IF;
+              
               v_ResultStr:='CreateTransaction';
               Ad_Sequence_Next('M_Transaction', Cur_InOutLine.AD_Org_ID, v_NextNo) ;
               INSERT
@@ -484,7 +500,7 @@
             END;
           ELSE
             v_ResultStr:='Check delivery rule for sales orders';
-            v_Message:='';
+            v_Message_aux:='';
             v_orderid_old:='0';
             FOR Cur_Order IN 
               (SELECT c_order.deliveryrule, m_inoutline.line, c_order.c_order_id,
@@ -502,20 +518,20 @@
                ORDER BY c_order.c_order_id, c_orderline.line) LOOP
               --Order lines not completely delivered with delivery rule O or L
               IF (v_orderid_old <> cur_order.c_order_id OR cur_order.deliveryrule <> 'O' ) THEN
-                v_Message := COALESCE(v_Message,'') || '@Shipment@' || ' ' || cur_inout.documentno;
-                v_Message := v_Message || ' ' || '@line@' || ' ' || cur_order.line || ': ';
-                v_Message := v_Message || '@SalesOrderDocumentno@' || cur_order.documentno;
+                v_Message_aux := COALESCE(v_Message_aux,'') || '@Shipment@' || ' ' || cur_inout.documentno;
+                v_Message_aux := v_Message_aux || ' ' || '@line@' || ' ' || cur_order.line || ': ';
+                v_Message_aux := v_Message_aux || '@SalesOrderDocumentno@' || cur_order.documentno;
                 IF (cur_order.deliveryrule = 'O') THEN
-                  v_Message := v_Message || ' ' || '@notCompleteDeliveryRuleOrder@' || '<br>';
+                  v_Message_aux := v_Message_aux || ' ' || '@notCompleteDeliveryRuleOrder@' || '<br>';
                 ELSE
-                  v_Message := v_Message || ' ' || '@line@' || ' ' || cur_order.orderline;
-                  v_Message := v_Message || ' ' || '@notCompleteDeliveryRuleLine@' || '<br>';
+                  v_Message_aux := v_Message_aux || ' ' || '@line@' || ' ' || cur_order.orderline;
+                  v_Message_aux := v_Message_aux || ' ' || '@notCompleteDeliveryRuleLine@' || '<br>';
                 END IF;
               END IF;
               v_orderid_old := cur_order.c_order_id;
             END LOOP;
-            IF (v_Message IS NOT NULL AND v_Message <> '') THEN
-              RAISE_APPLICATION_ERROR(-20000, v_message);
+            IF (v_Message_aux IS NOT NULL AND v_Message_aux <> '') THEN
+              RAISE_APPLICATION_ERROR(-20000, v_Message_aux);
             END IF;
           END IF;
           -- Close Shipment
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-db/database/model/functions/M_RESERVATION_CONSUMPTION.xml	Mon Oct 22 18:36:20 2012 +0200
@@ -0,0 +1,326 @@
+<?xml version="1.0"?>
+  <database name="FUNCTION M_RESERVATION_CONSUMPTION">
+    <function name="M_RESERVATION_CONSUMPTION" type="NULL">
+      <parameter name="p_reservation_id" type="VARCHAR" mode="in">
+        <default/>
+      </parameter>
+      <parameter name="p_locator_id" type="VARCHAR" mode="in">
+        <default/>
+      </parameter>
+      <parameter name="p_attributesetinstance_id" type="VARCHAR" mode="in">
+        <default/>
+      </parameter>
+      <parameter name="p_qty" type="NUMERIC" mode="in">
+        <default/>
+      </parameter>
+      <parameter name="p_user_id" type="VARCHAR" mode="in">
+        <default/>
+      </parameter>
+      <parameter name="p_result" type="NUMERIC" mode="out">
+        <default/>
+      </parameter>
+      <parameter name="p_message" type="VARCHAR" mode="out">
+        <default/>
+      </parameter>
+      <body><![CDATA[/*************************************************************************
+* The contents of this file are subject to the Openbravo  Public  License
+* Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
+* Version 1.1  with a permitted attribution clause; you may not  use this
+* file except in compliance with the License. You  may  obtain  a copy of
+* the License at http://www.openbravo.com/legal/license.html
+* Software distributed under the License  is  distributed  on  an "AS IS"
+* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
+* License for the specific  language  governing  rights  and  limitations
+* under the License.
+* The Original Code is Openbravo ERP.
+* The Initial Developer of the Original Code is Openbravo SLU
+* All portions are Copyright (C) 2012 Openbravo SLU
+* All Rights Reserved.
+* Contributor(s):  ______________________________________.
+************************************************************************/
+
+  v_pendingtorelease    NUMBER;
+  v_warehouse_id        VARCHAR2(32);
+  v_product_id          VARCHAR2(32);
+  v_uom_id              VARCHAR2(32);
+  v_res_stock_id        VARCHAR2(32);
+  v_storage_detail_id   VARCHAR2(32);
+  v_dim_warehouse_id    VARCHAR2(32);
+  v_dim_locator_id      VARCHAR2(32);
+  v_dim_asi_id          VARCHAR2(32);
+  v_this_notreserved    NUMBER;
+  v_this_reservedqty    NUMBER;
+  v_this_releasedqty    NUMBER;
+  v_this_allocated      NUMBER;
+  v_this_noalloc        NUMBER;
+  v_notreservedstock    NUMBER;
+  v_reservedbyothers    NUMBER;
+  v_qtytorelease        NUMBER;
+  v_qtytounreserve      NUMBER;
+  v_qtytounreserve_aux  NUMBER;
+  v_newreservedqty      NUMBER;
+  v_availablestock      NUMBER;
+  v_dummy               VARCHAR2(2000);
+
+  TYPE RECORD IS REF CURSOR;
+  cur_reserved_stock       RECORD;
+  cur_not_allocated_stock  RECORD;
+BEGIN
+  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
+    JOIN m_locator loc ON sd.m_locator_id = loc.m_locator_id
+    LEFT JOIN  (
+        SELECT SUM(rs.quantity - rs.releasedqty) AS reservedstock
+        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.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')
+      ) rs ON 1=1
+  WHERE sd.m_product_id = v_product_id
+    AND sd.c_uom_id = v_uom_id
+    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
+      OR COALESCE(v_dim_asi_id, p_attributesetinstance_id) != p_attributesetinstance_id) THEN
+    RAISE_APPLICATION_ERROR(-20000, '@WrongStockDimensionMismatch@');
+  END IF;
+
+  v_pendingtorelease := p_qty;
+  IF (v_pendingtorelease > v_this_reservedqty - v_this_releasedqty) THEN
+    v_pendingtorelease := v_this_reservedqty - v_this_releasedqty;
+    p_result := 2;
+    p_message := '@MoreQuantityToReleaseThanPending@';
+  END IF;
+  -- Release stock reserved in the reservation.
+  FOR cur_reserved_stock IN (
+      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
+      ORDER BY CASE isallocated WHEN 'Y' THEN 0 ELSE 1 END
+  ) LOOP
+    v_qtytorelease := LEAST(cur_reserved_stock.reservedqty, v_pendingtorelease);
+    UPDATE m_reservation_stock
+    SET releasedqty = releasedqty + v_qtytorelease,
+        updated = now(),
+        updatedby = p_user_id
+    WHERE m_reservation_stock_id = cur_reserved_stock.m_reservation_stock_id;
+    v_this_releasedqty := v_this_releasedqty + v_qtytorelease;
+    v_this_allocated := v_this_allocated - CASE cur_reserved_stock.isallocated WHEN 'Y' THEN v_qtytorelease ELSE 0 END;
+    v_this_noalloc := v_this_noalloc - CASE cur_reserved_stock.isallocated WHEN 'N' THEN v_qtytorelease ELSE 0 END;
+
+    v_pendingtorelease := v_pendingtorelease - v_qtytorelease;
+    IF (v_pendingtorelease = 0) THEN
+      -- Stock completely released from the reservation
+      RETURN;
+    END IF;
+  END LOOP;
+
+  IF (v_pendingtorelease > COALESCE(v_this_noalloc, 0) + COALESCE(v_this_notreserved, 0)) THEN
+    -- There is still stock pending to release but is higher than the not allocated or not reserved quantity, as it is
+    -- not possible to modify allocated reserved stock with a different dimension an exception is raised.
+    RAISE_APPLICATION_ERROR(-20000, '@CannotModifyAllocatedReserve@');
+  END IF;
+  
+  -- If exists not reserved stock reserve in current reservation.
+  IF (v_notreservedstock >= 0) THEN
+    -- Reserve available stock
+    v_qtytorelease := LEAST(v_notreservedstock, v_pendingtorelease);
+
+    IF (v_qtytorelease > COALESCE(v_this_notreserved, 0)) THEN
+      --The new reserved stock is higher than the pending quantity to reserve, other reserved stock must be unreserved.
+      v_qtytounreserve := v_qtytorelease - COALESCE(v_this_notreserved, 0);
+      FOR cur_reserved_stock IN (
+          SELECT m_reservation_stock_id, quantity - COALESCE(releasedqty, 0) AS reservedqty
+          FROM m_reservation_stock
+          WHERE m_reservation_id = p_reservation_id
+            AND isallocated = 'N'
+            AND quantity != releasedqty
+      ) LOOP
+        v_qtytounreserve_aux := LEAST(v_qtytounreserve, cur_reserved_stock.reservedqty);
+        UPDATE m_reservation_stock
+        SET quantity = quantity - v_qtytounreserve_aux
+        WHERE m_reservation_stock_id = cur_reserved_stock.m_reservation_stock_id;
+        v_qtytounreserve := v_qtytounreserve - v_qtytounreserve_aux;
+        v_this_noalloc := v_this_noalloc - v_qtytounreserve_aux;
+        v_this_notreserved := v_this_notreserved + v_qtytounreserve_aux;
+
+        IF (v_qtytounreserve = 0) THEN
+          EXIT;
+        END IF;
+      END LOOP;
+    END IF;
+
+    M_RESERVE_STOCK_MANUAL(p_reservation_id, 'SD', v_storage_detail_id, v_qtytorelease, p_user_id, v_res_stock_id);
+    v_this_notreserved := v_this_notreserved - v_qtytorelease;
+    v_this_noalloc := v_this_noalloc + v_qtytorelease;
+    -- Release reserved stock
+    UPDATE m_reservation_stock
+    SET releasedqty = releasedqty + v_qtytorelease,
+        updated = now(),
+        updatedby = p_user_id
+    WHERE m_reservation_stock_id = v_res_stock_id;
+
+    v_this_releasedqty := v_this_releasedqty + v_qtytorelease;
+    v_this_noalloc := v_this_noalloc - v_qtytorelease;
+
+    v_pendingtorelease := v_pendingtorelease - v_qtytorelease;
+    IF (v_pendingtorelease = 0) THEN
+      -- Stock completely released from the reservation
+      RETURN;
+    END IF;
+  END IF;
+
+  -- Finally take other reservation's not allocated stock.
+  FOR cur_not_allocated_stock IN (
+      SELECT r.m_reservation_id, rs.quantity, rs.releasedqty, rs.m_reservation_stock_id, r.reservedqty, r.quantity as quantitytoreserve,
+             r.ad_client_id, r.ad_org_id, r.m_product_id, r.c_uom_id, r.m_attributesetinstance_id, r.m_locator_id, r.m_warehouse_id,
+             r.c_orderline_id
+      FROM m_reservation_stock rs
+          JOIN m_reservation r ON rs.m_reservation_id = r.m_reservation_id
+      WHERE r.m_reservation_id != p_reservation_id
+        AND r.m_product_id = v_product_id
+        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')
+        AND rs.isallocated = 'N'
+        AND rs.quantity != rs.releasedqty
+  ) LOOP
+    v_qtytorelease := LEAST(v_pendingtorelease, cur_not_allocated_stock.quantity - cur_not_allocated_stock.releasedqty);
+    
+    IF (v_qtytorelease > COALESCE(v_this_notreserved, 0)) THEN
+      --The new reserved stock is higher than the pending quantity to reserve, other reserved stock must be unreserved.
+      v_qtytounreserve := v_qtytorelease - COALESCE(v_this_notreserved, 0);
+      FOR cur_reserved_stock IN (
+          SELECT m_reservation_stock_id, quantity - COALESCE(releasedqty, 0) AS reservedqty
+          FROM m_reservation_stock
+          WHERE m_reservation_id = p_reservation_id
+            AND isallocated = 'N'
+            AND quantity != releasedqty
+      ) LOOP
+        v_qtytounreserve_aux := LEAST(v_qtytounreserve, cur_reserved_stock.reservedqty);
+        UPDATE m_reservation_stock
+        SET quantity = quantity - v_qtytounreserve_aux
+        WHERE m_reservation_stock_id = cur_reserved_stock.m_reservation_stock_id;
+        v_qtytounreserve := v_qtytounreserve - v_qtytounreserve_aux;
+        v_this_noalloc := v_this_noalloc - v_qtytounreserve_aux;
+        v_this_notreserved := v_this_notreserved + v_qtytounreserve_aux;
+
+        IF (v_qtytounreserve = 0) THEN
+          EXIT;
+        END IF;
+      END LOOP;
+    END IF;
+    
+    UPDATE m_reservation_stock
+    SET quantity = quantity - v_qtytorelease
+    WHERE m_reservation_stock_id = cur_not_allocated_stock.m_reservation_stock_id;
+    M_RESERVE_STOCK_MANUAL(p_reservation_id, 'SD', v_storage_detail_id, v_qtytorelease, p_user_id, v_res_stock_id);
+    v_this_notreserved := v_this_notreserved - v_qtytorelease;
+    v_this_noalloc := v_this_noalloc + v_qtytorelease;
+    
+    -- Call get stock to check availability
+    DECLARE
+      v_pinstance_result      AD_PInstance.result%TYPE;
+      v_pinstance_msg         AD_PInstance.errormsg%TYPE;
+      v_warehouse_rule_id     VARCHAR2(32);
+      v_pinstance_id          VARCHAR2(32) := get_uuid();
+    BEGIN
+      INSERT INTO ad_pinstance (
+          ad_pinstance_id, ad_process_id, record_id, isactive, 
+          ad_user_id, ad_client_id, ad_org_id, created, createdby,
+          updated, updatedby
+      ) VALUES (
+          v_pinstance_id, 'FF80818132C964E30132C9747257002E', p_reservation_id, 'Y',
+          p_User_ID, cur_not_allocated_stock.ad_client_id, cur_not_allocated_stock.ad_Org_ID, now(), p_user_ID,
+          now(), p_user_ID
+      );
+  
+      AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '10', 'AD_Client_ID', cur_not_allocated_stock.ad_client_id, null, null, null, null, null);
+      AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '20', 'AD_Org_ID', cur_not_allocated_stock.ad_org_id, null, null, null, null, null);
+      AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '30', 'M_Product_ID', cur_not_allocated_stock.m_product_id, null, null, null, null, null);
+      AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '40', 'C_Uom_ID', cur_not_allocated_stock.c_uom_id, null, null, null, null, null);
+      --AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '50', 'M_Product_Uom_ID', v_Product_UOM_old, null, null, null, null, null);
+      AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '60', 'M_Warehouse_ID', cur_not_allocated_stock.m_warehouse_id, null, null, null, null, null);
+      AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '70', 'M_Locator_ID', cur_not_allocated_stock.m_locator_id, null, null, null, null, null);
+      AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '80', 'M_AttributesetInstance_ID', cur_not_allocated_stock.m_attributesetinstance_id, null, null, null, null, null);
+      AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '90', 'Quantity', null, null, cur_not_allocated_stock.quantitytoreserve, null, null, null);
+      /*
+      * ADD PROPER PROCESS ID!
+      */
+      AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '100', 'ProcessID', '---', null, null, null, null, null);
+  
+      IF (cur_not_allocated_stock.c_orderline_id IS NOT NULL) THEN
+        SELECT m_warehouse_rule_id INTO v_warehouse_rule_id
+        FROM c_orderline
+        WHERE c_orderline_id = cur_not_allocated_stock.c_orderline_id;
+        AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '110', 'M_Warehouse_Rule_ID', v_warehouse_rule_id, null, null, null, null, null);
+      END IF;
+  
+      M_GET_STOCK(v_pinstance_ID, 'N');
+      -- Check result
+      SELECT result, errormsg
+        INTO v_pinstance_result, v_pinstance_msg
+      FROM ad_pinstance
+      WHERE ad_pinstance_id = v_pinstance_id;
+      IF (v_pinstance_result = 0) THEN
+        -- Error on m_get_stock
+        RAISE_APPLICATION_ERROR(-20000, v_pinstance_msg);
+      END IF;
+      SELECT sum(quantity) INTO v_availablestock
+      FROM m_stock_proposed
+      WHERE ad_pinstance_id = v_pinstance_id;
+    END; -- End Call M_GET_STOCK
+    
+    IF (v_availablestock >= v_qtytorelease) THEN
+      M_RESERVE_STOCK_AUTO(cur_not_allocated_stock.m_reservation_id, p_user_id, v_newreservedqty);
+
+      v_pendingtorelease := v_pendingtorelease - v_qtytorelease;
+      IF (v_pendingtorelease = 0) THEN
+        -- Stock completely released from the reservation
+        RETURN;
+      END IF;
+    ELSE
+      --Not enough available stock has been able to reallocate using new stock, undo reallocation.
+      UPDATE m_reservation_stock
+      SET releasedqty = releasedqty - v_qtytorelease,
+          quantity = quantity - v_qtytorelease,
+          updated = now(),
+          updatedby = p_user_id
+      WHERE m_reservation_stock_id = v_res_stock_id;
+      v_this_notreserved := v_this_notreserved + v_qtytorelease;
+      v_this_noalloc := v_this_noalloc - v_qtytorelease;
+
+      UPDATE m_reservation_stock
+      SET quantity = quantity + v_qtytorelease
+      WHERE m_reservation_stock_id = cur_not_allocated_stock.m_reservation_stock_id;
+    END IF;
+  END LOOP;
+  -- Delete records with quantity 0 that might be left after a not successfull reallocation attempt.
+  DELETE FROM m_reservation_stock
+  WHERE m_reservation_id = p_reservation_id
+    AND quantity = 0;
+
+  IF (v_pendingtorelease > 0) THEN
+    RAISE_APPLICATION_ERROR(-20000, '@CannotConsumeAllStock@');
+  END IF;
+
+END M_RESERVATION_CONSUMPTION]]></body>
+    </function>
+  </database>
--- a/src-db/database/model/functions/M_RESERVATION_POST.xml	Fri Oct 19 11:49:54 2012 +0200
+++ b/src-db/database/model/functions/M_RESERVATION_POST.xml	Mon Oct 22 18:36:20 2012 +0200
@@ -87,7 +87,7 @@
     v_newaction := 'HO';
     
     -- ADD RESERVED STOCK
-    v_message := M_CREATE_RESERVE_STOCK (v_reservation_id, v_user_id);
+    M_RESERVE_STOCK_AUTO (v_reservation_id, v_user_id, v_message);
 
   ELSIF (v_resaction = 'HO') THEN
   /*
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-db/database/model/functions/M_RESERVE_STOCK_AUTO.xml	Mon Oct 22 18:36:20 2012 +0200
@@ -0,0 +1,134 @@
+<?xml version="1.0"?>
+  <database name="FUNCTION M_RESERVE_STOCK_AUTO">
+    <function name="M_RESERVE_STOCK_AUTO" type="NULL">
+      <parameter name="p_reservation_id" type="VARCHAR" mode="in">
+        <default/>
+      </parameter>
+      <parameter name="p_user_id" type="VARCHAR" mode="in">
+        <default/>
+      </parameter>
+      <parameter name="p_reservedqty" type="NUMERIC" mode="out">
+        <default/>
+      </parameter>
+      <body><![CDATA[/*************************************************************************
+* The contents of this file are subject to the Openbravo  Public  License
+* Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
+* Version 1.1  with a permitted attribution clause; you may not  use this
+* file except in compliance with the License. You  may  obtain  a copy of
+* the License at http://www.openbravo.com/legal/license.html
+* Software distributed under the License  is  distributed  on  an "AS IS"
+* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
+* License for the specific  language  governing  rights  and  limitations
+* under the License.
+* The Original Code is Openbravo ERP.
+* The Initial Developer of the Original Code is Openbravo SLU
+* All portions are Copyright (C) 2012 Openbravo SLU
+* All Rights Reserved.
+* Contributor(s):  ______________________________________.
+************************************************************************/
+/*************
+* Function to reserve stock using the get stock function.
+**************/
+  v_product_id                  VARCHAR2(32);
+  v_uom_id                      VARCHAR2(32);
+  v_asi_id                      VARCHAR2(32);
+  v_locator_id                  VARCHAR2(32);
+  v_warehouse_id                VARCHAR2(32);
+  v_client_id                   VARCHAR2(32);
+  v_org_id                      VARCHAR2(32);
+  v_soline_id                   VARCHAR2(32);
+  v_qty                         NUMBER;
+  v_pendingtoreserveqty         NUMBER;
+  v_pendingqty                  NUMBER;
+  v_reserveqty                  NUMBER;
+  v_dummy                       VARCHAR2(2000) := '';
+  v_pinstance_id                VARCHAR2(32) := get_uuid();
+  
+
+  TYPE RECORD IS REF CURSOR;
+  cur_stock                     RECORD;
+
+BEGIN
+  SELECT quantity - COALESCE(reservedqty,0),
+         m_product_id, c_uom_id, m_attributesetinstance_id, m_locator_id, m_warehouse_id,
+         ad_client_id, ad_org_id, c_orderline_id, quantity
+    INTO v_pendingtoreserveqty,
+         v_product_id, v_uom_id, v_asi_id, v_locator_id, v_warehouse_id,
+         v_client_id, v_org_id, v_soline_id, v_reserveqty
+  FROM m_reservation
+  WHERE m_reservation_id = p_reservation_id;
+
+  IF (v_pendingtoreserveqty <= 0) THEN
+    p_reservedqty := 0;
+    RETURN;
+  END IF;
+
+  -- Call M_GET_STOCK
+  DECLARE
+    v_pinstance_result AD_PInstance.result%TYPE;
+    v_pinstance_msg AD_PInstance.errormsg%TYPE;
+    v_warehouse_rule_id VARCHAR2(32);
+  BEGIN
+    INSERT INTO ad_pinstance (
+        ad_pinstance_id, ad_process_id, record_id, isactive, 
+        ad_user_id, ad_client_id, ad_org_id, created, createdby,
+        updated, updatedby
+    ) VALUES (
+        v_pinstance_id, 'FF80818132C964E30132C9747257002E', p_reservation_id, 'Y',
+        p_User_ID, v_client_id, v_Org_ID, now(), p_user_ID,
+        now(), p_user_ID
+    );
+
+    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '10', 'AD_Client_ID', v_client_id, null, null, null, null, null);
+    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '20', 'AD_Org_ID', v_org_id, null, null, null, null, null);
+    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '30', 'M_Product_ID', v_product_id, null, null, null, null, null);
+    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '40', 'C_Uom_ID', v_uom_id, null, null, null, null, null);
+    --AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '50', 'M_Product_Uom_ID', v_Product_UOM_old, null, null, null, null, null);
+    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '60', 'M_Warehouse_ID', v_warehouse_id, null, null, null, null, null);
+    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '70', 'M_Locator_ID', v_locator_id, null, null, null, null, null);
+    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '80', 'M_AttributesetInstance_ID', v_asi_id, null, null, null, null, null);
+    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '90', 'Quantity', null, null, v_pendingtoreserveqty, null, null, null);
+    /*
+    * ADD PROPER PROCESS ID!
+    */
+    AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '100', 'ProcessID', '---', null, null, null, null, null);
+
+    IF (v_soline_id IS NOT NULL) THEN
+      SELECT m_warehouse_rule_id INTO v_warehouse_rule_id
+      FROM c_orderline
+      WHERE c_orderline_id = v_soline_id;
+      AD_PINSTANCE_PARA_INSERT(v_pinstance_id, '110', 'M_Warehouse_Rule_ID', v_warehouse_rule_id, null, null, null, null, null);
+    END IF;
+
+    M_GET_STOCK(v_pinstance_ID, 'N');
+    -- Check result
+    SELECT result, errormsg
+      INTO v_pinstance_result, v_pinstance_msg
+    FROM ad_pinstance
+    WHERE ad_pinstance_id = v_pinstance_id;
+    IF (v_pinstance_result = 0) THEN
+      -- Error on m_get_stock
+      RAISE_APPLICATION_ERROR(-20000, v_pinstance_msg);
+    END IF;
+  END; -- End Call M_GET_STOCK
+  
+  v_pendingqty := v_pendingtoreserveqty;
+  FOR cur_stock IN (
+    SELECT *
+    FROM m_stock_proposed
+    WHERE ad_pinstance_id = v_pinstance_id
+  ) LOOP
+    v_qty := LEAST(cur_stock.quantity, v_pendingqty);
+    M_RESERVE_STOCK_MANUAL(p_reservation_id, 'SD', cur_stock.m_storage_detail_id, v_qty, p_user_id, v_dummy);
+    v_pendingqty := v_pendingqty - v_qty;
+    IF (v_pendingqty <= 0) THEN
+      p_reservedqty := v_reserveqty;
+      RETURN;
+    END IF;
+  END LOOP;
+
+  p_reservedqty := v_reserveqty - v_pendingqty;
+  RETURN;
+END M_RESERVE_STOCK_AUTO]]></body>
+    </function>
+  </database>
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-db/database/model/functions/M_RESERVE_STOCK_MANUAL.xml	Mon Oct 22 18:36:20 2012 +0200
@@ -0,0 +1,96 @@
+<?xml version="1.0"?>
+  <database name="FUNCTION M_RESERVE_STOCK_MANUAL">
+    <function name="M_RESERVE_STOCK_MANUAL" type="NULL">
+      <parameter name="p_reservation_id" type="VARCHAR" mode="in">
+        <default/>
+      </parameter>
+      <parameter name="p_type_id" type="VARCHAR" mode="in">
+        <default/>
+      </parameter>
+      <parameter name="p_stock_id" type="VARCHAR" mode="in">
+        <default/>
+      </parameter>
+      <parameter name="p_qty" type="NUMERIC" mode="in">
+        <default/>
+      </parameter>
+      <parameter name="p_user_id" type="VARCHAR" mode="in">
+        <default/>
+      </parameter>
+      <parameter name="p_reservation_stock_id" type="VARCHAR" mode="out">
+        <default/>
+      </parameter>
+      <body><![CDATA[/*************************************************************************
+* The contents of this file are subject to the Openbravo  Public  License
+* Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
+* Version 1.1  with a permitted attribution clause; you may not  use this
+* file except in compliance with the License. You  may  obtain  a copy of
+* the License at http://www.openbravo.com/legal/license.html
+* Software distributed under the License  is  distributed  on  an "AS IS"
+* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
+* License for the specific  language  governing  rights  and  limitations
+* under the License.
+* The Original Code is Openbravo ERP.
+* The Initial Developer of the Original Code is Openbravo SLU
+* All portions are Copyright (C) 2012 Openbravo SLU
+* All Rights Reserved.
+* Contributor(s):  ______________________________________.
+************************************************************************/
+/*************
+* Function to reserve given stock or purchase order line.
+* Available types are:
+*   - 'SD': p_stock_id represents a m_storage_detail_id reserves stock in the warehouse.
+*   - 'PO': p_stock_id represents a c_orderline_id of a pending to receipt purchase order line.
+**************/
+  v_reservation_stock_ID    VARCHAR2(32) := get_uuid();
+  v_client_id               VARCHAR2(32);
+  v_org_id                  VARCHAR2(32);
+  v_locator_id              VARCHAR2(32);
+  v_asi_id                  VARCHAR2(32);
+  v_poline_id               VARCHAR2(32);
+  v_count                   NUMBER;
+BEGIN
+  SELECT ad_client_id, ad_org_id
+    INTO v_client_id, v_org_id
+  FROM m_reservation
+  WHERE m_reservation_id = p_reservation_id;
+  IF (p_type_id = 'SD') THEN
+    SELECT count(*) INTO v_count
+    FROM m_storage_detail sd
+      JOIN m_locator loc ON sd.m_locator_id = loc.m_locator_id
+      JOIN m_reservation res ON res.m_reservation_id = p_reservation_id
+    WHERE sd.m_storage_detail_id = p_stock_id
+      AND sd.m_product_id = res.m_product_id
+      AND sd.m_attributesetinstance_id = COALESCE(res.m_attributesetinstance_id, sd.m_attributesetinstance_id)
+      AND sd.m_locator_id = COALESCE(res.m_locator_id, sd.m_locator_id)
+      AND loc.m_warehouse_id = COALESCE(res.m_warehouse_id, loc.m_warehouse_id);
+    IF (v_count = 0) THEN
+      RAISE_APPLICATION_ERROR(-20000, '@GivenStorageDetailDoesNotMatchReservationRequirements@');
+    END IF;
+
+    SELECT m_locator_id, m_attributesetinstance_id
+      INTO v_locator_id, v_asi_id
+    FROM m_storage_detail
+    WHERE m_storage_detail_id = p_stock_id;
+  ELSIF (p_type_id = 'PO') THEN
+    v_poline_id := p_stock_id;
+  ELSE
+    RAISE_APPLICATION_ERROR(-20000, '@UnsupportedReservationType@');
+  END IF;
+
+  INSERT INTO m_reservation_stock(
+    m_reservation_stock_id, ad_client_id, ad_org_id, isactive,
+    created, createdby, updated, updatedby,
+    m_reservation_id, m_attributesetinstance_id, m_locator_id, c_orderline_id,
+    quantity, releasedqty, isallocated
+  ) VALUES (
+    v_reservation_stock_id, v_client_id, v_org_id, 'Y',
+    now(), p_user_id, now(), p_user_id,
+    p_reservation_id, v_asi_id, v_locator_id, v_poline_id,
+    p_qty, 0, 'N'
+  );
+
+  p_reservation_stock_id := v_reservation_stock_id;
+  RETURN ;
+END M_RESERVE_STOCK_MANUAL]]></body>
+    </function>
+  </database>
--- a/src-db/database/model/tables/M_RESERVATION.xml	Fri Oct 19 11:49:54 2012 +0200
+++ b/src-db/database/model/tables/M_RESERVATION.xml	Mon Oct 22 18:36:20 2012 +0200
@@ -77,7 +77,7 @@
         <default/>
         <onCreateDefault/>
       </column>
-      <column name="PENDINGQTY" primaryKey="false" required="false" type="DECIMAL" autoIncrement="false">
+      <column name="RELEASEDQTY" primaryKey="false" required="false" type="DECIMAL" autoIncrement="false">
         <default/>
         <onCreateDefault/>
       </column>
--- a/src-db/database/model/tables/M_RESERVATION_STOCK.xml	Fri Oct 19 11:49:54 2012 +0200
+++ b/src-db/database/model/tables/M_RESERVATION_STOCK.xml	Mon Oct 22 18:36:20 2012 +0200
@@ -61,7 +61,7 @@
         <default/>
         <onCreateDefault/>
       </column>
-      <column name="PENDINGQTY" primaryKey="false" required="false" type="DECIMAL" autoIncrement="false">
+      <column name="RELEASEDQTY" primaryKey="false" required="false" type="DECIMAL" autoIncrement="false">
         <default/>
         <onCreateDefault/>
       </column>
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-db/database/model/triggers/M_RESERVATION_STOCK_TRG.xml	Mon Oct 22 18:36:20 2012 +0200
@@ -0,0 +1,48 @@
+<?xml version="1.0"?>
+  <database name="TRIGGER M_RESERVATION_STOCK_TRG">
+    <trigger name="M_RESERVATION_STOCK_TRG" table="M_RESERVATION_STOCK" fires="before" insert="true" update="true" delete="true" foreach="row">
+      <body><![CDATA[
+
+/*************************************************************************
+* The contents of this file are subject to the Openbravo  Public  License
+* Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
+* Version 1.1  with a permitted attribution clause; you may not  use this
+* file except in compliance with the License. You  may  obtain  a copy of
+* the License at http://www.openbravo.com/legal/license.html
+* Software distributed under the License  is  distributed  on  an "AS IS"
+* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
+* License for the specific  language  governing  rights  and  limitations
+* under the License.
+* The Original Code is Openbravo ERP.
+* The Initial Developer of the Original Code is Openbravo SLU
+* All portions are Copyright (C) 2012 Openbravo SLU
+* All Rights Reserved.
+* Contributor(s):  ______________________________________.
+************************************************************************/
+  v_reservedqtydiff     NUMBER :=0;
+  v_releasedqtydiff     NUMBER :=0;
+  v_reservation_id      VARCHAR2(32);
+BEGIN
+  IF (AD_isTriggerEnabled()='N') THEN
+    RETURN;
+  END IF;
+
+  IF (INSERTING OR UPDATING) THEN
+    v_reservedqtydiff := :NEW.quantity;
+    v_releasedqtydiff := :NEW.releasedqty;
+    v_reservation_id := :NEW.m_reservation_id;
+  END IF;
+
+  IF (UPDATING OR DELETING) THEN
+    v_reservedqtydiff := -:OLD.quantity;
+    v_releasedqtydiff := -:OLD.releasedqty;
+    v_reservation_id := :OLD.m_reservation_id;
+  END IF;
+
+  UPDATE m_reservation
+  SET reservedqty = COALESCE(reservedqty, 0) + v_reservedqtydiff,
+      releasedqty = COALESCE(releasedqty, 0) + v_releasedqtydiff
+  WHERE m_reservation_id = v_reservation_id;
+END]]></body>
+    </trigger>
+  </database>
--- a/src-db/database/sourcedata/AD_COLUMN.xml	Fri Oct 19 11:49:54 2012 +0200
+++ b/src-db/database/sourcedata/AD_COLUMN.xml	Mon Oct 22 18:36:20 2012 +0200
@@ -290993,8 +290993,9 @@
 <!--CAB22D2BB7ECB270E040007F010059C2-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
 <!--CAB22D2BB7ECB270E040007F010059C2-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
 <!--CAB22D2BB7ECB270E040007F010059C2-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
-<!--CAB22D2BB7ECB270E040007F010059C2-->  <NAME><![CDATA[Pending]]></NAME>
-<!--CAB22D2BB7ECB270E040007F010059C2-->  <COLUMNNAME><![CDATA[Pendingqty]]></COLUMNNAME>
+<!--CAB22D2BB7ECB270E040007F010059C2-->  <NAME><![CDATA[Released]]></NAME>
+<!--CAB22D2BB7ECB270E040007F010059C2-->  <HELP><![CDATA[Quantity released from the reservation. For example once it has been shipped to the customer.]]></HELP>
+<!--CAB22D2BB7ECB270E040007F010059C2-->  <COLUMNNAME><![CDATA[ReleasedQty]]></COLUMNNAME>
 <!--CAB22D2BB7ECB270E040007F010059C2-->  <AD_TABLE_ID><![CDATA[77264B07BB0E4FA483A07FB40C2E0FE0]]></AD_TABLE_ID>
 <!--CAB22D2BB7ECB270E040007F010059C2-->  <AD_REFERENCE_ID><![CDATA[29]]></AD_REFERENCE_ID>
 <!--CAB22D2BB7ECB270E040007F010059C2-->  <FIELDLENGTH><![CDATA[12]]></FIELDLENGTH>
@@ -291007,7 +291008,7 @@
 <!--CAB22D2BB7ECB270E040007F010059C2-->  <ISTRANSLATED><![CDATA[N]]></ISTRANSLATED>
 <!--CAB22D2BB7ECB270E040007F010059C2-->  <ISENCRYPTED><![CDATA[N]]></ISENCRYPTED>
 <!--CAB22D2BB7ECB270E040007F010059C2-->  <ISSELECTIONCOLUMN><![CDATA[N]]></ISSELECTIONCOLUMN>
-<!--CAB22D2BB7ECB270E040007F010059C2-->  <AD_ELEMENT_ID><![CDATA[B0D45E247BEBF1A8E040007F0100514B]]></AD_ELEMENT_ID>
+<!--CAB22D2BB7ECB270E040007F010059C2-->  <AD_ELEMENT_ID><![CDATA[CCA48454EB41B295E040007F01006B08]]></AD_ELEMENT_ID>
 <!--CAB22D2BB7ECB270E040007F010059C2-->  <ISSESSIONATTR><![CDATA[N]]></ISSESSIONATTR>
 <!--CAB22D2BB7ECB270E040007F010059C2-->  <ISSECONDARYKEY><![CDATA[N]]></ISSECONDARYKEY>
 <!--CAB22D2BB7ECB270E040007F010059C2-->  <ISDESENCRYPTABLE><![CDATA[N]]></ISDESENCRYPTABLE>
@@ -292158,8 +292159,9 @@
 <!--CC2F9E8FD666D7CDE040007F01002E21-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
 <!--CC2F9E8FD666D7CDE040007F01002E21-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
 <!--CC2F9E8FD666D7CDE040007F01002E21-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
-<!--CC2F9E8FD666D7CDE040007F01002E21-->  <NAME><![CDATA[Pending]]></NAME>
-<!--CC2F9E8FD666D7CDE040007F01002E21-->  <COLUMNNAME><![CDATA[PendingQty]]></COLUMNNAME>
+<!--CC2F9E8FD666D7CDE040007F01002E21-->  <NAME><![CDATA[Released]]></NAME>
+<!--CC2F9E8FD666D7CDE040007F01002E21-->  <HELP><![CDATA[Quantity released from the reservation. For example once it has been shipped to the customer.]]></HELP>
+<!--CC2F9E8FD666D7CDE040007F01002E21-->  <COLUMNNAME><![CDATA[ReleasedQty]]></COLUMNNAME>
 <!--CC2F9E8FD666D7CDE040007F01002E21-->  <AD_TABLE_ID><![CDATA[D6079A4A6C2542678D9A50114367B967]]></AD_TABLE_ID>
 <!--CC2F9E8FD666D7CDE040007F01002E21-->  <AD_REFERENCE_ID><![CDATA[29]]></AD_REFERENCE_ID>
 <!--CC2F9E8FD666D7CDE040007F01002E21-->  <FIELDLENGTH><![CDATA[12]]></FIELDLENGTH>
@@ -292172,7 +292174,7 @@
 <!--CC2F9E8FD666D7CDE040007F01002E21-->  <ISTRANSLATED><![CDATA[N]]></ISTRANSLATED>
 <!--CC2F9E8FD666D7CDE040007F01002E21-->  <ISENCRYPTED><![CDATA[N]]></ISENCRYPTED>
 <!--CC2F9E8FD666D7CDE040007F01002E21-->  <ISSELECTIONCOLUMN><![CDATA[N]]></ISSELECTIONCOLUMN>
-<!--CC2F9E8FD666D7CDE040007F01002E21-->  <AD_ELEMENT_ID><![CDATA[B0D45E247BEBF1A8E040007F0100514B]]></AD_ELEMENT_ID>
+<!--CC2F9E8FD666D7CDE040007F01002E21-->  <AD_ELEMENT_ID><![CDATA[CCA48454EB41B295E040007F01006B08]]></AD_ELEMENT_ID>
 <!--CC2F9E8FD666D7CDE040007F01002E21-->  <ISSESSIONATTR><![CDATA[N]]></ISSESSIONATTR>
 <!--CC2F9E8FD666D7CDE040007F01002E21-->  <ISSECONDARYKEY><![CDATA[N]]></ISSECONDARYKEY>
 <!--CC2F9E8FD666D7CDE040007F01002E21-->  <ISDESENCRYPTABLE><![CDATA[N]]></ISDESENCRYPTABLE>
--- a/src-db/database/sourcedata/AD_ELEMENT.xml	Fri Oct 19 11:49:54 2012 +0200
+++ b/src-db/database/sourcedata/AD_ELEMENT.xml	Mon Oct 22 18:36:20 2012 +0200
@@ -28727,6 +28727,32 @@
 <!--CC52016D7E7BA603E040007F01000DF9-->  <ISGLOSSARY><![CDATA[N]]></ISGLOSSARY>
 <!--CC52016D7E7BA603E040007F01000DF9--></AD_ELEMENT>
 
+<!--CCA48454EB40B295E040007F01006B08--><AD_ELEMENT>
+<!--CCA48454EB40B295E040007F01006B08-->  <AD_ELEMENT_ID><![CDATA[CCA48454EB40B295E040007F01006B08]]></AD_ELEMENT_ID>
+<!--CCA48454EB40B295E040007F01006B08-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--CCA48454EB40B295E040007F01006B08-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--CCA48454EB40B295E040007F01006B08-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--CCA48454EB40B295E040007F01006B08-->  <COLUMNNAME><![CDATA[ReleasedQty]]></COLUMNNAME>
+<!--CCA48454EB40B295E040007F01006B08-->  <NAME><![CDATA[Released]]></NAME>
+<!--CCA48454EB40B295E040007F01006B08-->  <PRINTNAME><![CDATA[Released]]></PRINTNAME>
+<!--CCA48454EB40B295E040007F01006B08-->  <HELP><![CDATA[Quantity released from the reservation. For example once it has been shipped to the customer.]]></HELP>
+<!--CCA48454EB40B295E040007F01006B08-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--CCA48454EB40B295E040007F01006B08-->  <ISGLOSSARY><![CDATA[N]]></ISGLOSSARY>
+<!--CCA48454EB40B295E040007F01006B08--></AD_ELEMENT>
+
+<!--CCA48454EB41B295E040007F01006B08--><AD_ELEMENT>
+<!--CCA48454EB41B295E040007F01006B08-->  <AD_ELEMENT_ID><![CDATA[CCA48454EB41B295E040007F01006B08]]></AD_ELEMENT_ID>
+<!--CCA48454EB41B295E040007F01006B08-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--CCA48454EB41B295E040007F01006B08-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--CCA48454EB41B295E040007F01006B08-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--CCA48454EB41B295E040007F01006B08-->  <COLUMNNAME><![CDATA[ReleasedQty]]></COLUMNNAME>
+<!--CCA48454EB41B295E040007F01006B08-->  <NAME><![CDATA[Released]]></NAME>
+<!--CCA48454EB41B295E040007F01006B08-->  <PRINTNAME><![CDATA[Released]]></PRINTNAME>
+<!--CCA48454EB41B295E040007F01006B08-->  <HELP><![CDATA[Quantity released from the reservation. For example once it has been shipped to the customer.]]></HELP>
+<!--CCA48454EB41B295E040007F01006B08-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--CCA48454EB41B295E040007F01006B08-->  <ISGLOSSARY><![CDATA[N]]></ISGLOSSARY>
+<!--CCA48454EB41B295E040007F01006B08--></AD_ELEMENT>
+
 <!--CD57A1DBADD440EC9551AE6BC51E8643--><AD_ELEMENT>
 <!--CD57A1DBADD440EC9551AE6BC51E8643-->  <AD_ELEMENT_ID><![CDATA[CD57A1DBADD440EC9551AE6BC51E8643]]></AD_ELEMENT_ID>
 <!--CD57A1DBADD440EC9551AE6BC51E8643-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
--- a/src-db/database/sourcedata/AD_FIELD.xml	Fri Oct 19 11:49:54 2012 +0200
+++ b/src-db/database/sourcedata/AD_FIELD.xml	Mon Oct 22 18:36:20 2012 +0200
@@ -231504,7 +231504,8 @@
 <!--CAB22ACCB6E96B2FE040007F010059CA-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
 <!--CAB22ACCB6E96B2FE040007F010059CA-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
 <!--CAB22ACCB6E96B2FE040007F010059CA-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
-<!--CAB22ACCB6E96B2FE040007F010059CA-->  <NAME><![CDATA[Pending]]></NAME>
+<!--CAB22ACCB6E96B2FE040007F010059CA-->  <NAME><![CDATA[Released]]></NAME>
+<!--CAB22ACCB6E96B2FE040007F010059CA-->  <HELP><![CDATA[Quantity released from the reservation. For example once it has been shipped to the customer.]]></HELP>
 <!--CAB22ACCB6E96B2FE040007F010059CA-->  <ISCENTRALLYMAINTAINED><![CDATA[Y]]></ISCENTRALLYMAINTAINED>
 <!--CAB22ACCB6E96B2FE040007F010059CA-->  <AD_TAB_ID><![CDATA[D53F675ADB2745059623175D8870A721]]></AD_TAB_ID>
 <!--CAB22ACCB6E96B2FE040007F010059CA-->  <AD_COLUMN_ID><![CDATA[CAB22D2BB7ECB270E040007F010059C2]]></AD_COLUMN_ID>
@@ -232367,7 +232368,8 @@
 <!--CC2FA246672B53CBE040007F01002E40-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
 <!--CC2FA246672B53CBE040007F01002E40-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
 <!--CC2FA246672B53CBE040007F01002E40-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
-<!--CC2FA246672B53CBE040007F01002E40-->  <NAME><![CDATA[Pending]]></NAME>
+<!--CC2FA246672B53CBE040007F01002E40-->  <NAME><![CDATA[Released]]></NAME>
+<!--CC2FA246672B53CBE040007F01002E40-->  <HELP><![CDATA[Quantity released from the reservation. For example once it has been shipped to the customer.]]></HELP>
 <!--CC2FA246672B53CBE040007F01002E40-->  <ISCENTRALLYMAINTAINED><![CDATA[Y]]></ISCENTRALLYMAINTAINED>
 <!--CC2FA246672B53CBE040007F01002E40-->  <AD_TAB_ID><![CDATA[F8DDCCBB72A5436ABACEB2A49B10BB27]]></AD_TAB_ID>
 <!--CC2FA246672B53CBE040007F01002E40-->  <AD_COLUMN_ID><![CDATA[CC2F9E8FD666D7CDE040007F01002E21]]></AD_COLUMN_ID>
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src/org/openbravo/materialmgmt/CSResponse.java	Mon Oct 22 18:36:20 2012 +0200
@@ -0,0 +1,24 @@
+/*
+ *************************************************************************
+ * The contents of this file are subject to the Openbravo  Public  License
+ * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
+ * Version 1.1  with a permitted attribution clause; you may not  use this
+ * file except in compliance with the License. You  may  obtain  a copy of
+ * the License at http://www.openbravo.com/legal/license.html 
+ * Software distributed under the License  is  distributed  on  an "AS IS"
+ * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
+ * License for the specific  language  governing  rights  and  limitations
+ * under the License. 
+ * The Original Code is Openbravo ERP. 
+ * The Initial Developer of the Original Code is Openbravo SLU 
+ * All portions are Copyright (C) 2012 Openbravo SLU 
+ * All Rights Reserved. 
+ * Contributor(s):  ______________________________________.
+ ************************************************************************
+ */
+package org.openbravo.materialmgmt;
+
+class CSResponse {
+  String returnValue;
+  String exito;
+}
\ No newline at end of file
--- a/src/org/openbravo/materialmgmt/ReservationUtils.java	Fri Oct 19 11:49:54 2012 +0200
+++ b/src/org/openbravo/materialmgmt/ReservationUtils.java	Mon Oct 22 18:36:20 2012 +0200
@@ -19,11 +19,11 @@
 package org.openbravo.materialmgmt;
 
 import java.math.BigDecimal;
-import java.util.ArrayList;
 import java.util.HashMap;
-import java.util.List;
 import java.util.Map;
 
+import javax.servlet.ServletException;
+
 import org.openbravo.base.exception.OBException;
 import org.openbravo.dal.core.DalUtil;
 import org.openbravo.dal.core.OBContext;
@@ -34,13 +34,15 @@
 import org.openbravo.model.ad.ui.Process;
 import org.openbravo.model.common.order.OrderLine;
 import org.openbravo.model.materialmgmt.onhandquantity.Reservation;
-import org.openbravo.model.materialmgmt.onhandquantity.StorageDetail;
+import org.openbravo.model.materialmgmt.onhandquantity.ReservationStock;
 import org.openbravo.service.db.CallProcess;
-import org.openbravo.service.db.CallStoredProcedure;
+import org.openbravo.service.db.DalConnectionProvider;
 
 public class ReservationUtils {
+  String returnValue;
+  String exito;
 
-  public Reservation createReserveFromSalesOrderLine(OrderLine soLine, boolean doProcess)
+  public static Reservation createReserveFromSalesOrderLine(OrderLine soLine, boolean doProcess)
       throws OBException {
     if (!soLine.getSalesOrder().isSalesTransaction()) {
       throw new OBException("@cannotReservePurchaseOrder@");
@@ -50,22 +52,35 @@
       throw new OBException("@cannotReserveDeliveredSalesOrderLine@");
     }
 
-    final List<Object> parameters = new ArrayList<Object>();
-    parameters.add(soLine.getId());
-    parameters.add(doProcess ? "Y" : "N");
-    parameters.add(DalUtil.getId(OBContext.getOBContext().getUser()));
-    Reservation reservation = (Reservation) CallStoredProcedure.getInstance().call(
-        "M_CREATE_RESERVE_FROM_SOL", parameters, null);
+    CSResponse cs = null;
+    try {
+      cs = ReservationUtilsData.createReserveFromSalesOrderLine(
+          OBDal.getInstance().getConnection(false), new DalConnectionProvider(), soLine.getId(),
+          doProcess ? "Y" : "N", (String) DalUtil.getId(OBContext.getOBContext().getUser()));
+    } catch (ServletException e) {
+    }
 
-    return reservation;
+    if (cs != null && cs.returnValue != null) {
+      return OBDal.getInstance().get(Reservation.class, cs.returnValue);
+    }
+
+    return null;
   }
 
-  public OBError createReserveStock(Reservation reservation) throws OBException {
+  public static OBError reserveStockAuto(Reservation reservation) throws OBException {
+    CSResponse cs = null;
+    try {
+      cs = ReservationUtilsData.reserveStockAuto(OBDal.getInstance().getConnection(false),
+          new DalConnectionProvider(), reservation.getId(),
+          (String) DalUtil.getId(OBContext.getOBContext().getUser()));
+    } catch (ServletException e) {
+      throw new OBException(e.getMessage());
+    }
 
-    final List<Object> parameters = new ArrayList<Object>();
-    parameters.add(reservation.getId());
-    String message = (String) CallStoredProcedure.getInstance().call("M_CREATE_RESERVE_STOCK",
-        parameters, null);
+    String message = "";
+    if (cs != null && cs.returnValue != null) {
+      message = cs.returnValue;
+    }
 
     OBError obmessage = new OBError();
     obmessage.setType("SUCCESS");
@@ -73,21 +88,23 @@
     return obmessage;
   }
 
-  public OBError addReserveStock(Reservation reservation, StorageDetail stock, OrderLine poLine,
-      BigDecimal quantity) throws OBException {
+  public static ReservationStock reserveStockManual(Reservation reservation, String strType,
+      String strStockId, BigDecimal quantity) throws OBException {
 
-    final List<Object> parameters = new ArrayList<Object>();
-    parameters.add(reservation.getId());
-    parameters.add(stock.getId());
-    parameters.add(poLine.getId());
-    parameters.add(quantity);
-    String message = (String) CallStoredProcedure.getInstance().call("M_ADD_RESERVED_STOCK",
-        parameters, null);
+    CSResponse cs = null;
+    try {
+      cs = ReservationUtilsData.reserveStockManual(OBDal.getInstance().getConnection(false),
+          new DalConnectionProvider(), reservation.getId(), strType, strStockId,
+          quantity.toString(), (String) DalUtil.getId(OBContext.getOBContext().getUser()));
+    } catch (ServletException e) {
+      throw new OBException(e.getMessage());
+    }
 
-    OBError obmessage = new OBError();
-    obmessage.setType("SUCCESS");
-    obmessage.setMessage(message);
-    return obmessage;
+    if (cs != null && cs.returnValue != null) {
+      return OBDal.getInstance().get(ReservationStock.class, cs.returnValue);
+    }
+
+    return null;
   }
 
   /**
@@ -100,7 +117,7 @@
    * <li>CL Close</li>
    * </ul>
    */
-  public OBError processReserve(Reservation reservation, String action) throws OBException {
+  public static OBError processReserve(Reservation reservation, String action) throws OBException {
 
     OBContext.setAdminMode(true);
     Process process = null;
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src/org/openbravo/materialmgmt/ReservationUtils_data.xsql	Mon Oct 22 18:36:20 2012 +0200
@@ -0,0 +1,60 @@
+<?xml version="1.0" encoding="UTF-8" ?>
+<!--
+ *************************************************************************
+ * The contents of this file are subject to the Openbravo  Public  License
+ * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
+ * Version 1.1  with a permitted attribution clause; you may not  use this
+ * file except in compliance with the License. You  may  obtain  a copy of
+ * the License at http://www.openbravo.com/legal/license.html 
+ * Software distributed under the License  is  distributed  on  an "AS IS"
+ * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
+ * License for the specific  language  governing  rights  and  limitations
+ * under the License. 
+ * The Original Code is Openbravo ERP. 
+ * The Initial Developer of the Original Code is Openbravo SLU 
+ * All portions are Copyright (C) 2012 Openbravo SLU 
+ * All Rights Reserved. 
+ * Contributor(s):  ______________________________________.
+ ************************************************************************
+-->
+
+
+<SqlClass name="ReservationUtilsData" package="org.openbravo.materialmgmt">
+   <SqlClassComment></SqlClassComment>
+   <SqlMethod name="select" type="preparedStatement" return="multiple">
+     <Sql>
+       select 1 as a from dual
+     </Sql>
+   </SqlMethod>
+   <SqlMethod name="createReserveFromSalesOrderLine" connection="true" type="callableStatement" return="object" object="CSResponse">
+      <SqlMethodComment></SqlMethodComment>
+      <Sql><![CDATA[
+        CALL M_CREATE_RESERVE_FROM_SOL(?,?,?,?)
+      ]]></Sql>
+      <Parameter name="cOrderLineId"/>
+      <Parameter name="doProcess"/>
+      <Parameter name="adUserId"/>
+      <Parameter name="returnValue" type="out"/>
+   </SqlMethod>
+   <SqlMethod name="reserveStockAuto" connection="true" type="callableStatement" return="object" object="CSResponse">
+      <SqlMethodComment></SqlMethodComment>
+      <Sql><![CDATA[
+        CALL M_RESERVE_STOCK_AUTO(?,?,?)
+      ]]></Sql>
+      <Parameter name="mReservationId"/>
+      <Parameter name="adUserId"/>
+      <Parameter name="returnValue" type="out"/>
+   </SqlMethod>
+   <SqlMethod name="reserveStockManual" connection="true" type="callableStatement" return="object" object="CSResponse">
+      <SqlMethodComment></SqlMethodComment>
+      <Sql><![CDATA[
+        CALL M_RESERVE_STOCK_MANUAL(?,?,?,?,?,?)
+      ]]></Sql>
+      <Parameter name="mReservationId"/>
+      <Parameter name="type"/>
+      <Parameter name="stockId"/>
+      <Parameter name="quantity"/>
+      <Parameter name="adUserId"/>
+      <Parameter name="returnValue" type="out"/>
+   </SqlMethod>
+</SqlClass>