src/org/openbravo/erpCommon/utility/WindowTree_data.xsql
author Asier Lostalé <asier.lostale@openbravo.com>
Wed, 11 Feb 2009 07:40:02 +0000
changeset 3052 dc3cf1513510
parent 3049 b4f024448c42
child 3057 ace8bf1d5ef9
permissions -rw-r--r--
fixed bug 0007407: Organization's tree works randomly, fixed query for PG syntax
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@3049
    15
 * All portions are Copyright (C) 2001-2009 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="WindowTreeData" package="org.openbravo.erpCommon.utility">
carlos@0
    26
   <SqlClassComment></SqlClassComment>
carlos@0
    27
   <SqlMethod name="select" type="preparedStatement" return="multiple">
carlos@0
    28
      <SqlMethodComment></SqlMethodComment>
carlos@0
    29
      <Sql>
carlos@0
    30
        SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive, m.AD_Menu_ID AS ID, m.Name,m.Description,m.IsSummary,
victor@2086
    31
        m.Action, m.AD_Window_ID, m.AD_Process_ID, m.AD_Form_ID, m.AD_Workflow_ID, m.AD_Task_ID, Mo.ISINDEVELOPMENT, 
victor@2086
    32
        '' as IsReady, '' as AD_OrgType_ID
carlos@0
    33
        FROM AD_TreeNode tn left join AD_TreeBar tb on tn.AD_Tree_ID=tb.AD_Tree_ID 
carlos@0
    34
                                                  AND tn.Node_ID=tb.Node_ID 
juanpablo@1605
    35
                                                  AND tb.AD_User_ID  = ?,          
asier@1929
    36
             AD_Menu m,
asier@1929
    37
             AD_Module Mo
carlos@0
    38
        WHERE tn.Node_ID = m.AD_Menu_ID 
juanpablo@1605
    39
        AND tn.AD_Tree_ID = ? 
asier@1929
    40
        AND Mo.AD_Module_ID = m.AD_Module_ID
juanpablo@1605
    41
        ORDER BY COALESCE(tn.Parent_ID, '-1'), tn.SeqNo
carlos@0
    42
      </Sql>
carlos@0
    43
        <Parameter name="adUserId"/>
carlos@0
    44
        <Parameter name="editable" optional="true" type="none" after="WHERE " text="tn.IsActive='Y' AND m.isActive='Y' AND "/>
juanpablo@1605
    45
        <Parameter name="parentId" optional="true" after="WHERE " text="tn.Parent_ID = ? AND "/>
juanpablo@1605
    46
        <Parameter name="nodeId" optional="true" after="WHERE " text="tn.Node_ID = ? AND "/>
carlos@0
    47
        <Parameter name="adTreeId"/>
carlos@0
    48
   </SqlMethod>
carlos@0
    49
   <SqlMethod name="selectTrl" type="preparedStatement" return="multiple">
carlos@0
    50
      <SqlMethodComment></SqlMethodComment>
carlos@0
    51
      <Sql>
carlos@0
    52
        SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive, m.AD_Menu_ID AS ID, t.Name,t.Description,m.IsSummary,
asier@1929
    53
        m.Action, m.AD_Window_ID, m.AD_Process_ID, m.AD_Form_ID, m.AD_Workflow_ID, m.AD_Task_ID, Mo.ISINDEVELOPMENT
carlos@0
    54
        FROM AD_TreeNode tn left join AD_TreeBar tb on tn.AD_Tree_ID=tb.AD_Tree_ID  
carlos@0
    55
                                                      AND tn.Node_ID=tb.Node_ID
juanpablo@1605
    56
                                                      AND tb.AD_User_ID  = ? , 
asier@1929
    57
             AD_Menu m, AD_Menu_Trl t,
asier@1929
    58
             AD_Module Mo
carlos@0
    59
        WHERE  m.AD_Menu_ID = t.AD_Menu_ID    
carlos@0
    60
        AND tn.Node_ID = m.AD_Menu_ID 
juanpablo@1605
    61
        AND tn.AD_Tree_ID = ? 
carlos@0
    62
        AND t.AD_Language = ? 
asier@1929
    63
        AND Mo.AD_Module_ID = m.AD_Module_ID
juanpablo@1605
    64
        ORDER BY COALESCE(tn.Parent_ID, '-1'), tn.SeqNo
carlos@0
    65
      </Sql>
carlos@0
    66
        
carlos@0
    67
        <Parameter name="adUserId"/>
