src-db/database/model/postscript-Oracle.sql
author Shankar Balachandran <shankar.balachandran@openbravo.com>
Wed, 20 Mar 2013 11:18:53 +0530
changeset 19935 347ce63c377b
parent 18312 42e96a0f2fb2
child 19951 815746476942
permissions -rw-r--r--
Fixes Issue 23140 : update audit infrastructure fails with client and org tables

With the fix, when update audit infrastructure is executed from application, the client and org tables are excluded.
An ant task has been created to run the same process including client and org tables.
antonio@665
     1
CREATE OR REPLACE function NOW
carlos@497
     2
RETURN DATE
carlos@497
     3
AS
asier@799
     4
/*************************************************************************
asier@799
     5
* The contents of this file are subject to the Openbravo  Public  License
priya@9072
     6
* Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
asier@799
     7
* Version 1.1  with a permitted attribution clause; you may not  use this
asier@799
     8
* file except in compliance with the License. You  may  obtain  a copy of
asier@799
     9
* the License at http://www.openbravo.com/legal/license.html
asier@799
    10
* Software distributed under the License  is  distributed  on  an "AS IS"
asier@799
    11
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
asier@799
    12
* License for the specific  language  governing  rights  and  limitations
asier@799
    13
* under the License.
asier@799
    14
* The Original Code is Openbravo ERP.
ggi@6701
    15
* The Initial Developer of the Original Code is Openbravo SLU
miguel@15726
    16
* All portions are Copyright (C) 2001-2012 Openbravo SLU
asier@799
    17
* All Rights Reserved.
asier@799
    18
* Contributor(s):  ______________________________________.
asier@799
    19
************************************************************************/
carlos@497
    20
BEGIN
carlos@497
    21
  RETURN SYSDATE;
carlos@497
    22
END NOW;
carlos@497
    23
/-- END NOW
carlos@497
    24
juanpablo@1605
    25
CREATE OR REPLACE FUNCTION hex_to_int (hexn VARCHAR)
juanpablo@1605
    26
  RETURN number
juanpablo@1605
    27
AS
juanpablo@1605
    28
/*************************************************************************
juanpablo@1605
    29
* The contents of this file are subject to the Openbravo  Public  License
priya@9072
    30
* Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
juanpablo@1605
    31
* Version 1.1  with a permitted attribution clause; you may not  use this
juanpablo@1605
    32
* file except in compliance with the License. You  may  obtain  a copy of
juanpablo@1605
    33
* the License at http://www.openbravo.com/legal/license.html
juanpablo@1605
    34
* Software distributed under the License  is  distributed  on  an "AS IS"
juanpablo@1605
    35
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
juanpablo@1605
    36
* License for the specific  language  governing  rights  and  limitations
juanpablo@1605
    37
* under the License.
juanpablo@1605
    38
* The Original Code is Openbravo ERP.
ggi@6701
    39
* The Initial Developer of the Original Code is Openbravo SLU
ggi@6701
    40
* All portions are Copyright (C) 2001-2008 Openbravo SLU
juanpablo@1605
    41
* All Rights Reserved.
juanpablo@1605
    42
* Contributor(s):  ______________________________________.
juanpablo@1605
    43
************************************************************************/
juanpablo@1605
    44
