src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger_data.xsql
author David Baz Fayos <david.baz@openbravo.com>
Tue, 27 May 2008 15:26:10 +0000
changeset 1044 8691bbc94032
parent 521 a4543fd50290
child 1605 8a0fe0193bef
permissions -rw-r--r--
Removed old frame parameter of windowTableId
carlos@0
     1
<?xml version="1.0" encoding="UTF-8" ?>
carlos@0
     2
<!--
carlos@0
     3
 *************************************************************************
carlos@0
     4
 * The contents of this file are subject to the Openbravo  Public  License
carlos@0
     5
 * Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
carlos@0
     6
 * Version 1.1  with a permitted attribution clause; you may not  use this
carlos@0
     7
 * file except in compliance with the License. You  may  obtain  a copy of
carlos@0
     8
 * the License at http://www.openbravo.com/legal/license.html 
carlos@0
     9
 * Software distributed under the License  is  distributed  on  an "AS IS"
carlos@0
    10
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
carlos@0
    11
 * License for the specific  language  governing  rights  and  limitations
carlos@0
    12
 * under the License. 
carlos@0
    13
 * The Original Code is Openbravo ERP. 
carlos@0
    14
 * The Initial Developer of the Original Code is Openbravo SL 
carlos@0
    15
 * All portions are Copyright (C) 2001-2006 Openbravo SL 
carlos@0
    16
 * All Rights Reserved.
carlos@0
    17
 * Contributor(s):  ______________________________________.
carlos@0
    18
 ************************************************************************
carlos@0
    19
-->
carlos@0
    20
carlos@0
    21
carlos@0
    22
carlos@0
    23
carlos@0
    24
carlos@0
    25
<SqlClass name="ReportGeneralLedgerData" package="org.openbravo.erpCommon.ad_reports">
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
    <![CDATA[
carlos@521
    31
     SELECT AA.*, 0 AS TOTALDR, 0 AS TOTALCR, '' AS PADRE FROM (
carlos@0
    32
      SELECT VALUE, PARTNER, dateacct, NAME, 
carlos@0
    33
      (CASE WHEN ABS(SUM(AMTACCTDR))>ABS(SUM(AMTACCTCR)) THEN SUM(AMTACCTDR)-SUM(AMTACCTCR) END) AS AMTACCTDR,
carlos@0
    34
      (CASE WHEN ABS(SUM(AMTACCTDR))<=ABS(SUM(AMTACCTCR)) THEN SUM(AMTACCTCR)-SUM(AMTACCTDR) END) AS AMTACCTCR,
carlos@0
    35
      (SUM(amtacctdr-amtacctcr)) AS TOTAL, fact_acct_group_id, ID, 0 AS SALDO, MAX(FACT_ACCT_ID) AS FACT_ACCT_ID,
carlos@0
    36
      DESCRIPTION, C_BPARTNER_ID
carlos@0
    37
      FROM (
carlos@0
    38
      SELECT FACT_ACCT.ACCTVALUE AS VALUE, C_BPARTNER.NAME AS PARTNER, dateacct, FACT_ACCT.ACCTDESCRIPTION AS NAME, 
carlos@0
    39
      amtacctdr,  amtacctcr AS amtacctcr, fact_acct_group_id, FACT_ACCT.ACCOUNT_ID AS ID, FACT_ACCT.FACT_ACCT_ID, 
carlos@0
    40
      fact_acct.record_id, fact_acct.ad_table_id, FACT_ACCT.DESCRIPTION, FACT_ACCT.C_BPARTNER_ID
carlos@0
    41
      FROM fact_acct left join c_bpartner on FACT_ACCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
carlos@0
    42
      WHERE fact_acct.AD_Client_ID IN ('1')
carlos@0
    43
      AND fact_acct.AD_ORG_ID IN ('1')
carlos@0
    44
      AND 1=1
carlos@0
    45
      AND FACT_ACCT.AD_ORG_ID IN ('2')
carlos@0
    46
      AND 2=2 ) AAA
carlos@0
    47
      GROUP BY ad_table_id, record_id, VALUE, DATEACCT, NAME, FACT_ACCT_GROUP_ID, ID, DESCRIPTION, PARTNER, C_BPARTNER_ID) AA
carlos@0
    48
      WHERE 3=3
carlos@0
    49
        AND 4=4
carlos@0
    50
      ORDER BY PARTNER, VALUE, dateacct, fact_acct_id
carlos@0
    51
      ]]></Sql>