carlos@0
    68
        <Parameter name="editable" optional="true" type="none" after="WHERE " text="tn.IsActive='Y' AND m.isActive='Y' AND "/>
juanpablo@1605
    69
        <Parameter name="parentId" optional="true" after="WHERE " text="tn.Parent_ID = ? AND "/>
juanpablo@1605
    70
        <Parameter name="nodeId" optional="true" after="WHERE " text="tn.Node_ID = ? AND "/>
carlos@0
    71
        <Parameter name="adTreeId"/>
carlos@0
    72
        <Parameter name="adLanguage"/>
carlos@0
    73
   </SqlMethod>
carlos@0
    74
   <SqlMethod name="selectOrg" type="preparedStatement" return="multiple">
carlos@0
    75
      <SqlMethodComment></SqlMethodComment>
carlos@0
    76
      <Sql>
victor@2086
    77
        SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive, m.AD_Org_ID AS ID, m.Name,m.Description,m.IsSummary,m.IsReady, m.AD_OrgType_ID
carlos@0
    78
        FROM AD_TreeNode tn left join AD_TreeBar tb on tn.AD_Tree_ID=tb.AD_Tree_ID 
carlos@0
    79
                                            AND tn.Node_ID=tb.Node_ID 
juanpablo@1605
    80
                                            AND tb.AD_User_ID  = ? ,
carlos@0
    81
         AD_Org m
carlos@0
    82
        WHERE tn.Node_ID = m.AD_Org_ID 
juanpablo@1605
    83
        AND tn.AD_Tree_ID = ? 
juanpablo@1605
    84
        ORDER BY COALESCE(tn.Parent_ID, '-1'), tn.SeqNo
carlos@0
    85
      </Sql>
carlos@0
    86
        <Parameter name="adUserId"/>
carlos@0
    87
        <Parameter name="editable" optional="true" type="none" after="WHERE " text="tn.IsActive='Y' AND m.isActive='Y' AND "/>
juanpablo@1605
    88
        <Parameter name="parentId" optional="true" after="WHERE " text="tn.Parent_ID = ? AND "/>
juanpablo@1605
    89
        <Parameter name="nodeId" optional="true" after="WHERE " text="tn.Node_ID = ? AND "/>
carlos@0
    90
        <Parameter name="adTreeId"/>
carlos@0
    91
   </SqlMethod>
carlos@0
    92
   <SqlMethod name="selectProduct" type="preparedStatement" return="multiple">
carlos@0
    93
      <SqlMethodComment></SqlMethodComment>
carlos@0
    94
      <Sql>
carlos@0
    95
        SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive, m.M_Product_ID AS ID, m.Name,m.Description,m.IsSummary 
carlos@0
    96
        FROM AD_TreeNodePR tn left join AD_TreeBar tb on tn.AD_Tree_ID=tb.AD_Tree_ID
carlos@0
    97
                              AND tn.Node_ID=tb.Node_ID 
juanpablo@1605
    98
                              AND tb.AD_User_ID  = ? , M_Product m
carlos@0
    99
        WHERE tn.Node_ID = m.M_Product_ID 
juanpablo@1605
   100
        AND tn.AD_Tree_ID = ? 
juanpablo@1605
   101
        ORDER BY COALESCE(tn.Parent_ID, '-1'), tn.SeqNo
carlos@0
   102
      </Sql>
carlos@0
   103
        <Parameter name="adUserId"/>
carlos@0
   104
        <Parameter name="editable" optional="true" type="none" after="WHERE " text="tn.IsActive='Y' AND m.isActive='Y' AND "/>
juanpablo@1605
   105
        <Parameter name="parentId" optional="true" after="WHERE " text="tn.Parent_ID = ? AND "/>
juanpablo@1605
   106
        <Parameter name="nodeId" optional="true" after="WHERE " text="tn.Node_ID = ? AND "/>
carlos@0
   107
        <Parameter name="adTreeId"/>
carlos@0
   108
   </SqlMethod>
carlos@0
   109
   <SqlMethod name="selectProductCategory" type="preparedStatement" return="multiple">
carlos@0
   110
      <SqlMethodComment></SqlMethodComment>
carlos@0
   111
      <Sql>
carlos@0
   112
        SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive, m.M_Product_Category_ID AS ID, m.Name,m.Description, 'N' AS IsSummary 
