src-wad/src/org/openbravo/wad/Fields_data.xsql
author Antonio Moreno <antonio.moreno@openbravo.com>
Mon, 04 Feb 2013 09:27:53 +0100
changeset 9910 6767429386d8
parent 9530 ee5bc772bdc7
permissions -rw-r--r--
Changes required to support PostgreSQL 9.2
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
priya@9072
     5
 * Version  1.1  (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. 
ggi@6701
    14
 * The Initial Developer of the Original Code is Openbravo SLU 
asier@6773
    15
 * All portions are Copyright (C) 2001-2010 Openbravo SLU 
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
asier@6126
    23
<SqlClass name="FieldsData" package="org.openbravo.wad" accessModifier="public">
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[
asier@1929
    28
      SELECT ad_column.ad_column_id as adColumnId, 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,
antonio@9910
    31
        REPLACE(ad_column.defaultvalue, 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, 
asier@4402
    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,
asier@9258
    39
        '' as tableModule, '' as columnModule, '' as clientcode, '' as isautosave, '' as ad_field_id
carlos@0
    40
      FROM ad_column left join ad_element on ad_column.ad_element_id = ad_element.ad_element_id,
carlos@0
    41
           ad_field,  ad_table
carlos@0
    42
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
carlos@0
    43
        AND ad_column.ad_table_id = ad_table.ad_table_id 
carlos@0
    44
        AND ad_column.isActive = 'Y' 
carlos@0
    45
        AND upper(ad_column.columnname) <> 'BINARYDATA' 
juanpablo@1605
    46
        AND ad_tab_id = ?
carlos@0
    47
      ORDER BY ad_field.SEQNO
carlos@0
    48
      ]]>
carlos@0
    49
    </Sql>
ivan@4810
    50
    <Field name="trytext" value="void" />
ivan@4810
    51
    <Field name="catchtext" value="void" />
carlos@0
    52
    <Parameter name="tab"/>
carlos@0
    53
  </SqlMethod>
carlos@0
    54
  <SqlMethod name="set" type="constant" return="multiple">
carlos@0
    55
      <SqlMethodComment>Create a registry</SqlMethodComment>
carlos@0
    56
      <Sql></Sql>
carlos@0
    57
  </SqlMethod>
carlos@0
    58
  <SqlMethod name="selectDescription" type="preparedStatement" return="multiple">
carlos@0
    59
    <SqlMethodComment>Description of the fields of a tab</SqlMethodComment>
carlos@0
    60
    <Sql>
carlos@0
    61
      SELECT f.Name As Name, f.isDisplayed as isdisplayed, f.isEncrypted, c.AD_REFERENCE_ID as reference, 
carlos@0
    62
      c.FIELDLENGTH, 'Medio' as Text_Align, f.displaylength, c.ColumnName, f.showInRelation 
carlos@0
    63
      FROM ad_field f, ad_column c 
carlos@0
    64
      WHERE f.ad_column_id = c.ad_column_id 
carlos@0
    65
      AND f.showInRelation = 'Y' 
juanpablo@1605
    66
      AND f.ad_tab_id = ? 
carlos@0
    67
      ORDER BY f.SEQNO
carlos@0
    68
    </Sql>
carlos@0
    69
    <Parameter name="tab"/>
carlos@0
    70
  </SqlMethod>
asier@6144
    71
  
carlos@0
    72
  <SqlMethod name="selectAuxiliar" type="preparedStatement" return="multiple">
carlos@0
    73
    <SqlMethodComment>Names of the columns and name of the fields of a tab</SqlMethodComment>
carlos@0
    74
    <Sql>
carlos@0
    75
      SELECT AD_AUXILIARINPUT_ID as reference, name as realname, name as columnname, name as name, 
carlos@0
    76
      code as defaultValue, 'x' as xmltext, '' as WHERECLAUSE
carlos@0
    77
      FROM ad_auxiliarinput 
juanpablo@1605
    78
      WHERE ad_tab_id = ?
carlos@0
    79
    </Sql>
carlos@0
    80
    <Parameter name="sql" optional="true" after="WHERE " text="code LIKE ? || '%' AND "/>
carlos@0
    81
    <Parameter name="tab"/>
carlos@0
    82
  </SqlMethod>
asier@6144
    83
  
carlos@0
    84
  <SqlMethod name="tableKeyColumnName" type="preparedStatement" return="multiple">
carlos@0
    85
      <SqlMethodComment>Name of the column key of the tab</SqlMethodComment>
carlos@0
    86
      <Sql><![CDATA[
carlos@0
    87
        SELECT ad_column.ColumnName As Name 
carlos@0
    88
        FROM ad_table, ad_column 
carlos@0
    89
        WHERE ad_table.ad_table_id = ad_column.ad_table_id
carlos@0
    90
          AND UPPER(ad_table.tablename) = UPPER(?) 
carlos@0
    91
          AND ad_column.isActive = 'Y'
carlos@0
    92
          and ad_column.iskey='Y'
carlos@0
    93
        UNION
carlos@0
    94
        SELECT ad_column.ColumnName As Name 
carlos@0
    95
        FROM ad_table, ad_column 
carlos@0
    96
        WHERE ad_table.ad_table_id = ad_column.ad_table_id
carlos@0
    97
          AND UPPER(ad_table.tablename) = UPPER(?) 
carlos@0
    98
          AND ad_column.isActive = 'Y'
carlos@0
    99
          and ad_column.issecondarykey='Y'
carlos@0
   100
      ]]></Sql>
carlos@0
   101
      <Parameter name="tablename"/>
carlos@0
   102
      <Parameter name="tablename"/>
carlos@0
   103
  </SqlMethod>
carlos@0
   104
  <SqlMethod name="keyColumnName" type="preparedStatement" return="multiple">
carlos@0
   105
      <SqlMethodComment>Name of the column key of the tab</SqlMethodComment>
carlos@0
   106
      <Sql><![CDATA[
carlos@0
   107
        SELECT ColumnName As Name, issecondarykey FROM ad_table, ad_column, ad_tab 
carlos@0
   108
        WHERE ad_table.ad_table_id = ad_column.ad_table_id
carlos@0
   109
          AND ad_tab.ad_table_id = ad_table.ad_table_id
juanpablo@1605
   110
          AND ad_tab_id = ? 
asier@5647
   111
          and iskey='Y'
carlos@0
   112
      ]]></Sql>
carlos@0
   113
      <Parameter name="tab"/>
carlos@0
   114
  </SqlMethod>
carlos@0
   115
  <SqlMethod name="parentsColumnNameSortTab" type="preparedStatement" return="multiple">
carlos@0
   116
      <SqlMethodComment>Name of the columns parent of the tab</SqlMethodComment>
carlos@0
   117
      <Sql><![CDATA[
asier@9258
   118
        SELECT AD_FIELD.AD_FIELD_ID, ColumnName AS NAME, AD_REFERENCE_id AS reference, ad_reference_value_id AS referencevalue,
carlos@0
   119
        (SELECT tableNAME FROM AD_TABLE, AD_TAB WHERE AD_TABLE.ad_table_id = AD_TAB.ad_table_id
asier@4402
   120
        AND AD_TAB.ad_tab_id=?) AS tablename,
asier@4402
   121
        (SELECT P.ad_module_id FROM AD_TABLE T, AD_PACKAGE P WHERE T.ad_table_id = AD_COLUMN.ad_table_id AND T.AD_PACKAGE_ID = P.AD_PACKAGE_ID) as tableModule,
asier@4402
   122
        AD_COLUMN.AD_Module_ID as columnModule
carlos@0
   123
        FROM AD_FIELD, AD_COLUMN 
juanpablo@1605
   124
        WHERE AD_FIELD.ad_column_id = AD_COLUMN.ad_column_id AND ad_table_id = ? AND isParent='Y' 
carlos@0
   125
      ]]></Sql>
carlos@0
   126
      <Parameter name="parentTab"/>
carlos@0
   127
      <Parameter name="adTableId"/>
carlos@0
   128
  </SqlMethod>
stefan@6352
   129
stefan@6352
   130
<!-- method copied to src/src/org/openbravo/erpCommon/businessUtility/AuditTrailPopup_data.xsql, both copies need to stay in sync -->
carlos@0
   131
  <SqlMethod name="parentsColumnName" type="preparedStatement" return="multiple">
carlos@0
   132
      <SqlMethodComment>Name of the columns parent of the tab</SqlMethodComment>
carlos@0
   133
      <Sql><![CDATA[
asier@9258
   134
        SELECT AD_FIELD.AD_FIELD_ID, ColumnName AS NAME, AD_REFERENCE_id AS reference, ad_reference_value_id AS referencevalue,
carlos@0
   135
        (SELECT tableNAME FROM AD_TABLE, AD_TAB WHERE AD_TABLE.ad_table_id = AD_TAB.ad_table_id
asier@4402
   136
        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,
asier@4402
   137
        (SELECT P.ad_module_id FROM AD_TABLE T, AD_PACKAGE P WHERE T.ad_table_id = AD_COLUMN.ad_table_id AND T.AD_PACKAGE_ID = P.AD_PACKAGE_ID) as tableModule,
asier@9530
   138
        AD_COLUMN.AD_Module_ID as columnModule, 
asier@9530
   139
          (SELECT c.issecondarykey 
asier@9530
   140
             FROM AD_COLUMN c, AD_FIELD f 
asier@9530
   141
            WHERE c.ad_column_id = f.ad_column_id AND (c.iskey='Y' OR c.issecondarykey='Y')
asier@9530
   142
              AND ad_tab_id=? AND UPPER(c.columnname) = UPPER(AD_COLUMN.columnname)) as issecondarykey 
carlos@0
   143
        FROM AD_FIELD, AD_COLUMN 
juanpablo@1605
   144
        WHERE AD_FIELD.ad_column_id = AD_COLUMN.ad_column_id AND ad_tab_id = ? AND isParent='Y' 
asier@9530
   145
        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
   146
        AND ad_tab_id=? AND UPPER(c.columnname) = UPPER(AD_COLUMN.columnname))
carlos@0
   147
      ]]></Sql>
carlos@0
   148
      <Parameter name="parentTab"/>
asier@431
   149
      <Parameter name="parentTab"/>
asier@431
   150
      <Parameter name="parentTab"/>
asier@9530
   151
      <Parameter name="parentTab"/>
carlos@0
   152
      <Parameter name="tab"/>
carlos@0
   153
      <Parameter name="parentTab"/>
carlos@0
   154
  </SqlMethod>
stefan@6352
   155
stefan@6352
   156
<!-- method copied to src/src/org/openbravo/erpCommon/businessUtility/AuditTrailPopup_data.xsql, both copies need to stay in sync -->
carlos@0
   157
  <SqlMethod name="parentsColumnReal" type="preparedStatement" return="multiple">
carlos@0
   158
      <SqlMethodComment>Name of the columns parent of the tab</SqlMethodComment>
carlos@0
   159
      <Sql>
asier@9258
   160
        SELECT AD_FIELD.AD_FIELD_ID, ColumnName AS NAME, AD_REFERENCE_id AS reference, ad_reference_value_id AS referencevalue,
carlos@0
   161
        (SELECT tableNAME FROM AD_TABLE, AD_TAB WHERE AD_TABLE.ad_table_id = AD_TAB.ad_table_id
asier@4402
   162
        AND AD_TAB.ad_tab_id=?) AS tablename,
asier@4402
   163
        (SELECT P.ad_module_id FROM AD_TABLE T, AD_PACKAGE P WHERE T.ad_table_id = AD_COLUMN.ad_table_id AND T.AD_PACKAGE_ID = P.AD_PACKAGE_ID) as tableModule,
asier@4402
   164
        AD_COLUMN.AD_Module_ID as columnModule
carlos@0
   165
        FROM AD_FIELD, AD_COLUMN 
juanpablo@1605
   166
        WHERE AD_FIELD.ad_column_id = AD_COLUMN.ad_column_id AND ad_tab_id = ?
asier@4402
   167
        AND (UPPER(columnname) IN (SELECT UPPER(columnname) 
asier@4402
   168
                                    FROM AD_FIELD, AD_COLUMN 
asier@4402
   169
                                   WHERE AD_FIELD.ad_column_id = AD_COLUMN.ad_column_id 
asier@4402
   170
                                     AND AD_COLUMN.iskey='Y' 
asier@4402
   171
                                     AND AD_FIELD.ad_tab_id=?)
asier@4402
   172
            OR (UPPER(columnname) LIKE 'EM_%'  
asier@4402
   173
               AND UPPER(SUBSTR(COLUMNNAME,4)) IN  (SELECT UPPER(columnname) 
asier@4402
   174
                                    FROM AD_FIELD, AD_COLUMN 
asier@4402
   175
                                   WHERE AD_FIELD.ad_column_id = AD_COLUMN.ad_column_id 
asier@4402
   176
                                     AND AD_COLUMN.iskey='Y' 
asier@4402
   177
                                     AND AD_FIELD.ad_tab_id=?)))
carlos@0
   178
      </Sql>
carlos@0
   179
      <Parameter name="parentTab"/>
carlos@0
   180
      <Parameter name="tab"/>
carlos@0
   181
      <Parameter name="parentTab"/>
asier@4402
   182
      <Parameter name="parentTab"/>
carlos@0
   183
  </SqlMethod>
asier@6144
   184
 
carlos@0
   185
  <SqlMethod name="tableName" type="preparedStatement" return="string">
carlos@0
   186
      <SqlMethodComment>Name of the table of the tab</SqlMethodComment>
carlos@0
   187
      <Sql>
carlos@0
   188
        SELECT ad_table.TABLEName FROM ad_tab, ad_table
juanpablo@1605
   189
        WHERE ad_table.ad_table_id = ad_tab.ad_table_id and ad_tab_id = ?
carlos@0
   190
      </Sql>
carlos@0
   191
      <Parameter name="tab"/>
carlos@0
   192
  </SqlMethod>
asier@1929
   193
carlos@0
   194
  <SqlMethod name="selectUpdatables" type="preparedStatement" return="multiple">
carlos@0
   195
    <SqlMethodComment>Names of the columns of the fields of a tab that are updatables</SqlMethodComment>
carlos@0
   196
    <Sql><![CDATA[
carlos@0
   197
      SELECT ColumnName As Name, '?' AS Xml_Format, ad_column.ad_reference_id as reference, 
carlos@0
   198
      ad_column.ad_reference_value_id as referencevalue FROM ad_field, ad_column 
carlos@0
   199
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
carlos@0
   200
      and UPPER(ad_column.columnname) not in ('CREATED', 'UPDATED', 'CREATEDBY', 'UPDATEDBY') 
juanpablo@1605
   201
      and ad_tab_id = ? 
carlos@0
   202
      AND upper(ad_column.columnname) <> 'BINARYDATA' 
carlos@0
   203
      AND ad_column.isEncrypted <> 'Y'
carlos@0
   204
      AND ad_column.isactive = 'Y'
carlos@0
   205
      ORDER BY ad_field.SEQNO
carlos@0
   206
    ]]></Sql>
carlos@0
   207
    <Parameter name="tab"/>
carlos@0
   208
  </SqlMethod>
asier@6144
   209
  
carlos@0
   210
  <SqlMethod name="columnIdentifier" type="preparedStatement" return="string">
carlos@0
   211
      <SqlMethodComment>Name of the table of the tab</SqlMethodComment>
carlos@0
   212
      <Sql>
carlos@0
   213
        SELECT MAX(columnname) AS NAME FROM AD_COLUMN, AD_TABLE 
asier@1929
   214
        WHERE AD_TABLE.TABLENAME = ?
carlos@0
   215
          AND AD_COLUMN.ad_table_id = AD_TABLE.ad_table_id
asier@1239
   216
          AND isidentifier = 'Y' 
asier@1929
   217
          AND SeqNo = (CASE TO_CHAR(AD_TABLE.TABLENAME) 
asier@1239
   218
                          WHEN 'C_PaySelectionCheck' THEN 2 
asier@1239
   219
                          ELSE (SELECT MIN(SeqNo) 
asier@1239
   220
                                  FROM AD_Column 
asier@1239
   221
                                 WHERE AD_Table_ID=AD_TABLE.AD_Table_ID 
asier@1239
   222
                                   AND IsIdentifier='Y')
asier@1239
   223
                           END)
carlos@0
   224
      </Sql>
carlos@0
   225
      <Parameter name="tableName"/>
carlos@0
   226
  </SqlMethod>
carlos@0
   227
  <SqlMethod name="identifierColumns" type="preparedStatement" return="multiple">
carlos@0
   228
      <SqlMethodComment>identifier columns of a table</SqlMethodComment>
carlos@0
   229
      <Sql>
carlos@0
   230
        SELECT ad_column.columnname as name, ad_reference_id as reference, ismandatory as required, 
carlos@0
   231
        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, 
asier@6126
   232
        ad_column.istranslated, ad_table.tablename, ad_reference_value_id
carlos@0
   233
        FROM ad_column left join ad_element on ad_column.ad_element_id = ad_element.ad_element_id, 
carlos@0
   234
             ad_table 
carlos@0
   235
        WHERE UPPER(ad_table.tablename) = UPPER(?)
carlos@0
   236
          AND ad_column.ad_table_id = ad_table.ad_table_id
carlos@0
   237
          AND isidentifier = 'Y' 
carlos@0
   238
        order by seqno
carlos@0
   239
      </Sql>
carlos@0
   240
      <Parameter name="tableName"/>
carlos@0
   241
  </SqlMethod>
carlos@0
   242
  <SqlMethod name="selectSequence" type="preparedStatement" return="multiple">
carlos@0
   243
    <SqlMethodComment>Names of the columns to order by</SqlMethodComment>
carlos@0
   244
    <Sql>
carlos@0
   245
      <![CDATA[
carlos@0
   246
      SELECT ColumnName As Name
carlos@0
   247
      FROM ad_field, ad_column
carlos@0
   248
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
juanpablo@1605
   249
        AND ad_tab_id = ?
carlos@0
   250
        AND ad_field.SORTNO is not null
carlos@0
   251
      ORDER BY ad_field.SORTNO, ad_field.SEQNO
carlos@0
   252
      ]]>
carlos@0
   253
    </Sql>
carlos@0
   254
    <Parameter name="tab"/>
carlos@0
   255
  </SqlMethod>
carlos@0
   256
  <SqlMethod name="isSOTrx" type="preparedStatement" return="String" default="N">
carlos@0
   257
    <SqlMethodComment>isSOTrx of the window</SqlMethodComment>
carlos@0
   258
    <Sql>
carlos@0
   259
      <![CDATA[
carlos@0
   260
      SELECT isSOTrx FROM AD_WINDOW, AD_TAB 
carlos@0
   261
      WHERE AD_TAB.AD_WINDOW_ID = AD_WINDOW.AD_WINDOW_ID 
juanpablo@1605
   262
      AND AD_TAB.AD_TAB_ID = ?
carlos@0
   263
      ]]>
carlos@0
   264
    </Sql>
carlos@0
   265
    <Parameter name="tab"/>
carlos@0
   266
  </SqlMethod>
carlos@0
   267
  <SqlMethod name="selectDefaultValue" type="preparedStatement" return="multiple">
carlos@0
   268
    <SqlMethodComment>Default values of the columns</SqlMethodComment>
carlos@0
   269
    <Sql>
carlos@0
   270
      <![CDATA[
asier@6929
   271
      SELECT ad_column.AD_COLUMN_ID as adColumnId, ad_column.ISMANDATORY as required, 
carlos@0
   272
      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, 
antonio@9910
   273
      REPLACE(ad_column.defaultvalue, CHR(10), '') as defaultValue, '' as WHERECLAUSE, 
asier@6929
   274
      ad_reference_id as reference,
carlos@0
   275
      isdisplayed, ad_column.columnname as columnname, ad_table.ACCESSLEVEL, 
asier@6929
   276
      ad_table.TABLENAME as NameRef, ad_reference_value_id as referencevalue, ad_column.name as realname
carlos@0
   277
      FROM ad_field, ad_column, ad_table
carlos@0
   278
      WHERE ad_field.ad_column_id = ad_column.ad_column_id
asier@6144
   279
        AND ad_column.AD_TABLE_ID = ad_table.AD_TABLE_ID 
juanpablo@1605
   280
        AND ad_tab_id = ? 
asier@1879
   281
        AND upper(ad_column.columnname) <> 'BINARYDATA'
asier@1879
   282
      union
asier@1879
   283
      SELECT ad_column.AD_COLUMN_ID as reference, ad_column.ISMANDATORY as required, 
stefan@1886
   284
      columnname as Name, 
antonio@9910
   285
      REPLACE(ad_column.defaultvalue, CHR(10), '') as defaultValue, '' as WHERECLAUSE, 
asier@1879
   286
      ad_reference_id as referencevalue,
asier@1879
   287
      'Y', ad_column.columnname as columnname, ad_table.ACCESSLEVEL, 
asier@1879
   288
      ad_table.TABLENAME as NameRef, ad_reference_value_id as type, ad_column.name as realname
asier@1879
   289
      FROM ad_column, ad_table, ad_tab t
asier@1879
   290
      WHERE lower(columnname) in ('createdby', 'updatedby')
asier@1879
   291
      AND ad_column.AD_TABLE_ID = ad_table.AD_TABLE_ID 
asier@1885
   292
        AND t.ad_tab_id = ?
asier@1879
   293
        and ad_table.ad_table_id = t.ad_table_id
stefan@8721
   294
      ORDER BY adcolumnid
carlos@0
   295
      ]]>
carlos@0
   296
    </Sql>
carlos@0
   297
    <Parameter name="sql" optional="true" after="WHERE " text="ad_column.defaultvalue LIKE ? || '%' AND "/>
carlos@0
   298
    <Parameter name="tab"/>
asier@1885
   299
    <Parameter name="tab"/>
carlos@0
   300
  </SqlMethod>
carlos@0
   301
  <SqlMethod name="selectSession" type="preparedStatement" return="multiple">
carlos@0
   302
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   303
    <Sql>
carlos@0
   304
      <![CDATA[
carlos@0
   305
      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
   306
        ad_reference_id as reference, ad_reference_value_id as referencevalue, 'x' as xmltext,
carlos@0
   307
        ismandatory as required, isDisplayed as isdisplayed, isupdateable as isupdateable,
carlos@0
   308
        ad_column.defaultvalue As defaultValue, ad_column.fieldlength As fieldLength,
carlos@0
   309
        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
   310
      FROM ad_field, ad_column left join ad_element on ad_column.ad_element_id = ad_element.ad_element_id
carlos@0
   311
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
juanpablo@1605
   312
        AND ad_tab_id = ?
carlos@0
   313
        AND (ad_column.ISSESSIONATTR = 'Y' 
carlos@0
   314
        OR ad_column.iskey = 'Y') 
carlos@0
   315
        AND upper(ad_column.columnname) <> 'BINARYDATA' 
carlos@0
   316
        AND ad_column.ColumnName <> 'Created'
carlos@0
   317
      ORDER BY ad_field.SEQNO
carlos@0
   318
      ]]>
carlos@0
   319
    </Sql>
carlos@0
   320
    <Parameter name="tab"/>
carlos@0
   321
  </SqlMethod>
carlos@0
   322
  <SqlMethod name="selectDocumentsNo" type="preparedStatement" return="multiple">
carlos@0
   323
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   324
    <Sql>
carlos@0
   325
      <![CDATA[
carlos@0
   326
      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
   327
      ad_column.defaultvalue as defaultValue, '' as WHERECLAUSE, ad_reference_id as referencevalue,
carlos@0
   328
      isdisplayed, ad_column.columnname as columnname, ad_table.ACCESSLEVEL, 
carlos@0
   329
      ad_table.TABLENAME as NameRef, '' as realname
carlos@0
   330
      FROM ad_field, ad_column, ad_table
carlos@0
   331
      WHERE ad_field.ad_column_id = ad_column.ad_column_id
asier@6144
   332
        AND ad_column.AD_TABLE_ID = ad_table.AD_TABLE_ID 
juanpablo@1605
   333
        AND ad_tab_id = ? 
carlos@0
   334
        and isParent='N'
carlos@0
   335
        and ad_column.columnname = 'DocumentNo'
carlos@0
   336
        and ad_column.defaultvalue is null
carlos@0
   337
      ORDER BY ad_field.SEQNO
carlos@0
   338
      ]]>
carlos@0
   339
    </Sql>
carlos@0
   340
    <Parameter name="tab"/>
carlos@0
   341
  </SqlMethod>
carlos@0
   342
  <SqlMethod name="selectActionButton" type="preparedStatement" return="multiple">
carlos@0
   343
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   344
    <Sql>
carlos@0
   345
      <![CDATA[
carlos@0
   346
      SELECT distinct ad_column.columnname, 
carlos@0
   347
      ad_column.ad_process_id as reference, ad_process.procedurename as name, 
carlos@0
   348
      ad_column.ad_reference_id as type, ad_column.ad_reference_value_id as referencevalue,
carlos@0
   349
      ad_val_rule_id as defaultvalue, ad_column.fieldlength, ad_field.name as realname, 
ivan@7065
   350
      ad_process.description as tablename, ad_process.help as xmltext, ad_column.ad_reference_value_id, ad_process.isjasper, ad_column.isautosave
carlos@0
   351
      FROM ad_column, ad_process, ad_field
carlos@0
   352
      where ad_column.ad_process_id = ad_process.ad_process_id 
asier@3907
   353
      and ad_column.ad_column_id = ad_field.ad_column_id  
asier@3946
   354
      AND (ad_process.procedurename is not null 
asier@3907
   355
           or ad_process.isjasper = 'Y'
asier@3907
   356
           or (UIPattern='S' AND EXISTS (SELECT 1 FROM AD_MODEL_OBJECT WHERE AD_PROCESS_ID = AD_PROCESS.AD_PROCESS_ID))) 
carlos@0
   357
      and ad_column.columnname not in('DocAction', 'PaymentRule') 
carlos@0
   358
      and (ad_column.columnname <> 'CreateFrom'
carlos@0
   359
      or ad_column.ad_process_id is not null)
carlos@0
   360
      and (ad_column.columnname <> 'Posted'
carlos@0
   361
      or ad_column.ad_process_id is not null)
carlos@0
   362
      and ad_column.ad_column_id in (select ad_column_id from ad_field where isdisplayed='Y' and isactive='Y')
carlos@0
   363
      and ad_column.isactive='Y'
carlos@0
   364
      order by ad_column.ad_process_id
carlos@0
   365
      ]]>
carlos@0
   366
    </Sql>
carlos@0
   367
  </SqlMethod>
asier@1929
   368
  
asier@2836
   369
  <SqlMethod name="buildActionButton" type="preparedStatement" return="boolean">
asier@2836
   370
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
asier@2836
   371
    <Sql>
asier@2836
   372
      <![CDATA[
asier@2836
   373
      SELECT count(*) as total
asier@2836
   374
      FROM ad_column, ad_process, ad_field, ad_system_info si
asier@2836
   375
      where ad_column.ad_process_id = ad_process.ad_process_id 
asier@2836
   376
      and ad_column.ad_column_id = ad_field.ad_column_id 
asier@3946
   377
      AND (ad_process.procedurename is not null 
asier@3907
   378
           or ad_process.isjasper = 'Y'
asier@3907
   379
           or (UIPattern='S' AND EXISTS (SELECT 1 FROM AD_MODEL_OBJECT WHERE AD_PROCESS_ID = AD_PROCESS.AD_PROCESS_ID)))
asier@2836
   380
      and ad_column.columnname not in('DocAction', 'PaymentRule') 
asier@2836
   381
      and (ad_column.columnname <> 'CreateFrom'
asier@2836
   382
      or ad_column.ad_process_id is not null)
asier@2836
   383
      and (ad_column.columnname <> 'Posted'
asier@2836
   384
      or ad_column.ad_process_id is not null)
asier@2836
   385
      and ad_column.ad_column_id in (select ad_column_id from ad_field where isdisplayed='Y' and isactive='Y')
asier@2836
   386
      and ad_column.isactive='Y'
asier@2836
   387
      and (ad_process.updated > si.last_build
asier@2836
   388
           or exists (select 1 
asier@2836
   389
                        from ad_process_para pp 
asier@2836
   390
                       where pp.ad_process_id = ad_process.ad_process_id
asier@2836
   391
                         and pp.updated > si.last_build))
asier@2836
   392
      ]]>
asier@2836
   393
    </Sql>
asier@2836
   394
  </SqlMethod>
asier@2836
   395
  
carlos@0
   396
  <SqlMethod name="selectActionButtonGenerics" type="preparedStatement" return="multiple">
carlos@0
   397
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   398
    <Sql>
carlos@0
   399
      <![CDATA[
carlos@0
   400
        SELECT DISTINCT 'ActionButton'  AS columnname, 
carlos@0
   401
        ad_process_id AS reference, procedurename AS NAME, 
carlos@0
   402
        '' AS TYPE, '' AS referencevalue,
carlos@0
   403
        '' AS defaultvalue, '' AS fieldlength, '' AS realname, 
ivan@7065
   404
        description AS tablename, help AS xmltext, isjasper, '' AS isautosave
carlos@0
   405
        FROM AD_PROCESS
carlos@0
   406
        WHERE isactive='Y'
asier@2202
   407
        AND UIPattern='S'
carlos@0
   408
        and ad_process_id in (select ad_process_id from ad_menu)
carlos@0
   409
        ORDER BY ad_process_id
carlos@0
   410
      ]]>
carlos@0
   411
    </Sql>
carlos@0
   412
  </SqlMethod>
asier@1929
   413
  
carlos@0
   414
  <SqlMethod name="isHighVolume" type="preparedStatement" return="String">
carlos@0
   415
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   416
    <Sql>
carlos@0
   417
      <![CDATA[
carlos@0
   418
      SELECT ishighvolume FROM AD_TAB, AD_TABLE 
carlos@0
   419
      WHERE AD_TAB.ad_table_id = AD_TABLE.ad_table_id
juanpablo@1605
   420
      AND AD_TAB.ad_tab_id=?
carlos@0
   421
      ]]>
carlos@0
   422
    </Sql>
carlos@0
   423
    <Parameter name="tab"/>
carlos@0
   424
  </SqlMethod>
carlos@0
   425
  <SqlMethod name="isSingleRow" type="preparedStatement" return="String">
carlos@0
   426
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   427
    <Sql>
carlos@0
   428
      <![CDATA[
carlos@0
   429
      SELECT issinglerow FROM AD_TAB 
juanpablo@1605
   430
      WHERE ad_tab_id=?
carlos@0
   431
      ]]>
carlos@0
   432
    </Sql>
carlos@0
   433
    <Parameter name="tab"/>
carlos@0
   434
  </SqlMethod>
carlos@0
   435
  <SqlMethod name="hasCreateFromButton" type="preparedStatement" return="String" default="0">
carlos@0
   436
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   437
    <Sql>
carlos@0
   438
      <![CDATA[
juanpablo@1605
   439
        SELECT coalesce(ad_column.ad_process_id, '-1') AS total 
carlos@0
   440
        FROM AD_FIELD, AD_COLUMN, AD_TABLE
carlos@0
   441
        WHERE AD_FIELD.ad_column_id = AD_COLUMN.ad_column_id
carlos@0
   442
        AND AD_COLUMN.AD_TABLE_ID = AD_TABLE.AD_TABLE_ID 
juanpablo@1605
   443
        AND ad_tab_id = ? 
juanpablo@1605
   444
        AND AD_COLUMN.ad_reference_id = '28'
carlos@0
   445
        AND AD_COLUMN.COLUMNNAME = 'CreateFrom'
carlos@0
   446
        AND AD_COLUMN.AD_PROCESS_ID IS NULL
carlos@0
   447
        AND AD_FIELD.ISDISPLAYED = 'Y'
carlos@0
   448
      ]]>
carlos@0
   449
    </Sql>
carlos@0
   450
    <Parameter name="tab"/>
carlos@0
   451
  </SqlMethod>
carlos@0
   452
  <SqlMethod name="hasPostedButton" type="preparedStatement" return="String" default="0">
carlos@0
   453
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   454
    <Sql>
carlos@0
   455
      <![CDATA[
juanpablo@1605
   456
        SELECT coalesce(ad_column.ad_process_id, '-1') AS total 
carlos@0
   457
        FROM AD_FIELD, AD_COLUMN, AD_TABLE
carlos@0
   458
        WHERE AD_FIELD.ad_column_id = AD_COLUMN.ad_column_id
carlos@0
   459
        AND AD_COLUMN.AD_TABLE_ID = AD_TABLE.AD_TABLE_ID 
juanpablo@1605
   460
        AND ad_tab_id = ? 
juanpablo@1605
   461
        AND AD_COLUMN.ad_reference_id = '28'
carlos@0
   462
        AND AD_COLUMN.COLUMNNAME = 'Posted'
carlos@0
   463
        AND AD_COLUMN.AD_PROCESS_ID IS NULL
carlos@0
   464
        AND AD_FIELD.ISDISPLAYED = 'Y'
carlos@0
   465
      ]]>
carlos@0
   466
    </Sql>
carlos@0
   467
    <Parameter name="tab"/>
carlos@0
   468
  </SqlMethod>
carlos@0
   469
  <SqlMethod name="selectValidationTab" type="preparedStatement" return="multiple">
stefan@3988
   470
    <SqlMethodComment>Method need to be synchronized with src/org/openbravo/erpCommon/businessUtility/Buscador_data.xsql::selectValidationTab</SqlMethodComment>
carlos@0
   471
    <Sql>
carlos@0
   472
      <![CDATA[
asier@683
   473
        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
   474
        c.ad_reference_value_id as NameRef, c.ad_val_rule_id as defaultvalue, f.isdisplayed, c.istranslated, c.columnname as name,
asier@683
   475
        (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
   476
        FROM AD_FIELD f, 
carlos@0
   477
             AD_COLUMN c left join  AD_VAL_RULE v on c.AD_VAL_RULE_ID = v.AD_VAL_RULE_ID
juanpablo@1605
   478
                         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
   479
        WHERE f.AD_COLUMN_ID = c.ad_column_id
juanpablo@1605
   480
        AND f.ad_tab_id = ? 
carlos@0
   481
        AND (t.whereclause IS NOT NULL
asier@683
   482
            OR v.code IS NOT NULL
juanpablo@1605
   483
            OR c.ad_reference_id in ('19','18','17'))
carlos@0
   484
      ]]>
carlos@0
   485
    </Sql>
carlos@0
   486
    <Parameter name="tab"/>
carlos@0
   487
  </SqlMethod>
asier@4039
   488
  
asier@4039
   489
  <SqlMethod name="selectProcessesWithReloads" type="preparedStatement" return="multiple">
asier@4039
   490
    <SqlMethodComment>Obtains all processes that might require combo reload</SqlMethodComment>
asier@4039
   491
    <Sql>
asier@4039
   492
      <![CDATA[
asier@4039
   493
         SELECT distinct c.AD_PROCESS_id as id
asier@4039
   494
        FROM AD_PROCESS_PARA c left join  AD_VAL_RULE v on c.AD_VAL_RULE_ID = v.AD_VAL_RULE_ID
asier@4039
   495
                         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     
asier@4039
   496
        WHERE (t.whereclause IS NOT NULL
asier@4039
   497
            OR v.code IS NOT NULL
asier@4039
   498
            OR c.ad_reference_id in ('19','18','17'))
asier@4039
   499
      ]]>
asier@4039
   500
    </Sql>
asier@4039
   501
  </SqlMethod>
asier@4039
   502
  
asier@4039
   503
  <SqlMethod name="selectValidationProcess" type="preparedStatement" return="multiple">
asier@4039
   504
    <SqlMethodComment>Obtains all param for processes that might require combo reload</SqlMethodComment>
asier@4039
   505
    <Sql>
asier@4039
   506
      <![CDATA[
asier@4039
   507
         SELECT c.AD_PROCESS_PARA_id as id, c.columnname, t.WHERECLAUSE as whereClause, v.code as referencevalue, c.ad_reference_id as reference,
asier@4039
   508
        c.ad_reference_value_id as NameRef, c.ad_val_rule_id as defaultvalue,  c.columnname as name,
asier@4039
   509
        (case when t.whereclause is not null or v.code is not null then 'C' else 'R' end) as type /*Combo reaload or Reference*/ 
asier@4039
   510
        FROM AD_PROCESS_PARA c left join  AD_VAL_RULE v on c.AD_VAL_RULE_ID = v.AD_VAL_RULE_ID
asier@4039
   511
                         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     
asier@4039
   512
        WHERE (t.whereclause IS NOT NULL
asier@4039
   513
            OR v.code IS NOT NULL
asier@4039
   514
            OR c.ad_reference_id in ('19','18','17'))
asier@4039
   515
         AND c.AD_Process_ID = ?
asier@4039
   516
      ]]>
asier@4039
   517
    </Sql>
asier@4039
   518
    <Parameter name="processId"/>
asier@4039
   519
  </SqlMethod>
asier@4039
   520
  
asier@7115
   521
    <SqlMethod name="processHasOrgParam" type="preparedStatement" return="boolean">
asier@7115
   522
    <Sql>
asier@7115
   523
      <![CDATA[
asier@7115
   524
         select count(*)
asier@7115
   525
           from ad_process_para 
asier@7115
   526
          where lower(columnname) = 'ad_org_id'
asier@7115
   527
            and ad_process_id = ?
asier@7115
   528
      ]]>
asier@7115
   529
    </Sql>
asier@7115
   530
    <Parameter name="processId"/>
asier@7115
   531
  </SqlMethod>
asier@7115
   532
  
carlos@0
   533
  <SqlMethod name="selectColumnTable" type="preparedStatement" return="multiple">
carlos@0
   534
    <SqlMethodComment>Fields of reference table in a tab</SqlMethodComment>
carlos@0
   535
    <Sql>
carlos@0
   536
      SELECT ColumnName As Name, 'x' as xmltext, ad_reference_value_id as referencevalue, 
carlos@0
   537
      ad_val_rule.CODE as defaultValue, ad_val_rule.AD_VAL_RULE_ID as columnname,
carlos@0
   538
      ad_ref_table.WHERECLAUSE, ad_table.tablename, 'TableList' as tablenametrl, ad_table.name as nameref, 
carlos@0
   539
      '18' as reference, 'Y' as required, ad_column.istranslated 
carlos@0
   540
      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
   541
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
carlos@0
   542
      AND ad_ref_table.ad_table_id = ad_table.ad_table_id 
carlos@0
   543
      AND ad_column.AD_REFERENCE_VALUE_ID = ad_ref_table.AD_REFERENCE_ID  
carlos@0
   544
      and ad_field.ISDISPLAYED = 'Y'
juanpablo@1605
   545
      AND ad_field.ad_tab_id = ?
juanpablo@1605
   546
      AND ad_column.ad_column_id = ? 
juanpablo@1605
   547
      AND ad_column.ad_reference_id = '18'
carlos@0
   548
    </Sql>
carlos@0
   549
    <Parameter name="adTabId"/>
carlos@0
   550
    <Parameter name="adColumnId"/>
carlos@0
   551
  </SqlMethod>
asier@4039
   552
  
asier@4039
   553
  <SqlMethod name="selectColumnTableProcess" type="preparedStatement" return="multiple">
asier@4039
   554
    <Sql>
asier@4039
   555
      SELECT ColumnName As Name, 'x' as xmltext, ad_reference_value_id as referencevalue, 
asier@4039
   556
      ad_val_rule.CODE as defaultValue, ad_val_rule.AD_VAL_RULE_ID as columnname,
asier@4039
   557
      ad_ref_table.WHERECLAUSE, ad_table.tablename, 'TableList' as tablenametrl, ad_table.name as nameref, 
asier@4045
   558
      '18' as reference, 'Y' as required, 'N'as istranslated 
asier@4039
   559
      FROM ad_process_para p left join ad_val_rule on p.AD_VAL_RULE_ID = ad_val_rule.AD_VAL_RULE_ID, 
asier@4039
   560
           ad_ref_table, 
asier@4039
   561
           ad_table 
asier@4039
   562
      WHERE ad_ref_table.ad_table_id = ad_table.ad_table_id 
asier@4039
   563
      AND p.AD_REFERENCE_VALUE_ID = ad_ref_table.AD_REFERENCE_ID  
asier@4039
   564
      AND p.ad_process_para_id = ?
asier@4039
   565
      AND p.ad_reference_id = '18'
asier@4039
   566
    </Sql>
asier@4039
   567
    <Parameter name="processParaId"/>
asier@4039
   568
  </SqlMethod>
asier@4039
   569
  
carlos@0
   570
  <SqlMethod name="selectColumnTableDir" type="preparedStatement" return="multiple">
carlos@0
   571
    <SqlMethodComment>Fields of reference table in a tab</SqlMethodComment>
carlos@0
   572
    <Sql>
carlos@0
   573
      SELECT ColumnName As Name, 'x' as xmltext, ColumnName as referencevalue, 
carlos@0
   574
      ad_val_rule.CODE as defaultValue, ad_val_rule.AD_VAL_RULE_ID as columnname, ad_table.tablename, 
carlos@0
   575
      'TableDir' as tablenametrl, '' as WHERECLAUSE, '19' as reference, ad_column.istranslated 
carlos@0
   576
      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
   577
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
carlos@0
   578
      and ad_column.ad_table_id = ad_table.ad_table_id 
carlos@0
   579
      and ad_field.ISDISPLAYED = 'Y'
juanpablo@1605
   580
      AND ad_field.ad_tab_id = ?
juanpablo@1605
   581
      AND ad_column.ad_column_id = ? 
juanpablo@1605
   582
      AND ad_reference_id = '19'
carlos@0
   583
    </Sql>
carlos@0
   584
    <Parameter name="adTabId"/>
carlos@0
   585
    <Parameter name="adColumnId"/>
carlos@0
   586
  </SqlMethod>
asier@4039
   587
  
asier@4039
   588
  <SqlMethod name="selectColumnTableDirProcess" type="preparedStatement" return="multiple">
asier@4039
   589
    <Sql>
asier@4039
   590
      SELECT ColumnName As Name, 'x' as xmltext, ColumnName as referencevalue, 
asier@4039
   591
      ad_val_rule.CODE as defaultValue, ad_val_rule.AD_VAL_RULE_ID as columnname,  
asier@4039
   592
      'TableDir' as tablenametrl, '' as WHERECLAUSE, '19' as reference, 'N' as istranslated 
asier@4039
   593
      FROM ad_process_para p left join ad_val_rule on p.AD_VAL_RULE_ID = ad_val_rule.AD_VAL_RULE_ID
asier@4039
   594
      WHERE p.ad_process_para_id = ?
asier@4039
   595
      AND ad_reference_id = '19'
asier@4039
   596
    </Sql>
asier@4039
   597
    <Parameter name="processParaId"/>
asier@4039
   598
  </SqlMethod>
asier@4039
   599
  
carlos@0
   600
  <SqlMethod name="columnName" type="preparedStatement" return="String" default="">
carlos@0
   601
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   602
    <Sql>
carlos@0
   603
      <![CDATA[
carlos@0
   604
      SELECT columnname FROM AD_column 
juanpablo@1605
   605
      WHERE ad_column_id=?
carlos@0
   606
      ]]>
carlos@0
   607
    </Sql>
carlos@0
   608
    <Parameter name="adColumnId"/>
carlos@0
   609
  </SqlMethod>
carlos@0
   610
  <SqlMethod name="selectIdentify" type="preparedStatement" return="multiple">
carlos@0
   611
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   612
    <Sql>
carlos@0
   613
      <![CDATA[
carlos@0
   614
      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
   615
      ad_column.defaultvalue as defaultValue, '' as WHERECLAUSE, ad_reference_id as referencevalue,
carlos@0
   616
      isdisplayed, ad_column.columnname as columnname, ad_table.ACCESSLEVEL, 
carlos@0
   617
      ad_table.TABLENAME as NameRef, '' as realname, ad_column.ISSESSIONATTR, ad_column.istranslated 
carlos@0
   618
      FROM ad_field, ad_column, ad_table
carlos@0
   619
      WHERE ad_field.ad_column_id = ad_column.ad_column_id
asier@6144
   620
        AND ad_column.AD_TABLE_ID = ad_table.AD_TABLE_ID 
juanpablo@1605
   621
        AND ad_tab_id = ? 
carlos@0
   622
        and ismandatory='Y' 
carlos@0
   623
        AND upper(ad_column.columnname) = 'VALUE'
carlos@0
   624
      ORDER BY ad_field.SEQNO
carlos@0
   625
      ]]>
carlos@0
   626
    </Sql>
carlos@0
   627
    <Parameter name="tab"/>
carlos@0
   628
  </SqlMethod>
carlos@0
   629
  <SqlMethod name="selectParentWhereClause" type="preparedStatement" return="String" default="">
carlos@0
   630
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   631
    <Sql>
carlos@0
   632
      <![CDATA[
carlos@0
   633
      SELECT whereclause
carlos@0
   634
      FROM ad_tab
juanpablo@1605
   635
      WHERE ad_tab_id = ? 
carlos@0
   636
      ]]>
carlos@0
   637
    </Sql>
carlos@0
   638
    <Parameter name="tab"/>
carlos@0
   639
  </SqlMethod>
carlos@0
   640
  <SqlMethod name="hasEncryptionFields" type="preparedStatement" return="String" default="0">
carlos@0
   641
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   642
    <Sql>
carlos@0
   643
      <![CDATA[
carlos@0
   644
        SELECT count(*) AS total 
carlos@0
   645
        FROM AD_FIELD, AD_COLUMN
juanpablo@1605
   646
        WHERE AD_FIELD.ad_tab_id = ? 
carlos@0
   647
        AND ad_field.ad_column_id = ad_column.ad_column_id 
carlos@0
   648
        AND ad_column.isEncrypted = 'Y' 
carlos@0
   649
        AND ad_field.ISDISPLAYED = 'Y'
carlos@0
   650
      ]]>
carlos@0
   651
    </Sql>
carlos@0
   652
    <Parameter name="tab"/>
carlos@0
   653
  </SqlMethod>
carlos@0
   654
  <SqlMethod name="selectEncrypted" type="preparedStatement" return="multiple">
carlos@0
   655
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   656
    <Sql>
carlos@0
   657
      <![CDATA[
carlos@0
   658
      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
   659
      FROM ad_field, ad_column
carlos@0
   660
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
carlos@0
   661
        AND ad_column.isEncrypted = 'Y' 
carlos@0
   662
        AND ad_field.ISDISPLAYED = 'Y'
juanpablo@1605
   663
        AND ad_tab_id = ?
carlos@0
   664
      ORDER BY ad_field.SEQNO
carlos@0
   665
      ]]>
carlos@0
   666
    </Sql>
carlos@0
   667
    <Parameter name="tab"/>
carlos@0
   668
  </SqlMethod>
carlos@0
   669
  <SqlMethod name="selectButton" type="preparedStatement" return="multiple">
carlos@0
   670
    <SqlMethodComment>Names of the columns of the fields of a tab</SqlMethodComment>
carlos@0
   671
    <Sql>
carlos@0
   672
      <![CDATA[
carlos@0
   673
      select ad_reference_value_id as id, columnname, columnname || '_BTN' as realName 
carlos@0
   674
      from ad_field f, ad_column c
carlos@0
   675
      where f.ad_column_id = c.ad_column_id 
juanpablo@1605
   676
      and f.ad_tab_id = ?
carlos@0
   677
      and f.isactive = 'Y'
carlos@0
   678
      and f.isdisplayed = 'Y'
carlos@0
   679
      and c.isactive = 'Y'
carlos@0
   680
      and ad_reference_value_id is not null
juanpablo@1605
   681
      and c.ad_reference_id = '28' 
carlos@0
   682
      and c.columnname <> 'ChangeProjectStatus'
carlos@0
   683
      ]]>
carlos@0
   684
    </Sql>
carlos@0
   685
    <Parameter name="tab"/>
carlos@0
   686
  </SqlMethod>
carlos@0
   687
  <SqlMethod name="hasActionButton" type="preparedStatement" return="String" default="0">
carlos@0
   688
    <SqlMethodComment>Checks if the tab has action buttons</SqlMethodComment>
carlos@0
   689
    <Sql>
carlos@0
   690
      <![CDATA[
carlos@0
   691
      select count(*) as actionButtons
carlos@0
   692
      from ad_field f, ad_column c
carlos@0
   693
      where f.ad_column_id = c.ad_column_id 
juanpablo@1605
   694
      and f.ad_tab_id = ?
carlos@0
   695
      and f.isactive = 'Y'
carlos@0
   696
      and f.isdisplayed = 'Y'
carlos@0
   697
      and c.isactive = 'Y'
carlos@0
   698
      and ad_reference_value_id is not null
juanpablo@1605
   699
      and c.ad_reference_id = '28' 
carlos@0
   700
      ]]>
carlos@0
   701
    </Sql>
carlos@0
   702
    <Parameter name="tab"/>
asier@528
   703
  </SqlMethod>
asier@6144
   704
asier@528
   705
  <SqlMethod name="hasButtonFixed" type="preparedStatement" return="String" default="0">
asier@528
   706
    <SqlMethodComment>Checks if the tab has action buttons</SqlMethodComment>
asier@528
   707
    <Sql>
asier@528
   708
      <![CDATA[
asier@528
   709
          select count(*) as total
asier@528
   710
        from ad_field f,
asier@528
   711
             ad_column c
juanpablo@1605
   712
       where f.ad_tab_id = ?
asier@528
   713
         and f.ad_column_id = c.ad_column_id
juanpablo@1605
   714
         and ad_reference_id = '28'
asier@528
   715
         and f.isdisplayed = 'Y'
asier@528
   716
         and f.isactive='Y'
asier@528
   717
      ]]>
asier@528
   718
    </Sql>
asier@528
   719
    <Parameter name="tab"/>
asier@528
   720
  </SqlMethod>
asier@528
   721
  </SqlClass>