Fixes issue 31813: Cannot void GoodsShipment if it has reservation associated
authorAlvaro Ferraz <alvaro.ferraz@openbravo.com>
Mon, 04 Jan 2016 19:14:21 +0100
changeset 28196 69f3a1a1c22d
parent 28195 4359653f248d
child 28197 338ed6567ec8
Fixes issue 31813: Cannot void GoodsShipment if it has reservation associated

Generate uuid inside the loop to avoid create more than one M_Reservation with the same M_Reservation_ID in M_INOUT_POST.
Format code properly ("Add a reservation stock with the ordered quantity" section) in M_INOUT_POST.
Remove duplicated variable v_reservedqty in M_RESERVATION_POST to avoid set wrong Reservation Status to Order Line.
Take into account only Order Lines with qtyordered > 0 and with product of stocked and item type when setting Reservation Status to Order in M_RESERVATION_POST.
Set "Completely Reserved" reservation status to Order Line also when reservedqty > quantity in M_RESERVATION_POST and M_RESERVATION_TRG.
src-db/database/model/functions/M_INOUT_POST.xml
src-db/database/model/functions/M_RESERVATION_POST.xml
src-db/database/model/triggers/M_RESERVATION_TRG.xml
--- a/src-db/database/model/functions/M_INOUT_POST.xml	Thu Dec 24 11:03:13 2015 +0100
+++ b/src-db/database/model/functions/M_INOUT_POST.xml	Mon Jan 04 19:14:21 2016 +0100
@@ -22,7 +22,7 @@
   * parts created by ComPiere are Copyright (C) ComPiere, Inc.;
   * All Rights Reserved.
   * Contributor(s): Openbravo SLU
-  * Contributions are Copyright (C) 2001-2015 Openbravo, S.L.U.
+  * Contributions are Copyright (C) 2001-2016 Openbravo, S.L.U.
   *
   * Specifically, this derivative work is based upon the following Compiere
   * file and version.
@@ -119,7 +119,7 @@
     v_IsQtyVariable M_Product.IsQuantityVariable%TYPE;
     v_IsReversedDoc CHAR(1);
 
-    v_uuid VARCHAR2(32) := get_uuid();
+    v_uuid VARCHAR2(32);
     v_qtyordered NUMBER;
 
   BEGIN
@@ -1160,6 +1160,8 @@
 	    LOOP
 	      IF (Cur_Reservation.Res_Status = 'CL') THEN
 
+		v_uuid := get_uuid();
+
 		-- Get the ordered quantity from the order line
 	        SELECT qtyordered
 	        INTO v_qtyordered
@@ -1182,20 +1184,19 @@
 		  Cur_Reservation.managereservation_pe, Cur_Reservation.reservedgoodmnt_pe
 	        );
 
-           -- Add a reservation stock with the ordered quantity
-      	   INSERT INTO M_RESERVATION_STOCK (
-     	      m_reservation_stock_id, ad_client_id, ad_org_id, isactive,
-   	        created, createdby, updated, updatedby,
-		        m_reservation_id, c_orderline_id, quantity, isallocated,
-		        m_locator_id, m_attributesetinstance_id
-	        )
-          SELECT get_uuid(), ad_client_id, ad_org_id, 'Y',
-            now(), v_user, now(), v_user,
-            v_uuid, c_orderline_id, v_qtyordered, isallocated,
-            m_locator_id, m_attributesetinstance_id
-          FROM M_RESERVATION_STOCK
-          WHERE M_RESERVATION_STOCK.M_Reservation_ID=Cur_Reservation.M_Reservation_ID
-          AND IsActive='Y';
+		-- Add a reservation stock with the ordered quantity
+		INSERT INTO M_RESERVATION_STOCK (
+		  m_reservation_stock_id, ad_client_id, ad_org_id, isactive,
+   	          created, createdby, updated, updatedby,
+		  m_reservation_id, c_orderline_id, quantity, isallocated,
+		  m_locator_id, m_attributesetinstance_id )
+		SELECT get_uuid(), ad_client_id, ad_org_id, 'Y',
+		  now(), v_user, now(), v_user,
+		  v_uuid, c_orderline_id, v_qtyordered, isallocated,
+		  m_locator_id, m_attributesetinstance_id
+		FROM M_RESERVATION_STOCK
+		WHERE M_RESERVATION_STOCK.M_Reservation_ID=Cur_Reservation.M_Reservation_ID
+		AND IsActive='Y';
 
 	        -- Process the reservation
 	        M_RESERVATION_POST(null, v_uuid, 'PR', v_user);
