src/org/openbravo/erpCommon/utility/TableSQLQuery_data.xsql
author Asier Lostalé <asier.lostale@openbravo.com>
Mon, 05 May 2008 06:59:24 +0000
changeset 799 fef2c5e2feb7
parent 683 8cc2b7ffa4f4
child 1605 8a0fe0193bef
permissions -rw-r--r--
Merged cleanup branch (r3931) with 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@683
    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
carlos@0
    24
carlos@0
    25
<SqlClass name="TableSQLQueryData" package="org.openbravo.erpCommon.utility">
carlos@0
    26
   <SqlClassComment></SqlClassComment>
carlos@0
    27
   <SqlMethod name="selectStructure" type="preparedStatement" return="multiple">
carlos@0
    28
    <SqlMethodComment></SqlMethodComment>
carlos@0
    29
    <Sql>
carlos@0
    30
      <![CDATA[
carlos@0
    31
       SELECT c.ColumnName, c.AD_Reference_ID, c.AD_Reference_Value_ID, c.AD_Val_Rule_ID, 
carlos@0
    32
       c.FieldLength, c.DefaultValue, c.IsKey, c.IsParent, c.IsMandatory, c.IsUpdateable, 
carlos@0
    33
       c.ReadOnlyLogic, c.IsIdentifier, c.SeqNo, c.IsTranslated, c.IsEncrypted, c.VFormat, 
carlos@0
    34
       c.ValueMin, c.ValueMax, c.IsSelectionColumn, c.AD_Process_ID, c.IsSessionAttr, 
carlos@0
    35
       c.IsSecondaryKey, c.IsDesencryptable, c.AD_CallOut_ID, COALESCE(f_trl.Name, f.Name) AS Name, 
carlos@0
    36
       f.AD_FieldGroup_ID, f.IsDisplayed, f.DisplayLogic, f.DisplayLength, f.IsReadOnly, 
asier@799
    37
       f.SortNo, f.IsSameLine, f.IsFieldOnly, f.ShowInRelation, 
carlos@0
    38
       c.AD_Table_ID, t.TabLevel, t.HasTree, t.WhereClause, t.OrderByClause, 
carlos@0
    39
       t.AD_ColumnSortOrder_ID, t.AD_ColumnSortYESNO_ID, t.IsSortTab, t.IsReadOnly AS IsTabReadOnly, 
carlos@0
    40
       t.FilterClause, t.EditReference, w.WindowType, w.IsSOTrx, w.name AS Window_Name, 
carlos@0
    41
       '' AS Window_Name_Trl, t.name AS Tab_Name, '' AS Tab_Name_Trl, t.AD_Window_ID, '' AS TableName, 
carlos@0
    42
       REPLACE(replace(REPLACE(REPLACE(REPLACE(REPLACE(c.columnname, 'C_Settlement_Generate_ID', 'C_Settlement_ID'), 'Ref_OrderLine_ID', 'C_OrderLine_ID'), 'Substitute_ID', 'M_Product_ID'), 'C_Settlement_Cancel_ID', 'C_Settlement_ID'), 'BOM_ID', '_ID'), 'M_LocatorTo_ID', 'M_Locator_ID') as columnname_Search
carlos@0
    43
       FROM AD_Tab t, AD_Window w, AD_Column c left join AD_Field f on c.AD_Column_ID = f.AD_Column_ID 
carlos@0
    44
                                  AND f.IsActive = 'Y' 
carlos@423
    45
                                  AND f.AD_Tab_ID = TO_NUMBER(?) 
carlos@0
    46
                                  left join AD_Field_Trl f_trl on f.AD_Field_ID = f_trl.AD_Field_ID 
carlos@0
    47
                                  AND f_trl.AD_Language = ?
carlos@0
    48
       WHERE t.AD_Table_ID = c.AD_Table_ID 
carlos@0
    49
       AND t.AD_Window_ID = w.AD_Window_ID
carlos@423
    50
       AND t.AD_Tab_ID = TO_NUMBER(?) 
carlos@0
    51
       AND c.IsActive = 'Y' 
carlos@0
    52
       ORDER BY f.SeqNo, c.SeqNo
carlos@0
    53
      ]]>
carlos@0
    54
    </Sql>
carlos@0
    55
    <Parameter name="adTabId"/>
