Fixes issue 0017749: The date of the matched invoice doesn't update correctly
authorJon Alegría <jon.alegria@openbravo.com>
Thu, 21 Jul 2011 20:12:26 +0200
changeset 13242 4675b0e190f7
parent 13241 24d223cc5c9e
child 13243 01bcb64861bd
Fixes issue 0017749: The date of the matched invoice doesn't update correctly
Now you can not change the dateacct if there is a matched invoice posted
src-db/database/model/triggers/C_INVOICE_TRG.xml
src-db/database/model/triggers/M_INOUT_TRG_PROV.xml
src-db/database/sourcedata/AD_MESSAGE.xml
--- a/src-db/database/model/triggers/C_INVOICE_TRG.xml	Thu Jul 21 19:55:20 2011 +0200
+++ b/src-db/database/model/triggers/C_INVOICE_TRG.xml	Thu Jul 21 20:12:26 2011 +0200
@@ -81,6 +81,19 @@
        END IF;
      END IF;
 
+   IF ((COALESCE(:OLD.DATEACCT,  TO_DATE('31-12-9999', 'DD-MM-YYYY')) <> COALESCE(:NEW.DATEACCT, TO_DATE('31-12-9999', 'DD-MM-YYYY'))) AND :new.DOCSTATUS NOT IN ('DR', 'RE')) THEN
+		SELECT COUNT(*) INTO V_COUNT FROM M_MATCHINV M WHERE M.C_INVOICELINE_ID IN (SELECT C.C_INVOICELINE_ID FROM C_INVOICELINE C WHERE C.C_INVOICE_ID = :old.C_INVOICE_ID)
+							AND M.M_INOUTLINE_ID IN (SELECT C.M_INOUTLINE_ID FROM C_INVOICELINE C WHERE C.C_INVOICE_ID = :old.C_INVOICE_ID)
+							AND M.POSTED = 'Y'; 
+		IF (:old.POSTED = 'Y') THEN
+			RAISE_APPLICATION_ERROR(-20000, '@20501@') ;
+		ELSIF (V_COUNT <> 0) THEN
+			RAISE_APPLICATION_ERROR(-20000, '@20632@') ;
+    ELSE
+      UPDATE M_MATCHINV SET DATETRX = :NEW.DATEACCT WHERE C_INVOICELINE_ID IN (SELECT C.C_INVOICELINE_ID FROM C_INVOICELINE C WHERE C.C_INVOICE_ID = :old.C_INVOICE_ID)
+      AND M_INOUTLINE_ID IN (SELECT C.M_INOUTLINE_ID FROM C_INVOICELINE C WHERE C.C_INVOICE_ID = :old.C_INVOICE_ID);
+		END IF;
+   END IF;
 
 
    IF(:OLD.Posted='Y' AND ((COALESCE(:OLD.DATEACCT,  TO_DATE('31-12-9999', 'DD-MM-YYYY')) <> COALESCE(:NEW.DATEACCT, TO_DATE('31-12-9999', 'DD-MM-YYYY'))) OR(COALESCE(:OLD.C_CAMPAIGN_ID, '0') <> COALESCE(:NEW.C_CAMPAIGN_ID, '0')) OR(COALESCE(:OLD.C_PROJECT_ID, '0') <> COALESCE(:NEW.C_PROJECT_ID, '0')) OR(COALESCE(:OLD.C_ACTIVITY_ID, '0') <> COALESCE(:NEW.C_ACTIVITY_ID, '0')))) THEN
--- a/src-db/database/model/triggers/M_INOUT_TRG_PROV.xml	Thu Jul 21 19:55:20 2011 +0200
+++ b/src-db/database/model/triggers/M_INOUT_TRG_PROV.xml	Thu Jul 21 20:12:26 2011 +0200
@@ -27,6 +27,7 @@
     ************************************************************************/
   v_docType_ID VARCHAR2(32) ;
   v_movementType VARCHAR(60) ;
+  v_count NUMBER;
     
 BEGIN
     
@@ -46,6 +47,21 @@
   FROM C_DOCTYPE
   WHERE C_DocType_ID=v_docType_ID;
   :NEW.MOVEMENTTYPE:=v_movementType;
+
+  IF UPDATING THEN
+	IF ((COALESCE(:OLD.DATEACCT,  TO_DATE('31-12-9999', 'DD-MM-YYYY')) <> COALESCE(:NEW.DATEACCT, TO_DATE('31-12-9999', 'DD-MM-YYYY'))) AND :new.DOCSTATUS NOT IN ('DR', 'RE')) THEN
+		SELECT COUNT(*) INTO V_COUNT FROM M_MATCHINV M WHERE M.M_INOUTLINE_ID IN (SELECT MI.M_INOUTLINE_ID FROM M_INOUTLINE MI WHERE MI.M_INOUT_ID = :old.M_INOUT_ID)
+							AND M.POSTED = 'Y'; 
+		IF (:old.POSTED = 'Y') THEN
+			RAISE_APPLICATION_ERROR(-20000, '@20501@') ;
+		ELSIF (V_COUNT <> 0) THEN
+			RAISE_APPLICATION_ERROR(-20000, '@20632@') ;
+    ELSE
+      UPDATE M_MATCHINV SET DATETRX = :NEW.DATEACCT WHERE M_INOUTLINE_ID IN (SELECT MI.M_INOUTLINE_ID FROM M_INOUTLINE MI WHERE MI.M_INOUT_ID = :old.M_INOUT_ID);
+		END IF;
+	END IF;
+  END IF;
+
 END M_INOUT_TRG_PROV
 ]]></body>
     </trigger>
--- a/src-db/database/sourcedata/AD_MESSAGE.xml	Thu Jul 21 19:55:20 2011 +0200
+++ b/src-db/database/sourcedata/AD_MESSAGE.xml	Thu Jul 21 20:12:26 2011 +0200
@@ -19675,4 +19675,16 @@
 <!--FF8081812F9C927B012F9CA0BF39000D-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
 <!--FF8081812F9C927B012F9CA0BF39000D--></AD_MESSAGE>
 
+<!--FF80808130E6C18B0130E6C3A38E0005--><AD_MESSAGE>
+<!--FF80808130E6C18B0130E6C3A38E0005-->  <AD_MESSAGE_ID><![CDATA[FF80808130E6C18B0130E6C3A38E0005]]></AD_MESSAGE_ID>
+<!--FF80808130E6C18B0130E6C3A38E0005-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--FF80808130E6C18B0130E6C3A38E0005-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--FF80808130E6C18B0130E6C3A38E0005-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--FF80808130E6C18B0130E6C3A38E0005-->  <VALUE><![CDATA[20632]]></VALUE>
+<!--FF80808130E6C18B0130E6C3A38E0005-->  <MSGTEXT><![CDATA[Invoice lines do have posted Matched Invoice documents associated. Please un-post them before modifying the accounting date.
+You can navigate to these matched invoice documents by pressing Linked Items in the lines of the invoice.]]></MSGTEXT>
+<!--FF80808130E6C18B0130E6C3A38E0005-->  <MSGTYPE><![CDATA[E]]></MSGTYPE>
+<!--FF80808130E6C18B0130E6C3A38E0005-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--FF80808130E6C18B0130E6C3A38E0005--></AD_MESSAGE>
+
 </data>