--- a/src-db/database/model/functions/M_RESERVATION_POST.xml	Thu Dec 24 11:03:13 2015 +0100
+++ b/src-db/database/model/functions/M_RESERVATION_POST.xml	Mon Jan 04 19:14:21 2016 +0100
@@ -25,7 +25,7 @@
 * 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-2015 Openbravo SLU
+* All portions are Copyright (C) 2012-2016 Openbravo SLU
 * All Rights Reserved.
 * Contributor(s):  ______________________________________.
 ************************************************************************/
@@ -115,7 +115,6 @@
       v_linecount             NUMBER;
       v_creservedcount        NUMBER;
       v_preservedcount        NUMBER;
-      v_reservedqty           NUMBER;
       v_qtytoreserve          NUMBER;
       v_aux                   NUMBER;
       v_res_stock_id          VARCHAR2(32);
@@ -245,7 +244,7 @@
         END IF;
   
         UPDATE c_orderline
-        SET so_res_status = CASE WHEN v_quantity = v_reservedqty THEN 'CR'
+        SET so_res_status = CASE WHEN v_quantity <= v_reservedqty THEN 'CR'
                                  WHEN v_reservedqty > 0 THEN 'PR'
                                  ELSE 'NR'
                             END
@@ -256,8 +255,12 @@
         WHERE c_orderline_id = v_orderline_id;
         SELECT COUNT(*), SUM(CASE so_res_status WHEN 'CR' THEN 1 ELSE 0 END), SUM(CASE so_res_status WHEN 'PR' THEN 1 ELSE 0 END)
           INTO v_linecount, v_creservedcount, v_preservedcount
-        FROM c_orderline
-        WHERE c_order_id = v_sales_order_id;
+        FROM c_orderline ol
+        JOIN m_product p ON ol.m_product_id = p.m_product_id
+        WHERE ol.c_order_id = v_sales_order_id
+        AND ol.qtyordered > 0
+        AND p.isstocked = 'Y'
+        AND p.producttype = 'I';
   
         UPDATE c_order
         SET so_res_status = CASE WHEN v_linecount = v_creservedcount THEN 'CR'
--- a/src-db/database/model/triggers/M_RESERVATION_TRG.xml	Thu Dec 24 11:03:13 2015 +0100
+++ b/src-db/database/model/triggers/M_RESERVATION_TRG.xml	Mon Jan 04 19:14:21 2016 +0100
@@ -15,7 +15,7 @@
 * 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-2015 Openbravo SLU
+* All portions are Copyright (C) 2012-2016 Openbravo SLU
 * All Rights Reserved.
 * Contributor(s):  ______________________________________.
 ************************************************************************/
@@ -59,7 +59,7 @@
   IF (v_updateOrderAndLines = 'Y') THEN
     IF (:NEW.res_status NOT IN ('DR', 'CL') AND :NEW.c_orderline_id IS NOT NULL) THEN
       UPDATE c_orderline
-      SET so_res_status = CASE WHEN :NEW.quantity = :NEW.reservedqty THEN 'CR'
+      SET so_res_status = CASE WHEN :NEW.quantity <= :NEW.reservedqty THEN 'CR'
                                WHEN :NEW.quantity > :NEW.reservedqty AND :NEW.reservedqty > 0 THEN 'PR'
                                ELSE 'NR'
                           END