carlos@0
    52
    <Field name="rownum" value="count"/>
carlos@0
    53
    <Parameter name="adUserClient" type="replace" optional="true" after="WHERE fact_acct.AD_Client_ID IN (" text="'1'"/>
carlos@0
    54
    <Parameter name="adUserOrg" type="replace" optional="true" after="AND fact_acct.AD_ORG_ID IN (" text="'1'"/>
eduardo@445
    55
    <Parameter name="acctschema" optional="true" after="AND 1=1"><![CDATA[and fact_acct.C_ACCTSCHEMA_ID = to_number(?)]]></Parameter>
carlos@0
    56
    <Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[AND dateacct >= to_date(?) ]]></Parameter>
carlos@0
    57
    <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[AND dateacct < to_date(?) ]]></Parameter>
carlos@0
    58
    <Parameter name="org" type="replace" optional="true" after="AND FACT_ACCT.AD_ORG_ID IN (" text="'2'"/>
carlos@0
    59
    <Parameter name="account" type="argument" optional="true" after="AND 2=2"><![CDATA[ AND FACT_ACCT.ACCOUNT_ID IN ]]></Parameter>
carlos@0
    60
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND FACT_ACCT.C_BPARTNER_ID IN]]></Parameter>
carlos@0
    61
    <Parameter name="paramAmtFrom" optional="true" after="3=3"><![CDATA[ and coalesce(amtacctdr,amtacctcr)>=? ]]></Parameter>   
carlos@0
    62
    <Parameter name="paramAmtTo" optional="true" after="4=4"><![CDATA[ and coalesce(amtacctdr,amtacctcr)<=? ]]></Parameter>
carlos@0
    63
    <Parameter name="orden" type="replace" optional="true" after="ORDER BY " text="PARTNER"/>
carlos@0
    64
  </SqlMethod>
carlos@0
    65
  
carlos@0
    66
  <SqlMethod name="selectHiding" type="preparedStatement" return="multiple">
carlos@0
    67
    <SqlMethodComment></SqlMethodComment>
carlos@0
    68
    <Sql>
carlos@0
    69
    <![CDATA[
carlos@0
    70
     SELECT AA.*, 0 AS TOTALDR, 0 AS TOTALCR FROM (
carlos@0
    71
      SELECT VALUE, PARTNER, dateacct, NAME, 
carlos@0
    72
      (CASE WHEN ABS(SUM(AMTACCTDR))>ABS(SUM(AMTACCTCR)) THEN SUM(AMTACCTDR)-SUM(AMTACCTCR) END) AS AMTACCTDR,
carlos@0
    73
      (CASE WHEN ABS(SUM(AMTACCTDR))<=ABS(SUM(AMTACCTCR)) THEN SUM(AMTACCTCR)-SUM(AMTACCTDR) END) AS AMTACCTCR,
carlos@0
    74
      (SUM(amtacctdr-amtacctcr)) AS TOTAL, fact_acct_group_id, ID, 0 AS SALDO, MAX(FACT_ACCT_ID) AS FACT_ACCT_ID,
carlos@0
    75
      DESCRIPTION, C_BPARTNER_ID
carlos@0
    76
      FROM (
carlos@0
    77
      SELECT FACT_ACCT.ACCTVALUE AS VALUE, C_BPARTNER.NAME AS PARTNER, dateacct, FACT_ACCT.ACCTDESCRIPTION AS NAME, 
carlos@0
    78
      amtacctdr,  amtacctcr AS amtacctcr, fact_acct_group_id, FACT_ACCT.ACCOUNT_ID AS ID, FACT_ACCT.FACT_ACCT_ID, 
carlos@0
    79
      fact_acct.record_id, fact_acct.ad_table_id, FACT_ACCT.DESCRIPTION, FACT_ACCT.C_BPARTNER_ID
carlos@0
    80
      FROM fact_acct left join c_bpartner on FACT_ACCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
carlos@0
    81
      WHERE fact_acct.AD_Client_ID IN ('1')
carlos@0
    82
      AND fact_acct.AD_ORG_ID IN ('1')
carlos@0
    83
      AND 1=1
carlos@0
    84
      AND FACT_ACCT.AD_ORG_ID IN ('2')
carlos@0
    85
      AND 2=2
carlos@0
    86
      AND ((record_id2 is null) or (0 != (select sum(amtacctdr-amtacctcr)
carlos@0
    87
                          from fact_acct f1
carlos@0
    88
                        where f1.record_id2 = fact_acct.record_id2
carlos@0
    89
                         and f1.account_id = fact_acct.account_id
carlos@0
    90
                         AND 5=5
carlos@0
    91
                         )))) AAA
carlos@0
    92
      GROUP BY ad_table_id, record_id, VALUE, DATEACCT, NAME, FACT_ACCT_GROUP_ID, ID, DESCRIPTION, PARTNER, C_BPARTNER_ID) AA
carlos@0
    93
      WHERE 3=3
carlos@0
    94
        AND 4=4
carlos@0
    95
      ORDER BY PARTNER, VALUE, dateacct, fact_acct_id
carlos@0
    96
      ]]></Sql>
