Fixes issue 19404: Error when you try to change a period's name in Oracle
authorJavier Etxarri <javier.echarri@openbravo.com>
Mon, 03 Sep 2012 18:21:23 +0200
changeset 9893 0a548ca8374d
parent 9892 e77200cbed9c
child 9894 c978db09cc26
Fixes issue 19404: Error when you try to change a period's name in Oracle
src-db/database/model/triggers/C_PERIOD_TRG2.xml
src-db/database/model/triggers/C_PERIOD_TRG3.xml
--- a/src-db/database/model/triggers/C_PERIOD_TRG2.xml	Wed Aug 29 12:49:23 2012 +0200
+++ b/src-db/database/model/triggers/C_PERIOD_TRG2.xml	Mon Sep 03 18:21:23 2012 +0200
@@ -48,19 +48,10 @@
 	IF (INSERTING AND :NEW.PERIODTYPE = 'C_YEARPERIOD') THEN
 		-- Creating periods from procedure, no need to check dates
 		:NEW.PERIODTYPE := 'S';
-	ELSE
+	Else
 		IF (:NEW.STARTDATE > :NEW.ENDDATE) THEN
 			RAISE_APPLICATION_ERROR(-20000, '@DatesWrong@');
-		END IF;
-		SELECT COUNT(*) INTO V_COUNT 
-		FROM C_PERIOD 
-		WHERE C_PERIOD_ID <> :NEW.C_PERIOD_ID 
-			AND AD_ORG_ID = :NEW.AD_ORG_ID
-			AND :NEW.STARTDATE <= ENDDATE 
-			AND :NEW.ENDDATE >= STARTDATE;
-		IF (V_COUNT > 0) THEN
-			RAISE_APPLICATION_ERROR(-20000, '@DatesOverlapped@');
-		END IF;
+		End If;
 	END IF;
    END IF;
 
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-db/database/model/triggers/C_PERIOD_TRG3.xml	Mon Sep 03 18:21:23 2012 +0200
@@ -0,0 +1,54 @@
+<?xml version="1.0"?>
+  <database name="TRIGGER C_PERIOD_TRG3">
+    <trigger name="C_PERIOD_TRG3" table="C_PERIOD" fires="after" insert="true" update="true" delete="false" foreach="statement">
+      <body><![CDATA[
+    /*************************************************************************
+    * The contents of this file are subject to the Openbravo  Public  License
+    * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
+    * Version 1.1  with a permitted attribution clause; you may not  use this
+    * file except in compliance with the License. You  may  obtain  a copy of
+    * the License at http://www.openbravo.com/legal/license.html
+    * Software distributed under the License  is  distributed  on  an "AS IS"
+    * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
+    * License for the specific  language  governing  rights  and  limitations
+    * 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 Rights Reserved.
+    * Contributor(s):  ______________________________________.
+    ************************************************************************/
+
+    V_Count NUMBER:= 0;
+    TYPE RECORD IS REF CURSOR;
+    Cur_Period RECORD;
+Begin
+
+    IF AD_isTriggerEnabled()='N' THEN RETURN;
+    END IF;
+
+    For Cur_Period In
+      (Select * 
+      From C_Period
+      Where C_Period_Id In (Select Distinct(Cp.C_Period_Id)
+      From C_Period Cp 
+            Join C_Periodcontrol Cpc On Cpc.C_Period_Id= Cp.C_Period_Id 
+      Where Periodstatus <> 'P'))
+    Loop
+       Select Count(*) 
+       Into V_COUNT
+       From C_Period 
+       Where C_Period_Id <> Cur_Period.C_Period_Id
+       And Ad_Org_Id=Cur_Period.Ad_Org_Id
+       And Ad_Client_Id=Cur_Period.Ad_Client_Id
+       And Cur_Period.startdate <= Enddate
+       And Cur_Period.Enddate >= Startdate;
+       If (V_COUNT > 0) Then
+         RAISE_APPLICATION_ERROR(-20000, '@DatesOverlapped@');
+       End if;
+    End Loop;
+
+    END C_PERIOD_TRG3
+]]></body>
+    </trigger>
+  </database>