carlos@0
   113
        FROM AD_TreeNode tn left join AD_TreeBar tb on tn.AD_Tree_ID=tb.AD_Tree_ID  
carlos@0
   114
                                            AND tn.Node_ID=tb.Node_ID 
juanpablo@1605
   115
                                            AND tb.AD_User_ID  = ? , 
carlos@0
   116
          M_Product_Category m
carlos@0
   117
        WHERE tn.Node_ID = m.M_Product_Category_ID 
juanpablo@1605
   118
        AND tn.AD_Tree_ID = ? 
juanpablo@1605
   119
        ORDER BY COALESCE(tn.Parent_ID, '-1'), tn.SeqNo
carlos@0
   120
      </Sql>
carlos@0
   121
        <Parameter name="adUserId"/>
carlos@0
   122
        <Parameter name="editable" optional="true" type="none" after="WHERE " text="tn.IsActive='Y' AND m.isActive='Y' AND "/>
juanpablo@1605
   123
        <Parameter name="parentId" optional="true" after="WHERE " text="tn.Parent_ID = ? AND "/>
juanpablo@1605
   124
        <Parameter name="nodeId" optional="true" after="WHERE " text="tn.Node_ID = ? AND "/>
carlos@0
   125
        <Parameter name="adTreeId"/>
carlos@0
   126
   </SqlMethod>
carlos@0
   127
   <SqlMethod name="selectBOM" type="preparedStatement" return="multiple">
carlos@0
   128
      <SqlMethodComment></SqlMethodComment>
carlos@0
   129
      <Sql>
carlos@0
   130
        SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive, m.M_Product_BOM_ID AS ID, p.Name,p.Description, p.IsBOM AS IsSummary 
carlos@0
   131
        FROM AD_TreeNode tn left join AD_TreeBar tb on  tn.AD_Tree_ID=tb.AD_Tree_ID 
carlos@0
   132
                                AND tn.Node_ID=tb.Node_ID 
juanpablo@1605
   133
                                AND tb.AD_User_ID = ? , 
carlos@0
   134
          M_Product_BOM m, M_Product p
carlos@0
   135
        WHERE tn.Node_ID = m.M_Product_BOM_ID 
carlos@0
   136
        AND m.M_ProductBOM_ID = p.M_Product_ID 
juanpablo@1605
   137
        AND tn.AD_Tree_ID = ? 
juanpablo@1605
   138
        ORDER BY COALESCE(tn.Parent_ID, '-1'), tn.SeqNo
carlos@0
   139
      </Sql>
carlos@0
   140
        <Parameter name="adUserId"/>
carlos@0
   141
        <Parameter name="editable" optional="true" type="none" after="WHERE " text="tn.IsActive='Y' AND m.isActive='Y' AND "/>
juanpablo@1605
   142
        <Parameter name="parentId" optional="true" after="WHERE " text="tn.Parent_ID = ? AND "/>
juanpablo@1605
   143
        <Parameter name="nodeId" optional="true" after="WHERE " text="tn.Node_ID = ? AND "/>
carlos@0
   144
        <Parameter name="adTreeId"/>
carlos@0
   145
   </SqlMethod>
carlos@0
   146
   <SqlMethod name="selectElementValue" type="preparedStatement" return="multiple">
carlos@0
   147
      <SqlMethodComment></SqlMethodComment>
carlos@0
   148
      <Sql>
juanpablo@1605
   149
        SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive, m.C_ElementValue_ID AS ID, AD_COLUMN_IDENTIFIER(to_char('c_elementvalue'),to_char(m.C_ElementValue_ID), to_char(?)) as Name,m.Description,m.IsSummary 
carlos@0
   150
        FROM AD_TreeNode tn left join AD_TreeBar tb on tn.AD_Tree_ID=tb.AD_Tree_ID 
carlos@0
   151
                                AND tn.Node_ID=tb.Node_ID 
juanpablo@1605
   152
                                AND tb.AD_User_ID = ? , 
carlos@0
   153
          C_ElementValue m
carlos@0
   154
        WHERE tn.Node_ID = m.C_ElementValue_ID 
juanpablo@1605
   155
        AND tn.AD_Tree_ID = ? 
juanpablo@1605
   156
        AND m.C_Element_ID = ? 
juanpablo@1605
   157
        ORDER BY COALESCE(tn.Parent_ID, '-1'), tn.SeqNo
carlos@0
   158
      </Sql>
juanpablo@1605
   159
	  	<Parameter name="adLanguage"/>