carlos@0
    56
    <Parameter name="adLanguage"/>
carlos@0
    57
    <Parameter name="adTabId"/>
carlos@0
    58
  </SqlMethod>
asier@399
    59
  
asier@400
    60
  <SqlMethod name="selectKeyMapStructure" type="preparedStatement" return="multiple">
asier@400
    61
    <SqlMethodComment></SqlMethodComment>
asier@400
    62
    <Sql>
asier@400
    63
      <![CDATA[
asier@400
    64
       SELECT t.name as tab_name, w.name as window_name
asier@400
    65
         FROM AD_Tab t, AD_Window w
asier@400
    66
        WHERE t.AD_Window_ID = w.AD_Window_ID
asier@400
    67
          AND t.AD_Tab_ID = ?
asier@400
    68
      ]]>
asier@400
    69
    </Sql>
asier@400
    70
    <Parameter name="adTabId"/>
asier@400
    71
  </SqlMethod>
asier@400
    72
  
asier@399
    73
  <SqlMethod name="selectRelationStructure" type="preparedStatement" return="multiple">
asier@399
    74
     <SqlMethodComment></SqlMethodComment>
asier@399
    75
     <Sql>
asier@399
    76
       <![CDATA[
asier@399
    77
        SELECT c.ColumnName, c.AD_Reference_ID, c.AD_Reference_Value_ID, c.AD_Val_Rule_ID,
asier@399
    78
        c.FieldLength, c.DefaultValue, c.IsKey, c.IsParent, c.IsMandatory, c.IsUpdateable,
asier@399
    79
        c.ReadOnlyLogic, c.IsIdentifier, c.SeqNo, c.IsTranslated, c.IsEncrypted, c.VFormat,
asier@399
    80
        c.ValueMin, c.ValueMax, c.IsSelectionColumn, c.AD_Process_ID, c.IsSessionAttr,
asier@399
    81
        c.IsSecondaryKey, c.IsDesencryptable, c.AD_CallOut_ID, COALESCE(f_trl.Name, f.Name) AS Name,
asier@399
    82
        f.AD_FieldGroup_ID, f.IsDisplayed, f.DisplayLogic, f.DisplayLength, f.IsReadOnly,
asier@799
    83
        f.SortNo, f.IsSameLine, f.IsFieldOnly, f.ShowInRelation,
asier@399
    84
        c.AD_Table_ID, t.TabLevel, t.HasTree, t.WhereClause, t.OrderByClause,
asier@399
    85
        t.AD_ColumnSortOrder_ID, t.AD_ColumnSortYESNO_ID, t.IsSortTab, t.IsReadOnly AS IsTabReadOnly,
asier@399
    86
        t.FilterClause, t.EditReference,
asier@399
    87
        '' AS WindowType, '' AS IsSOTrx, ''AS Window_Name,
asier@399
    88
        '' AS Window_Name_Trl, t.name AS Tab_Name, '' AS Tab_Name_Trl, t.AD_Window_ID, '' AS TableName,
asier@399
    89
        REPLACE(replace(REPLACE(REPLACE(REPLACE(REPLACE(c.columnname, 'C_Settlement_Generate_ID', 'C_Settlement_ID'), 'Ref_OrderLine_ID', 'C_OrderLine_ID'), 'Substitute_ID', 'M_Product_ID'), 'C_Settlement_Cancel_ID', 'C_Settlement_ID'), 'BOM_ID', '_ID'), 'M_LocatorTo_ID', 'M_Locator_ID') as columnname_Search
asier@399
    90
        FROM AD_Tab t
asier@399
    91
             inner join AD_Column c on t.AD_Table_ID = c.AD_Table_ID
asier@399
    92
             left join AD_Field f on c.AD_Column_ID = f.AD_Column_ID AND f.IsActive = 'Y' AND f.AD_Tab_ID = t.AD_Tab_ID
asier@399
    93
             left join AD_Field_Trl f_trl on f.AD_Field_ID = f_trl.AD_Field_ID AND f_trl.AD_Language = ?
asier@399
    94
        WHERE t.AD_Tab_ID = ?
asier@399
    95
        AND c.IsActive = 'Y'
asier@399
    96
        AND (c.IsKey = 'Y' or c.IsSecondaryKey = 'Y' or (f.IsDisplayed='Y' and f.ShowInRelation='Y' and c.IsEncrypted='N'))
asier@399
    97
        ORDER BY f.SeqNo, c.SeqNo
asier@399
    98
       ]]>
