Fixed bug 28645: reservations and product quantity variable
authorVíctor Martínez Romanos <victor.martinez@openbravo.com>
Fri, 22 May 2015 13:13:16 +0200
changeset 27101 d759928f7271
parent 27100 b685cad82eef
child 27102 e03c1808e195
Fixed bug 28645: reservations and product quantity variable

Improved javascript validation in stock reservation P&E to work with product quantity variable
Activated the reservation close status as a way to control and properly close the cycle for reservations related to quantity variable products
Modified M_RESERVATION_POST to set the line's quantity = releasedQty when closing the reservation
Modified C_ORDERLINE2_TRG trigger (on update event) to close reservations where QtyDelivered >= qtyordered
Button close is hidden when reservation status is closed
Added default filter to Stock Reservations window to hide closed records
modules/org.openbravo.client.application/web/org.openbravo.client.application/js/materialmgmt/ob-reservation.js
src-db/database/model/functions/M_RESERVATION_POST.xml
src-db/database/model/triggers/C_ORDERLINE2_TRG.xml
src-db/database/sourcedata/AD_AUXILIARINPUT.xml
src-db/database/sourcedata/AD_FIELD.xml
src-db/database/sourcedata/AD_REF_LIST.xml
src-db/database/sourcedata/AD_TAB.xml
--- a/modules/org.openbravo.client.application/web/org.openbravo.client.application/js/materialmgmt/ob-reservation.js	Thu Jul 02 09:40:16 2015 +0200
+++ b/modules/org.openbravo.client.application/web/org.openbravo.client.application/js/materialmgmt/ob-reservation.js	Fri May 22 13:13:16 2015 +0200
@@ -11,7 +11,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 Openbravo SLU
+ * All portions are Copyright (C) 2012-2015 Openbravo SLU
  * All Rights Reserved.
  * Contributor(s):  ______________________________________.
  ************************************************************************
@@ -33,6 +33,7 @@
       selectedRecords = item.grid.getSelectedRecords(),
       selectedRecordsLength = selectedRecords.length,
       editedRecord = null,
+      isQtyVariable = item.grid.view.sourceView.getContextInfo(false, true, true, true).isQuantityVariable,
       i;
 
   if (!isc.isA.Number(value)) {
@@ -56,7 +57,7 @@
       reservedQty = reservedQty.add(new BigDecimal(String(editedRecord.quantity)));
     }
   }
-  if (reservedQty.compareTo(totalQty) > 0) {
+  if (reservedQty.compareTo(totalQty) > 0 && isQtyVariable !== 'Y') {
     isc.warn(OB.I18N.getLabel('OBUIAPP_Res_MoreThanReservationQty', [totalQty.toString()]));
     return false;
   }
--- a/src-db/database/model/functions/M_RESERVATION_POST.xml	Thu Jul 02 09:40:16 2015 +0200
+++ b/src-db/database/model/functions/M_RESERVATION_POST.xml	Fri May 22 13:13:16 2015 +0200
@@ -381,17 +381,35 @@
     END IF;
     v_newstatus := 'CL';
     v_newaction := 'RE';
+    
+    -- Force to set reservation = CL here to avoid mutating table issues
+    -- when updating m_reservation_stock.quantity
+    UPDATE m_reservation
+    SET res_status = v_newstatus,
+      res_process = v_newaction,
+      updated = now(),
+      updatedby = v_user_id
+    WHERE m_reservation_id = v_reservation_id;
+    
+    UPDATE m_reservation_stock
+    SET quantity = releasedQty,
+    updated = now(),
+    updatedby = v_user_id
+    WHERE m_reservation_id = v_reservation_id;
 
   ELSE
     RAISE_APPLICATION_ERROR(-20000, '@UnsupportedResAction@');
   END IF;
 
-  UPDATE m_reservation
-  SET res_status = v_newstatus,
-      res_process = v_newaction,
-      updated = now(),
-      updatedby = v_user_id
-  WHERE m_reservation_id = v_reservation_id;
+  -- Close status already set
+  IF (v_newstatus <> 'CL') THEN
+    UPDATE m_reservation
+    SET res_status = v_newstatus,
+        res_process = v_newaction,
+        updated = now(),
+        updatedby = v_user_id
+    WHERE m_reservation_id = v_reservation_id;
+  END IF;
 
   IF (v_resaction = 'PR') THEN
     -- ADD RESERVED STOCK
--- a/src-db/database/model/triggers/C_ORDERLINE2_TRG.xml	Thu Jul 02 09:40:16 2015 +0200
+++ b/src-db/database/model/triggers/C_ORDERLINE2_TRG.xml	Fri May 22 13:13:16 2015 +0200
@@ -18,7 +18,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) 2001-2014 Openbravo SLU
+* All portions are Copyright (C) 2001-2015 Openbravo SLU
 * All Rights Reserved.
 * Contributor(s):  ______________________________________.
 ************************************************************************/
