src-wad/src/org/openbravo/wad/EditionFields_data.xsql
author Juan Pablo Aroztegi <juanpablo.aroztegi@openbravo.com>
Wed, 03 Sep 2008 17:55:37 +0000
changeset 1605 8a0fe0193bef
parent 814 d050def3cfbe
child 1929 ad0353d93b0f
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 
asier@814
    15
 * All portions are Copyright (C) 2001-2008 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="EditionFieldsData" package="org.openbravo.wad">
carlos@0
    24
  <SqlMethod name="select" type="preparedStatement" return="multiple">
carlos@0
    25
    <SqlMethodComment>Names of the columns and name of the fields of a tab</SqlMethodComment>
carlos@0
    26
    <Sql><![CDATA[
asier@217
    27
      SELECT ad_field.seqno, ad_column.ColumnName As columnName, ad_column.ColumnName As columnNameInp, 
carlos@0
    28
      ad_field.name as Name, ismandatory as required, ad_reference_value_id as referenceValue, 
carlos@0
    29
      ad_column.ad_reference_id as reference, displayLength as displaySize, 'x' as htmltext, '' as xmltext, 
carlos@0
    30
      isSameLine as issameline, (CASE ad_field.isActive WHEN 'N' THEN 'N' ELSE isDisplayed END) as isdisplayed, isupdateable as isupdateable, 
juanpablo@1605
    31
      isparent, ad_column.FIELDLENGTH, (CASE WHEN (ad_column.AD_REFERENCE_ID<>'30' OR AD_REFERENCE_VALUE_ID IS NULL) THEN REPLACE(REPLACE(REPLACE(ad_element.name, 'Substitute', 'Product'), 'BOM', ''), 'M_LocatorTo_ID', 'M_Locator_ID') ELSE (SELECT NAME FROM AD_REFERENCE WHERE AD_REFERENCE.AD_REFERENCE_ID=ad_column.AD_REFERENCE_VALUE_ID) END) as searchName, ad_column.ad_callout_id, 
carlos@259
    32
      ad_column.ad_process_id, ad_field.isreadonly, to_char(ad_field.DISPLAYLOGIC) as displaylogic, ad_field.isEncrypted, AD_FIELD.AD_FIELDGROUP_ID AS FieldGroup, ad_field.ad_tab_id as tabid, '' as ad_column_id, ad_column.ad_val_rule_id, '' as displaylength, '' as value, '' as ad_window_id, 
carlos@0
    33
      AD_COLUMN.ValueMin, AD_COLUMN.ValueMax, REPLACE(REPLACE(REPLACE(REPLACE(ad_column.columnname, 'Substitute', 'M_Product'), 'C_Settlement_Cancel_ID', 'C_Settlement_ID'), 'BOM', ''), 'M_LocatorTo_ID', 'M_Locator_ID') as realName, mom.mappingname as java_class_name, '' as realcolumnname, '' as xsqltext, ad_column.isEncrypted AS isColumnEncrypted, ad_column.isDesencryptable, 
carlos@0
    34
      ad_reference.name as reference_name, ad_reference.name as reference_name_trl, '' AS CLASSNAME, '' AS MAPPINGNAME, ad_callout.name as calloutname, 
asier@227
    35
      moc.classname as classname_callout, momc.mappingname as mappingname_callout, '' AS COLUMNNAME_END, ad_field.isactive, ad_column.readonlylogic 
carlos@0
    36
      FROM ad_column left join ad_element on ad_column.ad_element_id = ad_element.ad_element_id
carlos@0
    37
                     left join ad_process on ad_column.ad_process_id = ad_process.ad_process_id
carlos@0
    38
                     left join ad_reference on ad_column.ad_reference_id = ad_reference.ad_reference_id
carlos@0
    39
                     left join ad_model_object mo on ad_process.ad_process_id = mo.ad_process_id
carlos@0
    40
                                                 and mo.action   = 'P' 
carlos@0
    41
                                                 AND mo.isactive = 'Y' 
carlos@0
    42
                                                 AND mo.isdefault= 'Y'
carlos@0
    43
                     left join ad_model_object_mapping mom on mo.ad_model_object_id = mom.ad_model_object_id
carlos@0
    44
                                                AND mom.isactive = 'Y' 
carlos@0
    45
                                                AND mom.isdefault= 'Y'
carlos@0
    46
                     left join ad_callout on ad_column.ad_callout_id = ad_callout.ad_callout_id
carlos@0
    47
                     left join ad_model_object moc on ad_callout.ad_callout_id = moc.ad_callout_id
carlos@0
    48
                                                AND moc.isactive = 'Y'
carlos@0
    49
                                                AND moc.isdefault = 'Y' 
carlos@0
    50
                                                AND moc.action = 'C'
carlos@0
    51
                     left join ad_model_object_mapping momc on moc.ad_model_object_id = momc.ad_model_object_id
carlos@0
    52
                                                AND momc.isactive = 'Y' 
carlos@0
    53
                                                AND momc.isdefault= 'Y',
carlos@0
    54
            ad_field
carlos@0
    55
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
juanpablo@1605
    56
        AND ad_field.ad_tab_id = ?
carlos@0
    57
        AND ad_column.isActive = 'Y'
asier@217
    58
              union 
asier@217
    59
      select (case when upper(columnname)='CREATED' then 10000 
asier@217
    60
                   when upper(columnname)='CREATEDBY' then 10001
asier@217
    61
                   when upper(columnname)='UPDATED' then 10002
asier@217
    62
                   else 10003
asier@217
    63
              end), columnname,  columnname, c.name, 'N', 
asier@217
    64
             c.ad_reference_value_id, c.ad_reference_id, 44, 'x', 
asier@217
    65
             null, 
asier@217
    66
             (case when upper(columnname) in ('CREATED', 'UPDATED') then 'N'
asier@217
    67
                   else 'Y'
asier@217
    68
               end), 'Y', 'N', 'N', c.FIELDLENGTH, c.name, null, 
juanpablo@1605
    69
             null, 'Y', '@ShowAudit@=''Y''', 'N', '1000100001' as fieldgroup, tb.ad_tab_id, null, null, null, null, null, null, null, c.columnname, 
asier@227
    70
             null, null, null, 'N', 'N', r.name, r.name, null, null, null, null, null, null, 'Y', null
asier@217
    71
       from ad_column c, ad_tab tb, ad_reference r
asier@217
    72
      where c.ad_table_id = tb.ad_table_id
asier@217
    73
        and upper(columnname) in ('CREATED', 'CREATEDBY', 'UPDATED', 'UPDATEDBY')
juanpablo@1605
    74
        and tb.ad_tab_id = ?
asier@217
    75
        and r.ad_reference_id = c.ad_reference_id
asier@217
    76
      ORDER BY SEQNO
carlos@0
    77
    ]]></Sql>
