Fixes issue 21589: Checks what columns are updated before doing
authorDavid Miguelez <david.miguelez@openbravo.com>
Thu, 13 Sep 2012 19:27:19 +0200
changeset 17974 292bdf82cd53
parent 17973 861d5861059e
child 17975 af32d2655e73
Fixes issue 21589: Checks what columns are updated before doing
other operations.
src-db/database/model/triggers/M_TRANSACTION_TRG.xml
--- a/src-db/database/model/triggers/M_TRANSACTION_TRG.xml	Tue Sep 18 15:22:53 2012 +0200
+++ b/src-db/database/model/triggers/M_TRANSACTION_TRG.xml	Thu Sep 13 19:27:19 2012 +0200
@@ -30,6 +30,7 @@
   v_IsOneAtSetValReq M_Attributeset.Isoneattrsetvalrequired%TYPE;
   v_PRODUCTION_ID VARCHAR2(32);
   v_LINE NUMBER;
+  v_DateNull DATE := TO_DATE('01-01-1900', 'DD-MM-YYYY');
     
 BEGIN
     
@@ -43,19 +44,66 @@
     FROM M_PRODUCT P LEFT JOIN M_ATTRIBUTESET A
     ON A.M_ATTRIBUTESET_ID = P.M_ATTRIBUTESET_ID
     WHERE P.M_PRODUCT_ID=:NEW.M_PRODUCT_ID;
-    IF(COALESCE(v_UOM_ID, '0') <> COALESCE(:NEW.C_UOM_ID, '0')) THEN
-      RAISE_APPLICATION_ERROR(-20000, '@20111@') ;
+    
+		IF UPDATING THEN
+		  IF (COALESCE(:old.m_transaction_id, '0') <> COALESCE(:new.m_transaction_id, '0'))
+    	OR(COALESCE(:old.movementtype, '.') <> COALESCE(:new.movementtype, '.'))
+    	OR(COALESCE(:old.m_locator_id, '0') <> COALESCE(:new.m_locator_id, '0'))
+    	OR(COALESCE(:old.m_product_id, '0') <> COALESCE(:new.m_product_id, '0'))
+    	OR(COALESCE(:old.movementdate, v_DateNull) <> COALESCE(:new.movementdate, v_DateNull))
+	    OR(COALESCE(:old.movementqty, '0') <> COALESCE(:new.movementqty, '0'))
+	    OR(COALESCE(:old.m_inventoryline_id, '0') <> COALESCE(:new.m_inventoryline_id, '0'))
+    	OR(COALESCE(:old.m_movementline_id, '0') <> COALESCE(:new.m_movementline_id, '0'))
+    	OR(COALESCE(:old.m_inoutline_id, '0') <> COALESCE(:new.m_inoutline_id, '0'))
+    	OR(COALESCE(:old.m_productionline_id, '0') <> COALESCE(:new.m_productionline_id, '0'))
+    	OR(COALESCE(:old.c_projectissue_id, '0') <> COALESCE(:new.c_projectissue_id, '0'))
+    	OR(COALESCE(:old.m_attributesetinstance_id, '0') <> COALESCE(:new.m_attributesetinstance_id, '0'))
+    	OR(COALESCE(:old.m_product_uom_id, '0') <> COALESCE(:new.m_product_uom_id, '0'))
+    	OR(COALESCE(:old.quantityorder, '0') <> COALESCE(:new.quantityorder, '0'))
+    	OR(COALESCE(:old.c_uom_id, '0') <> COALESCE(:new.c_uom_id, '0'))
+    	OR(COALESCE(:old.m_internal_consumptionline_id ,'0') <> COALESCE(:new.m_internal_consumptionline_id ,'0')) THEN
+
+      	IF(COALESCE(v_UOM_ID, '0') <> COALESCE(:NEW.C_UOM_ID, '0')) THEN
+        	RAISE_APPLICATION_ERROR(-20000, '@20111@') ;
+      	END IF;
+      	IF(v_attributeset_id IS NOT NULL AND (v_AttrSetValueType IS NULL OR v_AttrSetValueType <> 'F') AND v_IsOneAtSetValReq = 'Y' AND COALESCE(:NEW.M_ATTRIBUTESETINSTANCE_ID, '0') = '0') THEN
+        	RAISE_APPLICATION_ERROR(-20000, '@20112@' || ' - ' || v_Name) ;
+      	END IF;
+      	--Does not allow to change the attribute set value
+      	-- for products which attribute set value type is Fixed
+      	IF (:NEW.M_PRODUCT_ID IS NOT NULL AND COALESCE(:new.M_AttributeSetInstance_ID,'0')!='0') THEN
+        	SELECT ATTRSETVALUETYPE 
+          	INTO v_AttrSetValueType
+        	FROM M_PRODUCT 
+        	WHERE M_PRODUCT_ID=:NEW.M_PRODUCT_ID;
+        	IF (v_AttrSetValueType='F') THEN
+          	RAISE_APPLICATION_ERROR(-20000, '@AttrSetValueFixed@');
+        	END IF;
+      	END IF;
+      END IF;
     END IF;
