src-wad/src/org/openbravo/wad/Fields_data.xsql
author Juan Pablo Aroztegi <juanpablo.aroztegi@openbravo.com>
Wed, 03 Sep 2008 17:55:37 +0000
changeset 1605 8a0fe0193bef
parent 1239 e5124b398f64
child 1879 b3ed43f23524
permissions -rw-r--r--
Merge r2.5x intro trunk
carlos@0
     1
<?xml version="1.0" encoding="UTF-8" ?>
carlos@0
     2
<!--
carlos@0
     3
 *************************************************************************
carlos@0
     4
 * The contents of this file are subject to the Openbravo  Public  License
carlos@0
     5
 * Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
carlos@0
     6
 * Version 1.1  with a permitted attribution clause; you may not  use this
carlos@0
     7
 * file except in compliance with the License. You  may  obtain  a copy of
carlos@0
     8
 * the License at http://www.openbravo.com/legal/license.html 
carlos@0
     9
 * Software distributed under the License  is  distributed  on  an "AS IS"
carlos@0
    10
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
carlos@0
    11
 * License for the specific  language  governing  rights  and  limitations
carlos@0
    12
 * under the License. 
carlos@0
    13
 * The Original Code is Openbravo ERP. 
carlos@0
    14
 * The Initial Developer of the Original Code is Openbravo SL 
carlos@0
    15
 * All portions are Copyright (C) 2001-2006 Openbravo SL 
carlos@0
    16
 * All Rights Reserved. 
carlos@0
    17
 * Contributor(s):  ______________________________________.
carlos@0
    18
 ************************************************************************
carlos@0
    19
-->
carlos@0
    20
carlos@0
    21
carlos@0
    22
carlos@0
    23
<SqlClass name="FieldsData" package="org.openbravo.wad">
carlos@0
    24
  <SqlMethod name="select" type="preparedStatement" return="multiple">
carlos@0
    25
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
    26
    <Sql>
carlos@0
    27
      <![CDATA[
carlos@0
    28
      SELECT ad_column.ColumnName as realName, ad_column.ColumnName As Name, '' as NameRef, 
carlos@0
    29
        'x' as xmltext, ad_reference_id as reference, ad_reference_value_id as referencevalue,
carlos@0
    30
        ismandatory as required, (CASE ad_field.isActive WHEN 'N' THEN 'N' ELSE isDisplayed END) as isdisplayed, isupdateable as isupdateable,
carlos@0
    31
        REPLACE(REPLACE(ad_column.defaultvalue, CHR(13), '\\n'), CHR(10), '') As defaultValue, 
carlos@0
    32
        ad_column.fieldlength As fieldLength, 
carlos@0
    33
        'Medio' as Text_Align, '' AS Xml_Format, 
carlos@0
    34
        ad_field.displaylength, REPLACE(replace(REPLACE(REPLACE(AD_ELEMENT.columnname, 'Substitute_ID', 'M_Product_ID'), 'C_Settlement_Cancel_ID', 'C_Settlement_ID'), 'BOM_ID', '_ID'), 'M_LocatorTo_ID', 'M_Locator_ID') as columnname, 
carlos@0
    35
        '' as WHERECLAUSE, ad_table.tablename, 'StringParameter' as Type, ad_column.ISSESSIONATTR, ad_column.iskey, 
carlos@0
    36
        isParent, '' as ACCESSLEVEL, ad_field.isreadonly, '' as issecondarykey, ad_field.showInRelation, ad_field.isEncrypted,
carlos@0
    37
        ad_field.SORTNO, ad_column.istranslated, '' as id, '' as htmltext, '' as htmltexttrl, '' as xmltexttrl, '' as tablenametrl, 
juanpablo@687
    38
        0 AS NOWRAP, ad_column.isEncrypted AS isColumnEncrypted, ad_column.isDesencryptable, ad_reference_value_id, ad_column.ad_val_rule_id, '' AS isjasper, ad_field.isactive, '' as AD_Tab_ID, '' as parent_tab_name, '' as orgcode
carlos@0
    39
      FROM ad_column left join ad_element on ad_column.ad_element_id = ad_element.ad_element_id,
carlos@0
    40
           ad_field,  ad_table
carlos@0
    41
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
carlos@0
    42
        AND ad_column.ad_table_id = ad_table.ad_table_id 
carlos@0
    43
        AND ad_column.isActive = 'Y' 
carlos@0
    44
        AND upper(ad_column.columnname) <> 'BINARYDATA' 
juanpablo@1605
    45
        AND ad_tab_id = ?
carlos@0
    46
      ORDER BY ad_field.SEQNO
carlos@0
    47
      ]]>
carlos@0
    48
    </Sql>
carlos@0
    49
    <Parameter name="tab"/>
carlos@0
    50
  </SqlMethod>
carlos@0
    51
  <SqlMethod name="set" type="constant" return="multiple">
carlos@0
    52
      <SqlMethodComment>Create a registry</SqlMethodComment>
carlos@0
    53
      <Sql></Sql>
carlos@0
    54
  </SqlMethod>
carlos@0
    55
  <SqlMethod name="selectDescription" type="preparedStatement" return="multiple">
carlos@0
    56
    <SqlMethodComment>Description of the fields of a tab</SqlMethodComment>
carlos@0
    57
    <Sql>
carlos@0
    58
      SELECT f.Name As Name, f.isDisplayed as isdisplayed, f.isEncrypted, c.AD_REFERENCE_ID as reference, 
carlos@0
    59
      c.FIELDLENGTH, 'Medio' as Text_Align, f.displaylength, c.ColumnName, f.showInRelation 
carlos@0
    60
      FROM ad_field f, ad_column c 
carlos@0
    61
      WHERE f.ad_column_id = c.ad_column_id 
carlos@0
    62
      AND f.showInRelation = 'Y' 
juanpablo@1605
    63
      AND f.ad_tab_id = ? 
carlos@0
    64
      ORDER BY f.SEQNO
carlos@0
    65
    </Sql>
carlos@0
    66
    <Parameter name="tab"/>
carlos@0
    67
  </SqlMethod>
carlos@0
    68
  <SqlMethod name="selectDescriptionTrl" type="preparedStatement" return="multiple">
carlos@0
    69
    <SqlMethodComment>Description of the fields of a tab</SqlMethodComment>
carlos@0
    70
    <Sql>
carlos@0
    71
      SELECT (CASE WHEN ft.Name IS NULL THEN '*' || f.Name ELSE ft.Name END) As Name, f.isDisplayed as isdisplayed, f.isEncrypted, c.AD_REFERENCE_ID as reference, 
carlos@0
    72
      c.FIELDLENGTH, 'Medio' as Text_Align, f.displaylength, c.ColumnName, f.showInRelation 
carlos@0
    73
      FROM ad_field f left join ad_field_trl ft on f.ad_field_id = ft.ad_field_id 
carlos@0
    74
                                              AND ft.isactive = 'Y'
carlos@0
    75
                                              AND ft.ad_language = ?, 
carlos@0
    76
           ad_column c 
carlos@0
    77
      WHERE f.ad_column_id = c.ad_column_id 
carlos@0
    78
      AND f.showInRelation = 'Y' 
juanpablo@1605
    79
      AND f.ad_tab_id = ?    
carlos@0
    80
      ORDER BY f.SEQNO
carlos@0
    81
    </Sql>
carlos@0
    82
    <Parameter name="adLanguage"/>
carlos@0
    83
    <Parameter name="tab"/>    
carlos@0
    84
  </SqlMethod>
carlos@0
    85
  <SqlMethod name="selectAuxiliar" type="preparedStatement" return="multiple">
carlos@0
    86
    <SqlMethodComment>Names of the columns and name of the fields of a tab</SqlMethodComment>
carlos@0
    87
    <Sql>
carlos@0
    88
      SELECT AD_AUXILIARINPUT_ID as reference, name as realname, name as columnname, name as name, 
carlos@0
    89
      code as defaultValue, 'x' as xmltext, '' as WHERECLAUSE
carlos@0
    90
      FROM ad_auxiliarinput 
juanpablo@1605
    91
      WHERE ad_tab_id = ?
carlos@0
    92
    </Sql>
carlos@0
    93
    <Parameter name="sql" optional="true" after="WHERE " text="code LIKE ? || '%' AND "/>
carlos@0
    94
    <Parameter name="tab"/>
carlos@0
    95
  </SqlMethod>
carlos@0
    96
  <SqlMethod name="tabName" type="preparedStatement" return="string">