carlos@0
    78
    <Parameter name="tab"/>
asier@217
    79
    <Parameter name="tab"/>
carlos@0
    80
  </SqlMethod>
carlos@0
    81
carlos@0
    82
  <SqlMethod name="selectTrl" type="preparedStatement" return="multiple">
carlos@0
    83
    <SqlMethodComment>Names of the columns and name of the fields of a tab</SqlMethodComment>
carlos@0
    84
    <Sql><![CDATA[
asier@217
    85
      SELECT ad_field.seqno, ad_column.ColumnName As columnName, ad_column.ColumnName As columnNameInp, 
carlos@0
    86
      (CASE WHEN ad_field_trl.name IS NULL THEN '*' || ad_field.name ELSE ad_field_trl.name END) as Name, ismandatory as required, ad_reference_value_id as referenceValue, 
carlos@0
    87
      ad_column.ad_reference_id as reference, displayLength as displaySize, 'x' as htmltext, 
carlos@0
    88
      isSameLine as issameline, (CASE ad_field.isActive WHEN 'N' THEN 'N' ELSE isDisplayed END) as isdisplayed, isupdateable as isupdateable, 
juanpablo@1605
    89
      isparent, ad_column.FIELDLENGTH, (CASE WHEN (ad_column.AD_REFERENCE_ID<>'30' OR AD_REFERENCE_VALUE_ID IS NULL) THEN REPLACE(REPLACE(REPLACE(ad_element.name, 'Substitute', 'Product'), 'BOM', ''), 'M_LocatorTo_ID', 'M_Locator_ID') ELSE (SELECT NAME FROM AD_REFERENCE WHERE AD_REFERENCE.AD_REFERENCE_ID=ad_column.AD_REFERENCE_VALUE_ID) END) as searchName, ad_column.ad_callout_id, 
carlos@259
    90
      ad_column.ad_process_id, ad_field.isreadonly, to_char(ad_field.DISPLAYLOGIC) as displaylogic, ad_field.isEncrypted, AD_FIELD.AD_FIELDGROUP_ID AS FieldGroup,
carlos@0
    91
      ad_field.ad_tab_id as tabid, AD_COLUMN.ValueMin, AD_COLUMN.ValueMax, REPLACE(REPLACE(REPLACE(REPLACE(ad_column.columnname, 'Substitute', 'M_Product'), 'C_Settlement_Cancel_ID', 'C_Settlement_ID'), 'BOM', ''), 'M_LocatorTo_ID', 'M_Locator_ID') as realName, mom.mappingname as java_class_name, ad_column.isEncrypted AS isColumnEncrypted, ad_column.isDesencryptable, 
carlos@0
    92
      ad_reference.name as reference_name, ad_column.ad_val_rule_id, 
carlos@0
    93
      (CASE WHEN ad_reference_trl.ad_reference_id IS NULL THEN ad_reference.name ELSE ad_reference_trl.name END) as reference_name_trl, ad_callout.name as calloutname, 
carlos@0
    94
      moc.classname as classname_callout, momc.mappingname as mappingname_callout, ad_field.isactive 
carlos@0
    95
      FROM ad_column left join ad_element on ad_column.ad_element_id = ad_element.ad_element_id
carlos@0
    96
                     left join ad_process on ad_column.ad_process_id = ad_process.ad_process_id
carlos@0
    97
                     left join ad_reference on ad_column.ad_reference_id = ad_reference.ad_reference_id
carlos@0
    98
                     left join ad_reference_trl on ad_reference.ad_reference_id = ad_reference_trl.ad_reference_id 
carlos@0
    99
                                               AND ad_reference_trl.ad_language = ? 
carlos@0
   100
                     left join ad_model_object mo on ad_process.ad_process_id = mo.ad_process_id 
carlos@0
   101
                                                  AND mo.action = 'P' 
carlos@0
   102
                                                  AND mo.isactive = 'Y' 
carlos@0
   103
                                                  AND mo.isdefault = 'Y'
carlos@0
   104
                     left join ad_model_object_mapping mom on mo.ad_model_object_id = mom.ad_model_object_id
carlos@0
   105
                                                  AND mom.isactive = 'Y' 
carlos@0
   106
                                                  AND mom.isdefault = 'Y'
carlos@0
   107
                     left join ad_callout on ad_column.ad_callout_id = ad_callout.ad_callout_id
carlos@0
   108
                     left join ad_model_object moc on ad_callout.ad_callout_id = moc.ad_callout_id
carlos@0
   109
                                                AND moc.isactive = 'Y'
carlos@0
   110
                                                AND moc.isdefault = 'Y' 
carlos@0
   111
                                                AND moc.action = 'C'
carlos@0
   112
                     left join ad_model_object_mapping momc on moc.ad_model_object_id = momc.ad_model_object_id
carlos@0
   113
                                                AND momc.isactive = 'Y' 
carlos@0
   114
                                                AND momc.isdefault= 'Y',
carlos@0
   115
           ad_field left join ad_field_trl on ad_field.ad_field_id = ad_field_trl.ad_field_id 
carlos@0
   116
                                                  AND ad_field_trl.isactive = 'Y'
carlos@0
   117
                                                  AND ad_field_trl.ad_language = ?
carlos@0
   118
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
juanpablo@1605
   119
        AND ad_field.ad_tab_id = ? 
carlos@0
   120
        AND ad_column.isActive = 'Y'
asier@217
   121
     union 
asier@217
   122
          SELECT (case when upper(c.columnname)='CREATED' then 10000 
asier@217
   123
                   when upper(c.columnname)='CREATEDBY' then 10001
asier@217
   124
                   when upper(c.columnname)='UPDATED' then 10002
asier@217
   125
                   else 10003
asier@217
   126
              end), c.ColumnName As columnName, c.ColumnName As columnNameInp, 
asier@368
   127
      (CASE WHEN ad_element_trl.name IS NULL THEN '*' || ad_element.name ELSE ad_element_trl.name END) as Name, 'N' as required, ad_reference_value_id as referenceValue, 
asier@217
   128
      c.ad_reference_id as reference, 44, 'x' as htmltext, 
asier@217
   129
      (case when upper(c.columnname) in ('CREATED', 'UPDATED') then 'N'
asier@217
   130
                   else 'Y'
asier@217
   131
               end) as issameline, 'Y' as isdisplayed, 'N' as isupdateable, 
juanpablo@1605
   132
      'Y', c.FIELDLENGTH, (CASE WHEN (c.AD_REFERENCE_ID<>'30' OR AD_REFERENCE_VALUE_ID IS NULL) THEN REPLACE(REPLACE(REPLACE(ad_element.name, 'Substitute', 'Product'), 'BOM', ''), 'M_LocatorTo_ID', 'M_Locator_ID') ELSE (SELECT NAME FROM AD_REFERENCE WHERE r.AD_REFERENCE_ID=c.AD_REFERENCE_VALUE_ID) END) as searchName, c.ad_callout_id, 
juanpablo@1605
   133
      c.ad_process_id, 'Y', '@ShowAudit@=''Y''' as displaylogic, 'N', '1000100001' AS FieldGroup,
asier@217
   134
      tb.ad_tab_id as tabid, c.ValueMin, c.ValueMax, REPLACE(REPLACE(REPLACE(REPLACE(c.columnname, 'Substitute', 'M_Product'), 'C_Settlement_Cancel_ID', 'C_Settlement_ID'), 'BOM', ''), 'M_LocatorTo_ID', 'M_Locator_ID') as realName,null as java_class_name, c.isEncrypted AS isColumnEncrypted, c.isDesencryptable, 
asier@217
   135
      r.name as reference_name, c.ad_val_rule_id, 
asier@217
   136
      (CASE WHEN ad_reference_trl.ad_reference_id IS NULL THEN r.name ELSE ad_reference_trl.name END) as reference_name_trl, null as calloutname, 
asier@217
   137
      null, null, 'Y' 
asier@217
   138
       from  ad_tab tb, ad_column c
asier@217
   139
                                                  left join ad_element on c.ad_element_id = ad_element.ad_element_id
asier@217
   140
                                                  left join ad_element_trl on ad_element_trl.ad_element_id = ad_element.ad_element_id
asier@217
   141
                                                  and ad_language = ?,
asier@217
   142
                                                  ad_reference r
asier@217
   143
                                                  left join ad_reference_trl on r.ad_reference_id = ad_reference_trl.ad_reference_id 
asier@217
   144
                                               AND ad_reference_trl.ad_language = ?
asier@217
   145
                                               
asier@217
   146
      where c.ad_table_id = tb.ad_table_id
asier@217
   147
        and upper(c.columnname) in ('CREATED', 'CREATEDBY', 'UPDATED', 'UPDATEDBY')
juanpablo@1605
   148
        and tb.ad_tab_id = ?
asier@217
   149
        and r.ad_reference_id = c.ad_reference_id
asier@217
   150
        ORDER BY SEQNO
carlos@0
   151
    ]]></Sql>