carlos@0
    97
    <Field name="rownum" value="count"/>
carlos@0
    98
    <Parameter name="adUserClient" type="replace" optional="true" after="WHERE fact_acct.AD_Client_ID IN (" text="'1'"/>
carlos@0
    99
    <Parameter name="adUserOrg" type="replace" optional="true" after="AND fact_acct.AD_ORG_ID IN (" text="'1'"/>
eduardo@445
   100
    <Parameter name="acctSchema" optional="true" after="AND 1=1"><![CDATA[and fact_acct.C_ACCTSCHEMA_ID = to_number(?) ]]></Parameter>
carlos@0
   101
    <Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[AND dateacct >= to_date(?) ]]></Parameter>
carlos@0
   102
    <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[AND dateacct < to_date(?) ]]></Parameter>
carlos@0
   103
    <Parameter name="org" type="replace" optional="true" after="AND FACT_ACCT.AD_ORG_ID IN (" text="'2'"/>
carlos@0
   104
    <Parameter name="account" type="argument" optional="true" after="AND 2=2"><![CDATA[ AND FACT_ACCT.ACCOUNT_ID IN ]]></Parameter>
carlos@0
   105
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND FACT_ACCT.C_BPARTNER_ID IN]]></Parameter>
carlos@0
   106
carlos@0
   107
    <Parameter name="parDateTo" optional="true" after="AND 5=5"><![CDATA[AND dateacct < to_date(?) ]]></Parameter>
carlos@0
   108
    
eduardo@445
   109
    <Parameter name="paramAmtFrom" optional="true" after="3=3"><![CDATA[ and coalesce(amtacctdr,amtacctcr)>=to_number(?) ]]></Parameter>   
eduardo@445
   110
    <Parameter name="paramAmtTo" optional="true" after="4=4"><![CDATA[ and coalesce(amtacctdr,amtacctcr)<=to_number(?) ]]></Parameter>
carlos@0
   111
    <Parameter name="orden" type="replace" optional="true" after="ORDER BY " text="PARTNER"/>
carlos@0
   112
  </SqlMethod>
carlos@0
   113
  
carlos@0
   114
  <SqlMethod name="selectAll" type="preparedStatement" return="multiple">
carlos@0
   115
    <SqlMethodComment></SqlMethodComment>
carlos@0
   116
    <Sql>