carlos@0
    97
    <SqlMethodComment>Name of the tab</SqlMethodComment>
carlos@0
    98
    <Sql>
carlos@0
    99
        SELECT Name FROM ad_tab
juanpablo@1605
   100
        WHERE ad_tab_id = ?
carlos@0
   101
    </Sql>
carlos@0
   102
    <Parameter name="tab"/>
carlos@0
   103
  </SqlMethod>
carlos@0
   104
  <SqlMethod name="windowName" type="preparedStatement" return="string">
carlos@0
   105
      <SqlMethodComment>Name of the window</SqlMethodComment>
carlos@0
   106
      <Sql>
carlos@0
   107
        SELECT ad_window.Name FROM ad_tab, ad_window
juanpablo@1605
   108
        WHERE ad_window.ad_window_id = ad_tab.ad_window_id and ad_tab_id = ?
carlos@0
   109
      </Sql>
carlos@0
   110
      <Parameter name="tab"/>
carlos@0
   111
  </SqlMethod>
carlos@0
   112
  <SqlMethod name="tableKeyColumnName" type="preparedStatement" return="multiple">
carlos@0
   113
      <SqlMethodComment>Name of the column key of the tab</SqlMethodComment>
carlos@0
   114
      <Sql><![CDATA[
carlos@0
   115
        SELECT ad_column.ColumnName As Name 
carlos@0
   116
        FROM ad_table, ad_column 
carlos@0
   117
        WHERE ad_table.ad_table_id = ad_column.ad_table_id
carlos@0
   118
          AND UPPER(ad_table.tablename) = UPPER(?) 
carlos@0
   119
          AND ad_column.isActive = 'Y'
carlos@0
   120
          and ad_column.iskey='Y'
carlos@0
   121
        UNION
carlos@0
   122
        SELECT ad_column.ColumnName As Name 
carlos@0
   123
        FROM ad_table, ad_column 
carlos@0
   124
        WHERE ad_table.ad_table_id = ad_column.ad_table_id
carlos@0
   125
          AND UPPER(ad_table.tablename) = UPPER(?) 
carlos@0
   126
          AND ad_column.isActive = 'Y'
carlos@0
   127
          and ad_column.issecondarykey='Y'
carlos@0
   128
      ]]></Sql>
carlos@0
   129
      <Parameter name="tablename"/>
carlos@0
   130
      <Parameter name="tablename"/>
carlos@0
   131
  </SqlMethod>
carlos@0
   132
  <SqlMethod name="keyColumnName" type="preparedStatement" return="multiple">
carlos@0
   133
      <SqlMethodComment>Name of the column key of the tab</SqlMethodComment>
carlos@0
   134
      <Sql><![CDATA[
carlos@0
   135
        SELECT ColumnName As Name, issecondarykey FROM ad_table, ad_column, ad_tab 
carlos@0
   136
        WHERE ad_table.ad_table_id = ad_column.ad_table_id
carlos@0
   137
          AND ad_tab.ad_table_id = ad_table.ad_table_id
juanpablo@1605
   138
          AND ad_tab_id = ? 
carlos@0
   139
          and (iskey='Y' OR isSecondaryKey='Y')
carlos@0
   140
          AND ad_column.ColumnName <> ? 
carlos@0
   141
          ORDER BY (CASE iskey WHEN 'Y' THEN 1 ELSE 2 END)
carlos@0
   142
      ]]></Sql>
carlos@0
   143
      <Parameter name="tab"/>
carlos@0
   144
      <Parameter name="parentField"/>
carlos@0
   145
  </SqlMethod>
carlos@0
   146
  <SqlMethod name="parentsColumnNameSortTab" type="preparedStatement" return="multiple">
carlos@0
   147
      <SqlMethodComment>Name of the columns parent of the tab</SqlMethodComment>
carlos@0
   148
      <Sql><![CDATA[
carlos@0
   149
        SELECT ColumnName AS NAME, AD_REFERENCE_id AS reference, ad_reference_value_id AS referencevalue,
carlos@0
   150
        (SELECT tableNAME FROM AD_TABLE, AD_TAB WHERE AD_TABLE.ad_table_id = AD_TAB.ad_table_id
juanpablo@1605
   151
        AND AD_TAB.ad_tab_id=?) AS tablename 
carlos@0
   152
        FROM AD_FIELD, AD_COLUMN 
juanpablo@1605
   153
        WHERE AD_FIELD.ad_column_id = AD_COLUMN.ad_column_id AND ad_table_id = ? AND isParent='Y' 
carlos@0
   154
      ]]></Sql>
carlos@0
   155
      <Parameter name="parentTab"/>
carlos@0
   156
      <Parameter name="adTableId"/>
carlos@0
   157
  </SqlMethod>
carlos@0
   158
  <SqlMethod name="parentsColumnName" type="preparedStatement" return="multiple">
carlos@0
   159
      <SqlMethodComment>Name of the columns parent of the tab</SqlMethodComment>
carlos@0
   160
      <Sql><![CDATA[
carlos@0
   161
        SELECT ColumnName AS NAME, AD_REFERENCE_id AS reference, ad_reference_value_id AS referencevalue,
carlos@0
   162
        (SELECT tableNAME FROM AD_TABLE, AD_TAB WHERE AD_TABLE.ad_table_id = AD_TAB.ad_table_id
juanpablo@1605
   163
        AND AD_TAB.ad_tab_id=?) AS tablename, ? as AD_Tab_ID, (select name from ad_tab where ad_tab_id = ?) as parent_tab_name
carlos@0
   164
        FROM AD_FIELD, AD_COLUMN 
juanpablo@1605
   165
        WHERE AD_FIELD.ad_column_id = AD_COLUMN.ad_column_id AND ad_tab_id = ? AND isParent='Y' 
carlos@0
   166
        AND EXISTS(SELECT 1 FROM AD_COLUMN c, AD_FIELD f WHERE c.ad_column_id = f.ad_column_id AND (c.iskey='Y' OR c.issecondarykey='Y')
juanpablo@1605
   167
        AND ad_tab_id=? AND UPPER(c.columnname) = UPPER(AD_COLUMN.columnname))
carlos@0
   168
      ]]></Sql>
carlos@0
   169
      <Parameter name="parentTab"/>
asier@431
   170
      <Parameter name="parentTab"/>
asier@431
   171
      <Parameter name="parentTab"/>
carlos@0
   172
      <Parameter name="tab"/>
carlos@0
   173
      <Parameter name="parentTab"/>
carlos@0
   174
  </SqlMethod>
carlos@0
   175
  <SqlMethod name="parentsColumnReal" type="preparedStatement" return="multiple">
carlos@0
   176
      <SqlMethodComment>Name of the columns parent of the tab</SqlMethodComment>
carlos@0
   177
      <Sql>
carlos@0
   178
        SELECT ColumnName AS NAME, AD_REFERENCE_id AS reference, ad_reference_value_id AS referencevalue,
carlos@0
   179
        (SELECT tableNAME FROM AD_TABLE, AD_TAB WHERE AD_TABLE.ad_table_id = AD_TAB.ad_table_id
juanpablo@1605
   180
        AND AD_TAB.ad_tab_id=?) AS tablename
carlos@0
   181
        FROM AD_FIELD, AD_COLUMN 
juanpablo@1605
   182
        WHERE AD_FIELD.ad_column_id = AD_COLUMN.ad_column_id AND ad_tab_id = ?
carlos@0
   183
        AND UPPER(columnname) IN (SELECT UPPER(columnname) FROM AD_FIELD, AD_COLUMN 
carlos@0
   184
        WHERE AD_FIELD.ad_column_id = AD_COLUMN.ad_column_id 
carlos@0
   185
        AND AD_COLUMN.iskey='Y' 
juanpablo@1605
   186
        AND AD_FIELD.ad_tab_id=?)
carlos@0
   187
      </Sql>
carlos@0
   188
      <Parameter name="parentTab"/>
carlos@0
   189
      <Parameter name="tab"/>
carlos@0
   190
      <Parameter name="parentTab"/>
carlos@0
   191
  </SqlMethod>
carlos@0
   192
  <SqlMethod name="parentsColumnRealAll" type="preparedStatement" return="multiple">
carlos@0
   193
      <SqlMethodComment>Name of the columns parent of the tab</SqlMethodComment>
carlos@0
   194
      <Sql>
carlos@0
   195
        SELECT ColumnName AS NAME, AD_REFERENCE_id AS reference, ad_reference_value_id AS referencevalue,
