src-db/database/model/postscript-Oracle.sql
author Asier Lostalé <asier.lostale@openbravo.com>
Wed, 12 Nov 2008 13:14:11 +0000
changeset 1929 ad0353d93b0f
parent 1605 8a0fe0193bef
child 2483 a92427f77522
permissions -rw-r--r--
Merge back modularity branch to trunk.

Due to svn problems it has been merged using patch after updating modularity
with trunk rev 9900. This means that the svn log info for the files modified
in modularity branch will not be available.
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
asier@799
     6
* Version  1.0  (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.
asier@799
    15
* The Initial Developer of the Original Code is Openbravo SL
asier@799
    16
* All portions are Copyright (C) 2001-2008 Openbravo SL
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
juanpablo@1605
    30
* Version  1.0  (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.
juanpablo@1605
    39
* The Initial Developer of the Original Code is Openbravo SL
juanpablo@1605
    40
* All portions are Copyright (C) 2001-2008 Openbravo SL
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_disable_triggers (p_seqNoStart NUMBER)
carlos@496
    50
  RETURN NUMBER
carlos@496
    51
AS
asier@799
    52
/*************************************************************************
asier@799
    53
* The contents of this file are subject to the Openbravo  Public  License
asier@799
    54
* Version  1.0  (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.
asier@799
    63
* The Initial Developer of the Original Code is Openbravo SL
asier@799
    64
* All portions are Copyright (C) 2001-2008 Openbravo SL
asier@799
    65
* All Rights Reserved.
asier@799
    66
* Contributor(s):  ______________________________________.
asier@799
    67
************************************************************************/
carlos@496
    68
 v_seqNo      NUMBER := p_seqNoStart;
carlos@496
    69
 TYPE RECORD IS REF CURSOR;
carlos@496
    70
 Cur_Triggers RECORD;
carlos@496
    71
BEGIN
carlos@496
    72
     FOR Cur_Triggers IN 
carlos@496
    73
      (SELECT OBJECT_NAME AS NAME 
carlos@496
    74
      FROM USER_OBJECTS 
carlos@496
    75
      WHERE OBJECT_TYPE='TRIGGER' 
carlos@496
    76
      ORDER BY OBJECT_NAME 
carlos@496
    77
      ) 
carlos@496
    78
    LOOP 
carlos@496
    79
      v_seqNo:=v_seqNo + 1; 
carlos@496
    80
      INSERT 
carlos@496
    81
      INTO AD_SCRIPT_SQL VALUES 
carlos@496
    82
        (v_seqNo, 'ALTER TRIGGER '||Cur_Triggers.NAME||' DISABLE') ; 
carlos@496
    83
    END LOOP; 
carlos@496
    84
carlos@496
    85
 RETURN v_seqNo;
carlos@496
    86
END ad_script_disable_triggers;
carlos@496
    87
/-- END
carlos@496
    88
carlos@496
    89
CREATE OR REPLACE FUNCTION ad_script_disable_constraints(p_seqNoStart NUMBER)
carlos@496
    90
  RETURN NUMBER
carlos@496
    91
AS
asier@799
    92
/*************************************************************************
asier@799
    93
* The contents of this file are subject to the Openbravo  Public  License
asier@799
    94
* Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
asier@799
    95
* Version 1.1  with a permitted attribution clause; you may not  use this
asier@799
    96
* file except in compliance with the License. You  may  obtain  a copy of
asier@799
    97
* the License at http://www.openbravo.com/legal/license.html
asier@799
    98
* Software distributed under the License  is  distributed  on  an "AS IS"
asier@799
    99
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
asier@799
   100
* License for the specific  language  governing  rights  and  limitations
asier@799
   101
* under the License.
asier@799
   102
* The Original Code is Openbravo ERP.
asier@799
   103
* The Initial Developer of the Original Code is Openbravo SL
asier@799
   104
* All portions are Copyright (C) 2001-2008 Openbravo SL
asier@799
   105
* All Rights Reserved.
asier@799
   106
* Contributor(s):  ______________________________________.
asier@799
   107
************************************************************************/
carlos@496
   108
 v_seqNo      NUMBER := p_seqNoStart;
carlos@496
   109
 TYPE RECORD IS REF CURSOR;
carlos@496
   110
 Cur_Constraints RECORD;
carlos@496
   111
BEGIN
carlos@496
   112
    FOR Cur_Constraints IN 
carlos@496
   113
      (SELECT TABLE_NAME, CONSTRAINT_NAME 
carlos@496
   114
    FROM USER_CONSTRAINTS C1 
asier@1597
   115
    WHERE CONSTRAINT_TYPE IN('P', 'U', 'R') AND COALESCE(DELETE_RULE,'.') NOT LIKE 'CASCADE' 
carlos@496
   116
    ORDER BY(CASE CONSTRAINT_TYPE WHEN 'R' THEN 1 WHEN 'U' THEN 2 WHEN 'P' THEN 3 
carlos@496
   117
      END 
carlos@496
   118
      ), TABLE_NAME, CONSTRAINT_NAME) 
carlos@496
   119
    LOOP 
carlos@496
   120
      v_seqNo:=v_seqNo + 1; 
carlos@496
   121
      INSERT 
carlos@496
   122
      INTO AD_SCRIPT_SQL VALUES 
carlos@496
   123
        (v_seqNo, 'ALTER TABLE '||Cur_Constraints.TABLE_NAME||' DISABLE CONSTRAINT '||Cur_Constraints.CONSTRAINT_NAME) ; 
carlos@496
   124
    END LOOP; 
carlos@496
   125
carlos@496
   126
 RETURN v_seqNo;
carlos@496
   127
END ad_script_disable_constraints;
carlos@496
   128
/-- END
carlos@496
   129
carlos@496
   130
carlos@496
   131
CREATE OR REPLACE FUNCTION ad_script_enable_triggers(p_seqNoStart NUMBER)
carlos@496
   132
  RETURN NUMBER 
carlos@496
   133
AS
asier@799
   134
/*************************************************************************
asier@799
   135
* The contents of this file are subject to the Openbravo  Public  License
asier@799
   136
* Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
asier@799
   137
* Version 1.1  with a permitted attribution clause; you may not  use this
asier@799
   138
* file except in compliance with the License. You  may  obtain  a copy of
asier@799
   139
* the License at http://www.openbravo.com/legal/license.html
asier@799
   140
* Software distributed under the License  is  distributed  on  an "AS IS"
asier@799
   141
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
asier@799
   142
* License for the specific  language  governing  rights  and  limitations
asier@799
   143
* under the License.
asier@799
   144
* The Original Code is Openbravo ERP.
asier@799
   145
* The Initial Developer of the Original Code is Openbravo SL
asier@799
   146
* All portions are Copyright (C) 2001-2008 Openbravo SL
asier@799
   147
* All Rights Reserved.
asier@799
   148
* Contributor(s):  ______________________________________.
asier@799
   149
************************************************************************/
carlos@496
   150
 v_seqNo      NUMBER := p_seqNoStart;
carlos@496
   151
 TYPE RECORD IS REF CURSOR;
carlos@496
   152
 Cur_Triggers RECORD;
carlos@496
   153
BEGIN
carlos@496
   154
    FOR Cur_Triggers IN 
carlos@496
   155
      (SELECT OBJECT_NAME AS NAME 
carlos@496
   156
    FROM USER_OBJECTS 
carlos@496
   157
    WHERE OBJECT_TYPE='TRIGGER' 
carlos@496
   158
    ORDER BY OBJECT_NAME) 
carlos@496
   159
    LOOP 
carlos@496
   160
      v_seqNo:=v_seqNo + 1; 
carlos@496
   161
      INSERT 
carlos@496
   162
      INTO AD_SCRIPT_SQL VALUES 
carlos@496
   163
        (v_seqNo, 'ALTER TRIGGER '||Cur_Triggers.NAME||' ENABLE') ; 
carlos@496
   164
    END LOOP; 
carlos@496
   165
carlos@496
   166
 RETURN v_seqNo;
carlos@496
   167
END ad_script_enable_triggers;
carlos@496
   168
/-- END
carlos@496
   169
carlos@496
   170
CREATE OR REPLACE FUNCTION ad_script_enable_constraints(p_seqNoStart NUMBER)
carlos@496
   171
  RETURN NUMBER
carlos@496
   172
AS
asier@799
   173
/*************************************************************************
asier@799
   174
* The contents of this file are subject to the Openbravo  Public  License
asier@799
   175
* Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
asier@799
   176
* Version 1.1  with a permitted attribution clause; you may not  use this
asier@799
   177
* file except in compliance with the License. You  may  obtain  a copy of
asier@799
   178
* the License at http://www.openbravo.com/legal/license.html
asier@799
   179
* Software distributed under the License  is  distributed  on  an "AS IS"
asier@799
   180
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
asier@799
   181
* License for the specific  language  governing  rights  and  limitations
asier@799
   182
* under the License.
asier@799
   183
* The Original Code is Openbravo ERP.
asier@799
   184
* The Initial Developer of the Original Code is Openbravo SL
asier@799
   185
* All portions are Copyright (C) 2001-2008 Openbravo SL
asier@799
   186
* All Rights Reserved.
asier@799
   187
* Contributor(s):  ______________________________________.
asier@799
   188
************************************************************************/
carlos@496
   189
 v_seqNo      NUMBER := p_seqNoStart;
carlos@496
   190
 TYPE RECORD IS REF CURSOR;
carlos@496
   191
 Cur_Constraints RECORD;
carlos@496
   192
BEGIN
carlos@496
   193
    FOR Cur_ConstraintsEnable IN 
carlos@496
   194
      (SELECT TABLE_NAME, CONSTRAINT_NAME 
carlos@496
   195
    FROM USER_CONSTRAINTS C1 
asier@1597
   196
    WHERE CONSTRAINT_TYPE IN('P', 'U', 'R') AND COALESCE(DELETE_RULE,'.') NOT LIKE 'CASCADE' 
carlos@496
   197
    ORDER BY(CASE CONSTRAINT_TYPE WHEN 'R' THEN 3 WHEN 'U' THEN 2 WHEN 'P' THEN 1 
carlos@496
   198
      END 
carlos@496
   199
      ), TABLE_NAME, CONSTRAINT_NAME) 
carlos@496
   200
    LOOP 
carlos@496
   201
      v_seqNo:=v_seqNo + 1; 
carlos@496
   202
      INSERT 
carlos@496
   203
      INTO AD_SCRIPT_SQL VALUES 
carlos@496
   204
        (v_seqNo, 'ALTER TABLE '||Cur_ConstraintsEnable.TABLE_NAME||' ENABLE CONSTRAINT '||Cur_ConstraintsEnable.CONSTRAINT_NAME) ; 
carlos@496
   205
    END LOOP; 
carlos@496
   206
carlos@496
   207
  RETURN v_seqNo;
carlos@496
   208
END ad_script_enable_constraints;
carlos@496
   209
/-- END
carlos@496
   210
carlos@496
   211
carlos@496
   212
CREATE OR REPLACE FUNCTION ad_script_execute (param_Message VARCHAR2)
carlos@496
   213
  RETURN VARCHAR2
carlos@496
   214
AS
asier@799
   215
/*************************************************************************
asier@799
   216
* The contents of this file are subject to the Openbravo  Public  License
asier@799
   217
* Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
asier@799
   218
* Version 1.1  with a permitted attribution clause; you may not  use this
asier@799
   219
* file except in compliance with the License. You  may  obtain  a copy of
asier@799
   220
* the License at http://www.openbravo.com/legal/license.html
asier@799
   221
* Software distributed under the License  is  distributed  on  an "AS IS"
asier@799
   222
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
asier@799
   223
* License for the specific  language  governing  rights  and  limitations
asier@799
   224
* under the License.
asier@799
   225
* The Original Code is Openbravo ERP.
asier@799
   226
* The Initial Developer of the Original Code is Openbravo SL
asier@799
   227
* All portions are Copyright (C) 2001-2008 Openbravo SL
asier@799
   228
* All Rights Reserved.
asier@799
   229
* Contributor(s):  ______________________________________.
asier@799
   230
************************************************************************/
carlos@496
   231
 v_Message       VARCHAR2(4000) := '';
carlos@496
   232
 v_ResultStr     VARCHAR2(4000) := '';
carlos@496
   233
 TYPE RECORD IS REF CURSOR;
carlos@496
   234
 Cur_Script RECORD;
carlos@496
   235
BEGIN
carlos@496
   236
    v_Message := param_Message;
carlos@496
   237
    FOR Cur_Script IN 
carlos@496
   238
      (SELECT STRSQL, SEQNO FROM AD_SCRIPT_SQL ORDER BY SEQNO)
carlos@496
   239
    LOOP 
carlos@496
   240
    BEGIN 
carlos@496
   241
      EXECUTE IMMEDIATE(Cur_Script.STRSQL) ; 
carlos@496
   242
    EXCEPTION 
carlos@496
   243
    WHEN OTHERS THEN 
carlos@496
   244
      
carlos@496
   245
      if (coalesce(length(v_Message),0)!=0) then
carlos@496
   246
        v_Message:=substr(v_Message||'<br><br>',1,2000);
carlos@496
   247
      end if;
carlos@496
   248
      v_Message := substr(v_Message||'@SQLScriptError@ '||Cur_Script.SeqNo||'. @Executing@'||Cur_Script.strSQL||'<br>'||SQLERRM,1,2000);
carlos@496
   249
    END;
carlos@496
   250
  END LOOP;
carlos@496
   251
carlos@496
   252
 IF( LENGTH(v_Message) > 0 ) THEN
carlos@496
   253
    DBMS_OUTPUT.PUT_LINE('Script errors: ' || v_Message);
carlos@496
   254
 END IF;
carlos@496
   255
 return substr(coalesce(v_ResultStr,'') || coalesce(v_Message,''), 1, 2000);
carlos@496
   256
END ad_script_execute;
carlos@496
   257
/-- END
carlos@496
   258
carlos@496
   259
CREATE OR REPLACE FUNCTION ad_script_drop_recreate_index (p_seqNoStart NUMBER)
carlos@496
   260
  RETURN NUMBER
carlos@496
   261
AS
asier@799
   262
/*************************************************************************
asier@799
   263
* The contents of this file are subject to the Openbravo  Public  License
asier@799
   264
* Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
asier@799
   265
* Version 1.1  with a permitted attribution clause; you may not  use this
asier@799
   266
* file except in compliance with the License. You  may  obtain  a copy of
asier@799
   267
* the License at http://www.openbravo.com/legal/license.html
asier@799
   268
* Software distributed under the License  is  distributed  on  an "AS IS"
asier@799
   269
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
asier@799
   270
* License for the specific  language  governing  rights  and  limitations
asier@799
   271
* under the License.
asier@799
   272
* The Original Code is Openbravo ERP.
asier@799
   273
* The Initial Developer of the Original Code is Openbravo SL
asier@799
   274
* All portions are Copyright (C) 2001-2008 Openbravo SL
asier@799
   275
* All Rights Reserved.
asier@799
   276
* Contributor(s):  ______________________________________.
asier@799
   277
************************************************************************/
carlos@496
   278
 v_seqNo         NUMBER; 
carlos@496
   279
 v_strSql        VARCHAR2(4000) := '';
carlos@496
   280
 v_strTemp       VARCHAR2(4000) := '';
carlos@496
   281
 v_Message       VARCHAR2(4000) := '';
carlos@496
   282
 v_ResultStr     VARCHAR2(4000) := '';
carlos@496
   283
 TYPE RECORD IS REF CURSOR;
carlos@496
   284
 Cur_UniqueIndex  RECORD;
carlos@496
   285
 Cur_IndexColumns RECORD;
carlos@496
   286
BEGIN
carlos@496
   287
    v_seqNo := p_seqNoStart;
carlos@496
   288
    FOR Cur_UniqueIndex IN (SELECT i.INDEX_NAME, i.TABLE_NAME, i.TABLESPACE_NAME, CONSTRAINT_TYPE
carlos@496
   289
                 FROM USER_INDEXES I left join USER_CONSTRAINTS C1 on c1.INDEX_NAME=I.INDEX_NAME
carlos@496
   290
                 WHERE UNIQUENESS='UNIQUE' AND INDEX_TYPE='NORMAL' AND TABLE_TYPE='TABLE'
carlos@496
   291
               --AND CONSTRAINT_TYPE != 'U'
carlos@496
   292
               ORDER BY INDEX_NAME)
carlos@496
   293
carlos@496
   294
    LOOP
carlos@496
   295
      v_seqNo:=v_seqNo + 1;
carlos@496
   296
      INSERT
carlos@496
   297
      INTO AD_SCRIPT_SQL VALUES (v_seqNo, 'DROP INDEX '||Cur_UniqueIndex.INDEX_NAME) ;
carlos@496
   298
carlos@496
   299
   IF Cur_UniqueIndex.CONSTRAINT_TYPE != 'P' THEN
carlos@496
   300
    v_strSql:='CREATE INDEX '||Cur_UniqueIndex.INDEX_NAME||' ON '||Cur_UniqueIndex.TABLE_NAME||'(';
carlos@496
   301
       v_strTemp:='';
carlos@496
   302
       FOR Cur_IndexColumns IN
carlos@496
   303
         (SELECT COLUMN_NAME
carlos@496
   304
       FROM USER_IND_COLUMNS
carlos@496
   305
       WHERE INDEX_NAME=Cur_UniqueIndex.INDEX_NAME
carlos@496
   306
       ORDER BY COLUMN_POSITION)
carlos@496
   307
       LOOP
carlos@496
   308
         v_strTemp:=v_strTemp ||','|| Cur_IndexColumns.COLUMN_NAME;
carlos@496
   309
       END LOOP;
carlos@496
   310
       v_strSql:=v_strSql || SUBSTR(v_strTemp, 2, 2000) || ') TABLESPACE '||Cur_UniqueIndex.TABLESPACE_NAME;
carlos@496
   311
       INSERT INTO AD_SCRIPT_SQL VALUES(v_seqNo+100000, v_strSql) ;
carlos@496
   312
   END IF;
carlos@496
   313
 END LOOP;
carlos@496
   314
 return v_seqNo;
carlos@496
   315
END ad_script_drop_recreate_index;
carlos@496
   316
/-- END
carlos@496
   317
juanpablo@1605
   318
CREATE OR REPLACE PROCEDURE DBA_RECOMPILE(p_PInstance_ID IN VARCHAR2) 
antonio@506
   319
AS
antonio@506
   320
/*************************************************************************
juanpablo@771
   321
  * The contents of this file are subject to the Compiere Public
juanpablo@771
   322
  * License 1.1 ("License"); You may not use this file except in
juanpablo@771
   323
  * compliance with the License. You may obtain a copy of the License in
juanpablo@771
   324
  * the legal folder of your Openbravo installation.
juanpablo@771
   325
antonio@506
   326
  * Software distributed under the License is distributed on an
antonio@506
   327
  * "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
antonio@506
   328
  * implied. See the License for the specific language governing rights
juanpablo@771
   329
antonio@506
   330
  * and limitations under the License.
antonio@506
   331
  * The Original Code is  Compiere  ERP &  Business Solution
juanpablo@771
   332
  * The Initial Developer of the Original Code is Jorg Janke and ComPiere, Inc.
juanpablo@771
   333
antonio@506
   334
  * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke,
antonio@506
   335
  * parts created by ComPiere are Copyright (C) ComPiere, Inc.;
antonio@506
   336
  * All Rights Reserved.
antonio@506
   337
  * Contributor(s): Openbravo SL
juanpablo@771
   338
antonio@506
   339
  * Contributions are Copyright (C) 1999-2005 Openbravo, S.L
juanpablo@771
   340
  *
juanpablo@771
   341
  * Specifically, this derivative work is based upon the following Compiere
juanpablo@771
   342
  * file and version.
antonio@506
   343
  *************************************************************************
antonio@506
   344
  * $Id: DBA_Recompile.sql,v 1.7 2003/03/14 06:11:21 jjanke Exp $
antonio@506
   345
  ***
antonio@506
   346
  * Title:  Recompile all User_Objects
antonio@506
   347
  * Description:
antonio@506
   348
  ************************************************************************/
antonio@506
   349
  -- Logistice
antonio@506
   350
  v_Message VARCHAR2(2000):=' ';
antonio@506
   351
  v_Result NUMBER:=1; --  0=failure
antonio@506
   352
  --
antonio@506
   353
  v_Buffer VARCHAR2(2000):='';
antonio@506
   354
  v_Line VARCHAR(100) ;
antonio@506
   355
  v_PrintInfo CHAR(1):='N'; -- Diagnostic
antonio@506
   356
  --
antonio@506
   357
  CURSOR Cur_Invalids IS
antonio@506
   358
    SELECT object_id,
antonio@506
   359
      object_name,
antonio@506
   360
      object_type
antonio@506
   361
    FROM user_objects
antonio@506
   362
    WHERE status<>'VALID'
antonio@506
   363
      AND object_type IN('VIEW', 'PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'JAVA CLASS')
antonio@506
   364
    ORDER BY object_type,
antonio@506
   365
      object_name;
antonio@506
   366
    CURSOR Cur_Valids (p_id NUMBER) IS
antonio@506
   367
      SELECT 'FOUND'  FROM user_objects  WHERE status='VALID'  AND object_id=p_id;
antonio@506
   368
      --  failed compile
antonio@506
   369
    TYPE invalid_tab IS TABLE OF Cur_Invalids%ROWTYPE INDEX BY BINARY_INTEGER;
antonio@506
   370
    invalid_tab_rec invalid_tab;
antonio@506
   371
    count_compiled PLS_INTEGER;
antonio@506
   372
    valid_text VARCHAR2(5) ;
antonio@506
   373
    exec_cursor PLS_INTEGER:=DBMS_SQL.OPEN_CURSOR;
antonio@506
   374
    sql_statement VARCHAR2(200) ;
antonio@506
   375
    count_object PLS_INTEGER:=0;
antonio@506
   376
  BEGIN
antonio@506
   377
    LOOP
antonio@506
   378
      count_compiled:=0;
antonio@506
   379
      FOR ci IN Cur_Invalids
antonio@506
   380
      LOOP
antonio@506
   381
        --  not unsuccessfuly compiled yet
antonio@506
   382
        IF NOT invalid_tab_rec.EXISTS(ci.object_id) THEN
antonio@506
   383
          IF(ci.object_type='JAVA CLASS') THEN
antonio@506
   384
            sql_statement:='ALTER JAVA CLASS "' || ci.object_name || '" RESOLVE';
antonio@506
   385
          ELSIF(ci.object_type='PACKAGE BODY') THEN
antonio@506
   386
            sql_statement:='ALTER PACKAGE ' || ci.object_name || ' COMPILE BODY';
antonio@506
   387
          ELSE
antonio@506
   388
            sql_statement:='ALTER ' || ci.object_type || ' ' || ci.object_name || ' COMPILE';
antonio@506
   389
          END IF;
antonio@506
   390
          --  compile
antonio@506
   391
        BEGIN
antonio@506
   392
          count_object:=count_object + 1;
antonio@506
   393
          DBMS_SQL.PARSE(exec_cursor, sql_statement, DBMS_SQL.NATIVE) ;
antonio@506
   394
        EXCEPTION
antonio@506
   395
        WHEN OTHERS THEN
antonio@506
   396
          NULL;
antonio@506
   397
        END;
antonio@506
   398
        --
antonio@506
   399
        OPEN Cur_Valids(ci.object_ID) ;
antonio@506
   400
        FETCH Cur_Valids INTO valid_text;
antonio@506
   401
        IF Cur_Valids%ROWCOUNT>0 THEN
antonio@506
   402
          IF(v_PrintInfo='Y') THEN
antonio@506
   403
            DBMS_OUTPUT.PUT_LINE('OK: ' || ci.object_type || ' ' || ci.object_name) ;
antonio@506
   404
          END IF;
antonio@506
   405
          count_compiled:=count_compiled + 1;
antonio@506
   406
          CLOSE Cur_Valids;
antonio@506
   407
          EXIT;
antonio@506
   408
        ELSE
antonio@506
   409
          IF(LENGTH(v_Message)<1950) THEN
antonio@506
   410
            v_Message:=v_Message || ci.object_name || ' ';
antonio@506
   411
          END IF;
antonio@506
   412
          IF(v_PrintInfo='Y') THEN
antonio@506
   413
            DBMS_OUTPUT.PUT_LINE('Error: ' || ci.object_type || ' ' || ci.object_name) ;
antonio@506
   414
          END IF;
antonio@506
   415
          --
antonio@506
   416
          invalid_tab_rec(ci.object_id) .object_name:=ci.object_name;
antonio@506
   417
          invalid_tab_rec(ci.object_id) .object_type:=ci.object_type;
antonio@506
   418
          CLOSE Cur_Valids;
antonio@506
   419
        END IF;
antonio@506
   420
      END IF; -- not unsuccessfuly compiled yet
antonio@506
   421
    END LOOP; -- Cur_Invalids
antonio@506
   422
    --  any other to be compiled
antonio@506
   423
    IF count_compiled=0 THEN
antonio@506
   424
      EXIT;
antonio@506
   425
    END IF;
antonio@506
   426
  END LOOP; -- outer loop
antonio@506
   427
  DBMS_SQL.CLOSE_CURSOR(exec_cursor) ;
antonio@506
   428
  --
antonio@506
   429
  -- Print Message
antonio@506
   430
  IF(LENGTH(v_Message)=1) THEN
antonio@506
   431
    v_Message:='All valid';
antonio@506
   432
    DBMS_OUTPUT.PUT_LINE(v_Message) ;
antonio@506
   433
  ELSIF(LENGTH(v_Message)>80) THEN
antonio@506
   434
    v_Buffer:=v_Message;
antonio@506
   435
    DBMS_OUTPUT.PUT_LINE('>') ;
antonio@506
   436
    WHILE(LENGTH(v_Buffer)>0)
antonio@506
   437
    LOOP
antonio@506
   438
      v_Line:=SUBSTR(v_Buffer, 1, 80) ;
antonio@506
   439
      DBMS_OUTPUT.PUT_LINE(v_Line) ;
antonio@506
   440
      v_Buffer:=SUBSTR(v_Buffer, 81) ;
antonio@506
   441
    END LOOP;
antonio@506
   442
    DBMS_OUTPUT.PUT_LINE('<') ;
antonio@506
   443
    v_Result:=0;
antonio@506
   444
    DBMS_OUTPUT.PUT_LINE('ERROR') ;
antonio@506
   445
  ELSE
antonio@506
   446
    DBMS_OUTPUT.PUT_LINE('>' || v_Message || '<') ;
antonio@506
   447
    v_Result:=0;
antonio@506
   448
    DBMS_OUTPUT.PUT_LINE('ERROR') ;
antonio@506
   449
  END IF;
antonio@506
   450
  --<<FINISH_PROCESS>>
antonio@506
   451
  IF(p_PInstance_ID IS NOT NULL) THEN
antonio@506
   452
    --  Update AD_PInstance
antonio@506
   453
    AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', v_Result, v_Message) ;
antonio@506
   454
  END IF;
antonio@506
   455
  RETURN;
antonio@506
   456
EXCEPTION
antonio@506
   457
WHEN OTHERS THEN
antonio@506
   458
  DBMS_OUTPUT.PUT_LINE(SQLERRM) ;
antonio@506
   459
  IF DBMS_SQL.IS_OPEN(exec_cursor) THEN
antonio@506
   460
    DBMS_SQL.CLOSE_CURSOR(exec_cursor) ;
antonio@506
   461
  END IF;
antonio@506
   462
  IF Cur_Valids%ISOPEN THEN
antonio@506
   463
    CLOSE Cur_Valids;
antonio@506
   464
  END IF;
antonio@506
   465
END DBA_Recompile;
antonio@506
   466
/-- END
antonio@506
   467
antonio@506
   468
CREATE OR REPLACE PROCEDURE DBA_AFTERIMPORT
antonio@506
   469
AS
antonio@506
   470
/*************************************************************************
juanpablo@771
   471
  * The contents of this file are subject to the Compiere Public
juanpablo@771
   472
  * License 1.1 ("License"); You may not use this file except in
juanpablo@771
   473
  * compliance with the License. You may obtain a copy of the License in
juanpablo@771
   474
  * the legal folder of your Openbravo installation.
antonio@506
   475
  * Software distributed under the License is distributed on an
antonio@506
   476
  * "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
antonio@506
   477
  * implied. See the License for the specific language governing rights
antonio@506
   478
  * and limitations under the License.
antonio@506
   479
  * The Original Code is  Compiere  ERP &  Business Solution
juanpablo@771
   480
  * The Initial Developer of the Original Code is Jorg Janke and ComPiere, Inc.
antonio@506
   481
  * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke,
antonio@506
   482
  * parts created by ComPiere are Copyright (C) ComPiere, Inc.;
antonio@506
   483
  * All Rights Reserved.
antonio@506
   484
  * Contributor(s): Openbravo SL
juanpablo@785
   485
  * Contributions are Copyright (C) 2001-2008 Openbravo, S.L.
juanpablo@771
   486
  *
juanpablo@771
   487
  * Specifically, this derivative work is based upon the following Compiere
juanpablo@771
   488
  * file and version.
antonio@506
   489
  *************************************************************************
antonio@506
   490
  * $Id: DBA_AfterImport.sql,v 1.5 2002/10/21 04:49:46 jjanke Exp $
antonio@506
   491
  * $Source: /cvsroot/compiere/db/database/Procedures/DBA_AfterImport.sql,v $
antonio@506
   492
  ***
antonio@506
   493
  * Title:  Run after Import
antonio@506
   494
  * Description:
antonio@506
   495
  * - Recompile
antonio@506
   496
  * - Compute Statistics
antonio@506
   497
  *****************************************************************************/
antonio@506
   498
  -- Statistics
antonio@506
   499
  TYPE RECORD IS REF CURSOR;
antonio@506
   500
    Cur_Stat RECORD;
antonio@506
   501
    --
antonio@506
   502
    v_Cmd VARCHAR2(256):='';
antonio@506
   503
    v_NoC NUMBER:=0;
antonio@506
   504
    --
antonio@506
   505
  BEGIN
antonio@506
   506
    -- Recompile
antonio@506
   507
    DBA_Recompile(NULL) ;
antonio@506
   508
    -- Statistics
antonio@506
   509
    FOR Cur_Stat IN
antonio@506
   510
      (SELECT Table_Name,
antonio@506
   511
        Blocks
antonio@506
   512
      FROM USER_TABLES
antonio@506
   513
      WHERE DURATION IS NULL -- No temporary tables
antonio@506
   514
        AND(LAST_ANALYZED IS NULL
antonio@506
   515
        OR LAST_ANALYZED<SysDate-7)
antonio@506
   516
      )
antonio@506
   517
    LOOP
antonio@506
   518
      v_Cmd:='ANALYZE TABLE ' || Cur_Stat.Table_Name || ' COMPUTE STATISTICS';
antonio@506
   519
      v_NoC:=v_NoC + 1;
antonio@506
   520
      EXECUTE IMMEDIATE v_Cmd;
antonio@506
   521
    END LOOP;
antonio@506
   522
    DBMS_OUTPUT.PUT_LINE('Statistics computed: ' || v_NoC) ;
antonio@506
   523
    --
antonio@506
   524
END DBA_AfterImport;
antonio@506
   525
/-- END
carlos@496
   526
asier@1929
   527
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) 
asier@1929
   528
 RETURN NUMBER
asier@1929
   529
 AS
asier@1929
   530
/*************************************************************************
asier@1929
   531
* The contents of this file are subject to the Openbravo  Public  License
asier@1929
   532
* Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
asier@1929
   533
* Version 1.1  with a permitted attribution clause; you may not  use this
asier@1929
   534
* file except in compliance with the License. You  may  obtain  a copy of
asier@1929
   535
* the License at http://www.openbravo.com/legal/license.html
asier@1929
   536
* Software distributed under the License  is  distributed  on  an "AS IS"
asier@1929
   537
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
asier@1929
   538
* License for the specific  language  governing  rights  and  limitations
asier@1929
   539
* under the License.
asier@1929
   540
* The Original Code is Openbravo ERP.
asier@1929
   541
* The Initial Developer of the Original Code is Openbravo SL
asier@1929
   542
* All portions are Copyright (C) 2008 Openbravo SL
asier@1929
   543
* All Rights Reserved.
asier@1929
   544
* Contributor(s):  ______________________________________.
asier@1929
   545
************************************************************************/
asier@1929
   546
   v_org_header_id ad_org.ad_org_id%TYPE;
asier@1929
   547
   v_isbusinessunit ad_orgtype.isbusinessunit%TYPE;
asier@1929
   548
   v_islegalentity ad_orgtype.islegalentity%TYPE;
asier@1929
   549
   v_is_included NUMBER:=0;
asier@1929
   550
  
asier@1929
   551
   TYPE RECORD IS REF CURSOR;
asier@1929
   552
   cur_doc_lines RECORD;
asier@1929
   553
 
asier@1929
   554
   v_line_org VARCHAR2(32);
asier@1929
   555
   v_org_line_id VARCHAR2(32);
asier@1929
   556
 BEGIN
asier@1929
   557
 
asier@1929
   558
   -- Gets the organization and the organization type of the document's header
asier@1929
   559
   EXECUTE IMMEDIATE 
asier@1929
   560
     'SELECT ad_org.ad_org_id, ad_orgtype.isbusinessunit, ad_orgtype.islegalentity 
asier@1929
   561
     FROM '||p_header_table||', ad_org, ad_orgtype
asier@1929
   562
     WHERE '||p_header_table||'.'||p_header_column_id||' = '''||p_document_id||'''
asier@1929
   563
     AND ad_org.ad_orgtype_id = ad_orgtype.ad_orgtype_id
asier@1929
   564
     AND '||p_header_table||'.ad_org_id=ad_org.ad_org_id ' 
asier@1929
   565
     INTO v_org_header_id, v_isbusinessunit, v_islegalentity; 
asier@1929
   566
 
asier@1929
   567
   -- Gets recursively the organization parent until finding a Business Unit or a Legal Entity
asier@1929
   568
   WHILE (v_isbusinessunit='N' AND v_islegalentity='N') LOOP
asier@1929
   569
     SELECT hh.parent_id, ad_orgtype.isbusinessunit, ad_orgtype.islegalentity
asier@1929
   570
     INTO v_org_header_id, v_isbusinessunit, v_islegalentity
asier@1929
   571
     FROM ad_org, ad_orgtype, ad_treenode pp, ad_treenode hh
asier@1929
   572
     WHERE pp.node_id = hh.parent_id
asier@1929
   573
     AND hh.ad_tree_id = pp.ad_tree_id
asier@1929
   574
     AND pp.node_id=ad_org.ad_org_id
asier@1929
   575
     AND hh.node_id=v_org_header_id
asier@1929
   576
     AND ad_org.ad_orgtype_id=ad_orgtype.ad_orgtype_id
asier@1929
   577
     AND ad_org.isready='Y'
asier@1929
   578
     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
   579
   END LOOP;
asier@1929
   580
 
asier@1929
   581
   -- Check the lines belong to the same BU or LE as the header
asier@1929
   582
   OPEN cur_doc_lines FOR
asier@1929
   583
   'SELECT DISTINCT('||p_lines_table||'.ad_org_id) AS v_line_org
asier@1929
   584
    FROM '||p_header_table||', '||p_lines_table||'
asier@1929
   585
    WHERE '||p_header_table||'.'||p_header_column_id||' = '||p_lines_table||'.'||p_lines_column_id||'
asier@1929
   586
    AND '||p_lines_table||'.'||p_lines_column_id||'='''||p_document_id||'''';    
asier@1929
   587
    LOOP
asier@1929
   588
      FETCH cur_doc_lines INTO v_line_org;
asier@1929
   589
      EXIT WHEN cur_doc_lines%NOTFOUND;
asier@1929
   590
 
asier@1929
   591
      SELECT ad_orgtype.isbusinessunit, ad_orgtype.islegalentity
asier@1929
   592
      INTO v_isbusinessunit, v_islegalentity
asier@1929
   593
      FROM AD_Org, AD_OrgType
asier@1929
   594
      WHERE AD_Org.AD_OrgType_ID=AD_OrgType.AD_OrgType_ID
asier@1929
   595
      AND AD_Org.AD_Org_ID=v_line_org;
asier@1929
   596
      
asier@1929
   597
      v_org_line_id:=v_line_org;
asier@1929
   598
      -- Gets recursively the organization parent until finding a Business Unit or a Legal Entity
asier@1929
   599
      WHILE (v_isbusinessunit='N' AND v_islegalentity='N') LOOP
asier@1929
   600
        SELECT hh.parent_id, ad_orgtype.isbusinessunit, ad_orgtype.islegalentity
asier@1929
   601
        INTO v_org_line_id, v_isbusinessunit, v_islegalentity
asier@1929
   602
        FROM ad_org, ad_orgtype, ad_treenode pp, ad_treenode hh
asier@1929
   603
        WHERE pp.node_id = hh.parent_id
asier@1929
   604
        AND hh.ad_tree_id = pp.ad_tree_id
asier@1929
   605
        AND pp.node_id=ad_org.ad_org_id
asier@1929
   606
        AND hh.node_id=v_line_org
asier@1929
   607
        AND ad_org.ad_orgtype_id=ad_orgtype.ad_orgtype_id
asier@1929
   608
        AND ad_org.isready='Y'
asier@1929
   609
        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
   610
      END LOOP;
asier@1929
   611
      
asier@1929
   612
     IF (v_org_line_id<>v_org_header_id) THEN
asier@1929
   613
       v_is_included:=-1;
asier@1929
   614
     END IF;
asier@1929
   615
     EXIT WHEN v_is_included=-1;
asier@1929
   616
 
asier@1929
   617
    END LOOP; 
asier@1929
   618
   CLOSE cur_doc_lines;
asier@1929
   619
 
asier@1929
   620
  RETURN v_is_included;
asier@1929
   621
 
asier@1929
   622
END AD_ORG_CHK_DOCUMENTS;
asier@1929
   623
/-- END
asier@1929
   624
asier@1929
   625
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
   626
 RETURN NUMBER
asier@1929
   627
 AS
asier@1929
   628
/*************************************************************************
asier@1929
   629
* The contents of this file are subject to the Openbravo  Public  License
asier@1929
   630
* Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
asier@1929
   631
* Version 1.1  with a permitted attribution clause; you may not  use this
asier@1929
   632
* file except in compliance with the License. You  may  obtain  a copy of
asier@1929
   633
* the License at http://www.openbravo.com/legal/license.html
asier@1929
   634
* Software distributed under the License  is  distributed  on  an "AS IS"
asier@1929
   635
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
asier@1929
   636
* License for the specific  language  governing  rights  and  limitations
asier@1929
   637
* under the License.
asier@1929
   638
* The Original Code is Openbravo ERP.
asier@1929
   639
* The Initial Developer of the Original Code is Openbravo SL
asier@1929
   640
* All portions are Copyright (C) 2008 Openbravo SL
asier@1929
   641
* All Rights Reserved.
asier@1929
   642
* Contributor(s):  ______________________________________.
asier@1929
   643
************************************************************************/
asier@1929
   644
   v_org_header_id ad_org.ad_org_id%TYPE;
asier@1929
   645
   v_isbusinessunit ad_orgtype.isbusinessunit%TYPE;
asier@1929
   646
   v_islegalentity ad_orgtype.islegalentity%TYPE;
asier@1929
   647
   v_is_included NUMBER:=0;
asier@1929
   648
 
asier@1929
   649
 
asier@1929
   650
   TYPE RECORD IS REF CURSOR;
asier@1929
   651
   cur_doc_lines_payment RECORD;
asier@1929
   652
 
asier@1929
   653
   v_line_org_payment VARCHAR2(32);
asier@1929
   654
   v_org_payment_line_id VARCHAR2(32);
asier@1929
   655
 BEGIN
asier@1929
   656
 
asier@1929
   657
   -- Gets the organization and the organization type of the document's header
asier@1929
   658
   EXECUTE IMMEDIATE 
asier@1929
   659
     'SELECT ad_org.ad_org_id, ad_orgtype.isbusinessunit, ad_orgtype.islegalentity 
asier@1929
   660
     FROM '||p_header_table||', ad_org, ad_orgtype
asier@1929
   661
     WHERE '||p_header_table||'.'||p_header_column_id||' = '''||p_document_id||'''
asier@1929
   662
     AND ad_org.ad_orgtype_id = ad_orgtype.ad_orgtype_id
asier@1929
   663
     AND '||p_header_table||'.ad_org_id=ad_org.ad_org_id ' 
asier@1929
   664
     INTO v_org_header_id, v_isbusinessunit, v_islegalentity;
asier@1929
   665
asier@1929
   666
   -- Gets recursively the organization parent until finding a Business Unit or a Legal Entity
asier@1929
   667
   WHILE (v_isbusinessunit='N' AND v_islegalentity='N') LOOP
asier@1929
   668
     SELECT hh.parent_id, ad_orgtype.isbusinessunit, ad_orgtype.islegalentity
asier@1929
   669
     INTO v_org_header_id, v_isbusinessunit, v_islegalentity
asier@1929
   670
     FROM ad_org, ad_orgtype, ad_treenode pp, ad_treenode hh
asier@1929
   671
     WHERE pp.node_id = hh.parent_id
asier@1929
   672
     AND hh.ad_tree_id = pp.ad_tree_id
asier@1929
   673
     AND pp.node_id=ad_org.ad_org_id
asier@1929
   674
     AND hh.node_id=v_org_header_id
asier@1929
   675
     AND ad_org.ad_orgtype_id=ad_orgtype.ad_orgtype_id
asier@1929
   676
     AND ad_org.isready='Y'
asier@1929
   677
     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
   678
   END LOOP;
asier@1929
   679
 
asier@1929
   680
 
asier@1929
   681
   -- Check the payments of the lines belong to the same BU or LE as the document header
asier@1929
   682
   OPEN cur_doc_lines_payment FOR
asier@1929
   683
   'SELECT DISTINCT(C_DEBT_PAYMENT.ad_org_id) AS v_line_org_payment
asier@1929
   684
    FROM '||p_header_table||', '||p_lines_table||', C_DEBT_PAYMENT
asier@1929
   685
    WHERE '||p_header_table||'.'||p_header_column_id||' = '||p_lines_table||'.'||p_lines_column_id||'
asier@1929
   686
    AND C_DEBT_PAYMENT.C_DEBT_PAYMENT_ID='||p_lines_table||'.'||p_lines_column_payment_id||'
asier@1929
   687
    AND '||p_lines_table||'.'||p_lines_column_id||'='''||p_document_id||'''';
asier@1929
   688
 
asier@1929
   689
 
asier@1929
   690
   LOOP
asier@1929
   691
    FETCH cur_doc_lines_payment INTO v_line_org_payment;
asier@1929
   692
    EXIT WHEN cur_doc_lines_payment%NOTFOUND;
asier@1929
   693
 
asier@1929
   694
asier@1929
   695
    SELECT ad_orgtype.isbusinessunit, ad_orgtype.islegalentity
asier@1929
   696
    INTO v_isbusinessunit, v_islegalentity
asier@1929
   697
    FROM AD_Org, AD_OrgType
asier@1929
   698
    WHERE AD_Org.AD_OrgType_ID=AD_OrgType.AD_OrgType_ID
asier@1929
   699
    AND AD_Org.AD_Org_ID=v_line_org_payment;
asier@1929
   700
asier@1929
   701
    v_org_payment_line_id:=v_line_org_payment;
asier@1929
   702
    -- Gets recursively the organization parent until finding a Business Unit or a Legal Entity
asier@1929
   703
    WHILE (v_isbusinessunit='N' AND v_islegalentity='N') LOOP
asier@1929
   704
      SELECT hh.parent_id, ad_orgtype.isbusinessunit, ad_orgtype.islegalentity
asier@1929
   705
      INTO v_org_payment_line_id, v_isbusinessunit, v_islegalentity
asier@1929
   706
      FROM ad_org, ad_orgtype, ad_treenode pp, ad_treenode hh
asier@1929
   707
      WHERE pp.node_id = hh.parent_id
asier@1929
   708
      AND hh.ad_tree_id = pp.ad_tree_id
asier@1929
   709
      AND pp.node_id=ad_org.ad_org_id
asier@1929
   710
      AND hh.node_id=v_line_org_payment
asier@1929
   711
      AND ad_org.ad_orgtype_id=ad_orgtype.ad_orgtype_id
asier@1929
   712
      AND ad_org.isready='Y'
asier@1929
   713
      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
   714
    END LOOP;
asier@1929
   715
asier@1929
   716
    IF (v_org_payment_line_id<>v_org_header_id) THEN
asier@1929
   717
      v_is_included:=-1;
asier@1929
   718
    END IF;
asier@1929
   719
    EXIT WHEN v_is_included=-1;
asier@1929
   720
asier@1929
   721
   END LOOP; 
asier@1929
   722
   CLOSE cur_doc_lines_payment;
asier@1929
   723
 
asier@1929
   724
   RETURN v_is_included;
asier@1929
   725
 
asier@1929
   726
END AD_ORG_CHK_DOC_PAYMENTS;
asier@1929
   727
/-- END
asier@1929
   728
carlos@0
   729
CALL DBA_RECOMPILE(NULL)
carlos@0
   730
/-- END
carlos@0
   731