carlos@0
   152
    <Parameter name="adLanguage"/>
carlos@0
   153
    <Parameter name="adLanguage"/>
carlos@0
   154
    <Parameter name="tab"/>
asier@217
   155
    <Parameter name="adLanguage"/>
asier@217
   156
    <Parameter name="adLanguage"/>
asier@217
   157
    <Parameter name="tab"/>
carlos@0
   158
  </SqlMethod>
carlos@0
   159
carlos@0
   160
  <SqlMethod name="selectAuxiliar" type="preparedStatement" return="multiple">
carlos@0
   161
    <SqlMethodComment>Names of the columns and name of the fields of a tab</SqlMethodComment>
carlos@0
   162
    <Sql>
carlos@0
   163
      SELECT ad_auxiliarinput_id as reference, name as columnname, name as columnnameinp, code as name, '' as htmltext 
carlos@0
   164
      FROM ad_auxiliarinput 
juanpablo@1605
   165
      WHERE ad_tab_id = ?
carlos@0
   166
    </Sql>
carlos@0
   167
    <Parameter name="tab"/>
carlos@0
   168
  </SqlMethod>
carlos@0
   169
carlos@0
   170
  <SqlMethod name="selectDisplayLogic" type="preparedStatement" return="multiple">
carlos@0
   171
    <SqlMethodComment>Names of the columns and name of the fields of a tab</SqlMethodComment>