carlos@0
   117
    <![CDATA[
carlos@0
   118
      select * from (
carlos@0
   119
      SELECT VALUE, PARTNER, dateacct, NAME, 
carlos@0
   120
      (CASE WHEN ABS(SUM(AMTACCTDR))>ABS(SUM(AMTACCTCR)) THEN SUM(AMTACCTDR)-SUM(AMTACCTCR) END) AS AMTACCTDR,
carlos@0
   121
      (CASE WHEN ABS(SUM(AMTACCTDR))<=ABS(SUM(AMTACCTCR)) THEN SUM(AMTACCTCR)-SUM(AMTACCTDR) END) AS AMTACCTCR,
carlos@0
   122
      (SUM(amtacctdr-amtacctcr)) AS TOTAL, fact_acct_group_id, ID, 0 AS SALDO, MAX(FACT_ACCT_ID) AS FACT_ACCT_ID,
carlos@0
   123
      DESCRIPTION, C_BPARTNER_ID
carlos@0
   124
      FROM (
carlos@0
   125
      SELECT FACT_ACCT.ACCTVALUE AS VALUE, C_BPARTNER.NAME AS PARTNER, dateacct, FACT_ACCT.ACCTDESCRIPTION AS NAME, 
carlos@0
   126
      amtacctdr,  amtacctcr AS amtacctcr, fact_acct_group_id, FACT_ACCT.ACCOUNT_ID AS ID, FACT_ACCT.FACT_ACCT_ID, 
carlos@0
   127
      fact_acct.record_id, fact_acct.ad_table_id, FACT_ACCT.DESCRIPTION, FACT_ACCT.C_BPARTNER_ID
carlos@0
   128
      FROM fact_acct left join C_BPARTNER on FACT_ACCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
carlos@0
   129
      WHERE fact_acct.AD_Client_ID IN ('1')
carlos@0
   130
      AND fact_acct.AD_ORG_ID IN ('1')
carlos@0
   131
      AND 1=1
carlos@0
   132
      AND FACT_ACCT.AD_ORG_ID IN ('2')
carlos@0
   133
      AND 2=2 ) AAA
carlos@0
   134
      GROUP BY ad_table_id, record_id, VALUE, DATEACCT, NAME, FACT_ACCT_GROUP_ID, ID,DESCRIPTION, PARTNER, C_BPARTNER_ID
carlos@0
   135
      ) AA
carlos@0
   136
      WHERE 3=3
carlos@0
   137
        AND 4=4
carlos@0
   138
        ORDER BY PARTNER, VALUE, dateacct, fact_acct_id
carlos@0
   139
      ]]></Sql>
carlos@0
   140
    <Field name="rownum" value="count"/>
carlos@0
   141
    <Parameter name="adUserClient" type="replace" optional="true" after="WHERE fact_acct.AD_Client_ID IN (" text="'1'"/>
carlos@0
   142
    <Parameter name="adUserOrg" type="replace" optional="true" after="AND fact_acct.AD_ORG_ID IN (" text="'1'"/>
eduardo@445
   143
    <Parameter name="acctSchema" optional="true" after="AND 1=1"><![CDATA[and fact_acct.C_ACCTSCHEMA_ID = to_number(?) ]]></Parameter>
carlos@0
   144
    <Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[AND dateacct >= to_date(?) ]]></Parameter>
carlos@0
   145
    <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[AND dateacct < to_date(?) ]]></Parameter>
carlos@0
   146
    <Parameter name="org" type="replace" optional="true" after="AND FACT_ACCT.AD_ORG_ID IN (" text="'2'"/>
carlos@0
   147
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 2=2 "><![CDATA[ AND FACT_ACCT.C_BPARTNER_ID IN]]></Parameter>
carlos@0
   148
eduardo@445
   149
    <Parameter name="paramAmtFrom" optional="true" after="3=3"><![CDATA[ and coalesce(amtacctdr,amtacctcr)>=to_number(?) ]]></Parameter>
eduardo@445
   150
    <Parameter name="paramAmtTo" optional="true" after="4=4"><![CDATA[ and coalesce(amtacctdr,amtacctcr)<=to_number(?) ]]></Parameter>
carlos@0
   151
    
carlos@0
   152
    <Parameter name="orden" type="replace" optional="true" after="ORDER BY " text="PARTNER"/>
carlos@0
   153
  </SqlMethod>
carlos@0
   154
  
carlos@0
   155
  <SqlMethod name="selectAllHiding" type="preparedStatement" return="multiple">
carlos@0
   156
    <SqlMethodComment></SqlMethodComment>
carlos@0
   157
    <Sql>