asier@399
    99
     </Sql>
asier@399
   100
     <Parameter name="adLanguage"/>
asier@399
   101
     <Parameter name="adTabId"/>
asier@399
   102
   </SqlMethod>
asier@683
   103
   
asier@683
   104
   <SqlMethod name="selectRelationStructureAudit" type="preparedStatement" return="multiple">
asier@683
   105
     <SqlMethodComment></SqlMethodComment>
asier@683
   106
     <Sql>
asier@683
   107
       <![CDATA[
asier@683
   108
        SELECT c.ColumnName, (case when c.AD_Reference_ID=16 then 15 else c.ad_Reference_id end) as ad_reference_id, c.AD_Reference_Value_ID, c.AD_Val_Rule_ID,
asier@683
   109
        c.FieldLength, c.DefaultValue, c.IsKey, c.IsParent, c.IsMandatory, c.IsUpdateable,
asier@683
   110
        c.ReadOnlyLogic, c.IsIdentifier, c.SeqNo, c.IsTranslated, c.IsEncrypted, c.VFormat,
asier@683
   111
        c.ValueMin, c.ValueMax, c.IsSelectionColumn, c.AD_Process_ID, c.IsSessionAttr,
asier@683
   112
        c.IsSecondaryKey, c.IsDesencryptable, c.AD_CallOut_ID, COALESCE(f_trl.Name, f.Name) AS Name,
asier@683
   113
        f.AD_FieldGroup_ID, 'Y' as IsDisplayed, f.DisplayLogic, 22 as DisplayLength, f.IsReadOnly,
asier@799
   114
        f.SortNo, f.IsSameLine, f.IsFieldOnly, 'Y' as ShowInRelation,
asier@683
   115
        c.AD_Table_ID, t.TabLevel, t.HasTree, t.WhereClause, t.OrderByClause,
asier@683
   116
        t.AD_ColumnSortOrder_ID, t.AD_ColumnSortYESNO_ID, t.IsSortTab, t.IsReadOnly AS IsTabReadOnly,
asier@683
   117
        t.FilterClause, t.EditReference,
asier@683
   118
        '' AS WindowType, '' AS IsSOTrx, ''AS Window_Name,
asier@683
   119
        '' AS Window_Name_Trl, t.name AS Tab_Name, '' AS Tab_Name_Trl, t.AD_Window_ID, '' AS TableName,
asier@683
   120
        REPLACE(replace(REPLACE(REPLACE(REPLACE(REPLACE(c.columnname, 'C_Settlement_Generate_ID', 'C_Settlement_ID'), 'Ref_OrderLine_ID', 'C_OrderLine_ID'), 'Substitute_ID', 'M_Product_ID'), 'C_Settlement_Cancel_ID', 'C_Settlement_ID'), 'BOM_ID', '_ID'), 'M_LocatorTo_ID', 'M_Locator_ID') as columnname_Search
asier@683
   121
        FROM AD_Tab t
asier@683
   122
             inner join AD_Column c on t.AD_Table_ID = c.AD_Table_ID
asier@683
   123
             left join AD_Field f on c.AD_Column_ID = f.AD_Column_ID AND f.IsActive = 'Y' AND f.AD_Tab_ID = t.AD_Tab_ID
asier@683
   124
             left join AD_Field_Trl f_trl on f.AD_Field_ID = f_trl.AD_Field_ID AND f_trl.AD_Language = ?
asier@683
   125
        WHERE t.AD_Tab_ID = ?
asier@683
   126
        AND c.IsActive = 'Y'
asier@683
   127
        AND upper(c.columnname) in ('UPDATED','UPDATEDBY','CREATED','CREATEDBY')
asier@683
   128
        ORDER BY (case when upper(columnname)='CREATED' then 10000 
asier@683
   129
                   when upper(columnname)='CREATEDBY' then 10001
asier@683
   130
                   when upper(columnname)='UPDATED' then 10002
asier@683
   131
                   else 10003
asier@683
   132
                  end)
asier@683
   133
       ]]>
asier@683
   134
     </Sql>