carlos@0
   196
        (SELECT tableNAME FROM AD_TABLE, AD_TAB WHERE AD_TABLE.ad_table_id = AD_TAB.ad_table_id
juanpablo@1605
   197
        AND AD_TAB.ad_tab_id=?) AS tablename 
carlos@0
   198
        FROM AD_FIELD, AD_COLUMN 
carlos@0
   199
        WHERE AD_FIELD.ad_column_id = AD_COLUMN.ad_column_id 
juanpablo@1605
   200
        AND ad_tab_id = ? 
carlos@0
   201
        AND isparent='Y'
carlos@0
   202
        order by ad_column.seqno
carlos@0
   203
      </Sql>
carlos@0
   204
      <Parameter name="parentTab"/>
carlos@0
   205
      <Parameter name="tab"/>
carlos@0
   206
  </SqlMethod>
carlos@0
   207
  <SqlMethod name="parentsColumnDisplayNameSortTab" type="preparedStatement" return="multiple">
carlos@0
   208
      <SqlMethodComment>Name of the columns parent of the tab</SqlMethodComment>
carlos@0
   209
      <Sql><![CDATA[
carlos@0
   210
        SELECT ad_field.name As Name, ad_field_trl.name as columnname 
carlos@0
   211
          FROM ad_field left join ad_field_trl on ad_field.ad_field_id = ad_field_trl.ad_field_id 
carlos@0
   212
                                              and ad_field_trl.ad_language = ?, 
carlos@0
   213
               ad_column 
carlos@0
   214
        WHERE ad_field.ad_column_id = ad_column.ad_column_id 
juanpablo@1605
   215
        and ad_table_id = ? and isParent='Y' 
carlos@0
   216
      ]]></Sql>
carlos@0
   217
      <Parameter name="adLanguage"/>
carlos@0
   218
      <Parameter name="table"/>
carlos@0
   219
  </SqlMethod>
carlos@0
   220
  <SqlMethod name="parentsColumnDisplayName" type="preparedStatement" return="multiple">
carlos@0
   221
      <SqlMethodComment>Name of the columns parent of the tab</SqlMethodComment>
carlos@0
   222
      <Sql><![CDATA[
carlos@0
   223
        SELECT ad_field.name As Name, ad_field_trl.name as columnname 
carlos@0
   224
          FROM ad_field left join ad_field_trl on ad_field.ad_field_id = ad_field_trl.ad_field_id 
carlos@0
   225
                                              and ad_field_trl.ad_language = ?, 
carlos@0
   226
               ad_column 
carlos@0
   227
        WHERE ad_field.ad_column_id = ad_column.ad_column_id 
juanpablo@1605
   228
        and ad_tab_id = ? and isParent='Y' 
carlos@0
   229
        and exists(select 1 from ad_column c, ad_field f where c.ad_column_id = f.ad_column_id and c.iskey='Y'
juanpablo@1605
   230
        and ad_tab_id=? and UPPER(c.columnname) = UPPER(ad_column.columnname))
carlos@0
   231
      ]]></Sql>
carlos@0
   232
      <Parameter name="adLanguage"/>
carlos@0
   233
      <Parameter name="tab"/>
carlos@0
   234
      <Parameter name="parentTab"/>
carlos@0
   235
  </SqlMethod>
carlos@0
   236
  <SqlMethod name="parentsColumnDisplayNameReal" type="preparedStatement" return="multiple">
carlos@0
   237
      <SqlMethodComment>Name of the columns parent of the tab</SqlMethodComment>
carlos@0
   238
      <Sql>
carlos@0
   239
        SELECT ad_field.Name AS NAME, AD_FIELD_TRL.NAME AS COLUMNNAME
carlos@0
   240
        FROM AD_FIELD left join AD_FIELD_TRL on AD_FIELD.AD_FIELD_ID = AD_FIELD_TRL.AD_FIELD_ID
carlos@0
   241
                                            AND AD_FIELD_TRL.AD_LANGUAGE = ? , 
carlos@0
   242
             AD_COLUMN 
carlos@0
   243
        WHERE AD_FIELD.ad_column_id = AD_COLUMN.ad_column_id 
juanpablo@1605
   244
        AND ad_tab_id = ?
carlos@0
   245
        AND UPPER(columnname) IN (SELECT UPPER(columnname) FROM AD_FIELD, AD_COLUMN 
carlos@0
   246
        WHERE AD_FIELD.ad_column_id = AD_COLUMN.ad_column_id 
carlos@0
   247
        AND AD_COLUMN.iskey='Y' 
juanpablo@1605
   248
        AND AD_FIELD.ad_tab_id=?)
carlos@0
   249
      </Sql>
carlos@0
   250
      <Parameter name="adLanguage"/>
carlos@0
   251
      <Parameter name="tab"/>
carlos@0
   252
      <Parameter name="parentTab"/>
carlos@0
   253
  </SqlMethod>
carlos@0
   254
  <SqlMethod name="parentsColumnDisplayNameRealAll" type="preparedStatement" return="multiple">
carlos@0
   255
      <SqlMethodComment>Name of the columns parent of the tab</SqlMethodComment>
carlos@0
   256
      <Sql>
carlos@0
   257
        SELECT ad_field.Name AS NAME, AD_FIELD_TRL.NAME AS COLUMNNAME
carlos@0
   258
        FROM AD_FIELD left join AD_FIELD_TRL on AD_FIELD.AD_FIELD_ID = AD_FIELD_TRL.AD_FIELD_ID 
carlos@0
   259
                                            AND AD_FIELD_TRL.AD_LANGUAGE = ? ,
carlos@0
   260
             AD_COLUMN 
carlos@0
   261
        WHERE AD_FIELD.ad_column_id = AD_COLUMN.ad_column_id 
juanpablo@1605
   262
        AND ad_tab_id = ?
carlos@0
   263
        AND isparent='Y'
carlos@0
   264
        ORDER BY AD_Column.SEQNO
carlos@0
   265
      </Sql>
carlos@0
   266
      <Parameter name="adLanguage"/>
carlos@0
   267
      <Parameter name="tab"/>
carlos@0
   268
  </SqlMethod>
carlos@0
   269
  <SqlMethod name="tableName" type="preparedStatement" return="string">
carlos@0
   270
      <SqlMethodComment>Name of the table of the tab</SqlMethodComment>
carlos@0
   271
      <Sql>
carlos@0
   272
        SELECT ad_table.TABLEName FROM ad_tab, ad_table
juanpablo@1605
   273
        WHERE ad_table.ad_table_id = ad_tab.ad_table_id and ad_tab_id = ?
carlos@0
   274
      </Sql>
carlos@0
   275
      <Parameter name="tab"/>
carlos@0
   276
  </SqlMethod>
carlos@0
   277
  <SqlMethod name="tabNameTrl" type="preparedStatement" return="string">
carlos@0
   278
      <SqlMethodComment>Name of the table of the tab</SqlMethodComment>
carlos@0
   279
      <Sql>
carlos@0
   280
        SELECT (CASE WHEN ad_tab_trl.Name IS NULL THEN '*' || ad_tab.Name ELSE ad_tab_trl.Name END) as NAME 
carlos@0
   281
          FROM ad_tab left join ad_tab_trl on  ad_tab.ad_tab_id = ad_tab_trl.ad_tab_id
carlos@0
   282
                                          and ad_tab_trl.isactive = 'Y'
carlos@0
   283
                                          and ad_tab_trl.ad_language = ?
juanpablo@1605
   284
        WHERE ad_tab.ad_tab_id = ?        
carlos@0
   285
      </Sql>
carlos@0
   286
      <Parameter name="adLanguage"/>
carlos@0
   287
      <Parameter name="tab"/>      
carlos@0
   288
  </SqlMethod>
carlos@0
   289
  <SqlMethod name="windowNameTrl" type="preparedStatement" return="string">
carlos@0
   290
      <SqlMethodComment>Name of the table of the tab</SqlMethodComment>
carlos@0
   291
      <Sql>
carlos@0
   292
        SELECT (CASE WHEN ad_window_trl.Name IS NULL THEN '*' || ad_window.Name ELSE ad_window_trl.Name END) as NAME 
carlos@0
   293
          FROM ad_tab, ad_window left join ad_window_trl on ad_window.ad_window_id = ad_window_trl.ad_window_id 
carlos@0
   294
                                                        and ad_window_trl.isactive = 'Y'