@@ -67,6 +67,24 @@
           END IF;
         END IF;
       END IF;
+      
+      -- Close reservations if QtyDelivered >= qtyordered
+      IF (COALESCE(:new.QtyDelivered, 0) > 0 AND COALESCE(:new.QtyDelivered, 0) >= COALESCE(:new.qtyordered, 0)) THEN
+         DECLARE
+           TYPE RECORD IS REF CURSOR;
+           cur_reservations RECORD;
+
+         BEGIN
+           FOR cur_reservations IN (
+             SELECT res.m_reservation_id as m_reservation_id
+             FROM m_reservation res
+             WHERE res.C_ORDERLINE_ID = :new.C_ORDERLINE_ID
+             AND res.RES_STATUS <> 'CL'
+           ) LOOP
+               M_RESERVATION_POST(null, cur_reservations.m_reservation_id, 'CL', :new.updatedBy);
+             END LOOP;
+         END;
+      END IF;
     END IF;
   END IF;
 
--- a/src-db/database/sourcedata/AD_AUXILIARINPUT.xml	Thu Jul 02 09:40:16 2015 +0200
+++ b/src-db/database/sourcedata/AD_AUXILIARINPUT.xml	Fri May 22 13:13:16 2015 +0200
@@ -1957,6 +1957,17 @@
 <!--51568B79156646C8966F49F82EE51CB7-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
 <!--51568B79156646C8966F49F82EE51CB7--></AD_AUXILIARINPUT>
 
+<!--51A01FEBD73F45189B342865ACE44FAC--><AD_AUXILIARINPUT>
+<!--51A01FEBD73F45189B342865ACE44FAC-->  <AD_AUXILIARINPUT_ID><![CDATA[51A01FEBD73F45189B342865ACE44FAC]]></AD_AUXILIARINPUT_ID>
+<!--51A01FEBD73F45189B342865ACE44FAC-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--51A01FEBD73F45189B342865ACE44FAC-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--51A01FEBD73F45189B342865ACE44FAC-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--51A01FEBD73F45189B342865ACE44FAC-->  <AD_TAB_ID><![CDATA[D53F675ADB2745059623175D8870A721]]></AD_TAB_ID>
+<!--51A01FEBD73F45189B342865ACE44FAC-->  <NAME><![CDATA[isQuantityVariable]]></NAME>
+<!--51A01FEBD73F45189B342865ACE44FAC-->  <CODE><![CDATA[@SQL=SELECT ISQUANTITYVARIABLE FROM M_PRODUCT where M_PRODUCT_ID = @M_Product_ID@]]></CODE>
+<!--51A01FEBD73F45189B342865ACE44FAC-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--51A01FEBD73F45189B342865ACE44FAC--></AD_AUXILIARINPUT>
+
 <!--523446BDF86D49E1942315BF330CD5B8--><AD_AUXILIARINPUT>
 <!--523446BDF86D49E1942315BF330CD5B8-->  <AD_AUXILIARINPUT_ID><![CDATA[523446BDF86D49E1942315BF330CD5B8]]></AD_AUXILIARINPUT_ID>
 <!--523446BDF86D49E1942315BF330CD5B8-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
@@ -3904,6 +3915,17 @@
 <!--E40878AA754F4735B145BB8564191534-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
 <!--E40878AA754F4735B145BB8564191534--></AD_AUXILIARINPUT>
 