carlos@0
   158
    <![CDATA[
carlos@0
   159
      select * from (
carlos@0
   160
      SELECT VALUE, PARTNER, dateacct, NAME, 
carlos@0
   161
      (CASE WHEN ABS(SUM(AMTACCTDR))>ABS(SUM(AMTACCTCR)) THEN SUM(AMTACCTDR)-SUM(AMTACCTCR) END) AS AMTACCTDR,
carlos@0
   162
      (CASE WHEN ABS(SUM(AMTACCTDR))<=ABS(SUM(AMTACCTCR)) THEN SUM(AMTACCTCR)-SUM(AMTACCTDR) END) AS AMTACCTCR,
carlos@0
   163
      (SUM(amtacctdr-amtacctcr)) AS TOTAL, fact_acct_group_id, ID, 0 AS SALDO, MAX(FACT_ACCT_ID) AS FACT_ACCT_ID,
carlos@0
   164
      DESCRIPTION, C_BPARTNER_ID
carlos@0
   165
      FROM (
carlos@0
   166
      SELECT FACT_ACCT.ACCTVALUE AS VALUE, C_BPARTNER.NAME AS PARTNER, dateacct, FACT_ACCT.ACCTDESCRIPTION AS NAME, 
carlos@0
   167
      amtacctdr,  amtacctcr AS amtacctcr, fact_acct_group_id, FACT_ACCT.ACCOUNT_ID AS ID, FACT_ACCT.FACT_ACCT_ID, 
carlos@0
   168
      fact_acct.record_id, fact_acct.ad_table_id, FACT_ACCT.DESCRIPTION, FACT_ACCT.C_BPARTNER_ID
carlos@0
   169
      FROM fact_acct left join C_BPARTNER on FACT_ACCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
carlos@0
   170
      WHERE fact_acct.AD_Client_ID IN ('1')
carlos@0
   171
      AND fact_acct.AD_ORG_ID IN ('1')
carlos@0
   172
      AND 1=1
carlos@0
   173
      AND FACT_ACCT.AD_ORG_ID IN ('2')
carlos@0
   174
      AND 2=2
carlos@0
   175
      AND ((record_id2 is null) or (0 != (select sum(amtacctdr-amtacctcr)
carlos@0
   176
                          from fact_acct f1
carlos@0
   177
                        where f1.record_id2 = fact_acct.record_id2
carlos@0
   178
                         and f1.account_id = fact_acct.account_id
carlos@0
   179
                         AND 5=5)))) AAA
carlos@0
   180
      GROUP BY ad_table_id, record_id, VALUE, DATEACCT, NAME, FACT_ACCT_GROUP_ID, ID,DESCRIPTION, PARTNER, C_BPARTNER_ID
carlos@0
   181
      ) AA
carlos@0
   182
      WHERE 3=3
carlos@0
   183
        AND 4=4
carlos@0
   184
        ORDER BY PARTNER, VALUE, dateacct, fact_acct_id
carlos@0
   185
      ]]></Sql>
carlos@0
   186
    <Field name="rownum" value="count"/>
carlos@0
   187
    <Parameter name="adUserClient" type="replace" optional="true" after="WHERE fact_acct.AD_Client_ID IN (" text="'1'"/>
carlos@0
   188
    <Parameter name="adUserOrg" type="replace" optional="true" after="AND fact_acct.AD_ORG_ID IN (" text="'1'"/>
eduardo@445
   189
    <Parameter name="acctSchema" optional="true" after="AND 1=1"><![CDATA[and fact_acct.C_ACCTSCHEMA_ID = to_number(?) ]]></Parameter>
carlos@0
   190
    <Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[AND dateacct >= to_date(?) ]]></Parameter>
carlos@0
   191
    <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[AND dateacct < to_date(?) ]]></Parameter>
carlos@0
   192
    <Parameter name="org" type="replace" optional="true" after="AND FACT_ACCT.AD_ORG_ID IN (" text="'2'"/>
carlos@0
   193
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 2=2 "><![CDATA[ AND FACT_ACCT.C_BPARTNER_ID IN]]></Parameter>
carlos@0
   194
carlos@0
   195
    <Parameter name="parDateTo" optional="true" after="AND 5=5"><![CDATA[AND dateacct < to_date(?) ]]></Parameter>
carlos@0
   196
    
eduardo@445
   197
    <Parameter name="paramAmtFrom" optional="true" after="3=3"><![CDATA[ and coalesce(amtacctdr,amtacctcr)>=to_number(?) ]]></Parameter>
eduardo@445
   198
    <Parameter name="paramAmtTo" optional="true" after="4=4"><![CDATA[ and coalesce(amtacctdr,amtacctcr)<=to_number(?) ]]></Parameter>
carlos@0
   199
    
carlos@0
   200
    <Parameter name="orden" type="replace" optional="true" after="ORDER BY " text="PARTNER"/>