carlos@0
   295
                                                        and ad_window_trl.ad_language = ?
carlos@0
   296
        WHERE ad_tab.ad_window_id = ad_window.ad_window_id 
juanpablo@1605
   297
        and ad_tab.ad_tab_id = ?
carlos@0
   298
        
carlos@0
   299
      </Sql>
carlos@0
   300
      <Parameter name="adLanguage"/>
carlos@0
   301
      <Parameter name="tab"/>      
carlos@0
   302
  </SqlMethod>
carlos@0
   303
  <SqlMethod name="selectUpdatables" type="preparedStatement" return="multiple">
carlos@0
   304
    <SqlMethodComment>Names of the columns of the fields of a tab that are updatables</SqlMethodComment>
carlos@0
   305
    <Sql><![CDATA[
carlos@0
   306
      SELECT ColumnName As Name, '?' AS Xml_Format, ad_column.ad_reference_id as reference, 
carlos@0
   307
      ad_column.ad_reference_value_id as referencevalue FROM ad_field, ad_column 
carlos@0
   308
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
carlos@0
   309
      and UPPER(ad_column.columnname) not in ('CREATED', 'UPDATED', 'CREATEDBY', 'UPDATEDBY') 
juanpablo@1605
   310
      and ad_tab_id = ? 
carlos@0
   311
      AND upper(ad_column.columnname) <> 'BINARYDATA' 
carlos@0
   312
      AND ad_column.isEncrypted <> 'Y'
carlos@0
   313
      AND ad_column.isactive = 'Y'
carlos@0
   314
      ORDER BY ad_field.SEQNO
carlos@0
   315
    ]]></Sql>
carlos@0
   316
    <Parameter name="tab"/>
carlos@0
   317
  </SqlMethod>
carlos@0
   318
  <SqlMethod name="selectLists" type="preparedStatement" return="multiple">
carlos@0
   319
    <SqlMethodComment>Fields of reference list in a tab</SqlMethodComment>
carlos@0
   320
    <Sql>
carlos@0
   321
      SELECT ColumnName As Name, 'x' as xmltext, ad_reference_value_id as referencevalue, ColumnName as NameRef, ad_table.tablename 
carlos@0
   322
      FROM ad_field, ad_column, ad_table
carlos@0
   323
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
carlos@0
   324
        and ad_column.ad_table_id = ad_table.ad_table_id 
carlos@0
   325
        and ad_field.ISDISPLAYED = 'Y'
juanpablo@1605
   326
        AND ad_tab_id = ?
juanpablo@1605
   327
        AND ad_reference_id = '17'
carlos@0
   328
    </Sql>
carlos@0
   329
    <Parameter name="tab"/>
carlos@0
   330
  </SqlMethod>
carlos@0
   331
  <SqlMethod name="selectTables" type="preparedStatement" return="multiple">
carlos@0
   332
    <SqlMethodComment>Fields of reference table in a tab</SqlMethodComment>
carlos@0
   333
    <Sql>
carlos@0
   334
      SELECT ColumnName As Name, 'x' as xmltext, ad_reference_value_id as referencevalue, 
carlos@0
   335
      ad_val_rule.CODE as defaultValue, ad_val_rule.AD_VAL_RULE_ID as columnname,
carlos@0
   336
      ad_ref_table.WHERECLAUSE, ad_table.tablename, ad_table.name as nameref, 'TableList' as tablenametrl, 
carlos@0
   337
      '18' as reference, 'Y' as required, ad_column.istranslated 
carlos@0
   338
      FROM ad_field, ad_column left join ad_val_rule on ad_column.AD_VAL_RULE_ID = ad_val_rule.AD_VAL_RULE_ID , 
carlos@0
   339
          ad_ref_table, ad_table 
carlos@0
   340
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
carlos@0
   341
      AND ad_ref_table.ad_table_id = ad_table.ad_table_id 
carlos@0
   342
      AND ad_column.AD_REFERENCE_VALUE_ID = ad_ref_table.AD_REFERENCE_ID  
carlos@0
   343
      and ad_field.ISDISPLAYED = 'Y'
juanpablo@1605
   344
      AND ad_tab_id = ? 
juanpablo@1605
   345
      AND ad_column.ad_reference_id = '18'
carlos@0
   346
    </Sql>
carlos@0
   347
    <Parameter name="tab"/>
carlos@0
   348
  </SqlMethod>
carlos@0
   349
  <SqlMethod name="selectTableDirs" type="preparedStatement" return="multiple">
carlos@0
   350
    <SqlMethodComment>Fields of reference table in a tab</SqlMethodComment>
carlos@0
   351
    <Sql>
carlos@0
   352
      SELECT ColumnName As Name, 'x' as xmltext, ColumnName as referencevalue, 
carlos@0
   353
      ad_val_rule.CODE as defaultValue, ad_val_rule.AD_VAL_RULE_ID as columnname, ad_table.tablename, 
carlos@0
   354
      'TableDir' as tablenametrl, '' as WHERECLAUSE, '19' as reference, ad_column.istranslated 
carlos@0
   355
      FROM ad_field, ad_column left join ad_val_rule on ad_column.AD_VAL_RULE_ID = ad_val_rule.AD_VAL_RULE_ID , ad_table 
carlos@0
   356
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
carlos@0
   357
      and ad_column.ad_table_id = ad_table.ad_table_id 
carlos@0
   358
      and ad_field.ISDISPLAYED = 'Y'
juanpablo@1605
   359
      AND ad_tab_id = ? 
juanpablo@1605
   360
      AND ad_reference_id = '19'
carlos@0
   361
    </Sql>
carlos@0
   362
    <Parameter name="tab"/>
carlos@0
   363
  </SqlMethod>
carlos@0
   364
  <SqlMethod name="columnIdentifier" type="preparedStatement" return="string">
carlos@0
   365
      <SqlMethodComment>Name of the table of the tab</SqlMethodComment>
carlos@0
   366
      <Sql>
carlos@0
   367
        SELECT MAX(columnname) AS NAME FROM AD_COLUMN, AD_TABLE 
carlos@0
   368
        WHERE AD_TABLE.NAME = ?
carlos@0
   369
          AND AD_COLUMN.ad_table_id = AD_TABLE.ad_table_id
asier@1239
   370
          AND isidentifier = 'Y' 
asier@1239
   371
          AND SeqNo = (CASE TO_CHAR(AD_TABLE.NAME) 
asier@1239
   372
                          WHEN 'C_PaySelectionCheck' THEN 2 
asier@1239
   373
                          ELSE (SELECT MIN(SeqNo) 
asier@1239
   374
                                  FROM AD_Column 
asier@1239
   375
                                 WHERE AD_Table_ID=AD_TABLE.AD_Table_ID 
asier@1239
   376
                                   AND IsIdentifier='Y')
asier@1239
   377
                           END)
carlos@0
   378
      </Sql>
carlos@0
   379
      <Parameter name="tableName"/>
carlos@0
   380
  </SqlMethod>
carlos@0
   381
  <SqlMethod name="identifierColumns" type="preparedStatement" return="multiple">
carlos@0
   382
      <SqlMethodComment>identifier columns of a table</SqlMethodComment>
carlos@0
   383
      <Sql>
carlos@0
   384
        SELECT ad_column.columnname as name, ad_reference_id as reference, ismandatory as required, 
carlos@0
   385
        REPLACE(replace(REPLACE(REPLACE(AD_ELEMENT.columnname, 'Substitute_ID', 'M_Product_ID'), 'C_Settlement_Cancel_ID', 'C_Settlement_ID'), 'BOM_ID', '_ID'), 'M_LocatorTo_ID', 'M_Locator_ID') as columnname, ad_reference_value_id as referencevalue, 
carlos@0
   386
        ad_column.istranslated, ad_table.tablename 
carlos@0
   387
        FROM ad_column left join ad_element on ad_column.ad_element_id = ad_element.ad_element_id, 
carlos@0
   388
             ad_table 
carlos@0
   389
        WHERE UPPER(ad_table.tablename) = UPPER(?)
carlos@0
   390
          AND ad_column.ad_table_id = ad_table.ad_table_id
carlos@0
   391
          AND isidentifier = 'Y' 
carlos@0
   392
        order by seqno
carlos@0
   393
      </Sql>
carlos@0
   394
      <Parameter name="tableName"/>
carlos@0
   395
  </SqlMethod>
carlos@0
   396
  <SqlMethod name="selectSequence" type="preparedStatement" return="multiple">