asier@683
   135
     <Parameter name="adLanguage"/>
asier@683
   136
     <Parameter name="adTabId"/>
asier@683
   137
   </SqlMethod>
asier@399
   138
  
carlos@0
   139
  <SqlMethod name="selectWindowDefinition" type="preparedStatement" return="multiple">
carlos@0
   140
    <SqlMethodComment></SqlMethodComment>
carlos@0
   141
    <Sql>
carlos@0
   142
      <![CDATA[
carlos@0
   143
       SELECT t.AD_Table_ID, t.TabLevel, t.IsReadOnly, t.HasTree, t.WhereClause, t.OrderByClause, t.AD_Process_ID, 
carlos@0
   144
       t.AD_ColumnSortOrder_ID, t.AD_ColumnSortYESNO_ID, t.IsSortTab, t.FilterClause, t.EditReference, 
carlos@0
   145
       w.WindowType, w.IsSOTrx, w.Name AS Window_Name, COALESCE(w_trl.Name, w.Name) AS Window_Name_Trl, 
carlos@0
   146
       t.Name AS Tab_Name, COALESCE(t_trl.Name, t.Name) AS Tab_Name_Trl, w.AD_Window_ID, 
carlos@0
   147
       tb.TableName 
carlos@0
   148
       FROM AD_Tab t left join AD_Tab_TRL t_trl on t.AD_Tab_ID = t_trl.AD_Tab_ID 
carlos@0
   149
                     AND t_trl.AD_Language = ?, 
carlos@0
   150
            AD_Window w left join AD_Window_TRL w_trl on w.AD_Window_ID = w_trl.AD_Window_ID 
carlos@0
   151
                        AND w_trl.AD_Language = ?, 
carlos@0
   152
            AD_Table tb 
carlos@0
   153
       WHERE t.AD_Window_ID = w.AD_Window_ID 
carlos@423
   154
       AND t.AD_Tab_ID = TO_NUMBER(?) 
carlos@0
   155
       AND t.AD_Table_ID = tb.AD_Table_ID 
carlos@0
   156
      ]]>
carlos@0
   157
    </Sql>
carlos@0
   158
    <Parameter name="adLanguage"/>
carlos@0
   159
    <Parameter name="adLanguage"/>
carlos@0
   160
    <Parameter name="adTabId"/>
carlos@0
   161
  </SqlMethod>
carlos@0
   162
  <SqlMethod name="selectOrderByFields" type="preparedStatement" return="multiple">
carlos@0
   163
    <SqlMethodComment></SqlMethodComment>
carlos@0
   164
    <Sql>
carlos@0
   165
      <![CDATA[
carlos@0
   166
       SELECT c.ColumnName
carlos@0
   167
       FROM AD_Tab t, AD_Field f, AD_Column c
carlos@0
   168
       WHERE t.AD_Tab_ID = f.AD_Tab_ID 
carlos@0
   169
       AND f.AD_Column_ID = c.AD_Column_ID 
carlos@423
   170
       AND t.AD_Tab_ID = TO_NUMBER(?) 
carlos@0
   171
       AND c.IsActive = 'Y'
carlos@0
   172
       AND f.IsActive = 'Y'
carlos@0
   173
       AND f.SortNo IS NOT NULL
carlos@0
   174
       ORDER BY f.SortNo
carlos@0
   175
      ]]>
carlos@0
   176
    </Sql>
carlos@0
   177
    <Parameter name="adTabId"/>
carlos@0
   178
  </SqlMethod>
carlos@0
   179
  <SqlMethod name="columnNameKey" type="preparedStatement" return="String" default="">
carlos@0
   180
      <SqlMethodComment>Name of the columns parent of the tab</SqlMethodComment>
carlos@0
   181
      <Sql><![CDATA[
carlos@0
   182
        SELECT ColumnName
carlos@0
   183
        FROM AD_FIELD, AD_COLUMN 
carlos@0
   184
        WHERE AD_FIELD.ad_column_id = AD_COLUMN.ad_column_id 
carlos@423
   185
        AND ad_tab_ID = TO_NUMBER(?) 
carlos@0
   186
        AND ad_Field.isActive = 'Y' 
carlos@0
   187
        AND ad_Column.isActive = 'Y' 
carlos@0
   188
        AND isKey='Y'
carlos@0
   189
      ]]></Sql>