carlos@0
   172
    <Sql>
asier@217
   173
      SELECT ad_field.seqno, ad_column.ColumnName As columnName, ad_column.ColumnName As columnNameInp, 
carlos@0
   174
      ad_field.name as Name, ismandatory as required, ad_reference_value_id as referenceValue, 
carlos@0
   175
      ad_column.ad_reference_id as reference, displayLength as displaySize, 'x' as htmltext, 
carlos@0
   176
      isSameLine as issameline, isDisplayed as isdisplayed, isupdateable as isupdateable, 
carlos@0
   177
      isparent, ad_column.FIELDLENGTH, ad_element.name as searchName, ad_column.ad_callout_id, 
carlos@259
   178
      ad_column.ad_process_id, ad_field.isreadonly, to_char(ad_field.DISPLAYLOGIC) as displaylogic, ad_field.ad_tab_id as tabid, 
carlos@0
   179
      ad_callout.name as calloutname, moc.classname as classname_callout, momc.mappingname as mappingname_callout 
carlos@0
   180
      FROM ad_field, 
carlos@0
   181
           ad_column left join ad_element on ad_column.ad_element_id = ad_element.ad_element_id
carlos@0
   182
           left join ad_callout on ad_column.ad_callout_id = ad_callout.ad_callout_id 
carlos@0
   183
           left join ad_model_object moc on ad_callout.ad_callout_id = moc.ad_callout_id 
carlos@0
   184
                              AND moc.action = 'C' 
carlos@0
   185
                              AND moc.isactive = 'Y'
carlos@0
   186
                              AND moc.isdefault = 'Y' 
carlos@0
   187
           left join ad_model_object_mapping momc on moc.ad_model_object_id = momc.ad_model_object_id 
carlos@0
   188
                              AND momc.isactive = 'Y' 
carlos@0
   189
                              AND momc.isdefault = 'Y'
carlos@0
   190
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
carlos@0
   191
        AND ad_field.displaylogic is not null 
carlos@0
   192
        AND AD_FIELD.ISDISPLAYED = 'Y' 
juanpablo@1605
   193
        AND ad_field.ad_tab_id = ?
asier@217
   194
      union
asier@217
   195
      select (case when upper(columnname)='CREATED' then 10000 
asier@217
   196
                   when upper(columnname)='CREATEDBY' then 10001
asier@217
   197
                   when upper(columnname)='UPDATED' then 10002
asier@217
   198
                   else 10003
asier@217
   199
              end), columnname,  columnname, c.name, 'N', 
asier@217
   200
             c.ad_reference_value_id, c.ad_reference_id, 44, 'x', 
asier@217
   201
             'N', 'Y', 'N', 'N', c.FIELDLENGTH, c.name, null, 
asier@683
   202
             null, 'Y', '@ShowAudit@=''Y''', tb.ad_tab_id, null, null, null