carlos@0
   397
    <SqlMethodComment>Names of the columns to order by</SqlMethodComment>
carlos@0
   398
    <Sql>
carlos@0
   399
      <![CDATA[
carlos@0
   400
      SELECT ColumnName As Name
carlos@0
   401
      FROM ad_field, ad_column
carlos@0
   402
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
juanpablo@1605
   403
        AND ad_tab_id = ?
carlos@0
   404
        AND ad_field.SORTNO is not null
carlos@0
   405
      ORDER BY ad_field.SORTNO, ad_field.SEQNO
carlos@0
   406
      ]]>
carlos@0
   407
    </Sql>
carlos@0
   408
    <Parameter name="tab"/>
carlos@0
   409
  </SqlMethod>
carlos@0
   410
  <SqlMethod name="isSOTrx" type="preparedStatement" return="String" default="N">
carlos@0
   411
    <SqlMethodComment>isSOTrx of the window</SqlMethodComment>
carlos@0
   412
    <Sql>
carlos@0
   413
      <![CDATA[
carlos@0
   414
      SELECT isSOTrx FROM AD_WINDOW, AD_TAB 
carlos@0
   415
      WHERE AD_TAB.AD_WINDOW_ID = AD_WINDOW.AD_WINDOW_ID 
juanpablo@1605
   416
      AND AD_TAB.AD_TAB_ID = ?
carlos@0
   417
      ]]>
carlos@0
   418
    </Sql>
carlos@0
   419
    <Parameter name="tab"/>
carlos@0
   420
  </SqlMethod>
carlos@0
   421
  <SqlMethod name="selectDefaultValue" type="preparedStatement" return="multiple">
carlos@0
   422
    <SqlMethodComment>Default values of the columns</SqlMethodComment>
carlos@0
   423
    <Sql>
carlos@0
   424
      <![CDATA[
carlos@0
   425
      SELECT ad_column.AD_COLUMN_ID as reference, ad_column.ISMANDATORY as required, 
carlos@0
   426
      REPLACE(replace(REPLACE(REPLACE(columnname, 'Substitute_ID', 'M_Product_ID'), 'C_Settlement_Cancel_ID', 'C_Settlement_ID'), 'BOM_ID', '_ID'), 'M_LocatorTo_ID', 'M_Locator_ID') as Name, 
carlos@0
   427
      REPLACE(REPLACE(ad_column.defaultvalue, CHR(13), '\\n'), CHR(10), '') as defaultValue, '' as WHERECLAUSE, 
carlos@0
   428
      ad_reference_id as referencevalue,
carlos@0
   429
      isdisplayed, ad_column.columnname as columnname, ad_table.ACCESSLEVEL, 
carlos@0
   430
      ad_table.TABLENAME as NameRef, ad_reference_value_id as type, ad_column.name as realname
carlos@0
   431
      FROM ad_field, ad_column, ad_table
carlos@0
   432
      WHERE ad_field.ad_column_id = ad_column.ad_column_id
carlos@0
   433
	  	AND ad_column.AD_TABLE_ID = ad_table.AD_TABLE_ID 
juanpablo@1605
   434
        AND ad_tab_id = ? 
carlos@0
   435
        AND upper(ad_column.columnname) <> 'BINARYDATA' 
carlos@0
   436
      ORDER BY ad_field.SEQNO
carlos@0
   437
      ]]>
carlos@0
   438
    </Sql>
carlos@0
   439
    <Parameter name="sql" optional="true" after="WHERE " text="ad_column.defaultvalue LIKE ? || '%' AND "/>
carlos@0
   440
    <Parameter name="tab"/>
carlos@0
   441
  </SqlMethod>
carlos@0
   442
  <SqlMethod name="selectSession" type="preparedStatement" return="multiple">
carlos@0
   443
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   444
    <Sql>
carlos@0
   445
      <![CDATA[
carlos@0
   446
      SELECT REPLACE(replace(REPLACE(REPLACE(AD_ELEMENT.columnname, 'Substitute_ID', 'M_Product_ID'), 'C_Settlement_Cancel_ID', 'C_Settlement_ID'), 'BOM_ID', '_ID'), 'M_LocatorTo_ID', 'M_Locator_ID') as realName, REPLACE(replace(REPLACE(REPLACE(AD_ELEMENT.columnname, 'Substitute_ID', 'M_Product_ID'), 'C_Settlement_Cancel_ID', 'C_Settlement_ID'), 'BOM_ID', '_ID'), 'M_LocatorTo_ID', 'M_Locator_ID') As Name, 
carlos@0
   447
        ad_reference_id as reference, ad_reference_value_id as referencevalue, 'x' as xmltext,
carlos@0
   448
        ismandatory as required, isDisplayed as isdisplayed, isupdateable as isupdateable,
carlos@0
   449
        ad_column.defaultvalue As defaultValue, ad_column.fieldlength As fieldLength,
carlos@0
   450
        ad_field.displaylength, REPLACE(replace(REPLACE(REPLACE(AD_ELEMENT.columnname, 'Substitute_ID', 'M_Product_ID'), 'C_Settlement_Cancel_ID', 'C_Settlement_ID'), 'BOM_ID', '_ID'), 'M_LocatorTo_ID', 'M_Locator_ID') as columnname
carlos@0
   451
      FROM ad_field, ad_column left join ad_element on ad_column.ad_element_id = ad_element.ad_element_id
carlos@0
   452
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
juanpablo@1605
   453
        AND ad_tab_id = ?
carlos@0
   454
        AND (ad_column.ISSESSIONATTR = 'Y' 
carlos@0
   455
        OR ad_column.iskey = 'Y') 
carlos@0
   456
        AND upper(ad_column.columnname) <> 'BINARYDATA' 
carlos@0
   457
        AND ad_column.ColumnName <> 'Created'
carlos@0
   458
      ORDER BY ad_field.SEQNO
carlos@0
   459
      ]]>
carlos@0
   460
    </Sql>
carlos@0
   461
    <Parameter name="tab"/>
carlos@0
   462
  </SqlMethod>
carlos@0
   463
  <SqlMethod name="selectDocumentsNo" type="preparedStatement" return="multiple">
carlos@0
   464
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   465
    <Sql>
carlos@0
   466
      <![CDATA[
carlos@0
   467
      SELECT ad_column.AD_COLUMN_ID as reference, REPLACE(replace(REPLACE(REPLACE(columnname, 'Substitute_ID', 'M_Product_ID'), 'C_Settlement_Cancel_ID', 'C_Settlement_ID'), 'BOM_ID', '_ID'), 'M_LocatorTo_ID', 'M_Locator_ID') as Name, 
carlos@0
   468
      ad_column.defaultvalue as defaultValue, '' as WHERECLAUSE, ad_reference_id as referencevalue,
carlos@0
   469
      isdisplayed, ad_column.columnname as columnname, ad_table.ACCESSLEVEL, 
carlos@0
   470
      ad_table.TABLENAME as NameRef, '' as realname
carlos@0
   471
      FROM ad_field, ad_column, ad_table
carlos@0
   472
      WHERE ad_field.ad_column_id = ad_column.ad_column_id
carlos@0
   473
	  	AND ad_column.AD_TABLE_ID = ad_table.AD_TABLE_ID 
juanpablo@1605
   474
        AND ad_tab_id = ? 
carlos@0
   475
        and isParent='N'
carlos@0
   476
        and ad_column.columnname = 'DocumentNo'
carlos@0
   477
        and ad_column.defaultvalue is null
carlos@0
   478
      ORDER BY ad_field.SEQNO
carlos@0
   479
      ]]>
carlos@0
   480
    </Sql>
carlos@0
   481
    <Parameter name="tab"/>
carlos@0
   482
  </SqlMethod>
carlos@0
   483
  <SqlMethod name="selectActionButton" type="preparedStatement" return="multiple">
carlos@0
   484
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   485
    <Sql>