+<!--E4536B2BCBDB4469A130B3BD4D7DEB53--><AD_AUXILIARINPUT>
+<!--E4536B2BCBDB4469A130B3BD4D7DEB53-->  <AD_AUXILIARINPUT_ID><![CDATA[E4536B2BCBDB4469A130B3BD4D7DEB53]]></AD_AUXILIARINPUT_ID>
+<!--E4536B2BCBDB4469A130B3BD4D7DEB53-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--E4536B2BCBDB4469A130B3BD4D7DEB53-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--E4536B2BCBDB4469A130B3BD4D7DEB53-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--E4536B2BCBDB4469A130B3BD4D7DEB53-->  <AD_TAB_ID><![CDATA[187]]></AD_TAB_ID>
+<!--E4536B2BCBDB4469A130B3BD4D7DEB53-->  <NAME><![CDATA[isQuantityVariable]]></NAME>
+<!--E4536B2BCBDB4469A130B3BD4D7DEB53-->  <CODE><![CDATA[@SQL=SELECT ISQUANTITYVARIABLE FROM M_PRODUCT where M_PRODUCT_ID = @M_Product_ID@]]></CODE>
+<!--E4536B2BCBDB4469A130B3BD4D7DEB53-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--E4536B2BCBDB4469A130B3BD4D7DEB53--></AD_AUXILIARINPUT>
+
 <!--E4A3C225FA09479DBCB33D4E15A410B8--><AD_AUXILIARINPUT>
 <!--E4A3C225FA09479DBCB33D4E15A410B8-->  <AD_AUXILIARINPUT_ID><![CDATA[E4A3C225FA09479DBCB33D4E15A410B8]]></AD_AUXILIARINPUT_ID>
 <!--E4A3C225FA09479DBCB33D4E15A410B8-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
@@ -4043,6 +4065,17 @@
 <!--ED9749CC067E495FA3D369E65B972450-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
 <!--ED9749CC067E495FA3D369E65B972450--></AD_AUXILIARINPUT>
 
+<!--F12E4458B64640EC9200B936D58337ED--><AD_AUXILIARINPUT>
+<!--F12E4458B64640EC9200B936D58337ED-->  <AD_AUXILIARINPUT_ID><![CDATA[F12E4458B64640EC9200B936D58337ED]]></AD_AUXILIARINPUT_ID>
+<!--F12E4458B64640EC9200B936D58337ED-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--F12E4458B64640EC9200B936D58337ED-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--F12E4458B64640EC9200B936D58337ED-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--F12E4458B64640EC9200B936D58337ED-->  <AD_TAB_ID><![CDATA[293]]></AD_TAB_ID>
+<!--F12E4458B64640EC9200B936D58337ED-->  <NAME><![CDATA[isQuantityVariable]]></NAME>
+<!--F12E4458B64640EC9200B936D58337ED-->  <CODE><![CDATA[@SQL=SELECT ISQUANTITYVARIABLE FROM M_PRODUCT where M_PRODUCT_ID = @M_Product_ID@]]></CODE>
+<!--F12E4458B64640EC9200B936D58337ED-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--F12E4458B64640EC9200B936D58337ED--></AD_AUXILIARINPUT>
+
 <!--F1B30F5F741C4709B745AB64E249F6F9--><AD_AUXILIARINPUT>
 <!--F1B30F5F741C4709B745AB64E249F6F9-->  <AD_AUXILIARINPUT_ID><![CDATA[F1B30F5F741C4709B745AB64E249F6F9]]></AD_AUXILIARINPUT_ID>
 <!--F1B30F5F741C4709B745AB64E249F6F9-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
--- a/src-db/database/sourcedata/AD_FIELD.xml	Thu Jul 02 09:40:16 2015 +0200
+++ b/src-db/database/sourcedata/AD_FIELD.xml	Fri May 22 13:13:16 2015 +0200
@@ -283733,6 +283733,7 @@
 <!--CC2F9E8FD677D7CDE040007F01002E21-->  <AD_COLUMN_ID><![CDATA[CC2F971F21813089E040007F01002D7C]]></AD_COLUMN_ID>
 <!--CC2F9E8FD677D7CDE040007F01002E21-->  <IGNOREINWAD><![CDATA[N]]></IGNOREINWAD>
 <!--CC2F9E8FD677D7CDE040007F01002E21-->  <ISDISPLAYED><![CDATA[Y]]></ISDISPLAYED>