BEGIN
juanpablo@1605
    45
    return to_number(hexn,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
juanpablo@1605
    46
END hex_to_int;
juanpablo@1605
    47
/-- END hex_to_int
adrian@101
    48
carlos@496
    49
CREATE OR REPLACE FUNCTION ad_script_execute (param_Message VARCHAR2)
carlos@496
    50
  RETURN VARCHAR2
carlos@496
    51
AS
asier@799
    52
/*************************************************************************
asier@799
    53
* The contents of this file are subject to the Openbravo  Public  License
priya@9072
    54
* Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
asier@799
    55
* Version 1.1  with a permitted attribution clause; you may not  use this
asier@799
    56
* file except in compliance with the License. You  may  obtain  a copy of
asier@799
    57
* the License at http://www.openbravo.com/legal/license.html
asier@799
    58
* Software distributed under the License  is  distributed  on  an "AS IS"
asier@799
    59
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
asier@799
    60
* License for the specific  language  governing  rights  and  limitations
asier@799
    61
* under the License.
asier@799
    62
* The Original Code is Openbravo ERP.
ggi@6701
    63
* The Initial Developer of the Original Code is Openbravo SLU
ggi@6701
    64
* All portions are Copyright (C) 2001-2008 Openbravo SLU
asier@799
    65
* All Rights Reserved.
asier@799
    66
* Contributor(s):  ______________________________________.
asier@799
    67
************************************************************************/
carlos@496
    68
 v_Message       VARCHAR2(4000) := '';
carlos@496
    69
 v_ResultStr     VARCHAR2(4000) := '';
carlos@496
    70
 TYPE RECORD IS REF CURSOR;
carlos@496
    71
 Cur_Script RECORD;
carlos@496
    72
BEGIN
carlos@496
    73
    v_Message := param_Message;
carlos@496
    74
    FOR Cur_Script IN 
carlos@496
    75
      (SELECT STRSQL, SEQNO FROM AD_SCRIPT_SQL ORDER BY SEQNO)
carlos@496
    76
    LOOP 
carlos@496
    77
    BEGIN 
carlos@496
    78
      EXECUTE IMMEDIATE(Cur_Script.STRSQL) ; 
carlos@496
    79
    EXCEPTION 
carlos@496
    80
    WHEN OTHERS THEN 
carlos@496
    81
      
carlos@496
    82
      if (coalesce(length(v_Message),0)!=0) then
carlos@496
    83
        v_Message:=substr(v_Message||'<br><br>',1,2000);
carlos@496
    84
      end if;
carlos@496
    85
      v_Message := substr(v_Message||'@SQLScriptError@ '||Cur_Script.SeqNo||'. @Executing@'||Cur_Script.strSQL||'<br>'||SQLERRM,1,2000);
carlos@496
    86
    END;
carlos@496
    87
  END LOOP;
carlos@496
    88
carlos@496
    89
 IF( LENGTH(v_Message) > 0 ) THEN
carlos@496
    90
    DBMS_OUTPUT.PUT_LINE('Script errors: ' || v_Message);
carlos@496
    91
 END IF;
carlos@496
    92
 return substr(coalesce(v_ResultStr,'') || coalesce(v_Message,''), 1, 2000);
carlos@496
    93
END ad_script_execute;
carlos@496
    94
/-- END
carlos@496
    95
carlos@496
    96
CREATE OR REPLACE FUNCTION ad_script_drop_recreate_index (p_seqNoStart NUMBER)
carlos@496
    97
  RETURN NUMBER
carlos@496
    98
AS
asier@799
    99
/*************************************************************************
asier@799
   100
* The contents of this file are subject to the Openbravo  Public  License
priya@9072
   101
* Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
asier@799
   102
* Version 1.1  with a permitted attribution clause; you may not  use this
asier@799
   103
* file except in compliance with the License. You  may  obtain  a copy of
asier@799
   104
* the License at http://www.openbravo.com/legal/license.html
asier@799
   105
* Software distributed under the License  is  distributed  on  an "AS IS"
asier@799
   106
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
asier@799
   107
* License for the specific  language  governing  rights  and  limitations
asier@799
   108
* under the License.
asier@799
   109
* The Original Code is Openbravo ERP.
ggi@6701
   110
* The Initial Developer of the Original Code is Openbravo SLU
ggi@6701
   111
* All portions are Copyright (C) 2001-2008 Openbravo SLU
asier@799
   112
* All Rights Reserved.
asier@799
   113
* Contributor(s):  ______________________________________.
asier@799
   114
************************************************************************/
carlos@496
   115
 v_seqNo         NUMBER; 
carlos@496
   116
 v_strSql        VARCHAR2(4000) := '';
carlos@496
   117
 v_strTemp       VARCHAR2(4000) := '';
carlos@496
   118
 v_Message       VARCHAR2(4000) := '';
carlos@496
   119
 v_ResultStr     VARCHAR2(4000) := '';
carlos@496
   120
 TYPE RECORD IS REF CURSOR;
carlos@496
   121
 Cur_UniqueIndex  RECORD;
carlos@496
   122
 Cur_IndexColumns RECORD;
carlos@496
   123
BEGIN
carlos@496
   124
    v_seqNo := p_seqNoStart;
carlos@496
   125
    FOR Cur_UniqueIndex IN (SELECT i.INDEX_NAME, i.TABLE_NAME, i.TABLESPACE_NAME, CONSTRAINT_TYPE
carlos@496
   126
                 FROM USER_INDEXES I left join USER_CONSTRAINTS C1 on c1.INDEX_NAME=I.INDEX_NAME
carlos@496
   127
                 WHERE UNIQUENESS='UNIQUE' AND INDEX_TYPE='NORMAL' AND TABLE_TYPE='TABLE'
carlos@496
   128
               --AND CONSTRAINT_TYPE != 'U'
carlos@496
   129
               ORDER BY INDEX_NAME)
carlos@496
   130
carlos@496
   131
    LOOP
carlos@496
   132
      v_seqNo:=v_seqNo + 1;
carlos@496
   133
      INSERT
carlos@496
   134
      INTO AD_SCRIPT_SQL VALUES (v_seqNo, 'DROP INDEX '||Cur_UniqueIndex.INDEX_NAME) ;
carlos@496
   135
carlos@496
   136
   IF Cur_UniqueIndex.CONSTRAINT_TYPE != 'P' THEN
carlos@496
   137
    v_strSql:='CREATE INDEX '||Cur_UniqueIndex.INDEX_NAME||' ON '||Cur_UniqueIndex.TABLE_NAME||'(';
carlos@496
   138
       v_strTemp:='';
carlos@496
   139
       FOR Cur_IndexColumns IN
carlos@496
   140
         (SELECT COLUMN_NAME
carlos@496
   141
       FROM USER_IND_COLUMNS
carlos@496
   142
       WHERE INDEX_NAME=Cur_UniqueIndex.INDEX_NAME
carlos@496
   143
       ORDER BY COLUMN_POSITION)
carlos@496
   144
       LOOP
carlos@496
   145
         v_strTemp:=v_strTemp ||','|| Cur_IndexColumns.COLUMN_NAME;
carlos@496
   146
       END LOOP;
carlos@496
   147
       v_strSql:=v_strSql || SUBSTR(v_strTemp, 2, 2000) || ') TABLESPACE '||Cur_UniqueIndex.TABLESPACE_NAME;
carlos@496
   148
       INSERT INTO AD_SCRIPT_SQL VALUES(v_seqNo+100000, v_strSql) ;
carlos@496
   149
   END IF;
carlos@496
   150
 END LOOP;
carlos@496
   151
 return v_seqNo;
carlos@496
   152
END ad_script_drop_recreate_index;
carlos@496
   153
/-- END
carlos@496
   154
juanpablo@1605
   155
CREATE OR REPLACE PROCEDURE DBA_RECOMPILE(p_PInstance_ID IN VARCHAR2) 
antonio@506
   156
AS
antonio@506
   157
/*************************************************************************
juanpablo@771
   158
  * The contents of this file are subject to the Compiere Public
juanpablo@771
   159
  * License 1.1 ("License"); You may not use this file except in
juanpablo@771
   160
  * compliance with the License. You may obtain a copy of the License in
juanpablo@771
   161
  * the legal folder of your Openbravo installation.
juanpablo@771
   162
antonio@506
   163
  * Software distributed under the License is distributed on an
antonio@506
   164
  * "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
antonio@506
   165
  * implied. See the License for the specific language governing rights
juanpablo@771
   166
antonio@506
   167
  * and limitations under the License.
antonio@506
   168
  * The Original Code is  Compiere  ERP &  Business Solution
juanpablo@771
   169
  * The Initial Developer of the Original Code is Jorg Janke and ComPiere, Inc.
juanpablo@771
   170
antonio@506
   171
  * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke,
antonio@506
   172
  * parts created by ComPiere are Copyright (C) ComPiere, Inc.;
antonio@506
   173
  * All Rights Reserved.
ggi@6701
   174
  * Contributor(s): Openbravo SLU
juanpablo@771
   175
ggi@6701
   176
  * Contributions are Copyright (C) 1999-2005 Openbravo, S.L.U.
juanpablo@771
   177
  *
juanpablo@771
   178
  * Specifically, this derivative work is based upon the following Compiere
juanpablo@771
   179
  * file and version.
antonio@506
   180
  *************************************************************************
antonio@506
   181
  * $Id: DBA_Recompile.sql,v 1.7 2003/03/14 06:11:21 jjanke Exp $
antonio@506
   182
  ***
antonio@506
   183
  * Title:  Recompile all User_Objects
antonio@506
   184
  * Description:
antonio@506
   185
  ************************************************************************/
antonio@506
   186
  -- Logistice
antonio@506
   187
  v_Message VARCHAR2(2000):=' ';
antonio@506
   188
  v_Result NUMBER:=1; --  0=failure
antonio@506
   189
  --
antonio@506
   190
  v_Buffer VARCHAR2(2000):='';
antonio@506
   191
  v_Line VARCHAR(100) ;
antonio@506
   192
  v_PrintInfo CHAR(1):='N'; -- Diagnostic
antonio@506
   193
  --
antonio@506
   194
  CURSOR Cur_Invalids IS
antonio@506
   195
    SELECT object_id,
antonio@506
   196
      object_name,
antonio@506
   197
      object_type
antonio@506
   198
    FROM user_objects
antonio@506
   199
    WHERE status<>'VALID'
antonio@506
   200
      AND object_type IN('VIEW', 'PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'JAVA CLASS')
antonio@506
   201
    ORDER BY object_type,
antonio@506
   202
      object_name;
antonio@506
   203
    CURSOR Cur_Valids (p_id NUMBER) IS
antonio@506
   204
      SELECT 'FOUND'  FROM user_objects  WHERE status='VALID'  AND object_id=p_id;
antonio@506
   205
      --  failed compile
antonio@506
   206
    TYPE invalid_tab IS TABLE OF Cur_Invalids%ROWTYPE INDEX BY BINARY_INTEGER;
antonio@506
   207
    invalid_tab_rec invalid_tab;
antonio@506
   208
    count_compiled PLS_INTEGER;
antonio@506
   209
    valid_text VARCHAR2(5) ;
antonio@506
   210
    exec_cursor PLS_INTEGER:=DBMS_SQL.OPEN_CURSOR;
antonio@506
   211
    sql_statement VARCHAR2(200) ;
antonio@506
   212
    count_object PLS_INTEGER:=0;
antonio@506
   213
  BEGIN
antonio@506
   214
    LOOP
antonio@506
   215
      count_compiled:=0;
antonio@506
   216
      FOR ci IN Cur_Invalids
antonio@506
   217
      LOOP
antonio@506
   218
        --  not unsuccessfuly compiled yet
antonio@506
   219
        IF NOT invalid_tab_rec.EXISTS(ci.object_id) THEN
antonio@506
   220
          IF(ci.object_type='JAVA CLASS') THEN
antonio@506
   221
            sql_statement:='ALTER JAVA CLASS "' || ci.object_name || '" RESOLVE';
antonio@506
   222
          ELSIF(ci.object_type='PACKAGE BODY') THEN
antonio@506
   223
            sql_statement:='ALTER PACKAGE ' || ci.object_name || ' COMPILE BODY';
antonio@506
   224
          ELSE
antonio@506
   225
            sql_statement:='ALTER ' || ci.object_type || ' ' || ci.object_name || ' COMPILE';
antonio@506
   226
          END IF;
antonio@506
   227
          --  compile
antonio@506
   228
        BEGIN
antonio@506
   229
          count_object:=count_object + 1;
antonio@506
   230
          DBMS_SQL.PARSE(exec_cursor, sql_statement, DBMS_SQL.NATIVE) ;
antonio@506
   231
        EXCEPTION
antonio@506
   232
        WHEN OTHERS THEN
antonio@506
   233
          NULL;
antonio@506
   234
        END;
antonio@506
   235
        --
antonio@506
   236
        OPEN Cur_Valids(ci.object_ID) ;
antonio@506
   237
        FETCH Cur_Valids INTO valid_text;
antonio@506
   238
        IF Cur_Valids%ROWCOUNT>0 THEN
antonio@506
   239
          IF(v_PrintInfo='Y') THEN
antonio@506
   240
            DBMS_OUTPUT.PUT_LINE('OK: ' || ci.object_type || ' ' || ci.object_name) ;
antonio@506
   241
          END IF;
antonio@506
   242
          count_compiled:=count_compiled + 1;
antonio@506
   243
          CLOSE Cur_Valids;
antonio@506
   244
          EXIT;
antonio@506
   245
        ELSE
antonio@506
   246
          IF(LENGTH(v_Message)<1950) THEN
antonio@506
   247
            v_Message:=v_Message || ci.object_name || ' ';
antonio@506
   248
          END IF;
antonio@506
   249
          IF(v_PrintInfo='Y') THEN
antonio@506
   250
            DBMS_OUTPUT.PUT_LINE('Error: ' || ci.object_type || ' ' || ci.object_name) ;
antonio@506
   251
          END IF;
antonio@506
   252
          --
antonio@506
   253
          invalid_tab_rec(ci.object_id) .object_name:=ci.object_name;
antonio@506
   254
          invalid_tab_rec(ci.object_id) .object_type:=ci.object_type;
antonio@506
   255
          CLOSE Cur_Valids;
antonio@506
   256
        END IF;
antonio@506
   257
      END IF; -- not unsuccessfuly compiled yet
antonio@506
   258
    END LOOP; -- Cur_Invalids
antonio@506
   259
    --  any other to be compiled
antonio@506
   260
    IF count_compiled=0 THEN
antonio@506
   261
      EXIT;
antonio@506
   262
    END IF;
antonio@506
   263
  END LOOP; -- outer loop
antonio@506
   264
  DBMS_SQL.CLOSE_CURSOR(exec_cursor) ;
antonio@506
   265
  --
antonio@506
   266
  -- Print Message
antonio@506
   267
  IF(LENGTH(v_Message)=1) THEN
antonio@506
   268
    v_Message:='All valid';
antonio@506
   269
    DBMS_OUTPUT.PUT_LINE(v_Message) ;
antonio@506
   270
  ELSIF(LENGTH(v_Message)>80) THEN
antonio@506
   271
    v_Buffer:=v_Message;
antonio@506
   272
    DBMS_OUTPUT.PUT_LINE('>') ;
antonio@506
   273
    WHILE(LENGTH(v_Buffer)>0)
antonio@506
   274
    LOOP
antonio@506
   275
      v_Line:=SUBSTR(v_Buffer, 1, 80) ;
antonio@506
   276
      DBMS_OUTPUT.PUT_LINE(v_Line) ;
antonio@506
   277
      v_Buffer:=SUBSTR(v_Buffer, 81) ;
antonio@506
   278
    END LOOP;
antonio@506
   279
    DBMS_OUTPUT.PUT_LINE('<') ;
antonio@506
   280
    v_Result:=0;
antonio@506
   281
    DBMS_OUTPUT.PUT_LINE('ERROR') ;
antonio@506
   282
  ELSE
antonio@506
   283
    DBMS_OUTPUT.PUT_LINE('>' || v_Message || '<') ;
antonio@506
   284
    v_Result:=0;
antonio@506
   285
    DBMS_OUTPUT.PUT_LINE('ERROR') ;
antonio@506
   286
  END IF;
antonio@506
   287
  --<<FINISH_PROCESS>>
antonio@506
   288
  IF(p_PInstance_ID IS NOT NULL) THEN
antonio@506
   289
    --  Update AD_PInstance
antonio@506
   290
    AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', v_Result, v_Message) ;
antonio@506
   291
  END IF;
antonio@506
   292
  RETURN;
antonio@506
   293
EXCEPTION
antonio@506
   294
WHEN OTHERS THEN
antonio@506
   295
  DBMS_OUTPUT.PUT_LINE(SQLERRM) ;
antonio@506
   296
  IF DBMS_SQL.IS_OPEN(exec_cursor) THEN
antonio@506
   297
    DBMS_SQL.CLOSE_CURSOR(exec_cursor) ;
antonio@506
   298
  END IF;
antonio@506
   299
  IF Cur_Valids%ISOPEN THEN
antonio@506
   300
    CLOSE Cur_Valids;
antonio@506
   301
  END IF;
antonio@506
   302
END DBA_Recompile;
antonio@506
   303
/-- END
antonio@506
   304
antonio@506
   305
CREATE OR REPLACE PROCEDURE DBA_AFTERIMPORT
antonio@506
   306
AS
antonio@506
   307
/*************************************************************************
juanpablo@771
   308
  * The contents of this file are subject to the Compiere Public
juanpablo@771
   309
  * License 1.1 ("License"); You may not use this file except in
juanpablo@771
   310
  * compliance with the License. You may obtain a copy of the License in
juanpablo@771
   311
  * the legal folder of your Openbravo installation.
antonio@506
   312
  * Software distributed under the License is distributed on an
antonio@506
   313
  * "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
antonio@506
   314
  * implied. See the License for the specific language governing rights
antonio@506
   315
  * and limitations under the License.
antonio@506
   316
  * The Original Code is  Compiere  ERP &  Business Solution
juanpablo@771
   317
  * The Initial Developer of the Original Code is Jorg Janke and ComPiere, Inc.
antonio@506
   318
  * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke,
antonio@506
   319
  * parts created by ComPiere are Copyright (C) ComPiere, Inc.;
antonio@506
   320
  * All Rights Reserved.
ggi@6701
   321
  * Contributor(s): Openbravo SLU
ggi@6701
   322
  * Contributions are Copyright (C) 2001-2008 Openbravo, S.L.U.
juanpablo@771
   323
  *
juanpablo@771
   324
  * Specifically, this derivative work is based upon the following Compiere
juanpablo@771
   325
  * file and version.
antonio@506
   326
  *************************************************************************
antonio@506
   327
  * $Id: DBA_AfterImport.sql,v 1.5 2002/10/21 04:49:46 jjanke Exp $
antonio@506
   328
  * $Source: /cvsroot/compiere/db/database/Procedures/DBA_AfterImport.sql,v $
antonio@506
   329
  ***
antonio@506
   330
  * Title:  Run after Import
antonio@506
   331
  * Description:
antonio@506
   332
  * - Recompile
antonio@506
   333
  * - Compute Statistics
antonio@506
   334
  *****************************************************************************/
antonio@506
   335
  -- Statistics
antonio@506
   336
  TYPE RECORD IS REF CURSOR;
antonio@506
   337
    Cur_Stat RECORD;
antonio@506
   338
    --
antonio@506
   339
    v_Cmd VARCHAR2(256):='';
antonio@506
   340
    v_NoC NUMBER:=0;
antonio@506
   341
    --
antonio@506
   342
  BEGIN
antonio@506
   343
    -- Recompile
antonio@506
   344
    DBA_Recompile(NULL) ;
antonio@506
   345
    -- Statistics
antonio@506
   346
    FOR Cur_Stat IN
antonio@506
   347
      (SELECT Table_Name,
antonio@506
   348
        Blocks
antonio@506
   349
      FROM USER_TABLES
antonio@506
   350
      WHERE DURATION IS NULL -- No temporary tables
antonio@506
   351
        AND(LAST_ANALYZED IS NULL
antonio@506
   352
        OR LAST_ANALYZED<SysDate-7)
antonio@506
   353
      )
antonio@506
   354
    LOOP
antonio@506
   355
      v_Cmd:='ANALYZE TABLE ' || Cur_Stat.Table_Name || ' COMPUTE STATISTICS';
antonio@506
   356
      v_NoC:=v_NoC + 1;
antonio@506
   357
      EXECUTE IMMEDIATE v_Cmd;
antonio@506
   358
    END LOOP;
antonio@506
   359
    DBMS_OUTPUT.PUT_LINE('Statistics computed: ' || v_NoC) ;
antonio@506
   360
    --
antonio@506
   361
END DBA_AfterImport;
antonio@506
   362
/-- END
carlos@496
   363
victor@3065
   364
CREATE OR REPLACE FUNCTION AD_ORG_CHK_DOCUMENTS(p_header_table IN VARCHAR2, p_lines_table IN VARCHAR2, p_document_id IN VARCHAR2, p_header_column_id IN VARCHAR2, p_lines_column_id IN VARCHAR2) 
victor@3065
   365
 RETURN NUMBER
victor@3065
   366
 AS
victor@3065
   367
/*************************************************************************
victor@3065
   368
* The contents of this file are subject to the Openbravo  Public  License
priya@9072
   369
* Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
victor@3065
   370
* Version 1.1  with a permitted attribution clause; you may not  use this
victor@3065
   371
* file except in compliance with the License. You  may  obtain  a copy of
victor@3065
   372
* the License at http://www.openbravo.com/legal/license.html
victor@3065
   373
* Software distributed under the License  is  distributed  on  an "AS IS"
victor@3065
   374
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
victor@3065
   375
* License for the specific  language  governing  rights  and  limitations
victor@3065
   376
* under the License.
victor@3065
   377
* The Original Code is Openbravo ERP.
ggi@6701
   378
* The Initial Developer of the Original Code is Openbravo SLU
ggi@6701
   379
* All portions are Copyright (C) 2008-2009 Openbravo SLU
victor@3065
   380
* All Rights Reserved.
victor@3065
   381
* Contributor(s):  ______________________________________.
victor@3065
   382
************************************************************************/
victor@3065
   383
   v_org_header_id ad_org.ad_org_id%TYPE;
victor@3065
   384
   v_isbusinessunit ad_orgtype.isbusinessunit%TYPE;
victor@3065
   385
   v_islegalentity ad_orgtype.islegalentity%TYPE;
victor@3065
   386
   v_is_included NUMBER:=0;
victor@3065
   387
  
victor@3065
   388
   TYPE RECORD IS REF CURSOR;
victor@3065
   389
   cur_doc_lines RECORD;
victor@3065
   390
 
victor@3065
   391
   v_line_org VARCHAR2(32);
victor@3065
   392
   v_org_line_id VARCHAR2(32);
victor@3065
   393
 BEGIN  
victor@3065
   394
	 
victor@3065
   395
   -- Gets the Business Unit or Legal Entity of the document
victor@3162
   396
   SELECT AD_GET_DOC_LE_BU(p_header_table, p_document_id, p_header_column_id, NULL)
victor@3065
   397
   INTO v_org_header_id
victor@3065
   398
   FROM DUAL;
asier@1929
   399
 
asier@1929
   400
   -- Check the lines belong to the same BU or LE as the header
asier@1929
   401
   OPEN cur_doc_lines FOR
asier@1929
   402
   'SELECT DISTINCT('||p_lines_table||'.ad_org_id) AS v_line_org
asier@1929
   403
    FROM '||p_header_table||', '||p_lines_table||'
asier@1929
   404
    WHERE '||p_header_table||'.'||p_header_column_id||' = '||p_lines_table||'.'||p_lines_column_id||'
victor@2483
   405
    AND '||p_lines_table||'.ad_org_id<>'||''''||v_org_header_id||'''
asier@1929
   406
    AND '||p_lines_table||'.'||p_lines_column_id||'='''||p_document_id||'''';    
asier@1929
   407
    LOOP
asier@1929
   408
      FETCH cur_doc_lines INTO v_line_org;
asier@1929
   409
      EXIT WHEN cur_doc_lines%NOTFOUND;
asier@1929
   410
 
asier@1929
   411
      SELECT ad_orgtype.isbusinessunit, ad_orgtype.islegalentity
asier@1929
   412
      INTO v_isbusinessunit, v_islegalentity
asier@1929
   413
      FROM AD_Org, AD_OrgType
asier@1929
   414
      WHERE AD_Org.AD_OrgType_ID=AD_OrgType.AD_OrgType_ID
asier@1929
   415
      AND AD_Org.AD_Org_ID=v_line_org;
asier@1929
   416
      
asier@1929
   417
      v_org_line_id:=v_line_org;
asier@1929
   418
      -- Gets recursively the organization parent until finding a Business Unit or a Legal Entity
asier@1929
   419
      WHILE (v_isbusinessunit='N' AND v_islegalentity='N') LOOP
asier@1929
   420
        SELECT hh.parent_id, ad_orgtype.isbusinessunit, ad_orgtype.islegalentity
asier@1929
   421
        INTO v_org_line_id, v_isbusinessunit, v_islegalentity
asier@1929
   422
        FROM ad_org, ad_orgtype, ad_treenode pp, ad_treenode hh
asier@1929
   423
        WHERE pp.node_id = hh.parent_id
asier@1929
   424
        AND hh.ad_tree_id = pp.ad_tree_id
asier@1929
   425
        AND pp.node_id=ad_org.ad_org_id
victor@2483
   426
        AND hh.node_id=v_org_line_id
asier@1929
   427
        AND ad_org.ad_orgtype_id=ad_orgtype.ad_orgtype_id
asier@1929
   428
        AND ad_org.isready='Y'
asier@1929
   429
        AND  EXISTS (SELECT 1 FROM ad_tree WHERE ad_tree.treetype='OO' AND hh.ad_tree_id=ad_tree.ad_tree_id AND hh.ad_client_id=ad_tree.ad_client_id);     
asier@1929
   430
      END LOOP;
asier@1929
   431
      
asier@1929
   432
     IF (v_org_line_id<>v_org_header_id) THEN
asier@1929
   433
       v_is_included:=-1;
asier@1929
   434
     END IF;
asier@1929
   435
     EXIT WHEN v_is_included=-1;
asier@1929
   436
 
asier@1929
   437
    END LOOP; 
asier@1929
   438
   CLOSE cur_doc_lines;
asier@1929
   439
 
asier@1929
   440
  RETURN v_is_included;
asier@1929
   441
 
asier@1929
   442
END AD_ORG_CHK_DOCUMENTS;
asier@1929
   443
/-- END
asier@1929
   444
asier@1929
   445
CREATE OR REPLACE FUNCTION AD_ORG_CHK_DOC_PAYMENTS(p_header_table IN VARCHAR2, p_lines_table IN VARCHAR2, p_document_id IN VARCHAR2, p_header_column_id IN VARCHAR2, p_lines_column_id IN VARCHAR2, p_lines_column_payment_id IN VARCHAR2) 
asier@1929
   446
 RETURN NUMBER
asier@1929
   447
 AS
asier@1929
   448
/*************************************************************************
asier@1929
   449
* The contents of this file are subject to the Openbravo  Public  License
priya@9072
   450
* Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
asier@1929
   451
* Version 1.1  with a permitted attribution clause; you may not  use this
asier@1929
   452
* file except in compliance with the License. You  may  obtain  a copy of
asier@1929
   453
* the License at http://www.openbravo.com/legal/license.html
asier@1929
   454
* Software distributed under the License  is  distributed  on  an "AS IS"
asier@1929
   455
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
asier@1929
   456
* License for the specific  language  governing  rights  and  limitations
asier@1929
   457
* under the License.
asier@1929
   458
* The Original Code is Openbravo ERP.
ggi@6701
   459
* The Initial Developer of the Original Code is Openbravo SLU
ggi@6701
   460
* All portions are Copyright (C) 2008-2009 Openbravo SLU
asier@1929
   461
* All Rights Reserved.
asier@1929
   462
* Contributor(s):  ______________________________________.
asier@1929
   463
************************************************************************/
asier@1929
   464
   v_org_header_id ad_org.ad_org_id%TYPE;
asier@1929
   465
   v_isbusinessunit ad_orgtype.isbusinessunit%TYPE;
asier@1929
   466
   v_islegalentity ad_orgtype.islegalentity%TYPE;
asier@1929
   467
   v_is_included NUMBER:=0;
asier@1929
   468
 
asier@1929
   469
 
asier@1929
   470
   TYPE RECORD IS REF CURSOR;
asier@1929
   471
   cur_doc_lines_payment RECORD;
asier@1929
   472
 
asier@1929
   473
   v_line_org_payment VARCHAR2(32);
asier@1929
   474
   v_org_payment_line_id VARCHAR2(32);
asier@1929
   475
 BEGIN
asier@1929
   476
 
victor@3065
   477
   -- Gets the Business Unit or Legal Entity of the document
victor@3162
   478
   SELECT AD_GET_DOC_LE_BU(p_header_table, p_document_id, p_header_column_id, NULL)
victor@3065
   479
   INTO v_org_header_id
victor@3065
   480
   FROM DUAL;
asier@1929
   481
 
asier@1929
   482
   -- Check the payments of the lines belong to the same BU or LE as the document header
asier@1929
   483
   OPEN cur_doc_lines_payment FOR
asier@1929
   484
   'SELECT DISTINCT(C_DEBT_PAYMENT.ad_org_id) AS v_line_org_payment
asier@1929
   485
    FROM '||p_header_table||', '||p_lines_table||', C_DEBT_PAYMENT
asier@1929
   486
    WHERE '||p_header_table||'.'||p_header_column_id||' = '||p_lines_table||'.'||p_lines_column_id||'
asier@1929
   487
    AND C_DEBT_PAYMENT.C_DEBT_PAYMENT_ID='||p_lines_table||'.'||p_lines_column_payment_id||'
victor@2483
   488
    AND '||p_lines_table||'.ad_org_id<>'||''''||v_org_header_id||'''
asier@1929
   489
    AND '||p_lines_table||'.'||p_lines_column_id||'='''||p_document_id||'''';
asier@1929
   490
 
asier@1929
   491
 
asier@1929
   492
   LOOP
asier@1929
   493
    FETCH cur_doc_lines_payment INTO v_line_org_payment;
asier@1929
   494
    EXIT WHEN cur_doc_lines_payment%NOTFOUND;
asier@1929
   495
 
asier@1929
   496
asier@1929
   497
    SELECT ad_orgtype.isbusinessunit, ad_orgtype.islegalentity
asier@1929
   498
    INTO v_isbusinessunit, v_islegalentity
asier@1929
   499
    FROM AD_Org, AD_OrgType
asier@1929
   500
    WHERE AD_Org.AD_OrgType_ID=AD_OrgType.AD_OrgType_ID
asier@1929
   501
    AND AD_Org.AD_Org_ID=v_line_org_payment;
asier@1929
   502
asier@1929
   503
    v_org_payment_line_id:=v_line_org_payment;
asier@1929
   504
    -- Gets recursively the organization parent until finding a Business Unit or a Legal Entity
asier@1929
   505
    WHILE (v_isbusinessunit='N' AND v_islegalentity='N') LOOP
asier@1929
   506
      SELECT hh.parent_id, ad_orgtype.isbusinessunit, ad_orgtype.islegalentity
asier@1929
   507
      INTO v_org_payment_line_id, v_isbusinessunit, v_islegalentity
asier@1929
   508
      FROM ad_org, ad_orgtype, ad_treenode pp, ad_treenode hh
asier@1929
   509
      WHERE pp.node_id = hh.parent_id
asier@1929
   510
      AND hh.ad_tree_id = pp.ad_tree_id
asier@1929
   511
      AND pp.node_id=ad_org.ad_org_id
victor@2483
   512
      AND hh.node_id=v_org_payment_line_id
asier@1929
   513
      AND ad_org.ad_orgtype_id=ad_orgtype.ad_orgtype_id
asier@1929
   514
      AND ad_org.isready='Y'
asier@1929
   515
      AND  EXISTS (SELECT 1 FROM ad_tree WHERE ad_tree.treetype='OO' AND hh.ad_tree_id=ad_tree.ad_tree_id AND hh.ad_client_id=ad_tree.ad_client_id);     
asier@1929
   516
    END LOOP;
asier@1929
   517
asier@1929
   518
    IF (v_org_payment_line_id<>v_org_header_id) THEN
asier@1929
   519
      v_is_included:=-1;
asier@1929
   520
    END IF;
asier@1929
   521
    EXIT WHEN v_is_included=-1;
asier@1929
   522
asier@1929
   523
   END LOOP; 
asier@1929
   524
   CLOSE cur_doc_lines_payment;
asier@1929
   525
 
asier@1929
   526
   RETURN v_is_included;
asier@1929
   527
 
asier@1929
   528
END AD_ORG_CHK_DOC_PAYMENTS;
asier@1929
   529
/-- END
asier@1929
   530
stefan@4016
   531
CREATE OR REPLACE VIEW AD_INTEGER AS 
stefan@4016
   532
SELECT to_number(a.value) AS value
stefan@4016
   533
   FROM ( SELECT ( SELECT count(*) AS count
stefan@4016
   534
                   FROM ad_element
stefan@4016
   535
                  WHERE ad_element.ad_element_id <= e.ad_element_id) AS value
stefan@4016
   536
           FROM ad_element e) a
stefan@4016
   537
  WHERE a.value <= 1024
stefan@4016
   538
/-- END
stefan@4016
   539
ivan@5690
   540
--Inserts an alert recipient for available updates
ivan@5690
   541
--See issue:  https://issues.openbravo.com/view.php?id=11743
ivan@5690
   542
BEGIN
ivan@5690
   543
    INSERT INTO ad_alertrecipient(ad_client_id, ad_org_id, isactive, created, createdby,
ivan@5690
   544
                              updated, updatedby, ad_alertrecipient_id, ad_alertrule_id,
ivan@5690
   545
                              ad_role_id, sendemail)
ivan@5695
   546
         VALUES('0', '0', 'Y', now(), '100', now(), '100', '8CC1347628D148FABA1FC26622F4B070', '1005400000', '0', 'N');
ivan@5690
   547
EXCEPTION WHEN OTHERS THEN NULL;
ivan@5690
   548
END;
ivan@5690
   549
/-- END
ivan@5690
   550
antonio@13928
   551
--Inserts role access for new Smartclient register window
antonio@13928
   552
--It needs to be done this way until this issue is fixed:  https://issues.openbravo.com/view.php?id=18689
antonio@13928
   553
BEGIN
antonio@13928
   554
    INSERT INTO OBUIAPP_View_Role_Access(OBUIAPP_View_Role_Access_ID, OBUIAPP_View_Impl_ID, AD_Role_ID, AD_Client_ID,
antonio@13928
   555
      AD_Org_ID, IsActive, Created,
antonio@13928
   556
      CreatedBy, Updated, UpdatedBy)
antonio@13928
   557
         VALUES(get_uuid(), 'FF808081329B023101329B0CE2080013', '0', '0', '0', 'Y', now(), '0', now(), '0');
antonio@13928
   558
EXCEPTION WHEN OTHERS THEN NULL;
antonio@13928
   559
END;
antonio@13928
   560
/-- END
antonio@13928
   561
asier@6205
   562
--update parent reference for old modules
asier@6413
   563
BEGIN
asier@6413
   564
   EXECUTE IMMEDIATE 'alter trigger ad_reference_mod_trg disable';
asier@6413
   565
END;
asier@6413
   566
/-- END
asier@6413
   567
asier@6205
   568
update ad_reference
asier@6205
   569
   set parentreference_id =( CASE VALIDATIONTYPE WHEN 'S' THEN '30' WHEN 'L' THEN '17' WHEN 'T' THEN '18' end)
asier@6205
   570
   where validationtype in ('S','L','T')
asier@6217
   571
   and parentreference_id is null
asier@6205
   572
/-- END
antonio@5779
   573
asier@6413
   574
BEGIN
asier@6413
   575
   EXECUTE IMMEDIATE 'alter trigger ad_reference_mod_trg enable';
asier@6413
   576
END;
asier@6413
   577
/-- END
asier@6413
   578
 
asier@6413
   579
asier@6275
   580
create or replace
asier@6298
   581
PROCEDURE AD_CREATE_AUDIT_TRIGGERS(p_pinstance_id IN VARCHAR2)
asier@6275
   582
asier@6275
   583
AS
asier@6275
   584
/*************************************************************************
asier@6275
   585
* The contents of this file are subject to the Openbravo  Public  License
priya@9072
   586
* Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
asier@6275
   587
* Version 1.1  with a permitted attribution clause; you may not  use this
asier@6275
   588
* file except in compliance with the License. You  may  obtain  a copy of
asier@6275
   589
* the License at http://www.openbravo.com/legal/license.html
asier@6275
   590
* Software distributed under the License  is  distributed  on  an "AS IS"
asier@6275
   591
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
asier@6275
   592
* License for the specific  language  governing  rights  and  limitations
asier@6275
   593
* under the License.
asier@6275
   594
* The Original Code is Openbravo ERP.
ggi@6701
   595
* The Initial Developer of the Original Code is Openbravo SLU
shankar@19935
   596
* All portions are Copyright (C) 2009-2013 Openbravo SLU
asier@6275
   597
* All Rights Reserved.
asier@6275
   598
* Contributor(s):  ______________________________________.
asier@6275
   599
************************************************************************/
asier@6275
   600
  code CLOB;
asier@6625
   601
  
asier@6625
   602
  cursor_id integer;
asier@6625
   603
  number_of_chunks integer;
asier@6625
   604
  codeSplit dbms_sql.varchar2s;
asier@6625
   605
  ret_val integer;
asier@6625
   606
  
asier@6275
   607
  TYPE RECORD IS REF CURSOR;
asier@6275
   608
  cur_triggers RECORD;
asier@6275
   609
  cur_tables RECORD;
asier@6275
   610
  cur_cols RECORD;
asier@6275
   611
  triggerName varchar2(30);
asier@6275
   612
  recordIdName varchar2(30);
asier@6275
   613
  datatype varchar2(30);
asier@6275
   614
  clientinfo number;
asier@6298
   615
  deleted number :=0;
asier@6298
   616
  created number :=0;
asier@6298
   617
  v_message varchar2(500);
shankar@19935
   618
  v_tableList varchar2(500);
asier@6303
   619
  v_isObps number;
asier@6625
   620
  
asier@6625
   621
  
asier@6625
   622
  FUNCTION splitClob(code clob, splitcode out dbms_sql.varchar2s ) RETURN number AS 
asier@6625
   623
  v_chunks number :=0;
asier@6625
   624
  cnt number;
asier@6625
   625
  v_chunk_size number := 250;
asier@6625
   626
  BEGIN
asier@6625
   627
       cnt := dbms_lob.getlength(code);
asier@6625
   628
      v_chunks := floor(cnt / v_chunk_size) + 1;
asier@6625
   629
      FOR i IN 0 .. v_chunks
asier@6625
   630
      LOOP
asier@6625
   631
        splitcode(i) := dbms_lob.substr(code, v_chunk_size, (i * v_chunk_size) + 1);
asier@6625
   632
      END LOOP;
asier@6625
   633
      return v_chunks;
asier@6625
   634
  END;
asier@6625
   635
  
asier@6275
   636
BEGIN 
asier@6303
   637
  select count(*) 
asier@6303
   638
    into v_isObps
asier@6303
   639
    from ad_system
asier@6303
   640
   where Instance_key is not null
asier@6303
   641
     and activation_key is not null;
asier@6303
   642
     
asier@6303
   643
  if v_isObps = 0 then
asier@6303
   644
    RAISE_APPLICATION_ERROR(-20000, '@OBPSNeededForAudit@') ;
asier@6303
   645
  end if;  
asier@6303
   646
shankar@19935
   647
  if p_pinstance_id is not null then
shankar@19935
   648
    for cur_triggers in (select trigger_name
shankar@19935
   649
                         from user_triggers
shankar@19935
   650
                        where trigger_name like 'AU\_%' escape '\'
shankar@19935
   651
                        and trigger_name <> 'au_ad_client_trg'
shankar@19935
   652
                        and trigger_name <> 'au_ad_org_trg') loop
shankar@19935
   653
      execute immediate 'drop trigger '||cur_triggers.trigger_name;
shankar@19935
   654
      deleted := deleted + 1;
shankar@19935
   655
    end loop;
shankar@19935
   656
  else
shankar@19935
   657
    for cur_triggers in (select trigger_name
asier@6275
   658
                         from user_triggers
asier@6275
   659
                        where trigger_name like 'AU\_%' escape '\') loop
shankar@19935
   660
      execute immediate 'drop trigger '||cur_triggers.trigger_name;
shankar@19935
   661
      deleted := deleted + 1;
shankar@19935
   662
    end loop;
shankar@19935
   663
  end if;
shankar@19935
   664
shankar@19935
   665
  if p_pinstance_id is not null then
shankar@19935
   666
  for cur_tables in (select * from ad_table
shankar@19935
   667
                      where isfullyaudited = 'Y'
shankar@19935
   668
                      and ISVIEW='N'
shankar@19935
   669
                      and (UPPER(TABLENAME) = 'AD_CLIENT'
shankar@19935
   670
                      or UPPER(TABLENAME) = 'AD_ORG')) loop
shankar@19935
   671
      if v_tableList is null then
shankar@19935
   672
        v_tableList := cur_tables.tablename;
shankar@19935
   673
      else
shankar@19935
   674
        v_tableList := v_tableList || ' , '|| cur_tables.tablename;
shankar@19935
   675
      end if;
asier@6275
   676
  end loop;
shankar@19935
   677
  end if;
asier@6275
   678
asier@6275
   679
  for cur_tables in (select *
asier@6275
   680
                       from ad_table
asier@6275
   681
                      where isfullyaudited = 'Y'
asier@6275
   682
                      AND ISVIEW='N'
shankar@19935
   683
                      AND UPPER(TABLENAME) != CASE WHEN p_pinstance_id is not null THEN 'AD_ORG' ELSE ' ' END
shankar@19935
   684
                      AND UPPER(TABLENAME) != CASE WHEN p_pinstance_id is not null THEN 'AD_CLIENT' ELSE ' ' END
asier@6275
   685
                      order by tablename) loop
asier@6275
   686
    dbms_output.put_line('Creating trigger for table '||cur_tables.tablename);
asier@6275
   687
    triggerName := 'AU_'||SUBSTR(cur_tables.tablename,1,23)||'_TRG';
asier@6275
   688
    
asier@6275
   689
    select count(*) into clientinfo
asier@6275
   690
      from dual
asier@6275
   691
     where exists (select 1 from ad_column
asier@6275
   692
                    where ad_table_id = cur_tables.ad_table_id
asier@6275
   693
                     and lower(columnname)='ad_client_id')
asier@6275
   694
       and exists (select 1 from ad_column
asier@6275
   695
                    where ad_table_id = cur_tables.ad_table_id
asier@6275
   696
                     and lower(columnname)='ad_org_id');                     
asier@6275
   697
                     
asier@6275
   698
    
asier@6275
   699
    select columnname
asier@6275
   700
      into recordIdName
asier@6275
   701
      from ad_column
asier@6275
   702
     where ad_table_id = cur_tables.ad_table_id
asier@6275
   703
       and iskey='Y';
asier@6275
   704
    
asier@6275
   705
      code := 'create or replace TRIGGER '||triggerName||' 
asier@6275
   706
AFTER INSERT OR UPDATE OR DELETE
asier@6275
   707
ON '|| cur_tables.tablename||' FOR EACH ROW
asier@6275
   708
DECLARE
asier@6275
   709
  V_USER_ID VARCHAR2(32);
asier@6275
   710
  V_PROCESS_TYPE VARCHAR2(60);
asier@6275
   711
  V_PROCESS_ID VARCHAR2(32);
asier@6275
   712
  V_RECORD_ID VARCHAR2(32);
asier@6275
   713
  V_RECORD_REV NUMBER;
asier@6275
   714
  V_ACTION CHAR(1);
asier@6275
   715
  V_NEW_CHAR VARCHAR2(4000) := NULL;
asier@6275
   716
  V_OLD_CHAR VARCHAR2(4000) := NULL;
asier@6275
   717
  V_NEW_NCHAR NVARCHAR2(2000) := NULL;
asier@6275
   718
  V_OLD_NCHAR NVARCHAR2(2000) := NULL;
asier@6275
   719
  V_OLD_NUMBER NUMBER := NULL;
asier@6275
   720
  V_NEW_NUMBER NUMBER := NULL;
asier@6275
   721
  V_OLD_DATE DATE := NULL;
asier@6275
   722
  V_NEW_DATE DATE := NULL;
asier@16850
   723
  V_OLD_TEXT CLOB := NULL;
asier@16850
   724
  V_NEW_TEXT CLOB := NULL;
asier@6275
   725
  V_TIME DATE;
asier@6275
   726
  V_ORG VARCHAR2(32);
asier@6275
   727
  V_CLIENT VARCHAR2(32);
asier@6302
   728
  V_ISAUDITED CHAR(1);
asier@6275
   729
BEGIN 
asier@6302
   730
';
asier@6302
   731
asier@6302
   732
if (cur_tables.ad_table_id != '100') then
asier@6302
   733
code := code ||
asier@6302
   734
'
asier@6302
   735
  SELECT ISFULLYAUDITED
asier@6302
   736
    INTO V_ISAUDITED
asier@6302
   737
    FROM AD_TABLE
asier@6302
   738
   WHERE AD_TABLE_ID = '''||cur_tables.ad_table_id||''';
asier@6302
   739
  IF V_ISAUDITED = ''N'' THEN 
asier@6302
   740
    RETURN;
asier@6302
   741
  END IF;
asier@6302
   742
';
asier@6302
   743
end if;
asier@6302
   744
asier@6302
   745
code := code ||
asier@6302
   746
'
asier@6275
   747
  BEGIN
asier@6275
   748
    SELECT AD_USER_ID, PROCESSTYPE, PROCESSID
asier@6275
   749
      INTO V_USER_ID, V_PROCESS_TYPE, V_PROCESS_ID
asier@6275
   750
      FROM AD_CONTEXT_INFO;
asier@6275
   751
  EXCEPTION WHEN OTHERS THEN NULL;
asier@6275
   752
  END;
asier@6275
   753
  
asier@6275
   754
  V_TIME := NOW();
asier@6275
   755
 
asier@6275
   756
  IF UPDATING THEN 
asier@6275
   757
    V_RECORD_ID := :NEW.'||recordIdName||';
asier@6275
   758
    V_ACTION := ''U'';';
asier@6275
   759
if (clientinfo!=0) then
asier@6275
   760
code := code ||'
asier@6275
   761
    V_CLIENT := :NEW.AD_CLIENT_ID;
asier@6275
   762
    V_ORG := :NEW.AD_ORG_ID;';
asier@6275
   763
end if;
asier@6275
   764
code := code ||'
asier@6275
   765
  ELSIF INSERTING THEN
asier@6275
   766
    V_RECORD_ID := :NEW.'||recordIdName||';
asier@6275
   767
    V_ACTION := ''I'';';
asier@6275
   768
if (clientinfo!=0) then
asier@6275
   769
code := code ||'
asier@6275
   770
    V_CLIENT := :NEW.AD_CLIENT_ID;
asier@6275
   771
    V_ORG := :NEW.AD_ORG_ID;';
asier@6275
   772
end if;
asier@6275
   773
code := code ||'
asier@6275
   774
  ELSE
asier@6275
   775
    V_RECORD_ID := :OLD.'||recordIdName||';
asier@6275
   776
    V_ACTION := ''D'';';
asier@6275
   777
if (clientinfo!=0) then
asier@6275
   778
code := code ||'
asier@6275
   779
    V_CLIENT := :OLD.AD_CLIENT_ID;
asier@6275
   780
    V_ORG := :OLD.AD_ORG_ID;';
asier@6275
   781
end if;
asier@6275
   782
code := code ||'
asier@6275
   783
  END IF;
asier@6275
   784
asier@6275
   785
SELECT COALESCE(MAX(RECORD_REVISION),0)+1
asier@6275
   786
      INTO V_RECORD_REV
asier@6275
   787
      FROM AD_AUDIT_TRAIL
asier@6275
   788
     WHERE AD_TABLE_ID='''|| cur_tables.ad_table_id||'''
asier@6275
   789
       AND RECORD_ID=V_RECORD_ID;
asier@6275
   790
';
asier@6275
   791
       
asier@6275
   792
    for cur_cols in (select *
asier@6275
   793
                       from user_tab_columns u, aD_column c
asier@6275
   794
                      where table_name = upper(cur_tables.tablename)
asier@6275
   795
                        AND c.ad_table_id = cur_tables.ad_table_id
asier@6275
   796
                        and upper(c.columnname) = u.column_name
asier@6275
   797
                        AND u.data_type != 'BLOB'
asier@6321
   798
                        and upper(c.columnname) not in ('CREATED','CREATEDBY','UPDATED', 'UPDATEDBY')
shankar@19935
   799
                        and c.isexcludeaudit='N'
asier@6275
   800
                        order by c.position) loop
asier@16850
   801
      if (cur_cols.data_type in ('VARCHAR2', 'CHAR')) then
asier@6275
   802
        datatype := 'CHAR';
asier@6363
   803
        code := code || 'IF (UPDATING AND ((COALESCE(:NEW.'||cur_cols.COLUMN_NAME||',''.'') != COALESCE(:OLD.'||cur_cols.COLUMN_NAME||',''.'')) OR ((:NEW.'||cur_cols.COLUMN_NAME||' IS NULL) AND :OLD.'||cur_cols.COLUMN_NAME||'=''.'') OR ((:OLD.'||cur_cols.COLUMN_NAME||' IS NULL) AND :NEW.'||cur_cols.COLUMN_NAME||'=''.'')))';
asier@6275
   804
      elsif (cur_cols.data_type in ('NVARCHAR2', 'NCHAR')) then
asier@6275
   805
        datatype := 'NCHAR';
asier@6363
   806
         code := code || 'IF (UPDATING AND ((COALESCE(:NEW.'||cur_cols.COLUMN_NAME||',''.'') != COALESCE(:OLD.'||cur_cols.COLUMN_NAME||',''.'')) OR ((:NEW.'||cur_cols.COLUMN_NAME||' IS NULL) AND :OLD.'||cur_cols.COLUMN_NAME||'=''.'') OR ((:OLD.'||cur_cols.COLUMN_NAME||' IS NULL) AND :NEW.'||cur_cols.COLUMN_NAME||'=''.'')))';
asier@6275
   807
      elsif (cur_cols.data_type in ('DATE')) then
asier@6275
   808
        datatype := 'DATE';
asier@6363
   809
        code := code || 'IF (UPDATING AND COALESCE(:NEW.'||cur_cols.COLUMN_NAME||', now()) != COALESCE(:OLD.'||cur_cols.COLUMN_NAME||', now()))';
asier@16850
   810
      elsif (cur_cols.data_type in ('CLOB')) then
asier@16850
   811
        datatype := 'TEXT';
asier@16850
   812
        code := code || 'IF (UPDATING AND ((COALESCE(:NEW.'||cur_cols.COLUMN_NAME||',''.'') != COALESCE(:OLD.'||cur_cols.COLUMN_NAME||',''.'')) OR ((:NEW.'||cur_cols.COLUMN_NAME||' IS NULL) AND :OLD.'||cur_cols.COLUMN_NAME||'=''.'') OR ((:OLD.'||cur_cols.COLUMN_NAME||' IS NULL) AND :NEW.'||cur_cols.COLUMN_NAME||'=''.'')))';
asier@6275
   813
      else
asier@6275
   814
        datatype := 'NUMBER';
asier@6363
   815
        code := code || 'IF (UPDATING AND COALESCE(:NEW.'||cur_cols.COLUMN_NAME||', -1) != COALESCE(:OLD.'||cur_cols.COLUMN_NAME||', -1))';
asier@6275
   816
      end if;
asier@6275
   817
      
asier@6275
   818
      
asier@6275
   819
      code := code ||
asier@6275
   820
'
asier@6363
   821
OR DELETING OR INSERTING THEN
asier@6275
   822
    IF (UPDATING OR INSERTING) THEN
asier@6275
   823
      V_NEW_'||datatype||' := :NEW.'||cur_cols.COLUMN_NAME||';
asier@6275
   824
    END IF;
asier@6275
   825
    IF (UPDATING OR DELETING) THEN
asier@6275
   826
      V_OLD_'||datatype||' := :OLD.'||cur_cols.COLUMN_NAME||';
asier@6275
   827
    END IF;
asier@6275
   828
    
asier@6275
   829
    INSERT INTO AD_AUDIT_TRAIL 
asier@6275
   830
           (AD_AUDIT_TRAIL_ID, AD_USER_ID, AD_TABLE_ID, AD_COLUMN_ID, 
asier@6275
   831
           PROCESSTYPE, PROCESS_ID, RECORD_ID, RECORD_REVISION, ACTION, 
asier@6329
   832
           EVENT_TIME, OLD_'||datatype||', NEW_'||datatype||',
asier@6275
   833
           AD_CLIENT_ID, AD_ORG_ID)
asier@6275
   834
          VALUES
asier@6275
   835
           (GET_UUID, V_USER_ID, '''|| cur_tables.ad_table_id||''', '''||cur_cols.ad_column_id||''', 
asier@6275
   836
           v_process_type, v_process_id, v_record_id, v_record_rev, v_action, 
asier@6275
   837
           v_time, v_old_'||datatype||', v_new_'||datatype||',
asier@6275
   838
           V_CLIENT, V_ORG);
asier@6363
   839
  END IF;
asier@6363
   840
';
asier@6275
   841
    end loop;
asier@6275
   842
 
asier@6275
   843
code := code ||
asier@6275
   844
'END
asier@6275
   845
;';
asier@6625
   846
cursor_id :=dbms_sql.open_cursor;
asier@6625
   847
number_of_chunks := splitClob(code, codeSplit);
asier@6625
   848
dbms_sql.parse(cursor_id, codeSplit, 0, number_of_chunks, NULL , dbms_sql.native); 
asier@6625
   849
ret_val := dbms_sql.execute(cursor_id);
asier@6625
   850
DBMS_SQL.close_cursor(cursor_id);
asier@6625
   851
asier@6298
   852
    created := created + 1;
asier@6275
   853
  end loop;
asier@6302
   854
  
shankar@19935
   855
  if v_tableList is null then
shankar@19935
   856
    v_Message := '@Deleted@: '||deleted||' @Created@: '||created;
shankar@19935
   857
  else
shankar@19935
   858
    v_Message := '@Deleted@: '||deleted||' @Created@: '||created||'. @RunAuditFromTerminalTbl@ '|| v_tableList || '. @RunAuditFromTerminalHint@' ;
shankar@19935
   859
  end if;
asier@6298
   860
  AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 1, v_Message) ;
asier@6298
   861
  EXCEPTION
asier@6298
   862
WHEN OTHERS THEN
asier@6298
   863
  v_Message:= '@ERROR=' || SQLERRM;
asier@6298
   864
  DBMS_OUTPUT.PUT_LINE(v_Message) ;
asier@6298
   865
  IF (p_PInstance_ID IS NOT NULL) THEN
asier@6298
   866
    AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 0, v_Message) ;
asier@6298
   867
  END IF;
asier@6298
   868
  RETURN;
asier@6275
   869
END AD_CREATE_AUDIT_TRIGGERS;
asier@6275
   870
/-- END
asier@6275
   871
asier@18312
   872
create or replace
asier@18312
   873
FUNCTION AD_Execute_Function(p_code IN VARCHAR2) RETURN VARCHAR2
asier@18312
   874
AS
asier@18312
   875
/*************************************************************************
asier@18312
   876
* The contents of this file are subject to the Openbravo  Public  License
asier@18312
   877
* Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
asier@18312
   878
* Version 1.1  with a permitted attribution clause; you may not  use this
asier@18312
   879
* file except in compliance with the License. You  may  obtain  a copy of
asier@18312
   880
* the License at http://www.openbravo.com/legal/license.html
asier@18312
   881
* Software distributed under the License  is  distributed  on  an "AS IS"
asier@18312
   882
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
asier@18312
   883
* License for the specific  language  governing  rights  and  limitations
asier@18312
   884
* under the License.
asier@18312
   885
* The Original Code is Openbravo ERP.
asier@18312
   886
* The Initial Developer of the Original Code is Openbravo SLU
asier@18312
   887
* All portions are Copyright (C) 2012 Openbravo SLU
asier@18312
   888
* All Rights Reserved.
asier@18312
   889
* Contributor(s):  ______________________________________.
asier@18312
   890
************************************************************************/
asier@18312
   891
asier@18312
   892
  v_response varchar2(32);
asier@18312
   893
begin
asier@18312
   894
   execute immediate 'begin :1 := '||p_code||'; end;' using out v_response;
asier@18312
   895
   return v_response;
asier@18312
   896
end;
asier@18312
   897
/-- END
asier@18312
   898
 
asier@18312
   899
carlos@0
   900
CALL DBA_RECOMPILE(NULL)
carlos@0
   901
/-- END
carlos@0
   902