carlos@0
   486
      <![CDATA[
carlos@0
   487
      SELECT distinct ad_column.columnname, 
carlos@0
   488
      ad_column.ad_process_id as reference, ad_process.procedurename as name, 
carlos@0
   489
      ad_column.ad_reference_id as type, ad_column.ad_reference_value_id as referencevalue,
carlos@0
   490
      ad_val_rule_id as defaultvalue, ad_column.fieldlength, ad_field.name as realname, 
carlos@0
   491
      ad_process.description as tablename, ad_process.help as xmltext, ad_column.ad_reference_value_id, ad_process.isjasper 
carlos@0
   492
      FROM ad_column, ad_process, ad_field
carlos@0
   493
      where ad_column.ad_process_id = ad_process.ad_process_id 
carlos@0
   494
      and ad_column.ad_column_id = ad_field.ad_column_id 
carlos@0
   495
      and (ad_process.procedurename is not null
carlos@0
   496
      OR ad_process.isjasper = 'Y') 
carlos@0
   497
      and ad_column.columnname not in('DocAction', 'PaymentRule') 
carlos@0
   498
      and (ad_column.columnname <> 'CreateFrom'
carlos@0
   499
      or ad_column.ad_process_id is not null)
carlos@0
   500
      and (ad_column.columnname <> 'Posted'
carlos@0
   501
      or ad_column.ad_process_id is not null)
carlos@0
   502
      and ad_column.ad_column_id in (select ad_column_id from ad_field where isdisplayed='Y' and isactive='Y')
carlos@0
   503
      and ad_column.isactive='Y'
carlos@0
   504
      order by ad_column.ad_process_id
carlos@0
   505
      ]]>
carlos@0
   506
    </Sql>
carlos@0
   507
  </SqlMethod>
carlos@0
   508
  <SqlMethod name="selectActionButtonTrl" type="preparedStatement" return="multiple">
carlos@0
   509
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   510
    <Sql>
carlos@0
   511
      <![CDATA[
carlos@0
   512
        SELECT DISTINCT AD_COLUMN.columnname, 
carlos@0
   513
        AD_COLUMN.ad_process_id AS reference, AD_PROCESS.procedurename AS NAME, 
carlos@0
   514
        AD_COLUMN.ad_reference_id AS TYPE, AD_COLUMN.ad_reference_value_id AS referencevalue,
carlos@0
   515
        ad_val_rule_id AS defaultvalue, AD_COLUMN.fieldlength, 
carlos@0
   516
        (CASE WHEN AD_FIELD_TRL.NAME IS NULL THEN '*' || AD_FIELD.NAME ELSE AD_FIELD_TRL.NAME END) AS realname, 
carlos@0
   517
        (CASE WHEN AD_PROCESS_TRL.description IS NULL THEN (CASE WHEN AD_PROCESS.DESCRIPTION IS NULL THEN '' ELSE '*' END) || AD_PROCESS.description ELSE AD_PROCESS_TRL.description END) AS tablename, 
carlos@0
   518
        (CASE WHEN AD_PROCESS_TRL.help IS NULL THEN (CASE WHEN AD_PROCESS.help IS NULL THEN '' ELSE '*' END) || AD_PROCESS.help ELSE AD_PROCESS_TRL.help END) AS xmltext, 
carlos@0
   519
        ad_column.ad_reference_value_id, ad_process.isjasper 
carlos@0
   520
        FROM AD_FIELD left join AD_FIELD_TRL on AD_FIELD.AD_FIELD_ID = AD_FIELD_TRL.AD_FIELD_ID 
carlos@0
   521
                                            AND AD_FIELD_TRL.AD_LANGUAGE = ?,
carlos@0
   522
             AD_PROCESS left join AD_PROCESS_TRL on AD_PROCESS.AD_PROCESS_ID = AD_PROCESS_TRL.AD_PROCESS_ID 
carlos@0
   523
                                                AND AD_PROCESS_TRL.AD_LANGUAGE = ?,
carlos@0
   524
        AD_COLUMN
carlos@0
   525
        WHERE AD_COLUMN.ad_process_id = AD_PROCESS.ad_process_id 
carlos@0
   526
        AND AD_COLUMN.ad_column_id = AD_FIELD.ad_column_id
carlos@0
   527
        AND (AD_PROCESS.procedurename IS NOT NULL
carlos@0
   528
        OR AD_PROCESS.ISJASPER = 'Y')
carlos@0
   529
        AND AD_COLUMN.columnname NOT IN('DocAction', 'PaymentRule')
carlos@0
   530
        and (ad_column.columnname <> 'CreateFrom'
carlos@0
   531
        or ad_column.ad_process_id is not null)
carlos@0
   532
        and (ad_column.columnname <> 'Posted'
carlos@0
   533
        or ad_column.ad_process_id is not null)
carlos@0
   534
        AND AD_COLUMN.ad_column_id IN (SELECT ad_column_id FROM AD_FIELD WHERE isdisplayed='Y' AND isactive='Y')
carlos@0
   535
        AND AD_COLUMN.isactive='Y'
carlos@0
   536
        ORDER BY AD_COLUMN.ad_process_id
carlos@0
   537
      ]]>
carlos@0
   538
    </Sql>
carlos@0
   539
      <Parameter name="adLanguage"/>
carlos@0
   540
      <Parameter name="adLanguage"/>
carlos@0
   541
  </SqlMethod>
carlos@0
   542
  <SqlMethod name="selectActionButtonGenerics" type="preparedStatement" return="multiple">
carlos@0
   543
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   544
    <Sql>
carlos@0
   545
      <![CDATA[
carlos@0
   546
        SELECT DISTINCT 'ActionButton'  AS columnname, 
carlos@0
   547
        ad_process_id AS reference, procedurename AS NAME, 
carlos@0
   548
        '' AS TYPE, '' AS referencevalue,
carlos@0
   549
        '' AS defaultvalue, '' AS fieldlength, '' AS realname, 
carlos@0
   550
        description AS tablename, help AS xmltext, isjasper 
carlos@0
   551
        FROM AD_PROCESS
carlos@0
   552
        WHERE isactive='Y'
carlos@0
   553
        AND (procedurename IS NOT NULL
carlos@0
   554
        OR isjasper='Y')
carlos@0
   555
        and ad_process_id in (select ad_process_id from ad_menu)
carlos@0
   556
        ORDER BY ad_process_id
carlos@0
   557
      ]]>
carlos@0
   558
    </Sql>
carlos@0
   559
  </SqlMethod>
carlos@0
   560
  <SqlMethod name="selectActionButtonGenericsTrl" type="preparedStatement" return="multiple">
carlos@0
   561
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   562
    <Sql>
carlos@0
   563
      <![CDATA[
carlos@0
   564
        SELECT DISTINCT 'ActionButton'  AS columnname, 
carlos@0
   565
        AD_PROCESS.ad_process_id AS reference, AD_PROCESS.procedurename AS NAME, 
carlos@0
   566
        '' AS TYPE, '' AS referencevalue,
carlos@0
   567
        '' AS defaultvalue, '' AS fieldlength, '' AS realname, 
carlos@0
   568
        (CASE WHEN AD_PROCESS_TRL.description IS NULL THEN (CASE WHEN AD_PROCESS.description IS NULL THEN '' ELSE '*' END) || AD_PROCESS.description ELSE AD_PROCESS_TRL.description END) AS tablename, 
carlos@0
   569
        (CASE WHEN AD_PROCESS_TRL.help IS NULL THEN (CASE WHEN AD_PROCESS.help IS NULL THEN '' ELSE '*' END) || AD_PROCESS.help ELSE AD_PROCESS_TRL.help END) AS xmltext, AD_PROCESS.ISJASPER 
carlos@0
   570
        FROM AD_PROCESS left join AD_PROCESS_TRL on AD_PROCESS.AD_PROCESS_ID = AD_PROCESS_TRL.AD_PROCESS_ID 
carlos@0
   571
                                                AND AD_PROCESS_TRL.AD_LANGUAGE  = ? 
carlos@0
   572
        WHERE AD_PROCESS.isactive='Y'
carlos@0
   573
        AND (AD_PROCESS.procedurename IS NOT NULL
carlos@0
   574
        OR AD_PROCESS.isjasper = 'Y')
carlos@0
   575
        AND AD_PROCESS.ad_process_id IN (SELECT ad_process_id FROM AD_MENU)
carlos@0
   576
        ORDER BY AD_PROCESS.ad_process_id
carlos@0
   577
      ]]>
carlos@0
   578
    </Sql>
carlos@0
   579
      <Parameter name="adLanguage"/>
carlos@0
   580
  </SqlMethod>
carlos@0
   581
  <SqlMethod name="isHighVolume" type="preparedStatement" return="String">
carlos@0
   582
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   583
    <Sql>
carlos@0
   584
      <![CDATA[
carlos@0
   585
      SELECT ishighvolume FROM AD_TAB, AD_TABLE 
carlos@0
   586
      WHERE AD_TAB.ad_table_id = AD_TABLE.ad_table_id
juanpablo@1605
   587
      AND AD_TAB.ad_tab_id=?
carlos@0
   588
      ]]>