+<!--CC2F9E8FD677D7CDE040007F01002E21-->  <DISPLAYLOGIC><![CDATA[@RES_Status@!'CL']]></DISPLAYLOGIC>
 <!--CC2F9E8FD677D7CDE040007F01002E21-->  <DISPLAYLENGTH><![CDATA[60]]></DISPLAYLENGTH>
 <!--CC2F9E8FD677D7CDE040007F01002E21-->  <ISREADONLY><![CDATA[N]]></ISREADONLY>
 <!--CC2F9E8FD677D7CDE040007F01002E21-->  <SEQNO><![CDATA[0]]></SEQNO>
--- a/src-db/database/sourcedata/AD_REF_LIST.xml	Thu Jul 02 09:40:16 2015 +0200
+++ b/src-db/database/sourcedata/AD_REF_LIST.xml	Fri May 22 13:13:16 2015 +0200
@@ -9400,7 +9400,7 @@
 <!--51E9ABC6ED344AD1A9DC0DAB968A1F65-->  <AD_REF_LIST_ID><![CDATA[51E9ABC6ED344AD1A9DC0DAB968A1F65]]></AD_REF_LIST_ID>
 <!--51E9ABC6ED344AD1A9DC0DAB968A1F65-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
 <!--51E9ABC6ED344AD1A9DC0DAB968A1F65-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
-<!--51E9ABC6ED344AD1A9DC0DAB968A1F65-->  <ISACTIVE><![CDATA[N]]></ISACTIVE>
+<!--51E9ABC6ED344AD1A9DC0DAB968A1F65-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
 <!--51E9ABC6ED344AD1A9DC0DAB968A1F65-->  <VALUE><![CDATA[CL]]></VALUE>
 <!--51E9ABC6ED344AD1A9DC0DAB968A1F65-->  <NAME><![CDATA[Closed]]></NAME>
 <!--51E9ABC6ED344AD1A9DC0DAB968A1F65-->  <AD_REFERENCE_ID><![CDATA[EE8B072E19034D0FB34CA1CEB3583620]]></AD_REFERENCE_ID>
@@ -10287,7 +10287,7 @@
 <!--85666C41D834448F83D5B3613D7B59D4-->  <AD_REF_LIST_ID><![CDATA[85666C41D834448F83D5B3613D7B59D4]]></AD_REF_LIST_ID>
 <!--85666C41D834448F83D5B3613D7B59D4-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
 <!--85666C41D834448F83D5B3613D7B59D4-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
-<!--85666C41D834448F83D5B3613D7B59D4-->  <ISACTIVE><![CDATA[N]]></ISACTIVE>
+<!--85666C41D834448F83D5B3613D7B59D4-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
 <!--85666C41D834448F83D5B3613D7B59D4-->  <VALUE><![CDATA[CL]]></VALUE>
 <!--85666C41D834448F83D5B3613D7B59D4-->  <NAME><![CDATA[Close]]></NAME>
 <!--85666C41D834448F83D5B3613D7B59D4-->  <AD_REFERENCE_ID><![CDATA[440DDA64A43F4799AAFF48BC86DC8F78]]></AD_REFERENCE_ID>
--- a/src-db/database/sourcedata/AD_TAB.xml	Thu Jul 02 09:40:16 2015 +0200
+++ b/src-db/database/sourcedata/AD_TAB.xml	Fri May 22 13:13:16 2015 +0200
@@ -18807,6 +18807,7 @@
 <!--D53F675ADB2745059623175D8870A721-->  <ISSORTTAB><![CDATA[N]]></ISSORTTAB>
 <!--D53F675ADB2745059623175D8870A721-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
 <!--D53F675ADB2745059623175D8870A721-->  <UIPATTERN><![CDATA[STD]]></UIPATTERN>
+<!--D53F675ADB2745059623175D8870A721-->  <HQLFILTERCLAUSE><![CDATA[e.rESStatus!='CL']]></HQLFILTERCLAUSE>
 <!--D53F675ADB2745059623175D8870A721-->  <SHOWPARENTBUTTONS><![CDATA[Y]]></SHOWPARENTBUTTONS>
 <!--D53F675ADB2745059623175D8870A721-->  <DISABLE_PARENT_KEY_PROPERTY><![CDATA[N]]></DISABLE_PARENT_KEY_PROPERTY>
 <!--D53F675ADB2745059623175D8870A721-->  <ISREADONLYTREE><![CDATA[N]]></ISREADONLYTREE>