asier@217
   203
       from ad_column c, ad_tab tb, ad_reference r
asier@217
   204
      where c.ad_table_id = tb.ad_table_id
asier@217
   205
        and upper(columnname) in ('CREATED', 'CREATEDBY', 'UPDATED', 'UPDATEDBY')
juanpablo@1605
   206
        and tb.ad_tab_id = ?
asier@217
   207
        and r.ad_reference_id = c.ad_reference_id
asier@217
   208
      ORDER BY SEQNO
carlos@0
   209
    </Sql>
carlos@0
   210
    <Parameter name="tab"/>
asier@217
   211
    <Parameter name="tab"/>
carlos@0
   212
  </SqlMethod>
carlos@0
   213
carlos@0
   214
  <SqlMethod name="selectDisplayLogicTrl" type="preparedStatement" return="multiple">
carlos@0
   215
    <SqlMethodComment>Names of the columns and name of the fields of a tab</SqlMethodComment>
carlos@0
   216
    <Sql>
asier@217
   217
      SELECT ad_field.seqno, ad_column.ColumnName As columnName, ad_column.ColumnName As columnNameInp, 
carlos@0
   218
      (CASE WHEN ad_field_trl.name IS NULL THEN '*' || ad_field.name ELSE ad_field_trl.name END) as Name, ismandatory as required, ad_reference_value_id as referenceValue, 
carlos@0
   219
      ad_column.ad_reference_id as reference, displayLength as displaySize, 'x' as htmltext, 
carlos@0
   220
      isSameLine as issameline, isDisplayed as isdisplayed, isupdateable as isupdateable, 
carlos@0
   221
      isparent, ad_column.FIELDLENGTH, ad_element.name as searchName, ad_column.ad_callout_id, 
carlos@259
   222
      ad_column.ad_process_id, ad_field.isreadonly, to_char(ad_field.DISPLAYLOGIC) as displaylogic, ad_field.ad_tab_id as tabid, ad_callout.name as calloutname, 
carlos@0
   223
      moc.classname as classname_callout, momc.mappingname as mappingname_callout
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.isactive = 'Y'
carlos@0
   226
                                          AND ad_field_trl.ad_language = ?,
carlos@0
   227
          ad_column left join ad_element on ad_column.ad_element_id = ad_element.ad_element_id
carlos@0
   228
          left join ad_callout on ad_column.ad_callout_id = ad_callout.ad_callout_id 
carlos@0
   229
           left join ad_model_object moc on ad_callout.ad_callout_id = moc.ad_callout_id 
carlos@0
   230
                              AND moc.action = 'C' 
carlos@0
   231
                              AND moc.isactive = 'Y'
carlos@0
   232
                              AND moc.isdefault = 'Y' 
carlos@0
   233
           left join ad_model_object_mapping momc on moc.ad_model_object_id = momc.ad_model_object_id 
carlos@0
   234
                              AND momc.isactive = 'Y' 
carlos@0
   235
                              AND momc.isdefault = 'Y'
carlos@0
   236
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
juanpablo@1605
   237
        AND ad_field.ad_tab_id = ?
carlos@0
   238
        AND ad_field.displaylogic is not null 
carlos@0
   239
        AND AD_FIELD.ISDISPLAYED = 'Y' 
asier@217
   240
        union
asier@217
   241
      select (case when upper(columnname)='CREATED' then 10000 
asier@217
   242
                   when upper(columnname)='CREATEDBY' then 10001
asier@217
   243
                   when upper(columnname)='UPDATED' then 10002
asier@217
   244
                   else 10003
asier@217
   245
              end), columnname,  columnname, c.name, 'N', 
asier@217
   246
             c.ad_reference_value_id, c.ad_reference_id, 44, 'x', 
asier@217
   247
             'N', 'Y', 'N', 'N', c.FIELDLENGTH, c.name, null, 
asier@683
   248
             null, 'Y', '@ShowAudit@=''Y''', tb.ad_tab_id, null, null, null
asier@217
   249
       from ad_column c, ad_tab tb, ad_reference r
asier@217
   250
      where c.ad_table_id = tb.ad_table_id
asier@217
   251
        and upper(columnname) in ('CREATED', 'CREATEDBY', 'UPDATED', 'UPDATEDBY')
juanpablo@1605
   252
        and tb.ad_tab_id = ?
asier@217
   253
         and r.ad_reference_id = c.ad_reference_id
asier@217
   254
      ORDER BY SEQNO
carlos@0
   255
    </Sql>
carlos@0
   256
    <Parameter name="adLanguage"/>
carlos@0
   257
    <Parameter name="tab"/>    
asier@217
   258
    <Parameter name="tab"/>  
carlos@0
   259
  </SqlMethod>
asier@227
   260
  
asier@227
   261
  <SqlMethod name="selectReadOnlyLogic" type="preparedStatement" return="multiple">
asier@227
   262
    <SqlMethodComment>Names of the columns and name of the fields of a tab</SqlMethodComment>
asier@227
   263
    <Sql>