carlos@0
   589
    </Sql>
carlos@0
   590
    <Parameter name="tab"/>
carlos@0
   591
  </SqlMethod>
carlos@0
   592
  <SqlMethod name="isSingleRow" type="preparedStatement" return="String">
carlos@0
   593
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   594
    <Sql>
carlos@0
   595
      <![CDATA[
carlos@0
   596
      SELECT issinglerow FROM AD_TAB 
juanpablo@1605
   597
      WHERE ad_tab_id=?
carlos@0
   598
      ]]>
carlos@0
   599
    </Sql>
carlos@0
   600
    <Parameter name="tab"/>
carlos@0
   601
  </SqlMethod>
carlos@0
   602
  <SqlMethod name="hasCreateFromButton" type="preparedStatement" return="String" default="0">
carlos@0
   603
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   604
    <Sql>
carlos@0
   605
      <![CDATA[
juanpablo@1605
   606
        SELECT coalesce(ad_column.ad_process_id, '-1') AS total 
carlos@0
   607
        FROM AD_FIELD, AD_COLUMN, AD_TABLE
carlos@0
   608
        WHERE AD_FIELD.ad_column_id = AD_COLUMN.ad_column_id
carlos@0
   609
        AND AD_COLUMN.AD_TABLE_ID = AD_TABLE.AD_TABLE_ID 
juanpablo@1605
   610
        AND ad_tab_id = ? 
juanpablo@1605
   611
        AND AD_COLUMN.ad_reference_id = '28'
carlos@0
   612
        AND AD_COLUMN.COLUMNNAME = 'CreateFrom'
carlos@0
   613
        AND AD_COLUMN.AD_PROCESS_ID IS NULL
carlos@0
   614
        AND AD_FIELD.ISDISPLAYED = 'Y'
carlos@0
   615
      ]]>
carlos@0
   616
    </Sql>
carlos@0
   617
    <Parameter name="tab"/>
carlos@0
   618
  </SqlMethod>
carlos@0
   619
  <SqlMethod name="hasPostedButton" type="preparedStatement" return="String" default="0">
carlos@0
   620
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   621
    <Sql>
carlos@0
   622
      <![CDATA[
juanpablo@1605
   623
        SELECT coalesce(ad_column.ad_process_id, '-1') AS total 
carlos@0
   624
        FROM AD_FIELD, AD_COLUMN, AD_TABLE
carlos@0
   625
        WHERE AD_FIELD.ad_column_id = AD_COLUMN.ad_column_id
carlos@0
   626
        AND AD_COLUMN.AD_TABLE_ID = AD_TABLE.AD_TABLE_ID 
juanpablo@1605
   627
        AND ad_tab_id = ? 
juanpablo@1605
   628
        AND AD_COLUMN.ad_reference_id = '28'
carlos@0
   629
        AND AD_COLUMN.COLUMNNAME = 'Posted'
carlos@0
   630
        AND AD_COLUMN.AD_PROCESS_ID IS NULL
carlos@0
   631
        AND AD_FIELD.ISDISPLAYED = 'Y'
carlos@0
   632
      ]]>
carlos@0
   633
    </Sql>
carlos@0
   634
    <Parameter name="tab"/>
carlos@0
   635
  </SqlMethod>
carlos@0
   636
  <SqlMethod name="selectValidationTab" type="preparedStatement" return="multiple">
carlos@0
   637
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   638
    <Sql>
carlos@0
   639
      <![CDATA[
asier@683
   640
        SELECT c.ad_column_id as id, c.columnname, t.WHERECLAUSE as whereClause, v.code as referencevalue, c.ad_reference_id as reference,
asier@683
   641
        c.ad_reference_value_id as NameRef, c.ad_val_rule_id as defaultvalue, f.isdisplayed, c.istranslated, c.columnname as name,
asier@683
   642
        (case when t.whereclause is not null or v.code is not null then 'C' else 'R' end) as type /*Combo reaload or Reference*/ 
carlos@0
   643
        FROM AD_FIELD f, 
carlos@0
   644
             AD_COLUMN c left join  AD_VAL_RULE v on c.AD_VAL_RULE_ID = v.AD_VAL_RULE_ID
juanpablo@1605
   645
                         left join AD_REF_TABLE t on (CASE c.ad_reference_id WHEN '18' THEN c.AD_REFERENCE_VALUE_ID ELSE '0' END) = t.AD_REFERENCE_ID     
carlos@0
   646
        WHERE f.AD_COLUMN_ID = c.ad_column_id
juanpablo@1605
   647
        AND f.ad_tab_id = ? 
carlos@0
   648
        AND (t.whereclause IS NOT NULL
asier@683
   649
            OR v.code IS NOT NULL
juanpablo@1605
   650
            OR c.ad_reference_id in ('19','18','17'))
carlos@0
   651
      ]]>
carlos@0
   652
    </Sql>
carlos@0
   653
    <Parameter name="tab"/>
carlos@0
   654
  </SqlMethod>
carlos@0
   655
  <SqlMethod name="selectColumnTable" type="preparedStatement" return="multiple">
carlos@0
   656
    <SqlMethodComment>Fields of reference table in a tab</SqlMethodComment>
carlos@0
   657
    <Sql>
carlos@0
   658
      SELECT ColumnName As Name, 'x' as xmltext, ad_reference_value_id as referencevalue, 
carlos@0
   659
      ad_val_rule.CODE as defaultValue, ad_val_rule.AD_VAL_RULE_ID as columnname,
carlos@0
   660
      ad_ref_table.WHERECLAUSE, ad_table.tablename, 'TableList' as tablenametrl, ad_table.name as nameref, 
carlos@0
   661
      '18' as reference, 'Y' as required, ad_column.istranslated 
carlos@0
   662
      FROM ad_field, ad_column left join ad_val_rule on ad_column.AD_VAL_RULE_ID = ad_val_rule.AD_VAL_RULE_ID, ad_ref_table, ad_table 
carlos@0
   663
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
carlos@0
   664
      AND ad_ref_table.ad_table_id = ad_table.ad_table_id 
carlos@0
   665
      AND ad_column.AD_REFERENCE_VALUE_ID = ad_ref_table.AD_REFERENCE_ID  
carlos@0
   666
      and ad_field.ISDISPLAYED = 'Y'
juanpablo@1605
   667
      AND ad_field.ad_tab_id = ?
juanpablo@1605
   668
      AND ad_column.ad_column_id = ? 
juanpablo@1605
   669
      AND ad_column.ad_reference_id = '18'
carlos@0
   670
    </Sql>
carlos@0
   671
    <Parameter name="adTabId"/>
carlos@0
   672
    <Parameter name="adColumnId"/>
carlos@0
   673
  </SqlMethod>
carlos@0
   674
  <SqlMethod name="selectColumnTableDir" type="preparedStatement" return="multiple">
carlos@0
   675
    <SqlMethodComment>Fields of reference table in a tab</SqlMethodComment>
carlos@0
   676
    <Sql>
carlos@0
   677
      SELECT ColumnName As Name, 'x' as xmltext, ColumnName as referencevalue, 
carlos@0
   678
      ad_val_rule.CODE as defaultValue, ad_val_rule.AD_VAL_RULE_ID as columnname, ad_table.tablename, 
carlos@0
   679
      'TableDir' as tablenametrl, '' as WHERECLAUSE, '19' as reference, ad_column.istranslated 
carlos@0
   680
      FROM ad_field, ad_column left join ad_val_rule on ad_column.AD_VAL_RULE_ID = ad_val_rule.AD_VAL_RULE_ID , ad_table 
carlos@0
   681
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
carlos@0
   682
      and ad_column.ad_table_id = ad_table.ad_table_id 
carlos@0
   683
      and ad_field.ISDISPLAYED = 'Y'
juanpablo@1605
   684
      AND ad_field.ad_tab_id = ?
juanpablo@1605
   685
      AND ad_column.ad_column_id = ? 
juanpablo@1605
   686
      AND ad_reference_id = '19'
carlos@0
   687
    </Sql>
carlos@0
   688
    <Parameter name="adTabId"/>
carlos@0
   689
    <Parameter name="adColumnId"/>
carlos@0
   690
  </SqlMethod>
carlos@0
   691
  <SqlMethod name="columnName" type="preparedStatement" return="String" default="">
carlos@0
   692
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   693
    <Sql>