carlos@0
   201
  </SqlMethod>
carlos@0
   202
  
carlos@0
   203
  
carlos@0
   204
  <SqlMethod name="set" type="constant" return="multiple">
carlos@0
   205
      <SqlMethodComment></SqlMethodComment>
carlos@0
   206
      <Sql></Sql>
carlos@0
   207
  </SqlMethod>
carlos@0
   208
carlos@0
   209
  <SqlMethod name="treeOrg" type="preparedStatement" return="string">
carlos@0
   210
    <SqlMethodComment></SqlMethodComment>
carlos@0
   211
    <Sql>
carlos@0
   212
        SELECT AD_TREE_ORG_ID FROM AD_CLIENTINFO
eduardo@445
   213
        WHERE AD_CLIENT_ID = to_number(?)
carlos@0
   214
    </Sql>
carlos@0
   215
    <Parameter name="client"/>
carlos@0
   216
  </SqlMethod>
carlos@0
   217
carlos@0
   218
  <!--SqlMethod name="selectChildren" type="preparedStatement" return="multiple">
carlos@0
   219
    <SqlMethodComment></SqlMethodComment>
carlos@0
   220
    <Sql>
carlos@0
   221
        SELECT NODE_ID AS ID
carlos@0
   222
        FROM AD_TREENODE 
eduardo@445
   223
        WHERE AD_TREE_ID = to_number(?) 
carlos@0
   224
        CONNECT BY PRIOR NODE_ID = PARENT_ID 
eduardo@445
   225
        START WITH NODE_ID = to_number(?) AND AD_TREE_ID = to_number(?)
carlos@0
   226
    </Sql>
carlos@0
   227
    <Parameter name="tree"/>
carlos@0
   228
    <Parameter name="element"/>
carlos@0
   229
    <Parameter name="tree"/>
carlos@0
   230
  </SqlMethod-->
carlos@0
   231
carlos@0
   232
  <SqlMethod name="treeAccount" type="preparedStatement" return="string">
carlos@0
   233
    <SqlMethodComment></SqlMethodComment>
carlos@0
   234
    <Sql>
carlos@0
   235
        SELECT AD_TREE_ID FROM AD_TREE
carlos@423
   236
        WHERE AD_CLIENT_ID = TO_NUMBER(?)
carlos@0
   237
        AND TREETYPE = 'EV'
carlos@0
   238
        
carlos@0
   239
    </Sql>
carlos@0
   240
    <Parameter name="client"/>
carlos@0
   241
  </SqlMethod>
carlos@0
   242
carlos@0
   243
  <SqlMethod name="selectC_ElementValue_ID" type="preparedStatement" return="multiple">
carlos@0
   244
    <SqlMethodComment></SqlMethodComment>
carlos@0
   245
    <Sql><![CDATA[
carlos@0
   246
      SELECT C_ElementValue.C_ElementValue_ID as id, ((CASE C_ElementValue.isActive WHEN 'N' THEN '**' ELSE '' END) || C_ElementValue.Value || ' - ' || C_ElementValue.Name) as name FROM C_ElementValue
carlos@423
   247
      WHERE C_ElementValue.AD_Org_ID IN('1') AND C_ElementValue.AD_Client_ID IN('1')  AND (C_ElementValue.isActive = 'Y' OR C_ElementValue.C_ElementValue_ID = TO_NUMBER(?) )
carlos@0
   248
      and c_elementvalue.ELEMENTLEVEL='S'
eduardo@445
   249
      AND 1=1
carlos@0
   250
      ORDER BY name
carlos@0
   251
    ]]></Sql>
carlos@0
   252
    
carlos@0
   253
<Parameter name="adOrgClient" type="replace" optional="true" after="C_ElementValue.AD_Org_ID IN(" text="'1'"/>
carlos@0
   254
<Parameter name="adUserClient" type="replace" optional="true" after="C_ElementValue.AD_Client_ID IN(" text="'1'"/>
carlos@0
   255
<Parameter name="cElementvalueId"/>
eduardo@445
   256
<Parameter name="schema" optional="true" after="AND 1=1"><![CDATA[ AND (select max(c_element_id) from c_acctschema_element where c_acctschema_id = ? and ELEMENTTYPE = 'AC') = C_ElementValue.c_element_id ]]></Parameter>
carlos@0
   257
  </SqlMethod>