asier@227
   264
      SELECT ad_field.seqno, ad_column.ColumnName As columnName, ad_column.ColumnName As columnNameInp, 
asier@227
   265
      ad_field.name as Name, ismandatory as required, ad_reference_value_id as referenceValue, 
asier@227
   266
      ad_column.ad_reference_id as reference, displayLength as displaySize, 'x' as htmltext, 
asier@227
   267
      isSameLine as issameline, isDisplayed as isdisplayed, isupdateable as isupdateable, 
asier@227
   268
      isparent, ad_column.FIELDLENGTH, ad_element.name as searchName, ad_column.ad_callout_id, 
carlos@259
   269
      ad_column.ad_process_id, ad_field.isreadonly, to_char(ad_field.DISPLAYLOGIC) as displaylogic, ad_field.ad_tab_id as tabid, 
asier@227
   270
      ad_callout.name as calloutname, moc.classname as classname_callout, momc.mappingname as mappingname_callout,
asier@227
   271
      ad_column.readonlylogic 
asier@227
   272
      FROM ad_field, 
asier@227
   273
           ad_column left join ad_element on ad_column.ad_element_id = ad_element.ad_element_id
asier@227
   274
           left join ad_callout on ad_column.ad_callout_id = ad_callout.ad_callout_id 
asier@227
   275
           left join ad_model_object moc on ad_callout.ad_callout_id = moc.ad_callout_id 
asier@227
   276
                              AND moc.action = 'C' 
asier@227
   277
                              AND moc.isactive = 'Y'
asier@227
   278
                              AND moc.isdefault = 'Y' 
asier@227
   279
           left join ad_model_object_mapping momc on moc.ad_model_object_id = momc.ad_model_object_id 
asier@227
   280
                              AND momc.isactive = 'Y' 
asier@227
   281
                              AND momc.isdefault = 'Y'
asier@227
   282
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
asier@227
   283
        AND ad_column.readonlylogic is not null 
asier@227
   284
        AND AD_FIELD.ISDISPLAYED = 'Y' 
juanpablo@1605
   285
        AND ad_field.ad_tab_id = ?
asier@227
   286
        AND ad_field.isreadonly='N'
asier@227
   287
        AND ad_column.isupdateable='Y'
asier@227
   288
       order by seqno
asier@227
   289
    </Sql>
asier@227
   290
    <Parameter name="tab"/>
asier@227
   291
  </SqlMethod>
carlos@0
   292
carlos@0
   293
  <SqlMethod name="selectionColumns" type="preparedStatement" return="multiple">
carlos@0
   294
    <SqlMethodComment>Names of the columns and name of the fields of a tab</SqlMethodComment>
carlos@0
   295
    <Sql>
carlos@0
   296
      SELECT ad_column.ColumnName As columnName, ad_column.ColumnName As columnNameInp, 
carlos@0
   297
      ad_field.name as Name, ismandatory as required, ad_reference_value_id as referenceValue, 
carlos@0
   298
      ad_column.ad_reference_id as reference, displayLength as displaySize, 'x' as htmltext, 
carlos@0
   299
      isSameLine as issameline, isDisplayed as isdisplayed, isupdateable as isupdateable, 
carlos@0
   300
      isparent, ad_column.FIELDLENGTH, ad_element.name as searchName, ad_column.ad_callout_id, 
carlos@0
   301
      ad_column.ad_process_id, ad_field.isreadonly, ad_field.DISPLAYLOGIC, ad_field.ad_tab_id as tabid, 
carlos@0
   302
      ad_callout.name as calloutname, moc.classname as classname_callout, momc.mappingname as mappingname_callout
carlos@0
   303
      FROM ad_field, 
carlos@0
   304
           ad_column left join ad_element on ad_column.ad_element_id = ad_element.ad_element_id
carlos@0
   305
           left join ad_callout on ad_column.ad_callout_id = ad_callout.ad_callout_id 
carlos@0
   306
           left join ad_model_object moc on ad_callout.ad_callout_id = moc.ad_callout_id 
carlos@0
   307
                              AND moc.action = 'C' 
carlos@0
   308
                              AND moc.isactive = 'Y'
carlos@0
   309
                              AND moc.isdefault = 'Y' 
carlos@0
   310
           left join ad_model_object_mapping momc on moc.ad_model_object_id = momc.ad_model_object_id 
carlos@0
   311
                              AND momc.isactive = 'Y' 
carlos@0
   312
                              AND momc.isdefault = 'Y'
carlos@0
   313
      WHERE ad_field.ad_column_id = ad_column.ad_column_id 
juanpablo@1605
   314
        AND ad_field.ad_tab_id = ?
carlos@0
   315
        AND upper(ad_column.columnname) in ('VALUE', 'NAME', 'DESCRIPTION')
carlos@0
   316
      ORDER BY ad_field.SEQNO
carlos@0
   317
    </Sql>
carlos@0
   318
    <Parameter name="tab"/>
carlos@0
   319
  </SqlMethod>
carlos@0
   320
  <SqlMethod name="fieldGroupName" type="preparedStatement" return="String" default="">