carlos@0
   694
      <![CDATA[
carlos@0
   695
      SELECT columnname FROM AD_column 
juanpablo@1605
   696
      WHERE ad_column_id=?
carlos@0
   697
      ]]>
carlos@0
   698
    </Sql>
carlos@0
   699
    <Parameter name="adColumnId"/>
carlos@0
   700
  </SqlMethod>
carlos@0
   701
  <SqlMethod name="selectIdentify" type="preparedStatement" return="multiple">
carlos@0
   702
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   703
    <Sql>
carlos@0
   704
      <![CDATA[
carlos@0
   705
      SELECT ad_column.AD_COLUMN_ID as reference, REPLACE(replace(REPLACE(REPLACE(columnname, 'Substitute_ID', 'M_Product_ID'), 'C_Settlement_Cancel_ID', 'C_Settlement_ID'), 'BOM_ID', '_ID'), 'M_LocatorTo_ID', 'M_Locator_ID') as Name, 
carlos@0
   706
      ad_column.defaultvalue as defaultValue, '' as WHERECLAUSE, ad_reference_id as referencevalue,
carlos@0
   707
      isdisplayed, ad_column.columnname as columnname, ad_table.ACCESSLEVEL, 
carlos@0
   708
      ad_table.TABLENAME as NameRef, '' as realname, ad_column.ISSESSIONATTR, ad_column.istranslated 
carlos@0
   709
      FROM ad_field, ad_column, ad_table
carlos@0
   710
      WHERE ad_field.ad_column_id = ad_column.ad_column_id
carlos@0
   711
	  	AND ad_column.AD_TABLE_ID = ad_table.AD_TABLE_ID 
juanpablo@1605
   712
        AND ad_tab_id = ? 
carlos@0
   713
        and ismandatory='Y' 
carlos@0
   714
        AND upper(ad_column.columnname) = 'VALUE'
carlos@0
   715
      ORDER BY ad_field.SEQNO
carlos@0
   716
      ]]>
carlos@0
   717
    </Sql>
carlos@0
   718
    <Parameter name="tab"/>
carlos@0
   719
  </SqlMethod>
carlos@0
   720
  <SqlMethod name="selectParentWhereClause" type="preparedStatement" return="String" default="">
carlos@0
   721
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   722
    <Sql>
carlos@0
   723
      <![CDATA[
carlos@0
   724
      SELECT whereclause
carlos@0
   725
      FROM ad_tab
juanpablo@1605
   726
      WHERE ad_tab_id = ? 
carlos@0
   727
      ]]>
carlos@0
   728
    </Sql>
carlos@0
   729
    <Parameter name="tab"/>
carlos@0
   730
  </SqlMethod>
carlos@0
   731
  <SqlMethod name="hasEncryptionFields" type="preparedStatement" return="String" default="0">
carlos@0
   732
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   733
    <Sql>
carlos@0
   734
      <![CDATA[
carlos@0
   735
        SELECT count(*) AS total 
carlos@0
   736
        FROM AD_FIELD, AD_COLUMN
juanpablo@1605
   737
        WHERE AD_FIELD.ad_tab_id = ? 
carlos@0
   738
        AND ad_field.ad_column_id = ad_column.ad_column_id 
carlos@0
   739
        AND ad_column.isEncrypted = 'Y' 
carlos@0
   740
        AND ad_field.ISDISPLAYED = 'Y'
carlos@0
   741
      ]]>
carlos@0
   742
    </Sql>
carlos@0
   743
    <Parameter name="tab"/>
carlos@0
   744
  </SqlMethod>
carlos@0
   745
  <SqlMethod name="selectEncrypted" type="preparedStatement" return="multiple">
carlos@0
   746
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   747
    <Sql>
carlos@0
   748
      <![CDATA[
carlos@0
   749
      SELECT ad_field.ad_field_id as id, ad_column.ColumnName, ad_column.ColumnName as realName, ad_column.ColumnName as Name, ad_column.isDesencryptable, '' AS Xml_Format, '' as htmltext 
carlos@0
   750
      FROM ad_field, ad_column
carlos@0
   751
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
carlos@0
   752
        AND ad_column.isEncrypted = 'Y' 
carlos@0
   753
        AND ad_field.ISDISPLAYED = 'Y'
juanpablo@1605
   754
        AND ad_tab_id = ?
carlos@0
   755
      ORDER BY ad_field.SEQNO
carlos@0
   756
      ]]>
carlos@0
   757
    </Sql>
carlos@0
   758
    <Parameter name="tab"/>
carlos@0
   759
  </SqlMethod>
carlos@0
   760
  <SqlMethod name="selectButton" type="preparedStatement" return="multiple">
carlos@0
   761
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   762
    <Sql>
carlos@0
   763
      <![CDATA[
carlos@0
   764
      select ad_reference_value_id as id, columnname, columnname || '_BTN' as realName 
carlos@0
   765
      from ad_field f, ad_column c
carlos@0
   766
      where f.ad_column_id = c.ad_column_id 
juanpablo@1605
   767
      and f.ad_tab_id = ?
carlos@0
   768
      and f.isactive = 'Y'
carlos@0
   769
      and f.isdisplayed = 'Y'
carlos@0
   770
      and c.isactive = 'Y'
carlos@0
   771
      and ad_reference_value_id is not null
juanpablo@1605
   772
      and c.ad_reference_id = '28' 
carlos@0
   773
      and c.columnname <> 'ChangeProjectStatus'
carlos@0
   774
      ]]>
carlos@0
   775
    </Sql>
carlos@0
   776
    <Parameter name="tab"/>
carlos@0
   777
  </SqlMethod>
carlos@0
   778
  <SqlMethod name="hasActionButton" type="preparedStatement" return="String" default="0">
carlos@0
   779
    <SqlMethodComment>Checks if the tab has action buttons</SqlMethodComment>
carlos@0
   780
    <Sql>
carlos@0
   781
      <![CDATA[
carlos@0
   782
      select count(*) as actionButtons
carlos@0
   783
      from ad_field f, ad_column c
carlos@0
   784
      where f.ad_column_id = c.ad_column_id 
juanpablo@1605
   785
      and f.ad_tab_id = ?
carlos@0
   786
      and f.isactive = 'Y'
carlos@0
   787
      and f.isdisplayed = 'Y'
carlos@0
   788
      and c.isactive = 'Y'
carlos@0
   789
      and ad_reference_value_id is not null
juanpablo@1605
   790
      and c.ad_reference_id = '28' 
carlos@0
   791
      ]]>
carlos@0
   792
    </Sql>
carlos@0
   793
    <Parameter name="tab"/>
asier@528
   794
  </SqlMethod>
asier@528
   795
  <SqlMethod name="hasButtonList" type="preparedStatement" return="String" default="0">
asier@528
   796
    <SqlMethodComment>Checks if the tab has action buttons</SqlMethodComment>
asier@528
   797
    <Sql>
asier@528
   798
      <![CDATA[
asier@528
   799
          select count(*) as total
asier@528
   800
        from ad_field f,
asier@528
   801
             ad_column c
juanpablo@1605
   802
       where f.ad_tab_id = ?
asier@528
   803
         and f.ad_column_id = c.ad_column_id
juanpablo@1605
   804
         and ad_reference_id = '28'
asier@528
   805
         and ad_reference_value_id is not null
asier@528
   806
         and f.isdisplayed = 'Y'
asier@528
   807
         and f.isactive='Y'
asier@528
   808
      ]]>
asier@528
   809
    </Sql>
asier@528
   810
    <Parameter name="tab"/>
asier@528
   811
  </SqlMethod>
asier@528
   812
  <SqlMethod name="hasButtonFixed" type="preparedStatement" return="String" default="0">
asier@528
   813
    <SqlMethodComment>Checks if the tab has action buttons</SqlMethodComment>
asier@528
   814
    <Sql>
asier@528
   815
      <![CDATA[
asier@528
   816
          select count(*) as total
asier@528
   817
        from ad_field f,
asier@528
   818
             ad_column c
juanpablo@1605
   819
       where f.ad_tab_id = ?
asier@528
   820
         and f.ad_column_id = c.ad_column_id
juanpablo@1605
   821
         and ad_reference_id = '28'
asier@528
   822
         and f.isdisplayed = 'Y'
asier@528
   823
         and f.isactive='Y'
asier@528
   824
      ]]>
asier@528
   825
    </Sql>
asier@528
   826
    <Parameter name="tab"/>
asier@528
   827
  </SqlMethod>
asier@528
   828
  </SqlClass>