carlos@0
   258
carlos@521
   259
  <SqlMethod name="selectC_ElementValue_ID_Double" type="preparedStatement" return="multiple">
carlos@521
   260
    <SqlMethodComment></SqlMethodComment>
carlos@521
   261
    <Sql><![CDATA[
carlos@521
   262
      SELECT c_acctschema_element.c_acctschema_id AS PADRE, C_ElementValue.C_ElementValue_ID as id, ((CASE C_ElementValue.isActive WHEN 'N' THEN '**' ELSE '' END) || C_ElementValue.Value || ' - ' || C_ElementValue.Name) as name 
carlos@521
   263
      FROM C_ElementValue, c_acctschema_element
carlos@521
   264
      WHERE C_ElementValue.AD_Org_ID IN('1') AND C_ElementValue.AD_Client_ID IN('1')
carlos@521
   265
      AND (C_ElementValue.isActive = 'Y' OR C_ElementValue.C_ElementValue_ID = TO_NUMBER(?) )
carlos@521
   266
      and c_elementvalue.ELEMENTLEVEL='S'
carlos@521
   267
      AND 1=1
carlos@521
   268
      AND c_acctschema_element.ELEMENTTYPE = 'AC'
carlos@521
   269
      AND c_acctschema_element.c_element_id = C_ElementValue.c_element_id
carlos@521
   270
      ORDER BY PADRE ASC, name ASC 
carlos@521
   271
    ]]></Sql>    
carlos@521
   272
    <Parameter name="adOrgClient" type="replace" optional="true" after="C_ElementValue.AD_Org_ID IN(" text="'1'"/>
carlos@521
   273
    <Parameter name="adUserClient" type="replace" optional="true" after="C_ElementValue.AD_Client_ID IN(" text="'1'"/>
carlos@521
   274
    <Parameter name="cElementvalueId"/>
carlos@521
   275
  </SqlMethod>
carlos@521
   276
  
carlos@0
   277
  <SqlMethod name="selectC_ACCTSCHEMA_ID" type="preparedStatement" return="multiple">
carlos@0
   278
    <SqlMethodComment></SqlMethodComment>
carlos@0
   279
    <Sql><![CDATA[
carlos@0
   280
      SELECT C_ACCTSCHEMA_ID as id, ((CASE C_ACCTSCHEMA.isActive WHEN 'N' THEN '**' ELSE '' END) || C_ACCTSCHEMA.Name) as name FROM C_ACCTSCHEMA
eduardo@445
   281
      WHERE C_ACCTSCHEMA.AD_Org_ID IN('1') AND C_ACCTSCHEMA.AD_Client_ID IN('1')  AND (C_ACCTSCHEMA.isActive = 'Y' OR C_ACCTSCHEMA.C_ACCTSCHEMA_ID = to_number(?) )
carlos@0
   282
      ORDER BY name
carlos@0
   283
    ]]></Sql>
carlos@0
   284
    
carlos@0
   285
<Parameter name="adOrgClient" type="replace" optional="true" after="C_ACCTSCHEMA.AD_Org_ID IN(" text="'1'"/>
carlos@0
   286
<Parameter name="adUserClient" type="replace" optional="true" after="C_ACCTSCHEMA.AD_Client_ID IN(" text="'1'"/>
carlos@0
   287
<Parameter name="cAcctschemaId"/>
carlos@0
   288
  </SqlMethod>
carlos@0
   289
carlos@0
   290
  <SqlMethod name="selectCompany" type="preparedStatement" return="string" default="0">
carlos@0
   291
    <SqlMethodComment></SqlMethodComment>
carlos@0
   292
    <Sql>
carlos@0
   293
    <![CDATA[
carlos@0
   294
        SELECT NAME
carlos@0
   295
        FROM AD_CLIENT
eduardo@445
   296
        WHERE AD_CLIENT_ID = to_number(?)
carlos@0
   297
      ]]></Sql>
carlos@0
   298
    <Parameter name="client"/>
carlos@0
   299
  </SqlMethod>
carlos@0
   300
carlos@0
   301
  <SqlMethod name="selectRange" type="preparedStatement" return="multiple">
carlos@0
   302
      <SqlMethodComment></SqlMethodComment>