-    IF(v_attributeset_id IS NOT NULL AND (v_AttrSetValueType IS NULL OR v_AttrSetValueType <> 'F') AND v_IsOneAtSetValReq = 'Y' AND COALESCE(:NEW.M_ATTRIBUTESETINSTANCE_ID, '0') = '0') THEN
-      RAISE_APPLICATION_ERROR(-20000, '@20112@' || ' - ' || v_Name) ;
-    END IF;
-    SELECT MAX(MOVEMENTDATE)
-    INTO v_DATEINVENTORY
-    FROM M_INVENTORY I,
-      M_INVENTORYLINE IL
-    WHERE I.M_INVENTORY_ID=IL.M_INVENTORY_ID
-      AND IL.M_INVENTORYLINE_ID=:NEW.M_INVENTORYLINE_ID;
+    
+		IF INSERTING THEN
+			IF(COALESCE(v_UOM_ID, '0') <> COALESCE(:NEW.C_UOM_ID, '0')) THEN
+       	RAISE_APPLICATION_ERROR(-20000, '@20111@') ;
+     	END IF;
+      IF(v_attributeset_id IS NOT NULL AND (v_AttrSetValueType IS NULL OR v_AttrSetValueType <> 'F') AND v_IsOneAtSetValReq = 'Y' AND COALESCE(:NEW.M_ATTRIBUTESETINSTANCE_ID, '0') = '0') THEN
+       	RAISE_APPLICATION_ERROR(-20000, '@20112@' || ' - ' || v_Name) ;
+     	END IF;
+      --Does not allow to change the attribute set value
+      -- for products which attribute set value type is Fixed
+      IF (:NEW.M_PRODUCT_ID IS NOT NULL AND COALESCE(:new.M_AttributeSetInstance_ID,'0')!='0') THEN
+       	SELECT ATTRSETVALUETYPE 
+         	INTO v_AttrSetValueType
+       	FROM M_PRODUCT 
+        WHERE M_PRODUCT_ID=:NEW.M_PRODUCT_ID;
+       	IF (v_AttrSetValueType='F') THEN
+         	RAISE_APPLICATION_ERROR(-20000, '@AttrSetValueFixed@');
+       	END IF;
+     	END IF;
+		END IF;
   END IF;
+  
   -- Updating inventory
   IF UPDATING OR DELETING THEN
     M_UPDATE_INVENTORY(:OLD.AD_CLIENT_ID, :OLD.AD_ORG_ID, :OLD.UPDATEDBY, :OLD.M_PRODUCT_ID, :OLD.M_LOCATOR_ID, :OLD.M_ATTRIBUTESETINSTANCE_ID, :OLD.C_UOM_ID, :OLD.M_PRODUCT_UOM_ID, -:OLD.MOVEMENTQTY, -:OLD.QUANTITYORDER, NULL, :OLD.MOVEMENTQTY, :OLD.QUANTITYORDER) ;
@@ -71,6 +119,13 @@
     -- END FIXME
   END IF;
   IF INSERTING OR UPDATING THEN
+    SELECT MAX(MOVEMENTDATE)
+      INTO v_DATEINVENTORY
+    FROM M_INVENTORY I,
+      M_INVENTORYLINE IL
+    WHERE I.M_INVENTORY_ID=IL.M_INVENTORY_ID
+      AND IL.M_INVENTORYLINE_ID=:NEW.M_INVENTORYLINE_ID;
+        
     M_UPDATE_INVENTORY(:NEW.AD_CLIENT_ID, :NEW.AD_ORG_ID, :NEW.UPDATEDBY, :NEW.M_PRODUCT_ID, :NEW.M_LOCATOR_ID, :NEW.M_ATTRIBUTESETINSTANCE_ID, :NEW.C_UOM_ID, :NEW.M_PRODUCT_UOM_ID, :NEW.MOVEMENTQTY, :NEW.QUANTITYORDER, v_DATEINVENTORY, -:NEW.MOVEMENTQTY, -:NEW.QUANTITYORDER) ;
     -- FIXME: this shall be removed/reviewed when new warehouse management is implemented
     IF (:NEW.M_PRODUCTIONLINE_ID IS NOT NULL) THEN    
@@ -83,19 +138,6 @@
     END IF;
     -- END FIXME
   END IF;
-  IF (INSERTING OR UPDATING) THEN
-   --Does not allow to change the attribute set value
-   -- for products which attribute set value type is Fixed
-   IF (:NEW.M_PRODUCT_ID IS NOT NULL AND COALESCE(:new.M_AttributeSetInstance_ID,'0')!='0') THEN
-    SELECT ATTRSETVALUETYPE 
-    INTO v_AttrSetValueType
-    FROM M_PRODUCT 
-    WHERE M_PRODUCT_ID=:NEW.M_PRODUCT_ID;
-    IF (v_AttrSetValueType='F') THEN
-     RAISE_APPLICATION_ERROR(-20000, '@AttrSetValueFixed@');
-    END IF;
-   END IF;
-  END IF;
 END M_TRANSACTION_TRG
 ]]></body>
     </trigger>