carlos@0
   190
      <Parameter name="tab"/>
carlos@0
   191
  </SqlMethod>
carlos@0
   192
  <SqlMethod name="parentsColumnName" type="preparedStatement" return="multiple">
carlos@0
   193
      <SqlMethodComment>Name of the columns parent of the tab</SqlMethodComment>
carlos@0
   194
      <Sql><![CDATA[
carlos@0
   195
        SELECT ColumnName, AD_REFERENCE_id, ad_reference_value_id
carlos@0
   196
        FROM AD_FIELD, AD_COLUMN 
carlos@0
   197
        WHERE AD_FIELD.ad_column_id = AD_COLUMN.ad_column_id 
carlos@423
   198
        AND ad_tab_ID = TO_NUMBER(?) 
carlos@0
   199
        AND isParent='Y' 
carlos@0
   200
        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')
carlos@0
   201
                    AND ad_tab_id=(SELECT max(t1.ad_tab_id)
carlos@0
   202
                        FROM ad_tab t1, ad_tab t2
carlos@0
   203
                        WHERE t1.ad_window_id = t2.ad_window_id
carlos@0
   204
                        AND t2.ad_tab_id = AD_FIELD.AD_Tab_ID
carlos@0
   205
                        AND t1.tablevel = (t2.tablevel -1)
carlos@0
   206
                        AND t1.seqno =(SELECT max(t3.seqno)
carlos@0
   207
                        FROM ad_tab t3
carlos@0
   208
                        WHERE t3.ad_window_id = t2.ad_window_id
carlos@0
   209
                        AND t3.tablevel = (t2.tablevel -1)
carlos@0
   210
                        AND t3.seqno < t2.seqno))
carlos@0
   211
                    AND UPPER(c.columnname) = UPPER(AD_COLUMN.columnname))
carlos@0
   212
      ]]></Sql>
carlos@0
   213
      <Parameter name="tab"/>
carlos@0
   214
  </SqlMethod>
carlos@0
   215
  <SqlMethod name="parentsColumnNameKey" type="preparedStatement" return="multiple">
carlos@0
   216
      <SqlMethodComment>Name of the columns parent of the tab</SqlMethodComment>
carlos@0
   217
      <Sql><![CDATA[
carlos@0
   218
        SELECT ColumnName, AD_REFERENCE_id, ad_reference_value_id
carlos@0
   219
        FROM AD_FIELD, AD_COLUMN 
carlos@0
   220
        WHERE AD_FIELD.ad_column_id = AD_COLUMN.ad_column_id 
carlos@423
   221
        AND ad_tab_ID = TO_NUMBER(?) 
carlos@0
   222
        AND isKey='Y' 
carlos@0
   223
        AND EXISTS(SELECT 1 FROM AD_Tab t1, ad_tab t2 
carlos@0
   224
                        WHERE t1.ad_window_id = t2.ad_window_id
carlos@0
   225
                        AND t2.ad_tab_id = AD_FIELD.AD_Tab_ID 
carlos@0
   226
                        AND t1.tablevel = (t2.tablevel -1)
carlos@0
   227
                        AND t1.seqno < t2.seqno
carlos@0
   228
                        AND t1.ad_table_id = t2.ad_table_id) 
carlos@0
   229
      ]]></Sql>
carlos@0
   230
      <Parameter name="tab"/>
carlos@0
   231
  </SqlMethod>
carlos@0
   232
  <SqlMethod name="searchInfo" type="preparedStatement" return="multiple">
carlos@0
   233
      <SqlMethodComment>Name of the columns parent of the tab</SqlMethodComment>
carlos@0
   234
      <Sql><![CDATA[
carlos@0
   235
        select t.tablename, c.columnname 
carlos@0
   236
        from ad_ref_search rs, ad_table t, ad_column c
carlos@0
   237
        where rs.ad_table_id = t.ad_table_id 
carlos@0
   238
        and rs.ad_column_id = c.ad_column_id
carlos@423
   239
        and rs.ad_reference_ID = TO_NUMBER(?)
carlos@0
   240
      ]]></Sql>
carlos@0
   241
      <Parameter name="adReferenceValueId"/>
carlos@0
   242
  </SqlMethod>
carlos@0
   243
</SqlClass>