carlos@0
   160
        <Parameter name="adUserId"/>
carlos@0
   161
        <Parameter name="editable" optional="true" type="none" after="WHERE " text="tn.IsActive='Y' AND m.isActive='Y' AND "/>
juanpablo@1605
   162
        <Parameter name="parentId" optional="true" after="WHERE " text="tn.Parent_ID = ? AND "/>
juanpablo@1605
   163
        <Parameter name="nodeId" optional="true" after="WHERE " text="tn.Node_ID = ? AND "/>
carlos@0
   164
        <Parameter name="adTreeId"/>
carlos@0
   165
        <Parameter name="cElementId"/>
carlos@0
   166
   </SqlMethod>
carlos@0
   167
   <SqlMethod name="selectBPartner" type="preparedStatement" return="multiple">
carlos@0
   168
      <SqlMethodComment></SqlMethodComment>
carlos@0
   169
      <Sql>
carlos@0
   170
        SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive, m.C_BPartner_ID AS ID, m.Name,m.Description,m.IsSummary 
carlos@0
   171
        FROM AD_TreeNodeBP tn left join AD_TreeBar tb on tn.AD_Tree_ID=tb.AD_Tree_ID 
carlos@0
   172
                              AND tn.Node_ID=tb.Node_ID 
juanpablo@1605
   173
                              AND tb.AD_User_ID  = ?  , 
carlos@0
   174
             C_BPartner m
carlos@0
   175
        WHERE tn.Node_ID = m.C_BPartner_ID 
juanpablo@1605
   176
        AND tn.AD_Tree_ID = ? 
juanpablo@1605
   177
        ORDER BY COALESCE(tn.Parent_ID, '-1'), tn.SeqNo
carlos@0
   178
      </Sql>
carlos@0
   179
        <Parameter name="adUserId"/>
carlos@0
   180
        <Parameter name="editable" optional="true" type="none" after="WHERE " text="tn.IsActive='Y' AND m.isActive='Y' AND "/>
juanpablo@1605
   181
        <Parameter name="parentId" optional="true" after="WHERE " text="tn.Parent_ID = ? AND "/>
juanpablo@1605
   182
        <Parameter name="nodeId" optional="true" after="WHERE " text="tn.Node_ID = ? AND "/>
carlos@0
   183
        <Parameter name="adTreeId"/>
carlos@0
   184
   </SqlMethod>
carlos@0
   185
   <SqlMethod name="selectCampaign" type="preparedStatement" return="multiple">
carlos@0
   186
      <SqlMethodComment></SqlMethodComment>
carlos@0
   187
      <Sql>
carlos@0
   188
        SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive, m.C_Campaign_ID AS ID, m.Name,m.Description, 'N' AS IsSummary 
carlos@0
   189
        FROM AD_TreeNode tn left join AD_TreeBar tb on tn.AD_Tree_ID=tb.AD_Tree_ID 
carlos@0
   190
                            AND tn.Node_ID=tb.Node_ID 
juanpablo@1605
   191
                            AND tb.AD_User_ID = ? , 
carlos@0
   192
              C_Campaign m
carlos@0
   193
        WHERE  tn.Node_ID = m.C_Campaign_ID 
juanpablo@1605
   194
        AND tn.AD_Tree_ID = ? 
juanpablo@1605
   195
        ORDER BY COALESCE(tn.Parent_ID, '-1'), tn.SeqNo
carlos@0
   196
      </Sql>
carlos@0
   197
        <Parameter name="adUserId"/>
carlos@0
   198
        <Parameter name="editable" optional="true" type="none" after="WHERE " text="tn.IsActive='Y' AND m.isActive='Y' AND "/>
juanpablo@1605
   199
        <Parameter name="parentId" optional="true" after="WHERE " text="tn.Parent_ID = ? AND "/>
juanpablo@1605
   200
        <Parameter name="nodeId" optional="true" after="WHERE " text="tn.Node_ID = ? AND "/>
carlos@0
   201
        <Parameter name="adTreeId"/>
carlos@0
   202
   </SqlMethod>
carlos@0
   203
   <SqlMethod name="selectProject" type="preparedStatement" return="multiple">
carlos@0
   204
      <SqlMethodComment></SqlMethodComment>
carlos@0
   205
      <Sql>
carlos@0
   206
        SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive, m.C_Project_ID AS ID, m.Name,m.Description,m.IsSummary 
