Fixes issue 31835: Not possible to change product to stocked
authorAlvaro Ferraz <alvaro.ferraz@openbravo.com>
Mon, 11 Jan 2016 17:23:56 +0100
changeset 28250 e3220d26e44f
parent 28249 afa165be3e59
child 28251 8d9510cd867e
Fixes issue 31835: Not possible to change product to stocked

Stocked flag in Product window will not be updatable in case product is present in any order, invoice or warehouse document.
src-db/database/model/triggers/M_PRODUCT_TRG.xml
src-db/database/sourcedata/AD_MESSAGE.xml
--- a/src-db/database/model/triggers/M_PRODUCT_TRG.xml	Wed Jan 13 15:33:23 2016 +0100
+++ b/src-db/database/model/triggers/M_PRODUCT_TRG.xml	Mon Jan 11 17:23:56 2016 +0100
@@ -17,7 +17,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.
@@ -64,21 +64,19 @@
 
  -- Do not allow to set the product as stocked if it appears in
  -- processed documents which generate inventory transactions
-  IF (:old.isStocked = 'N' AND :new.isStocked = 'Y') THEN
+  IF (:old.isStocked <> :new.isStocked) THEN
     SELECT count(*) 
     INTO v_Count
     FROM dual
-    WHERE EXISTS ( SELECT 1 FROM M_Inventory iv JOIN M_InventoryLine ivl ON iv.m_inventory_id = ivl.m_inventory_id 
-	 	   WHERE ivl.m_product_id = :new.m_product_id AND iv.processed = 'Y' )
-       OR EXISTS ( SELECT 1 FROM M_Movement mv JOIN M_MovementLine mvl ON mv.m_movement_id = mvl.m_movement_id 
-		   WHERE mvl.m_product_id = :new.m_product_id AND mv.processed = 'Y' )
-       OR EXISTS ( SELECT 1 FROM M_Inout io JOIN M_InoutLine iol ON io.m_inout_id = iol.m_inout_id 
-		   WHERE iol.m_product_id = :new.m_product_id AND io.docstatus = 'CO' )
-       OR EXISTS ( SELECT 1 FROM M_Production pr JOIN M_ProductionPlan pp ON pr.m_production_id = pp.m_production_id 
-		   JOIN M_ProductionLine prl ON pp.m_productionplan_id = prl.m_productionplan_id 
-		   WHERE prl.m_product_id = :new.m_product_id AND pr.processed = 'Y' );
+    WHERE EXISTS ( SELECT 1 FROM C_OrderLine WHERE m_product_id = :new.m_product_id )
+       OR EXISTS ( SELECT 1 FROM C_InvoiceLine WHERE m_product_id = :new.m_product_id )
+       OR EXISTS ( SELECT 1 FROM M_InventoryLine WHERE m_product_id = :new.m_product_id )
+       OR EXISTS ( SELECT 1 FROM M_MovementLine WHERE m_product_id = :new.m_product_id )
+       OR EXISTS ( SELECT 1 FROM M_InoutLine WHERE m_product_id = :new.m_product_id )
+       OR EXISTS ( SELECT 1 FROM M_ProductionLine WHERE m_product_id = :new.m_product_id )
+       OR EXISTS ( SELECT 1 FROM M_Internal_ConsumptionLine WHERE m_product_id = :new.m_product_id );
     IF (v_count > 0) THEN
-      RAISE_APPLICATION_ERROR(-20000, '@NonStockedProductWithTransactions@');
+      RAISE_APPLICATION_ERROR(-20000, '@StockedFlagWithDocuments@');
     END IF;
   END IF;
   
@@ -87,7 +85,6 @@
   IF ((:new.IsActive='N' AND :old.IsActive='Y')
       OR (COALESCE(:new.m_attributeset_id,'null') <> COALESCE(:old.m_attributeset_id,'null'))
       OR (COALESCE(:new.c_uom_id,'null') <> COALESCE(:old.c_uom_id,'null'))
-      OR (:old.isStocked='Y' AND :new.isStocked='N')
       OR (:old.Production <> :new.Production)) THEN
    SELECT  COALESCE(SUM(QtyOnHand)+SUM(QtyReserved)*.111+SUM(QtyOrdered)*999, 0) ,
    COALESCE(sum(QtyOnHand),0), COALESCE(SUM(QtyReserved),0) , COALESCE(SUM(QtyOrdered),0)