carlos@0
   303
      <Sql><![CDATA[
carlos@0
   304
	   SELECT c_elementvalue_id as name
carlos@0
   305
       FROM C_ELEMENTVALUE 
eduardo@445
   306
       WHERE value between (select value from c_elementvalue where c_elementvalue_id = to_number(?)) 
eduardo@445
   307
       and  (select value from c_elementvalue where c_elementvalue_id = to_number(?))
carlos@0
   308
	   and c_elementvalue.ELEMENTLEVEL = 'S'
carlos@0
   309
       ORDER BY value asc
carlos@0
   310
      ]]></Sql>
carlos@0
   311
      <Parameter name="accountfrom"/>
carlos@0
   312
      <Parameter name="accountto"/>
carlos@0
   313
   </SqlMethod>
carlos@0
   314
carlos@0
   315
  <SqlMethod name="selectPrevious" type="preparedStatement" return="multiple">
carlos@0
   316
      <SqlMethodComment></SqlMethodComment>
carlos@0
   317
      <Sql><![CDATA[
carlos@0
   318
      SELECT COALESCE(SUM(AMTACCTDR),0) AS TOTALDR, COALESCE(SUM(AMTACCTCR),0) AS TOTALCR, 
carlos@0
   319
      COALESCE(SUM(AMTACCTDR-AMTACCTCR),0) AS TOTAL
carlos@0
   320
      FROM FACT_ACCT
carlos@0
   321
      WHERE 1=1
carlos@423
   322
      AND FACT_ACCT.ACCOUNT_ID = TO_NUMBER(?)
carlos@423
   323
      AND DATEACCT < TO_DATE(?)
carlos@0
   324
      AND FACT_ACCT.AD_ORG_ID IN ('1')
carlos@0
   325
      ]]></Sql>
eduardo@445
   326
      <Parameter name="bpartner" optional="true" after="1=1"><![CDATA[ AND FACT_ACCT.C_BPARTNER_ID = to_number(?) ]]></Parameter>
eduardo@445
   327
      <Parameter name="acctschema" optional="true" after="1=1"><![CDATA[ and fact_acct.C_ACCTSCHEMA_ID = to_number(?) ]]></Parameter>
carlos@0
   328
      <Parameter name="account"/>
carlos@0
   329
      <Parameter name="date"/>
carlos@0
   330
      <Parameter name="orgs" type="replace" optional="true" after="AND FACT_ACCT.AD_ORG_ID IN (" text="'1'"/>
carlos@0
   331
   </SqlMethod>
carlos@0
   332
carlos@0
   333
  <SqlMethod name="selectTotal" type="preparedStatement" return="multiple">
carlos@0
   334
      <SqlMethodComment></SqlMethodComment>
carlos@0
   335
      <Sql><![CDATA[
carlos@0
   336
      SELECT COALESCE(SUM(AMTACCTDR),0) AS TOTALDR, COALESCE(SUM(AMTACCTCR),0) AS TOTALCR, 
carlos@0
   337
      COALESCE(SUM(AMTACCTDR-AMTACCTCR),0) AS TOTAL
carlos@0
   338
      FROM FACT_ACCT
carlos@0
   339
      WHERE 1=1
carlos@423
   340
      AND FACT_ACCT.ACCOUNT_ID = TO_NUMBER(?)
carlos@423
   341
      AND DATEACCT < TO_DATE(?)
carlos@0
   342
      AND FACT_ACCT.AD_ORG_ID IN ('1')
carlos@0
   343
      ]]></Sql>
eduardo@445
   344
      <Parameter name="bpartner" optional="true" after="1=1"><![CDATA[ AND FACT_ACCT.C_BPARTNER_ID = to_number(?) ]]></Parameter>
eduardo@445
   345
      <Parameter name="acctschema" optional="true" after="1=1"><![CDATA[ and fact_acct.C_ACCTSCHEMA_ID = to_number(?) ]]></Parameter>
carlos@0
   346
      <Parameter name="account"/>
carlos@0
   347
      <Parameter name="date"/>
carlos@0
   348
      <Parameter name="orgs" type="replace" optional="true" after="AND FACT_ACCT.AD_ORG_ID IN (" text="'1'"/>
carlos@0
   349
   </SqlMethod>
carlos@0
   350
juanpablo@152
   351
</SqlClass>