carlos@0
   321
    <SqlMethodComment>Names of the columns and name of the fields of a tab</SqlMethodComment>
carlos@0
   322
    <Sql>
carlos@0
   323
      select name 
carlos@0
   324
      from ad_fieldgroup 
juanpablo@1605
   325
      where ad_fieldgroup_id=? 
carlos@0
   326
      and isactive='Y'
carlos@0
   327
    </Sql>
carlos@0
   328
    <Parameter name="adFieldgroupId"/>
carlos@0
   329
  </SqlMethod>
carlos@0
   330
  <SqlMethod name="fieldGroupNameTrl" type="preparedStatement" return="String" default="">
carlos@0
   331
    <SqlMethodComment>Names of the columns and name of the fields of a tab</SqlMethodComment>
carlos@0
   332
    <Sql>
carlos@0
   333
      select (CASE WHEN ft.name IS NULL THEN '*' || f.name ELSE ft.name END) as name 
carlos@0
   334
      from ad_fieldgroup f left join ad_fieldgroup_trl ft on f.ad_fieldgroup_id = ft.ad_fieldgroup_id 
carlos@0
   335
                                                          and ft.ad_language = ?
juanpablo@1605
   336
      where f.ad_fieldgroup_id = ? 
carlos@0
   337
      and f.isactive='Y'
carlos@0
   338
    </Sql>
carlos@0
   339
    <Parameter name="adLanguage"/>
carlos@0
   340
    <Parameter name="adFieldgroupId"/>    
carlos@0
   341
  </SqlMethod>
carlos@0
   342
  <SqlMethod name="selectSerchFieldsSelection" type="preparedStatement" return="multiple">
carlos@0
   343
      <SqlMethodComment>Campos de búsqueda</SqlMethodComment>
carlos@0
   344
      <Sql> <![CDATA[
carlos@0
   345
        SELECT AD_COLUMN.ad_column_id, (CASE WHEN AD_FIELD_TRL.NAME IS NULL THEN AD_FIELD.NAME ELSE AD_FIELD_TRL.NAME END) AS NAME, 
carlos@0
   346
        ad_column.ad_reference_id as reference, ad_reference_value_id as referencevalue, ad_val_rule_id, fieldlength, displaylength, 
carlos@0
   347
        columnname, '' as value, ad_tab.ad_window_id, columnname as columnnameinp, '' as htmltext, '' as xmltext, 
carlos@0
   348
        columnname as realcolumnname, '' as xsqltext 
carlos@0
   349
        FROM AD_COLUMN, 
carlos@0
   350
             AD_FIELD left join AD_FIELD_TRL on AD_FIELD.ad_field_id = AD_FIELD_TRL.ad_field_id 
carlos@0
   351
                                            AND AD_FIELD_TRL.AD_LANGUAGE =? , 
carlos@0
   352
             AD_TAB 
carlos@0
   353
        WHERE AD_COLUMN.ad_column_id = AD_FIELD.ad_column_id
carlos@0
   354
        AND AD_FIELD.ad_tab_id = AD_TAB.ad_tab_id 
carlos@0
   355
        AND AD_COLUMN.isactive='Y'
carlos@0
   356
        AND AD_FIELD.isactive='Y' 
carlos@0
   357
        AND ISSELECTIONCOLUMN='Y' 
juanpablo@1605
   358
        AND ad_field.ad_tab_id=?
carlos@0
   359
        ORDER BY AD_COLUMN.seqno, AD_FIELD.seqno
carlos@0
   360
      ]]></Sql>
carlos@0
   361
        <Parameter name="adLanguage"/>
carlos@0
   362
        <Parameter name="adTabId"/>
carlos@0
   363
   </SqlMethod>
carlos@0
   364
   <SqlMethod name="selectSerchFields" type="preparedStatement" return="multiple">
carlos@0
   365
      <SqlMethodComment>Campos de búsqueda</SqlMethodComment>
carlos@0
   366
      <Sql> <![CDATA[
carlos@0
   367
        SELECT AD_COLUMN.ad_column_id, (CASE WHEN AD_FIELD_TRL.NAME IS NULL THEN AD_FIELD.NAME ELSE AD_FIELD_TRL.NAME END) AS NAME, 
carlos@0
   368
        ad_column.ad_reference_id as reference, ad_reference_value_id as referencevalue, ad_val_rule_id, fieldlength, displaylength, 
carlos@0
   369
        columnname, '' as value, ad_tab.ad_window_id, columnname as columnnameinp, '' as htmltext, '' as xmltext, 
carlos@0
   370
        columnname as realcolumnname, '' as xsqltext 
carlos@0
   371
        FROM AD_COLUMN, 
carlos@0
   372
             AD_FIELD left join AD_FIELD_TRL on AD_FIELD.ad_field_id = AD_FIELD_TRL.ad_field_id 
carlos@0
   373
                                            AND AD_FIELD_TRL.AD_LANGUAGE =? , 
carlos@0
   374
             AD_TAB 
carlos@0
   375
        WHERE AD_COLUMN.ad_column_id = AD_FIELD.ad_column_id
carlos@0
   376
        AND AD_FIELD.ad_tab_id = AD_TAB.ad_tab_id 
carlos@0
   377
        AND AD_COLUMN.isactive='Y'
carlos@0
   378
        AND AD_FIELD.isactive='Y' 
carlos@0
   379
        AND isidentifier='Y'
juanpablo@1605
   380
        AND ad_field.ad_tab_id=?
carlos@0
   381
        ORDER BY AD_COLUMN.seqno, AD_FIELD.seqno
carlos@0
   382
      ]]></Sql>
