Fixed bug 1000. Added a trigger on AD_COLUMN table to generate Sequences on existing clients when the columnname is DocumentNo or Value.
authorGorka Ion Damián <gorkaion.damian@openbravo.com>
Thu, 19 Jun 2008 17:07:08 +0000
changeset 1291 b56cf1635810
parent 1290 47adb4d05065
child 1292 5a6316dd8fb8
Fixed bug 1000. Added a trigger on AD_COLUMN table to generate Sequences on existing clients when the columnname is DocumentNo or Value.
src-db/database/model/triggers/AD_COLUMN_TRG2.xml
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-db/database/model/triggers/AD_COLUMN_TRG2.xml	Thu Jun 19 17:07:08 2008 +0000
@@ -0,0 +1,61 @@
+<?xml version="1.0"?>
+  <database name="TRIGGER AD_COLUMN_TRG2">
+    <trigger name="AD_COLUMN_TRG2" table="AD_COLUMN" fires="after" insert="true" update="true" delete="false" foreach="row">
+      <body><![CDATA[
+/*************************************************************************
+* The contents of this file are subject to the Openbravo  Public  License
+* Version  1.0  (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 SL
+* All portions are Copyright (C) 2008 Openbravo SL
+* All Rights Reserved.
+* Contributor(s):  ______________________________________.
+************************************************************************/
+  TYPE RECORD IS REF CURSOR;
+  CUR_Clients RECORD;
+  v_TableName VARCHAR2(40);
+  v_NextNo NUMBER;
+  BEGIN
+  /**
+  * Create Sequence for DocumentNo and Value columns
+  */
+  IF (:new.ColumnName = 'DocumentNo' OR :new.ColumnName = 'Value') THEN
+    SELECT TableName INTO v_TableName
+    FROM ad_table
+    WHERE ad_table.ad_table_id = :new.ad_table_id;
+         
+    FOR CUR_Clients IN (
+      SELECT ad_client_id
+      FROM ad_client
+      WHERE NOT EXISTS (SELECT 1 FROM ad_sequence WHERE name = 'DocumentNo_' || v_tablename)
+        AND ad_client_id <> 0
+    ) LOOP
+      Ad_Sequence_Next('AD_Sequence', CUR_Clients.ad_client_id, v_NextNo) ;
+      INSERT INTO AD_Sequence (
+        AD_Sequence_ID, AD_Client_ID, AD_Org_ID, IsActive, 
+        Created, CreatedBy, Updated, UpdatedBy,
+        Name, Description, 
+        VFormat, IsAutoSequence, IncrementNo, 
+        StartNo, CurrentNext, CurrentNextSys, 
+        IsTableID, Prefix, Suffix, StartNewYear
+      ) VALUES (
+        v_NextNo, CUR_Clients.ad_client_id, 0, 'Y',
+        now(), 0, now(), 0,
+        'DocumentNo_' || v_TableName,  'DocumentNo/Value for Table ' || v_TableName,
+        NULL,  'Y', 1,
+        10000000, 10000000, 10000000,
+        'N', NULL, NULL, 'N'
+      );
+    END LOOP;
+  END IF;
+END AD_COLUMN_TRG2
+]]></body>
+    </trigger>
+  </database>