@@ -105,8 +102,6 @@
 				RAISE_APPLICATION_ERROR(-20000, '@20633@');
 			ELSIF (COALESCE(:new.c_uom_id,'null') <> COALESCE(:old.c_uom_id,'null')) THEN
 				RAISE_APPLICATION_ERROR(-20000, '@20634@');
-			ELSIF (:old.isStocked = 'Y' AND :new.isStocked = 'N') THEN
-				RAISE_APPLICATION_ERROR(-20000, '@StockedProductWithMovements@');
 			ELSIF (:old.Production <> :new.Production) THEN
 				RAISE_APPLICATION_ERROR(-20000, '@ManufacturingFlagWithStock@');
 			ELSE
--- a/src-db/database/sourcedata/AD_MESSAGE.xml	Wed Jan 13 15:33:23 2016 +0100
+++ b/src-db/database/sourcedata/AD_MESSAGE.xml	Mon Jan 11 17:23:56 2016 +0100
@@ -21951,18 +21951,6 @@
 <!--88A1D0A0F67F49339B2639EA4E5F6B4B-->  <ISINCLUDEINI18N><![CDATA[N]]></ISINCLUDEINI18N>
 <!--88A1D0A0F67F49339B2639EA4E5F6B4B--></AD_MESSAGE>
 
-<!--88E5D345A1684656A952C0A379D5351E--><AD_MESSAGE>
-<!--88E5D345A1684656A952C0A379D5351E-->  <AD_MESSAGE_ID><![CDATA[88E5D345A1684656A952C0A379D5351E]]></AD_MESSAGE_ID>
-<!--88E5D345A1684656A952C0A379D5351E-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
-<!--88E5D345A1684656A952C0A379D5351E-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
-<!--88E5D345A1684656A952C0A379D5351E-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
-<!--88E5D345A1684656A952C0A379D5351E-->  <VALUE><![CDATA[StockedProductWithMovements]]></VALUE>
-<!--88E5D345A1684656A952C0A379D5351E-->  <MSGTEXT><![CDATA[You cannot change the product to non stocked because it has inventory]]></MSGTEXT>
-<!--88E5D345A1684656A952C0A379D5351E-->  <MSGTYPE><![CDATA[E]]></MSGTYPE>
-<!--88E5D345A1684656A952C0A379D5351E-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
-<!--88E5D345A1684656A952C0A379D5351E-->  <ISINCLUDEINI18N><![CDATA[N]]></ISINCLUDEINI18N>
-<!--88E5D345A1684656A952C0A379D5351E--></AD_MESSAGE>
-
 <!--8927B08F10B745E9883A329F1FCBC63A--><AD_MESSAGE>
 <!--8927B08F10B745E9883A329F1FCBC63A-->  <AD_MESSAGE_ID><![CDATA[8927B08F10B745E9883A329F1FCBC63A]]></AD_MESSAGE_ID>
 <!--8927B08F10B745E9883A329F1FCBC63A-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
@@ -25081,8 +25069,8 @@
 <!--CE436C734491444CAAF5FFEE073AE2E1-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
 <!--CE436C734491444CAAF5FFEE073AE2E1-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
 <!--CE436C734491444CAAF5FFEE073AE2E1-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
-<!--CE436C734491444CAAF5FFEE073AE2E1-->  <VALUE><![CDATA[NonStockedProductWithTransactions]]></VALUE>
-<!--CE436C734491444CAAF5FFEE073AE2E1-->  <MSGTEXT><![CDATA[You cannot change the product to stocked because it has completed documents which would generate inventory transactions]]></MSGTEXT>
+<!--CE436C734491444CAAF5FFEE073AE2E1-->  <VALUE><![CDATA[StockedFlagWithDocuments]]></VALUE>
+<!--CE436C734491444CAAF5FFEE073AE2E1-->  <MSGTEXT><![CDATA[You cannot change the product stocked flag because it has related documents]]></MSGTEXT>
 <!--CE436C734491444CAAF5FFEE073AE2E1-->  <MSGTYPE><![CDATA[E]]></MSGTYPE>
 <!--CE436C734491444CAAF5FFEE073AE2E1-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
 <!--CE436C734491444CAAF5FFEE073AE2E1-->  <ISINCLUDEINI18N><![CDATA[N]]></ISINCLUDEINI18N>