carlos@0
   383
        <Parameter name="adLanguage"/>
carlos@0
   384
        <Parameter name="adTabId"/>
carlos@0
   385
   </SqlMethod>
carlos@0
   386
carlos@0
   387
   <SqlMethod name="selectSearchs" type="preparedStatement" return="multiple">
carlos@0
   388
    <SqlMethodComment>Names of the columns and name of the fields of a tab</SqlMethodComment>
carlos@0
   389
    <Sql><![CDATA[
carlos@0
   390
      SELECT R.NAME, T.TABLENAME AS REFERENCE, C.COLUMNNAME AS COLUMNNAME, RSC.NAME AS COLUMNNAMEINP, 
carlos@0
   391
      RSC.COLUMNNAME AS REFERENCEVALUE, MO.CLASSNAME, MOM.MAPPINGNAME, RSC.COLUMN_SUFFIX AS COLUMNNAME_END 
carlos@0
   392
      FROM AD_REFERENCE R left join AD_REF_SEARCH RS  on R.AD_REFERENCE_ID = RS.AD_REFERENCE_ID 
carlos@0
   393
                          left join AD_REF_SEARCH_COLUMN RSC on RS.AD_REF_SEARCH_ID = RSC.AD_REF_SEARCH_ID
carlos@0
   394
                                AND RSC.COLUMNTYPE = ?, 
carlos@0
   395
           AD_MODEL_OBJECT MO, AD_MODEL_OBJECT_MAPPING MOM, 
carlos@0
   396
           AD_TABLE T, AD_COLUMN C 
carlos@0
   397
      WHERE R.VALIDATIONTYPE = 'S' 
juanpablo@1605
   398
      AND R.AD_REFERENCE_ID = ?
carlos@0
   399
      AND R.AD_REFERENCE_ID = MO.AD_REFERENCE_ID
carlos@0
   400
      AND MO.ACTION = 'S' 
carlos@0
   401
      AND MO.ISACTIVE = 'Y'
carlos@0
   402
      AND MO.ISDEFAULT = 'Y'
carlos@0
   403
      AND MO.AD_MODEL_OBJECT_ID = MOM.AD_MODEL_OBJECT_ID 
carlos@0
   404
      AND MOM.ISACTIVE = 'Y' 
carlos@0
   405
      AND MOM.ISDEFAULT = 'Y'
carlos@0
   406
      AND RS.AD_TABLE_ID = T.AD_TABLE_ID 
carlos@0
   407
      AND RS.AD_COLUMN_ID = C.AD_COLUMN_ID 
carlos@0
   408
    ]]></Sql>
carlos@0
   409
    <Parameter name="columntype"/>
carlos@0
   410
    <Parameter name="searchId"/>
carlos@0
   411
  </SqlMethod>
david@628
   412
  
david@628
   413
   <SqlMethod name="selectFirstFocused" type="preparedStatement" return="multiple">
david@628
   414
    <Sql><![CDATA[
david@628
   415
        select c.columnname, c.ad_reference_id as reference
david@628
   416
          from ad_column c,
david@628
   417
               ad_field f
david@628
   418
        where c.isactive = 'Y'
david@628
   419
          and f.isactive = 'Y'
david@628
   420
          and f.isdisplayed = 'Y'
david@628
   421
          and c.ad_column_id = f.ad_column_id
juanpablo@1605
   422
          and f.ad_tab_id = ?
david@628
   423
          and f.isfirstFocusedField='Y'
david@628
   424
        order by f.seqno
david@628
   425
    ]]></Sql>
david@628
   426
    <Parameter name="adTabId"/>
david@628
   427
  </SqlMethod>
asier@814
   428
  
asier@814
   429
  <SqlMethod name="isOrgKey" type="preparedStatement" return="string">
asier@814
   430
    <Sql><![CDATA[
asier@814
   431
        select count(*) as reference
asier@814
   432
          from ad_column c,
asier@814
   433
               ad_tab t
juanpablo@1605
   434
        where ad_tab_id = ?
asier@814
   435
          and c.ad_table_id = t.ad_table_id
asier@814
   436
          and c.isSecondaryKey = 'Y'
asier@814
   437
          and upper(c.columnname)='AD_ORG_ID'
asier@814
   438
    ]]></Sql>
asier@814
   439
    <Parameter name="adTabId"/>
asier@814
   440
  </SqlMethod>
carlos@0
   441
</SqlClass>