carlos@0
   207
        FROM AD_TreeNode tn left join AD_TreeBar tb on  tn.AD_Tree_ID=tb.AD_Tree_ID 
carlos@0
   208
                            AND tn.Node_ID=tb.Node_ID 
juanpablo@1605
   209
                            AND tb.AD_User_ID  = ?  , 
carlos@0
   210
              C_Project m
carlos@0
   211
        WHERE tn.Node_ID = m.C_Project_ID 
juanpablo@1605
   212
        AND tn.AD_Tree_ID = ? 
juanpablo@1605
   213
        ORDER BY COALESCE(tn.Parent_ID, '-1'), tn.SeqNo
carlos@0
   214
      </Sql>
carlos@0
   215
        <Parameter name="adUserId"/>
carlos@0
   216
        <Parameter name="editable" optional="true" type="none" after="WHERE " text="tn.IsActive='Y' AND m.isActive='Y' AND "/>
juanpablo@1605
   217
        <Parameter name="parentId" optional="true" after="WHERE " text="tn.Parent_ID = ? AND "/>
juanpablo@1605
   218
        <Parameter name="nodeId" optional="true" after="WHERE " text="tn.Node_ID = ? AND "/>
carlos@0
   219
        <Parameter name="adTreeId"/>
carlos@0
   220
   </SqlMethod>
carlos@0
   221
   <SqlMethod name="selectActivity" type="preparedStatement" return="multiple">
carlos@0
   222
      <SqlMethodComment></SqlMethodComment>
carlos@0
   223
      <Sql>
carlos@0
   224
        SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive, m.C_Activity_ID AS ID, m.Name,m.Description,m.IsSummary 
carlos@0
   225
        FROM AD_TreeNode tn left join AD_TreeBar tb on tn.AD_Tree_ID=tb.AD_Tree_ID 
carlos@0
   226
                            AND tn.Node_ID=tb.Node_ID 
juanpablo@1605
   227
                            AND tb.AD_User_ID  = ?  , 
carlos@0
   228
                        C_Activity m
carlos@0
   229
        WHERE  tn.Node_ID = m.C_Activity_ID 
juanpablo@1605
   230
        AND tn.AD_Tree_ID = ? 
juanpablo@1605
   231
        ORDER BY COALESCE(tn.Parent_ID, '-1'), tn.SeqNo
carlos@0
   232
      </Sql>
carlos@0
   233
        <Parameter name="adUserId"/>
carlos@0
   234
        <Parameter name="editable" optional="true" type="none" after="WHERE " text="tn.IsActive='Y' AND m.isActive='Y' AND "/>
juanpablo@1605
   235
        <Parameter name="parentId" optional="true" after="WHERE " text="tn.Parent_ID = ? AND "/>
juanpablo@1605
   236
        <Parameter name="nodeId" optional="true" after="WHERE " text="tn.Node_ID = ? AND "/>
carlos@0
   237
        <Parameter name="adTreeId"/>
carlos@0
   238
   </SqlMethod>
carlos@0
   239
   <SqlMethod name="selectSalesRegion" type="preparedStatement" return="multiple">
carlos@0
   240
      <SqlMethodComment></SqlMethodComment>
carlos@0
   241
      <Sql>
carlos@0
   242
        SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive, m.C_SalesRegion_ID AS ID, m.Name,m.Description,m.IsSummary 
carlos@0
   243
        FROM AD_TreeNode tn left join AD_TreeBar tb on  tn.AD_Tree_ID=tb.AD_Tree_ID
carlos@0
   244
                                  AND tn.Node_ID=tb.Node_ID 
juanpablo@1605
   245
                                  AND tb.AD_User_ID = ?  , 
carlos@0
   246
            C_SalesRegion m
carlos@0
   247
        WHERE tn.Node_ID = m.C_SalesRegion_ID 
juanpablo@1605
   248
        AND tn.AD_Tree_ID = ? 
juanpablo@1605
   249
        ORDER BY COALESCE(tn.Parent_ID, '-1'), tn.SeqNo
carlos@0
   250
      </Sql>
carlos@0
   251
        <Parameter name="adUserId"/>
carlos@0
   252
        <Parameter name="editable" optional="true" type="none" after="WHERE " text="tn.IsActive='Y' AND m.isActive='Y' AND "/>
juanpablo@1605
   253
        <Parameter name="parentId" optional="true" after="WHERE " text="tn.Parent_ID = ? AND "/>
juanpablo@1605
   254
        <Parameter name="nodeId" optional="true" after="WHERE " text="tn.Node_ID = ? AND "/>
carlos@0
   255
        <Parameter name="adTreeId"/>
carlos@0
   256
   </SqlMethod>
carlos@0
   257
   <SqlMethod name="selectAccountingReport" type="preparedStatement" return="multiple">
carlos@0
   258
      <SqlMethodComment></SqlMethodComment>
carlos@0
   259
      <Sql>
carlos@0
   260
        SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive, ar.AD_AccountingRpt_Element_ID AS ID, ar.Name,ar.Description,ar.IsSummary 
carlos@0
   261
        FROM AD_TreeNode tn left join AD_TreeBar tb on  tn.AD_Tree_ID=tb.AD_Tree_ID
carlos@0
   262
                                  AND tn.Node_ID=tb.Node_ID 
juanpablo@1605
   263
                                  AND tb.AD_User_ID = ?  , 
carlos@0
   264
            AD_AccountingRpt_Element ar
carlos@0
   265
        WHERE tn.Node_ID = ar.AD_AccountingRpt_Element_ID 
juanpablo@1605
   266
        AND tn.AD_Tree_ID = ? 
juanpablo@1605
   267
        ORDER BY COALESCE(tn.Parent_ID, '-1'), tn.SeqNo
carlos@0
   268
      </Sql>
carlos@0
   269
        <Parameter name="adUserId"/>
carlos@0
   270
        <Parameter name="editable" optional="true" type="none" after="WHERE " text="tn.IsActive='Y' AND ar.isActive='Y' AND "/>
juanpablo@1605
   271
        <Parameter name="parentId" optional="true" after="WHERE " text="tn.Parent_ID = ? AND "/>
juanpablo@1605
   272
        <Parameter name="nodeId" optional="true" after="WHERE " text="tn.Node_ID = ? AND "/>
carlos@0
   273
        <Parameter name="adTreeId"/>
carlos@0
   274
   </SqlMethod>
carlos@0
   275
   <SqlMethod name="selectTaxReport" type="preparedStatement" return="multiple">
carlos@0
   276
      <SqlMethodComment></SqlMethodComment>
carlos@0
   277
      <Sql>
carlos@0
   278
        SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive, tr.C_TAX_REPORT_ID AS ID, tr.NAME,tr.Description,tr.IsSummary 
carlos@0
   279
        FROM AD_TREENODE tn LEFT JOIN AD_TREEBAR tb ON  tn.AD_Tree_ID=tb.AD_Tree_ID
carlos@0
   280
                                  AND tn.Node_ID=tb.Node_ID 
juanpablo@1605
   281
                                  AND tb.AD_User_ID = ?  , 
carlos@0
   282
            C_TAX_REPORT tr
carlos@0
   283
        WHERE tn.Node_ID = tr.C_TAX_REPORT_ID
juanpablo@1605
   284
        AND tn.AD_Tree_ID = ? 
juanpablo@1605
   285
        ORDER BY COALESCE(tn.Parent_ID, '-1'), tn.SeqNo
carlos@0
   286
      </Sql>
carlos@0
   287
        <Parameter name="adUserId"/>
carlos@0
   288
        <Parameter name="editable" optional="true" type="none" after="WHERE " text="tn.IsActive='Y' AND ar.isActive='Y' AND "/>
juanpablo@1605
   289
        <Parameter name="parentId" optional="true" after="WHERE " text="tn.Parent_ID = ? AND "/>
juanpablo@1605
   290
        <Parameter name="nodeId" optional="true" after="WHERE " text="tn.Node_ID = ? AND "/>
carlos@0
   291
        <Parameter name="adTreeId"/>
carlos@0
   292
   </SqlMethod>
carlos@0
   293
   <SqlMethod name="selectTreeID" type="preparedStatement" return="multiple">
carlos@0
   294
      <SqlMethodComment></SqlMethodComment>
carlos@0
   295
      <Sql>
asier@3049
   296
        SELECT NODE_ID, ID, NAME 
asier@3049
   297
        FROM (
asier@3049
   298
         SELECT AD_Client_ID as Node_ID, AD_Tree_ID AS ID, NAME, 1 AS SEQNO
carlos@0
   299
        FROM AD_Tree 
asier@3049
   300
        WHERE AD_Client_ID IN ('1')  
asier@3049
   301
        AND AD_Client_ID != '0'
carlos@0
   302
        AND TreeType = ? 
carlos@0
   303
        AND IsActive = 'Y'
asier@3049
   304
      UNION
asier@3049
   305
       SELECT AD_Client_ID as Node_ID, AD_Tree_ID AS ID, NAME, 2 AS SEQNO
asier@3049
   306
        FROM AD_Tree 
asier@3049
   307
        WHERE AD_Client_ID IN ('2') 
asier@3049
   308
        AND AD_Client_id = '0'
asier@3049
   309
        AND TreeType = ?
asier@3052
   310
        AND IsActive = 'Y') f
asier@3049
   311
        ORDER BY SEQNO
carlos@0
   312
      </Sql>
asier@906
   313
        <Parameter name="adClientId" optional="true" type="replace" after="AD_Client_ID IN (" text="'1'"/>
carlos@0
   314
        <Parameter name="treetype"/>
asier@3049
   315
        <Parameter name="adClientId" optional="true" type="replace" after="AD_Client_id IN (" text="'2'"/>
asier@3049
   316
        <Parameter name="treetype"/>
carlos@0
   317
   </SqlMethod>
carlos@0
   318
   <SqlMethod name="TreeName" type="preparedStatement" return="multiple">
carlos@0
   319
      <SqlMethodComment></SqlMethodComment>
carlos@0
   320
      <Sql>
carlos@0
   321
        SELECT NAME, DESCRIPTION 
carlos@0
   322
        FROM AD_Tree 
juanpablo@1605
   323
        WHERE AD_Tree_ID = ? 
carlos@0
   324
        AND AD_Org_ID IN ('1') 
carlos@0
   325
        AND TreeType = ? 
carlos@0
   326
        AND IsActive = 'Y'
carlos@0
   327
      </Sql>
carlos@0
   328
        <Parameter name="adTreeId"/>
carlos@0
   329
        <Parameter name="adClientId" optional="true" type="replace" after="AD_Org_ID IN (" text="'1'"/>
carlos@0
   330
        <Parameter name="treetype"/>
carlos@0
   331
   </SqlMethod>
carlos@0
   332
   <SqlMethod name="selectKey" type="preparedStatement" return="String" default="">
carlos@0
   333
      <SqlMethodComment></SqlMethodComment>
carlos@0
   334
      <Sql>
carlos@0
   335
        SELECT C.COLUMNNAME
carlos@0
   336
        FROM AD_TAB T, AD_COLUMN C 
carlos@0
   337
        WHERE T.AD_TABLE_ID = C.AD_TABLE_ID
carlos@0
   338
        AND (C.ISKEY = 'Y'
carlos@0
   339
        OR C.ISSECONDARYKEY='Y')
juanpablo@1605
   340
        AND T.AD_TAB_ID = ? 
carlos@0
   341
        AND C.ISACTIVE='Y'
carlos@0
   342
        ORDER BY C.SEQNO
carlos@0
   343
      </Sql>
carlos@0
   344
        <Parameter name="adTabId"/>
carlos@0
   345
   </SqlMethod>
carlos@0
   346
   <SqlMethod name="selectTableName" type="preparedStatement" return="String" default="">
carlos@0
   347
      <SqlMethodComment></SqlMethodComment>
carlos@0
   348
      <Sql>
carlos@0
   349
        SELECT TB.TABLENAME
carlos@0
   350
        FROM AD_TAB T, AD_TABLE TB 
carlos@0
   351
        WHERE T.AD_TABLE_ID = TB.AD_TABLE_ID
juanpablo@1605
   352
        AND T.AD_TAB_ID = ? 
carlos@0
   353
      </Sql>
carlos@0
   354
        <Parameter name="adTabId"/>
carlos@0
   355
   </SqlMethod>
carlos@0
   356
   <SqlMethod name="selectEditable" type="preparedStatement" return="String" default="N">
carlos@0
   357
      <SqlMethodComment></SqlMethodComment>
carlos@0
   358
      <Sql>
carlos@0
   359
        SELECT ISREADONLY
carlos@0
   360
        FROM AD_TAB 
juanpablo@1605
   361
        WHERE AD_TAB_ID = ? 
carlos@0
   362
      </Sql>
carlos@0
   363
        <Parameter name="adTabId"/>
carlos@0
   364
   </SqlMethod>
carlos@0
   365
   <SqlMethod name="selectTabName" type="preparedStatement" return="multiple">
carlos@0
   366
      <SqlMethodComment></SqlMethodComment>
carlos@0
   367
      <Sql>
carlos@0
   368
        SELECT w.Name as Description, t.Name as NAME
carlos@0
   369
        FROM AD_Window w, AD_TAB t
carlos@0
   370
        WHERE w.AD_Window_ID = t.AD_Window_ID 
juanpablo@1605
   371
        AND t.AD_TAB_ID = ? 
carlos@0
   372
        AND t.isActive = 'Y'
carlos@0
   373
      </Sql>
carlos@0
   374
        <Parameter name="adTabId"/>
carlos@0
   375
   </SqlMethod>
carlos@0
   376
   <SqlMethod name="selectParents" type="preparedStatement" return="multiple">
carlos@0
   377
      <SqlMethodComment></SqlMethodComment>
carlos@0
   378
      <Sql>
carlos@0
   379
        SELECT COLUMNNAME AS Name, ad_window_id AS node_Id
carlos@0
   380
        FROM AD_COLUMN c, AD_FIELD f, AD_TAB t 
carlos@0
   381
        WHERE t.ad_tab_id = f.ad_tab_id 
carlos@0
   382
        AND f.ad_column_id = c.ad_column_id 
juanpablo@1605
   383
        AND t.ad_tab_id = ?
carlos@0
   384
        AND c.isparent='Y' 
carlos@0
   385
        ORDER BY f.SeqNo
carlos@0
   386
      </Sql>
carlos@0
   387
        <Parameter name="adTabId"/>
carlos@0
   388
   </SqlMethod>
carlos@0
   389
   <SqlMethod name="update" type="preparedStatement" return="rowCount">
carlos@0
   390
      <SqlMethodComment></SqlMethodComment>
carlos@0
   391
      <Sql>
juanpablo@1605
   392
        UPDATE AD_TREENODE SET UPDATED=now(), UPDATEDBY = ?, PARENT_id = ?, SEQNO=TO_NUMBER(?) 
juanpablo@1605
   393
        WHERE AD_TREE_ID = ? 
juanpablo@1605
   394
        AND NODE_ID = ?
carlos@0
   395
      </Sql>
carlos@0
   396
        <Parameter name="updatedby"/>
carlos@0
   397
        <Parameter name="parentId"/>
carlos@0
   398
        <Parameter name="seqno"/>
carlos@0
   399
        <Parameter name="adTreeId"/>
carlos@0
   400
        <Parameter name="nodeId"/>
carlos@0
   401
   </SqlMethod>
carlos@0
   402
   <SqlMethod name="updatePR" type="preparedStatement" return="rowCount">
carlos@0
   403
      <SqlMethodComment></SqlMethodComment>
carlos@0
   404
      <Sql>
juanpablo@1605
   405
        UPDATE AD_TREENODEPR SET UPDATED=now(), UPDATEDBY = ?, PARENT_id = ?, SEQNO=TO_NUMBER(?) 
juanpablo@1605
   406
        WHERE AD_TREE_ID = ? 
juanpablo@1605
   407
        AND NODE_ID = ?
carlos@0
   408
      </Sql>
carlos@0
   409
        <Parameter name="updatedby"/>
carlos@0
   410
        <Parameter name="parentId"/>
carlos@0
   411
        <Parameter name="seqno"/>
carlos@0
   412
        <Parameter name="adTreeId"/>
carlos@0
   413
        <Parameter name="nodeId"/>
carlos@0
   414
   </SqlMethod>
carlos@0
   415
   <SqlMethod name="updateBP" type="preparedStatement" return="rowCount">
carlos@0
   416
      <SqlMethodComment></SqlMethodComment>
carlos@0
   417
      <Sql>
juanpablo@1605
   418
        UPDATE AD_TREENODEBP SET UPDATED=now(), UPDATEDBY = ?, PARENT_id = ?, SEQNO=TO_NUMBER(?) 
juanpablo@1605
   419
        WHERE AD_TREE_ID = ? 
juanpablo@1605
   420
        AND NODE_ID = ?
carlos@0
   421
      </Sql>
carlos@0
   422
        <Parameter name="updatedby"/>
carlos@0
   423
        <Parameter name="parentId"/>
carlos@0
   424
        <Parameter name="seqno"/>
carlos@0
   425
        <Parameter name="adTreeId"/>
carlos@0
   426
        <Parameter name="nodeId"/>
carlos@0
   427
   </SqlMethod>
carlos@0
   428
</SqlClass>