src/org/openbravo/erpCommon/ad_reports/ReportAccountingCountDimensionalAnalyses_data.xsql
author David Baz Fayos <david.baz@openbravo.com>
Tue, 27 May 2008 15:26:10 +0000
changeset 1044 8691bbc94032
parent 423 ecf368072c48
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
<SqlClass name="ReportAccountingCountDimensionalAnalysesData" package="org.openbravo.erpCommon.ad_reports">
carlos@0
    22
  <SqlClassComment></SqlClassComment>
carlos@0
    23
  <SqlMethod name="select" type="preparedStatement" return="multiple">
carlos@0
    24
    <SqlMethodComment></SqlMethodComment>
carlos@0
    25
    <Sql>
carlos@0
    26
    <![CDATA[
carlos@423
    27
      SELECT ID, LEVEL_ID, NIVEL,totalAmt, COLUMN_LEVEL, LEVEL_REPORT,TO_NUMBER(LEVEL_REPORT)*2 AS WIDTH_MARGIN, 76-to_number(LEVEL_REPORT)*2 AS WIDTH_TEXT, NAME, (CASE LEVEL_REPORT WHEN to_char(1) THEN ID ELSE LEVEL1 END) AS LEVEL1,(CASE LEVEL_REPORT WHEN to_char(2) THEN ID ELSE LEVEL2 END) AS LEVEL2,(CASE LEVEL_REPORT WHEN to_char(3) THEN ID ELSE LEVEL3 END) AS LEVEL3,(CASE LEVEL_REPORT WHEN to_char(4) THEN ID ELSE LEVEL4 END) AS LEVEL4,(CASE LEVEL_REPORT WHEN to_char(1) THEN 'TableHeaderLevel1' WHEN to_char(2) THEN 'TableHeaderLevel2' WHEN to_char(3) THEN 'TableHeaderLevel3' ELSE 'TableHeaderLevel4' END) AS CLASS_LEVEL, '' AS ORDEN, '' AS TOTALAMT_REF, '' AS COLUMNNAME, '' AS LINE, '' AS DESCRIPTION, '' AS JOIN_GROUP1, '' AS TABLENAME, '' AS JOIN_GROUP2, '' AS CLASS_LEVEL_REF, '' AS CLASS_IMAGE
carlos@0
    28
      FROM (
carlos@0
    29
      SELECT A.ID, to_char('12')||A.ID||to_char('99') AS LEVEL_ID, to_char('1') AS NIVEL, to_char('13') AS COLUMN_LEVEL, A.totalAmt, to_char('7') AS LEVEL_REPORT, '' AS NAME, to_char('8') AS LEVEL1, to_char('9') AS LEVEL2, to_char('10') AS LEVEL3, to_char('11') AS LEVEL4 FROM
carlos@0
    30
        (SELECT to_char('2') AS ID, (CASE ev.accountSign WHEN 'D' THEN SUM(amtacctdr - amtacctcr) ELSE SUM(amtacctcr - amtacctdr) END)  AS totalAmt
carlos@0
    31
        FROM fact_acct fa, c_elementvalue ev
carlos@0
    32
        WHERE fa.account_id = ev.c_elementValue_id
carlos@0
    33
        AND fa.ad_client_id IN ('3')
carlos@423
    34
        AND fa.account_id = TO_number(?)
carlos@423
    35
        AND fa.dateacct >= TO_DATE(?)
carlos@423
    36
        AND fa.dateacct < TO_DATE(?)
carlos@0
    37
        AND fa.factaccttype NOT IN ('O','C','R')
carlos@0
    38
        AND fa.ad_org_id IN ('1')
carlos@0
    39
        AND 1=1
carlos@0
    40
        AND  2=2
carlos@0
    41
        GROUP BY ev.accountSign, 1) A, DUAL
carlos@0
    42
      WHERE A.ID = '1'
carlos@0
    43
      ORDER BY 1) AA
carlos@0
    44
     ]]></Sql> 
carlos@0
    45
    <Parameter name="levelId" type="replace" optional="true" after="SELECT A.ID, to_char(" text="'12'"/>
carlos@0
    46
    <Parameter name="parentLevel" type="replace" optional="true" after="||A.ID||to_char(" text="'99'"/>
carlos@0
    47
    <Parameter name="nivel" type="replace" optional="true" after="AS LEVEL_ID, to_char(" text="'1'"/>
carlos@0
    48
    <Parameter name="levelId" type="replace" optional="true" after="AS NIVEL, to_char(" text="'13'"/>
carlos@0
    49
    <Parameter name="level" type="replace" optional="true" after="A.totalAmt, to_char(" text="'7'"/>
carlos@0
    50
    <Parameter name="level1" type="replace" optional="true" after="AS NAME, to_char(" text="'8'"/>
carlos@0
    51
    <Parameter name="level2" type="replace" optional="true" after="AS LEVEL1, to_char(" text="'9'"/>
carlos@0
    52
    <Parameter name="level3" type="replace" optional="true" after="AS LEVEL2, to_char(" text="'10'"/>
carlos@0
    53
    <Parameter name="level4" type="replace" optional="true" after="AS LEVEL3, to_char(" text="'11'"/>
carlos@0
    54
    <Parameter name="idDimension" type="replace" optional="true" after="(SELECT to_char(" text="'2'"/>
carlos@0
    55
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN (" text="'3'"/>
carlos@0
    56
    <Parameter name="accountId"/>
carlos@0
    57
    <Parameter name="dateFrom"/>
carlos@0
    58
    <Parameter name="dateTo"/>
carlos@0
    59
    <Parameter name="adOrgId" optional="true" type="replace" after="AND fa.ad_org_id IN (" text="'1'"/>
carlos@0
    60
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
carlos@0
    61
    <Parameter name="mProductId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
carlos@423
    62
    <Parameter name="cProjectId" optional="true" after="AND 1=1"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
    63
    <Parameter name="filter" type="replace" optional="true" after="AND 1=1" text="AND  2=2"/>
carlos@0
    64
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY ev.accountSign" text=", 1"/>
carlos@0
    65
    <Parameter name="tableDimension" type="replace" optional="true" after=") A, " text="DUAL"/>
carlos@0
    66
    <Parameter name="idtableDimension" type="replace" optional="true" after="WHERE A.ID = " text="'1'"/>
carlos@0
    67
    <Parameter name="nivel" type="replace" optional="true" after="ORDER BY " text="1"/>
carlos@0
    68
  </SqlMethod>
carlos@0
    69
carlos@0
    70
  <SqlMethod name="set" type="constant" return="multiple">
carlos@0
    71
      <SqlMethodComment></SqlMethodComment>
carlos@0
    72
      <Sql></Sql>
carlos@0
    73
  </SqlMethod>
carlos@0
    74
carlos@0
    75
  <SqlMethod name="selectComparative" type="preparedStatement" return="multiple">
carlos@0
    76
    <SqlMethodComment></SqlMethodComment>
carlos@0
    77
    <Sql>
carlos@0
    78
    <![CDATA[
carlos@0
    79
      SELECT ID, LEVEL_ID, NIVEL,TOTALAMT, TOTALAMT_REF, COLUMN_LEVEL, LEVEL_REPORT,TO_NUMBER(LEVEL_REPORT)*2 AS WIDTH_MARGIN, 76-TO_NUMBER(LEVEL_REPORT)*2 AS WIDTH_TEXT, NAME, (CASE LEVEL_REPORT WHEN to_char(1) THEN ID ELSE LEVEL1 END) AS LEVEL1,(CASE LEVEL_REPORT WHEN to_char(2) THEN ID ELSE LEVEL2 END) AS LEVEL2,(CASE LEVEL_REPORT WHEN to_char(3) THEN ID ELSE LEVEL3 END) AS LEVEL3,(CASE LEVEL_REPORT WHEN to_char(4) THEN ID ELSE LEVEL4 END) AS LEVEL4, (CASE LEVEL_REPORT WHEN to_char(1) THEN 'TableHeaderLevel1' WHEN to_char(2) THEN 'TableHeaderLevel2' WHEN to_char(3) THEN 'TableHeaderLevel3' ELSE 'TableHeaderLevel4' END) AS CLASS_LEVEL
carlos@0
    80
      FROM (
carlos@0
    81
      SELECT AM.ID, to_char('15')||AM.ID||to_char('99') AS LEVEL_ID, to_char('1') AS NIVEL, to_char('15') AS COLUMN_LEVEL, SUM((CASE AM.ORDEN WHEN 1 THEN AM.totalAmt ELSE 0 END)) AS TOTALAMT, SUM((CASE AM.ORDEN WHEN 2 THEN AM.TOTALAMT ELSE 0 END)) AS TOTALAMT_REF, to_char('7') AS LEVEL_REPORT, '' AS NAME, to_char('8') AS LEVEL1, to_char('9') AS LEVEL2, to_char('10') AS LEVEL3, to_char('11') AS LEVEL4
carlos@0
    82
        FROM 
carlos@0
    83
        (SELECT ID, ORDEN, SUM(TOTALAMT) AS TOTALAMT
carlos@0
    84
         FROM(SELECT to_char('2') AS ID, 1 AS ORDEN, (CASE ev.accountSign WHEN 'D' THEN SUM(amtacctdr - amtacctcr) ELSE SUM(amtacctcr - amtacctdr) END)  AS totalAmt
carlos@0
    85
        FROM fact_acct fa, c_elementvalue ev
carlos@0
    86
        WHERE fa.account_id = ev.c_elementValue_id
carlos@0
    87
        AND fa.ad_client_id IN ('3')
carlos@423
    88
        AND fa.account_ID = TO_NUMBER(?)
carlos@423
    89
        AND fa.dateacct >= TO_DATE(?)
carlos@423
    90
        AND fa.dateacct < TO_DATE(?)
carlos@0
    91
        AND fa.factaccttype NOT IN ('O','C','R')
carlos@0
    92
        AND 0=0 AND fa.ad_org_id IN ('1')
carlos@0
    93
        AND 1=1
carlos@0
    94
        AND  2=2
carlos@0
    95
        GROUP BY ev.accountSign, 1 
carlos@0
    96
        UNION SELECT to_char('12') AS ID, 2 AS ORDEN, (CASE ev.accountSign WHEN 'D' THEN SUM(amtacctdr - amtacctcr) ELSE SUM(amtacctcr - amtacctdr) END)  AS totalAmt
carlos@0
    97
        FROM fact_acct fa, c_elementvalue ev
carlos@0
    98
        WHERE fa.account_id = ev.c_elementValue_id
carlos@0
    99
        AND fa.ad_client_id IN('13')
carlos@423
   100
        AND fa.account_ID = TO_NUMBER(?)
carlos@423
   101
        AND fa.dateacct >= TO_DATE(?)
carlos@423
   102
        AND fa.dateacct < TO_DATE(?)
carlos@0
   103
        AND fa.factaccttype NOT IN ('O','C','R')
carlos@0
   104
        AND 5=5 AND fa.ad_org_id IN ('1')
carlos@0
   105
        AND 3=3
carlos@0
   106
        AND  4=4
carlos@0
   107
        GROUP BY  ev.accountSign, 1) BB
carlos@0
   108
        GROUP BY ID, ORDEN) AM, DUAL
carlos@0
   109
      WHERE AM.ID = '5'
carlos@0
   110
      GROUP BY AM.ID, 1 
carlos@0
   111
      ORDER BY 1) AA
carlos@0
   112
     ]]></Sql>
carlos@0
   113
    <Parameter name="levelId" type="replace" optional="true" after="SELECT AM.ID, to_char(" text="'15'"/>
carlos@0
   114
    <Parameter name="parentLevel" type="replace" optional="true" after="||AM.ID||to_char(" text="'99'"/>
carlos@0
   115
    <Parameter name="nivel" type="replace" optional="true" after="AS LEVEL_ID, to_char(" text="'1'"/>
carlos@0
   116
    <Parameter name="levelId" type="replace" optional="true" after="AS NIVEL, to_char(" text="'15'"/>
carlos@0
   117
    <Parameter name="level" type="replace" optional="true" after="SUM((CASE AM.ORDEN WHEN 2 THEN AM.TOTALAMT ELSE 0 END)) AS TOTALAMT_REF, to_char(" text="'7'"/>
carlos@0
   118
    <Parameter name="level1" type="replace" optional="true" after="AS NAME, to_char(" text="'8'"/>
carlos@0
   119
    <Parameter name="level2" type="replace" optional="true" after="AS LEVEL1, to_char(" text="'9'"/>
carlos@0
   120
    <Parameter name="level3" type="replace" optional="true" after="AS LEVEL2, to_char(" text="'10'"/>
carlos@0
   121
    <Parameter name="level4" type="replace" optional="true" after="AS LEVEL3, to_char(" text="'11'"/>
carlos@0
   122
    <Parameter name="idDimension" type="replace" optional="true" after="FROM(SELECT to_char(" text="'2'"/>
carlos@0
   123
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN (" text="'3'"/>
carlos@0
   124
    <Parameter name="accountId"/>
carlos@0
   125
    <Parameter name="dateFrom"/>
carlos@0
   126
    <Parameter name="dateTo"/>
carlos@0
   127
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 0=0 AND fa.ad_org_id IN (" text="'1'"/>
carlos@0
   128
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
carlos@0
   129
    <Parameter name="mProductId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
carlos@423
   130
    <Parameter name="cProjectId" optional="true" after="AND 1=1"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   131
    <Parameter name="filter" type="replace" optional="true" after="AND 1=1" text="AND  2=2"/>
carlos@0
   132
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY ev.accountSign" text=", 1"/>
carlos@0
   133
    <Parameter name="idDimension" type="replace" optional="true" after="UNION SELECT to_char(" text="'12'"/>
carlos@0
   134
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN(" text="'13'"/>
carlos@0
   135
    <Parameter name="accountId"/>
carlos@0
   136
    <Parameter name="dateFromRef"/>
carlos@0
   137
    <Parameter name="dateToRef"/>
carlos@0
   138
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 5=5 AND fa.ad_org_id IN (" text="'1'"/>
carlos@0
   139
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 3=3"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
carlos@0
   140
    <Parameter name="mProductId" optional="true" type="argument" after="AND 3=3"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
carlos@423
   141
    <Parameter name="cProjectId" optional="true" after="AND 3=3"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   142
    <Parameter name="filter" type="replace" optional="true" after="AND 3=3" text="AND  4=4"/>
carlos@0
   143
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY  ev.accountSign" text=", 1"/>
carlos@0
   144
    <Parameter name="tableDimension" type="replace" optional="true" after="GROUP BY ID, ORDEN) AM, " text="DUAL"/>
carlos@0
   145
    <Parameter name="idtableDimension" type="replace" optional="true" after="WHERE AM.ID = " text="'5'"/>
carlos@0
   146
    <Parameter name="nivel" type="replace" optional="true" after="GROUP BY AM.ID, " text="1"/>
carlos@0
   147
    <Parameter name="nivel" type="replace" optional="true" after="ORDER BY " text="1"/>
carlos@0
   148
  </SqlMethod>
carlos@0
   149
carlos@0
   150
  <SqlMethod name="selectPeriod" type="preparedStatement" return="multiple">
carlos@0
   151
    <SqlMethodComment></SqlMethodComment>
carlos@0
   152
    <Sql>
carlos@0
   153
    <![CDATA[
carlos@0
   154
      SELECT ID, LEVEL_ID, NIVEL,totalAmt, COLUMN_LEVEL, LEVEL_REPORT,TO_NUMBER(LEVEL_REPORT) AS WIDTH_MARGIN, 22-TO_NUMBER(LEVEL_REPORT) AS WIDTH_TEXT, NAME, (CASE LEVEL_REPORT WHEN to_char(1) THEN ID ELSE LEVEL1 END) AS LEVEL1,(CASE LEVEL_REPORT WHEN to_char(2) THEN ID ELSE LEVEL2 END) AS LEVEL2,CASE LEVEL_REPORT WHEN to_char(3) THEN  ID ELSE  LEVEL3 END AS LEVEL3,CASE LEVEL_REPORT WHEN to_char(4) THEN  ID ELSE  LEVEL4 END AS LEVEL4,CASE LEVEL_REPORT WHEN to_char(1) THEN 'TableHeaderLevel1' WHEN to_char(2) THEN 'TableHeaderLevel2' WHEN to_char(3) THEN 'TableHeaderLevel3' ELSE 'TableHeaderLevel4' END AS CLASS_LEVEL
carlos@0
   155
      FROM(
carlos@0
   156
      SELECT A.ID, to_char('10')||A.ID||to_char('99') AS LEVEL_ID, A.PERIOD, to_char('11') AS NIVEL, to_char('12') AS COLUMN_LEVEL, SUM(A.totalAmt) AS TOTALAMT, to_char('13') AS LEVEL_REPORT, '' AS NAME, to_char('14') AS LEVEL1, to_char('15') AS LEVEL2, to_char('16') AS LEVEL3, to_char('17') AS LEVEL4
carlos@0
   157
         FROM (SELECT to_char('2') AS ID, TO_NUMBER(TO_CHAR(fa.dateacct,'MM')) AS period, (CASE ev.accountSign WHEN 'D' THEN SUM(amtacctdr - amtacctcr) ELSE SUM(amtacctcr - amtacctdr) END)  AS totalAmt
carlos@0
   158
            FROM fact_acct fa, c_elementvalue ev
carlos@0
   159
            WHERE fa.account_id = ev.c_elementValue_id
carlos@0
   160
            AND fa.ad_client_id IN ('3')
carlos@423
   161
            AND fa.account_ID = TO_NUMBER(?)
carlos@423
   162
            AND fa.dateacct >= TO_DATE(?)
carlos@423
   163
            AND fa.dateacct < TO_DATE(?)
carlos@0
   164
            AND fa.factaccttype NOT IN ('O','C','R')
carlos@0
   165
            AND 0=0 AND fa.ad_org_id IN ('1')
carlos@0
   166
            AND 1=1
carlos@0
   167
            AND  2=2
carlos@0
   168
            GROUP BY ev.accountSign, TO_NUMBER(TO_CHAR(fa.dateacct,'MM')), 1
carlos@0
   169
            UNION SELECT to_char('5') AS ID, D.PERIOD AS period, 0  AS totalAmt
carlos@0
   170
            FROM fact_acct fa, c_elementvalue ev, (SELECT TO_NUMBER(VALUE) AS PERIOD
carlos@0
   171
                                                          FROM AD_MONTH
carlos@0
   172
                                                          WHERE TO_NUMBER(VALUE) >= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))
carlos@0
   173
                                                          AND TO_NUMBER(VALUE) <= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))) D
carlos@0
   174
            WHERE fa.account_id = ev.c_elementValue_id
carlos@0
   175
            AND fa.ad_client_id IN('6')
carlos@423
   176
            AND fa.account_ID = TO_NUMBER(?)
carlos@423
   177
            AND fa.dateacct >= TO_DATE(?)
carlos@423
   178
            AND fa.dateacct < TO_DATE(?)
carlos@0
   179
            AND fa.factaccttype NOT IN ('O','C','R')
carlos@0
   180
            AND 5=5 AND fa.ad_org_id IN ('1')
carlos@0
   181
            AND 3=3
carlos@0
   182
            AND  4=4
carlos@0
   183
            GROUP BY D.PERIOD, 1 ) A, DUAL
carlos@0
   184
            WHERE A.ID = '8'
carlos@0
   185
            GROUP BY A.ID, A.PERIOD, 1 
carlos@0
   186
            ORDER BY 1, A.PERIOD) AA
carlos@0
   187
     ]]></Sql>
carlos@0
   188
    <Parameter name="levelId" type="replace" optional="true" after="SELECT A.ID, to_char(" text="'10'"/>
carlos@0
   189
    <Parameter name="parentLevel" type="replace" optional="true" after="||A.ID||to_char(" text="'99'"/>
carlos@0
   190
    <Parameter name="nivel" type="replace" optional="true" after="LEVEL_ID, A.PERIOD, to_char(" text="'11'"/>
carlos@0
   191
    <Parameter name="levelId" type="replace" optional="true" after="AS NIVEL, to_char(" text="'12'"/>
carlos@0
   192
    <Parameter name="level" type="replace" optional="true" after="AS TOTALAMT, to_char(" text="'13'"/>
carlos@0
   193
    <Parameter name="level1" type="replace" optional="true" after="AS NAME, to_char(" text="'14'"/>
carlos@0
   194
    <Parameter name="level2" type="replace" optional="true" after="AS LEVEL1, to_char(" text="'15'"/>
carlos@0
   195
    <Parameter name="level3" type="replace" optional="true" after="AS LEVEL2, to_char(" text="'16'"/>
carlos@0
   196
    <Parameter name="level4" type="replace" optional="true" after="AS LEVEL3, to_char(" text="'17'"/>
carlos@0
   197
    <Parameter name="idDimension" type="replace" optional="true" after="FROM (SELECT to_char(" text="'2'"/>
carlos@0
   198
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN (" text="'3'"/>
carlos@0
   199
    <Parameter name="accountId"/>
carlos@0
   200
    <Parameter name="dateFrom"/>
carlos@0
   201
    <Parameter name="dateTo"/>
carlos@0
   202
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 0=0 AND fa.ad_org_id IN (" text="'1'"/>
carlos@0
   203
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
carlos@0
   204
    <Parameter name="mProductId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
carlos@423
   205
    <Parameter name="cProjectId" optional="true" after="AND 1=1"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   206
    <Parameter name="filter" type="replace" optional="true" after="AND 1=1" text="AND  2=2"/>
carlos@0
   207
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY ev.accountSign, TO_NUMBER(TO_CHAR(fa.dateacct,'MM'))" text=", 1"/>
carlos@0
   208
    <Parameter name="idDimension" type="replace" optional="true" after="UNION SELECT to_char(" text="'5'"/>
carlos@0
   209
    <Parameter name="dateFrom"/>
carlos@0
   210
    <Parameter name="dateToAux"/>
carlos@0
   211
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN(" text="'6'"/>
carlos@0
   212
     <Parameter name="accountId"/>
carlos@0
   213
    <Parameter name="dateFrom"/>
carlos@0
   214
    <Parameter name="dateTo"/>
carlos@0
   215
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 5=5 AND fa.ad_org_id IN (" text="'1'"/>
carlos@0
   216
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 3=3"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
carlos@0
   217
    <Parameter name="mProductId" optional="true" type="argument" after="AND 3=3"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
carlos@423
   218
    <Parameter name="cProjectId" optional="true" after="AND 3=3"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   219
    <Parameter name="filter" type="replace" optional="true" after="AND 3=3" text="AND  4=4"/>
carlos@0
   220
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY D.PERIOD" text=", 1"/>
carlos@0
   221
    <Parameter name="tableDimension" type="replace" optional="true" after=") A, " text="DUAL"/>
carlos@0
   222
    <Parameter name="idtableDimension" type="replace" optional="true" after="WHERE A.ID = " text="'8'"/>
carlos@0
   223
    <Parameter name="nivel" type="replace" optional="true" after="GROUP BY A.ID, A.PERIOD, " text="1"/>
carlos@0
   224
    <Parameter name="nivel" type="replace" optional="true" after="ORDER BY " text="1"/>
carlos@0
   225
  </SqlMethod>
carlos@0
   226
carlos@0
   227
  <SqlMethod name="selectPeriodQuarter" type="preparedStatement" return="multiple">
carlos@0
   228
    <SqlMethodComment></SqlMethodComment>
carlos@0
   229
    <Sql>
carlos@0
   230
    <![CDATA[
carlos@0
   231
      SELECT ID, LEVEL_ID, NIVEL,totalAmt, COLUMN_LEVEL, LEVEL_REPORT,TO_NUMBER(LEVEL_REPORT) AS WIDTH_MARGIN, 22-TO_NUMBER(LEVEL_REPORT) AS WIDTH_TEXT, NAME, (CASE LEVEL_REPORT WHEN to_char(1) THEN ID ELSE LEVEL1 END) AS LEVEL1,(CASE LEVEL_REPORT WHEN to_char(2) THEN ID ELSE LEVEL2 END) AS LEVEL2,(CASE LEVEL_REPORT WHEN to_char(3) THEN ID ELSE LEVEL3 END) AS LEVEL3,(CASE LEVEL_REPORT WHEN to_char(4) THEN ID ELSE LEVEL4 END) AS LEVEL4,(CASE LEVEL_REPORT WHEN to_char(1) THEN 'TableHeaderLevel1' WHEN to_char(2) THEN 'TableHeaderLevel2' WHEN to_char(3) THEN 'TableHeaderLevel3' ELSE 'TableHeaderLevel4' END) AS CLASS_LEVEL
carlos@0
   232
      FROM(
carlos@0
   233
      SELECT A.ID, to_char('10')||A.ID||to_char('99') AS LEVEL_ID, A.PERIOD, to_char('11') AS NIVEL, to_char('12') AS COLUMN_LEVEL, SUM(A.totalAmt) AS TOTALAMT, to_char('13') AS LEVEL_REPORT, '' AS NAME, to_char('14') AS LEVEL1, to_char('15') AS LEVEL2, to_char('16') AS LEVEL3, to_char('17') AS LEVEL4
carlos@0
   234
         FROM (SELECT to_char('2') AS ID, TO_NUMBER(AD_MONTH.QUARTER) AS period, CASE ev.accountSign WHEN  'D' THEN  SUM(amtacctdr - amtacctcr) ELSE  SUM(amtacctcr - amtacctdr) END  AS totalAmt
carlos@0
   235
            FROM fact_acct fa, c_elementvalue ev, AD_MONTH
carlos@0
   236
            WHERE fa.account_id = ev.c_elementValue_id
carlos@0
   237
            AND fa.ad_client_id IN ('3')
carlos@423
   238
            AND fa.account_ID = TO_NUMBER(?)
carlos@423
   239
            AND fa.dateacct >= TO_DATE(?)
carlos@423
   240
            AND fa.dateacct < TO_DATE(?)
carlos@0
   241
            AND fa.factaccttype NOT IN ('O','C','R')
carlos@0
   242
            AND TO_NUMBER(TO_CHAR(TO_DATE(fa.dateacct), 'MM')) = TO_NUMBER(AD_MONTH.VALUE)
carlos@0
   243
            AND 0=0 AND fa.ad_org_id IN ('1')
carlos@0
   244
            AND 1=1
carlos@0
   245
            AND  2=2
carlos@0
   246
            GROUP BY ev.accountSign, TO_NUMBER(AD_MONTH.QUARTER), 1
carlos@0
   247
            UNION SELECT TO_CHAR('5') AS ID, D.PERIOD AS period, 0  AS totalAmt
carlos@0
   248
            FROM fact_acct fa, c_elementvalue ev, (SELECT TO_NUMBER(QUARTER) AS PERIOD
carlos@0
   249
                                                          FROM AD_MONTH
carlos@0
   250
                                                          WHERE TO_NUMBER(VALUE) >= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))
carlos@0
   251
                                                          AND TO_NUMBER(VALUE) <= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))) D
carlos@0
   252
            WHERE fa.account_id = ev.c_elementValue_id
carlos@0
   253
            AND fa.ad_client_id IN('6')
carlos@423
   254
            AND fa.account_ID = TO_NUMBER(?)
carlos@423
   255
            AND fa.dateacct >= TO_DATE(?)
carlos@423
   256
            AND fa.dateacct < TO_DATE(?)
carlos@0
   257
            AND fa.factaccttype NOT IN ('O','C','R')
carlos@0
   258
            AND 5=5 AND fa.ad_org_id IN ('1')
carlos@0
   259
            AND 3=3
carlos@0
   260
            AND  4=4
carlos@0
   261
            GROUP BY D.PERIOD, 1) A, DUAL
carlos@0
   262
            WHERE A.ID = '8'
carlos@0
   263
            GROUP BY A.ID, A.PERIOD, 1
carlos@0
   264
            ORDER BY 1, A.PERIOD) AA
carlos@0
   265
     ]]></Sql>
carlos@0
   266
    <Parameter name="levelId" type="replace" optional="true" after="SELECT A.ID, to_char(" text="'10'"/>
carlos@0
   267
    <Parameter name="parentLevel" type="replace" optional="true" after="||A.ID||to_char(" text="'99'"/>
carlos@0
   268
    <Parameter name="nivel" type="replace" optional="true" after="LEVEL_ID, A.PERIOD, to_char(" text="'11'"/>
carlos@0
   269
    <Parameter name="levelId" type="replace" optional="true" after="AS NIVEL, to_char(" text="'12'"/>
carlos@0
   270
    <Parameter name="level" type="replace" optional="true" after="AS TOTALAMT, to_char(" text="'13'"/>
carlos@0
   271
    <Parameter name="level1" type="replace" optional="true" after="AS NAME, to_char(" text="'14'"/>
carlos@0
   272
    <Parameter name="level2" type="replace" optional="true" after="AS LEVEL1, to_char(" text="'15'"/>
carlos@0
   273
    <Parameter name="level3" type="replace" optional="true" after="AS LEVEL2, to_char(" text="'16'"/>
carlos@0
   274
    <Parameter name="level4" type="replace" optional="true" after="AS LEVEL3, to_char(" text="'17'"/>
carlos@0
   275
    <Parameter name="idDimension" type="replace" optional="true" after="FROM (SELECT to_char(" text="'2'"/>
carlos@0
   276
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN (" text="'3'"/>
carlos@0
   277
    <Parameter name="accountId"/>
carlos@0
   278
    <Parameter name="dateFrom"/>
carlos@0
   279
    <Parameter name="dateTo"/>
carlos@0
   280
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 0=0 AND fa.ad_org_id IN (" text="'1'"/>
carlos@0
   281
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
carlos@0
   282
    <Parameter name="mProductId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
carlos@423
   283
    <Parameter name="cProjectId" optional="true" after="AND 1=1"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   284
    <Parameter name="filter" type="replace" optional="true" after="AND 1=1" text="AND  2=2"/>
carlos@0
   285
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY ev.accountSign, TO_NUMBER(AD_MONTH.QUARTER)" text=", 1"/>
carlos@0
   286
    <Parameter name="idDimension" type="replace" optional="true" after="UNION SELECT TO_CHAR(" text="'5'"/>
carlos@0
   287
    <Parameter name="dateFrom"/>
carlos@0
   288
    <Parameter name="dateToAux"/>
carlos@0
   289
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN(" text="'6'"/>
carlos@0
   290
     <Parameter name="accountId"/>
carlos@0
   291
    <Parameter name="dateFrom"/>
carlos@0
   292
    <Parameter name="dateTo"/>
carlos@0
   293
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 5=5 AND fa.ad_org_id IN (" text="'1'"/>
carlos@0
   294
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 3=3"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
carlos@0
   295
    <Parameter name="mProductId" optional="true" type="argument" after="AND 3=3"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
carlos@423
   296
    <Parameter name="cProjectId" optional="true" after="AND 3=3"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   297
    <Parameter name="filter" type="replace" optional="true" after="AND 3=3" text="AND  4=4"/>
carlos@0
   298
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY D.PERIOD" text=", 1"/>
carlos@0
   299
    <Parameter name="tableDimension" type="replace" optional="true" after=") A, " text="DUAL"/>
carlos@0
   300
    <Parameter name="idtableDimension" type="replace" optional="true" after="WHERE A.ID = " text="'8'"/>
carlos@0
   301
    <Parameter name="nivel" type="replace" optional="true" after="GROUP BY A.ID, A.PERIOD, " text="1"/>
carlos@0
   302
    <Parameter name="nivel" type="replace" optional="true" after="ORDER BY " text="1"/>
carlos@0
   303
  </SqlMethod>
carlos@0
   304
carlos@0
   305
  <SqlMethod name="selectPeriodComparative" type="preparedStatement" return="multiple">
carlos@0
   306
    <SqlMethodComment></SqlMethodComment>
carlos@0
   307
    <Sql>
carlos@0
   308
    <![CDATA[
carlos@0
   309
      SELECT ID, LEVEL_ID, NIVEL,totalAmt, totalamt_ref, COLUMN_LEVEL, LEVEL_REPORT,TO_NUMBER(LEVEL_REPORT)*4 AS WIDTH_MARGIN, 100-TO_NUMBER(LEVEL_REPORT)*4 AS WIDTH_TEXT, NAME, CASE LEVEL_REPORT WHEN to_char(1) THEN  ID ELSE  LEVEL1 END AS LEVEL1,CASE LEVEL_REPORT WHEN to_char(2) THEN  ID ELSE  LEVEL2 END AS LEVEL2,CASE LEVEL_REPORT WHEN to_char(3) THEN  ID ELSE  LEVEL3 END AS LEVEL3,CASE LEVEL_REPORT WHEN to_char(4) THEN  ID ELSE  LEVEL4 END AS LEVEL4, CLASS_LEVEL, CLASS_LEVEL_REF, CLASS_IMAGE
carlos@0
   310
      FROM (
carlos@0
   311
      SELECT AM.ID, to_char('1')||AM.ID||to_char('99') AS LEVEL_ID, to_char('2') AS NIVEL, to_char('3') AS COLUMN_LEVEL, SUM(AM.totalAmt) AS TOTALAMT, SUM(AM.totalamt_ref) AS TOTALAMT_REF, to_char('4') AS LEVEL_REPORT, '' AS NAME, to_char('5') AS LEVEL1, to_char('6') AS LEVEL2, to_char('7') AS LEVEL3, to_char('8') AS LEVEL4 , CLASS_LEVEL, CLASS_LEVEL_REF, CLASS_IMAGE
carlos@0
   312
        FROM 
carlos@0
   313
        (SELECT ID, ORDEN, PERIOD, SUM(CASE ORDEN WHEN 1 THEN  TOTALAMT ELSE  0 END) AS TOTALAMT, SUM(CASE ORDEN WHEN 2 THEN  TOTALAMT ELSE  0 END) AS TOTALAMT_REF, CASE LEVEL_REPORT WHEN to_char(1) THEN 'TableHeaderLevel1' WHEN to_char(2) THEN 'TableHeaderLevel2' WHEN to_char(3) THEN 'TableHeaderLevel3' ELSE 'TableHeaderLevel4' END AS CLASS_LEVEL, to_char('HeaderExcelLevel1') AS CLASS_LEVEL_REF, CASE ORDEN WHEN 1 THEN  'datawarehouseclose' ELSE  '' END AS CLASS_IMAGE
carlos@0
   314
		FROM 
carlos@0
   315
		(SELECT ID, ORDEN, PERIOD , to_char('23') AS LEVEL_REPORT, SUM(TOTALAMT) AS TOTALAMT
carlos@0
   316
         FROM(SELECT to_char('9') AS ID, 1 AS ORDEN, TO_NUMBER(TO_CHAR(fa.dateacct,'MM')) AS period, CASE ev.accountSign WHEN  'D' THEN  SUM(amtacctdr - amtacctcr) ELSE  SUM(amtacctcr - amtacctdr) END  AS totalAmt
carlos@0
   317
        FROM fact_acct fa, c_elementvalue ev
carlos@0
   318
        WHERE fa.account_id = ev.c_elementValue_id
carlos@0
   319
        AND fa.ad_client_id IN ('10')
carlos@423
   320
        AND fa.account_ID = TO_NUMBER(?)
carlos@423
   321
        AND fa.dateacct >= TO_DATE(?)
carlos@423
   322
        AND fa.dateacct < TO_DATE(?)
carlos@0
   323
        AND fa.factaccttype NOT IN ('O','C','R')
carlos@0
   324
        AND 0=0 AND fa.ad_org_id IN ('1')
carlos@0
   325
        AND 1=1
carlos@0
   326
        AND  2=2
carlos@0
   327
        GROUP BY ev.accountSign, TO_NUMBER(TO_CHAR(fa.dateacct,'MM')), 1
carlos@0
   328
        UNION SELECT to_char('12') AS ID, 2 AS ORDEN, TO_NUMBER(TO_CHAR(fa.dateacct,'MM')) AS period, CASE ev.accountSign WHEN  'D' THEN  SUM(amtacctdr - amtacctcr) ELSE  SUM(amtacctcr - amtacctdr) END  AS totalAmt
carlos@0
   329
        FROM fact_acct fa, c_elementvalue ev
carlos@0
   330
        WHERE fa.account_id = ev.c_elementValue_id
carlos@0
   331
        AND fa.ad_client_id IN('13')
carlos@423
   332
        AND fa.account_ID = TO_NUMBER(?)
carlos@423
   333
        AND fa.dateacct >= TO_DATE(?)
carlos@423
   334
        AND fa.dateacct < TO_DATE(?)
carlos@0
   335
        AND fa.factaccttype NOT IN ('O','C','R')
carlos@0
   336
        AND 9=9 AND fa.ad_org_id IN ('1')
carlos@0
   337
        AND 3=3
carlos@0
   338
        AND  4=4
carlos@0
   339
        GROUP BY  ev.accountSign, TO_NUMBER(TO_CHAR(fa.dateacct,'MM')), 1) BB
carlos@0
   340
        GROUP BY ID, ORDEN, PERIOD
carlos@0
   341
		UNION
carlos@0
   342
		SELECT ID,ORDEN,PERIOD, to_char('24') AS LEVEL_REPORT, SUM(TOTALAMT) AS TOTALAMT
carlos@0
   343
         FROM (SELECT to_char('15') AS ID, 1 AS ORDEN, D.PERIOD, 0  AS totalAmt
carlos@0
   344
        FROM fact_acct fa, c_elementvalue ev, (SELECT TO_NUMBER(VALUE) AS PERIOD
carlos@0
   345
                                                          FROM AD_MONTH
carlos@0
   346
                                                          WHERE TO_NUMBER(VALUE) >= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))
carlos@0
   347
                                                          AND TO_NUMBER(VALUE) <= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))) D
carlos@0
   348
        WHERE fa.account_id = ev.c_elementValue_id
carlos@0
   349
        AND fa.ad_client_id IN  ('16')
carlos@423
   350
        AND fa.account_ID = TO_NUMBER(?)
carlos@423
   351
        AND fa.dateacct >= TO_DATE(?)
carlos@423
   352
        AND fa.dateacct < TO_DATE(?)
carlos@0
   353
        AND fa.factaccttype NOT IN ('O','C','R')
carlos@0
   354
        AND 10=10 AND fa.ad_org_id IN ('1')
carlos@0
   355
        AND 5=5
carlos@0
   356
        AND  6=6
carlos@0
   357
        GROUP BY ev.accountSign, D.PERIOD, 1
carlos@0
   358
        UNION  SELECT to_char('18') AS ID, 2 AS ORDEN, D.PERIOD, 0 AS totalAmt
carlos@0
   359
        FROM fact_acct fa, c_elementvalue ev, (SELECT TO_NUMBER(VALUE) AS PERIOD
carlos@0
   360
                                                          FROM AD_MONTH
carlos@0
   361
                                                          WHERE TO_NUMBER(VALUE) >= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))
carlos@0
   362
                                                          AND TO_NUMBER(VALUE) <= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))) D
carlos@0
   363
        WHERE fa.account_id = ev.c_elementValue_id
carlos@0
   364
        AND fa.ad_client_id IN   ('19')
carlos@423
   365
        AND fa.account_ID = TO_NUMBER(?)
carlos@423
   366
        AND fa.dateacct >= TO_DATE(?)
carlos@423
   367
        AND fa.dateacct < TO_DATE(?)
carlos@0
   368
        AND fa.factaccttype NOT IN ('O','C','R')
carlos@0
   369
        AND 11=11 AND fa.ad_org_id IN ('1')
carlos@0
   370
        AND 7=7
carlos@0
   371
        AND  8=8
carlos@0
   372
        GROUP BY  ev.accountSign, D.PERIOD, 1) BBB
carlos@0
   373
        GROUP BY ID, ORDEN, PERIOD) AAA
carlos@0
   374
		GROUP BY ID, ORDEN, PERIOD, LEVEL_REPORT) AM, DUAL
carlos@0
   375
      WHERE AM.ID = '21'
carlos@0
   376
      GROUP BY AM.ID, AM.PERIOD, CLASS_LEVEL, CLASS_LEVEL_REF, CLASS_IMAGE, 1
carlos@0
   377
      ORDER BY 1, AM.PERIOD) AA
carlos@0
   378
     ]]></Sql>
carlos@0
   379
    <Parameter name="levelId" type="replace" optional="true" after="SELECT AM.ID, to_char(" text="'1'"/>
carlos@0
   380
    <Parameter name="parentLevel" type="replace" optional="true" after="||AM.ID||to_char(" text="'99'"/>
carlos@0
   381
    <Parameter name="nivel" type="replace" optional="true" after="AS LEVEL_ID, to_char(" text="'2'"/>
carlos@0
   382
    <Parameter name="levelId" type="replace" optional="true" after="AS NIVEL, to_char(" text="'3'"/>
carlos@0
   383
    <Parameter name="level" type="replace" optional="true" after=", SUM(AM.totalamt_ref) AS TOTALAMT_REF, to_char(" text="'4'"/>
carlos@0
   384
    <Parameter name="level1" type="replace" optional="true" after=" AS NAME, to_char(" text="'5'"/>
carlos@0
   385
    <Parameter name="level2" type="replace" optional="true" after="AS LEVEL1, to_char(" text="'6'"/>
carlos@0
   386
    <Parameter name="level3" type="replace" optional="true" after="AS LEVEL2, to_char(" text="'7'"/>
carlos@0
   387
    <Parameter name="level4" type="replace" optional="true" after="AS LEVEL3, to_char(" text="'8'"/>
carlos@0
   388
    <Parameter name="level" type="replace" optional="true" after="(SELECT ID, ORDEN, PERIOD , to_char(" text="'23'"/>
carlos@0
   389
    <Parameter name="idDimension" type="replace" optional="true" after="FROM(SELECT to_char(" text="'9'"/>
carlos@0
   390
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN (" text="'10'"/>
carlos@0
   391
    <Parameter name="accountId"/>
carlos@0
   392
    <Parameter name="dateFrom"/>
carlos@0
   393
    <Parameter name="dateTo"/>
carlos@0
   394
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 0=0 AND fa.ad_org_id IN (" text="'1'"/>
carlos@0
   395
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
carlos@0
   396
    <Parameter name="mProductId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
carlos@423
   397
    <Parameter name="cProjectId" optional="true" after="AND 1=1"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   398
    <Parameter name="filter" type="replace" optional="true" after="AND 1=1" text="AND  2=2"/>
carlos@0
   399
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY ev.accountSign, TO_NUMBER(TO_CHAR(fa.dateacct,'MM'))" text=", 1"/>
carlos@0
   400
    <Parameter name="idDimension" type="replace" optional="true" after="UNION SELECT to_char(" text="'12'"/>
carlos@0
   401
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN(" text="'13'"/>
carlos@0
   402
     <Parameter name="accountId"/>
carlos@0
   403
    <Parameter name="dateFromRef"/>
carlos@0
   404
    <Parameter name="dateToRef"/>
carlos@0
   405
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 9=9 AND fa.ad_org_id IN (" text="'1'"/>
carlos@0
   406
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 3=3"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
carlos@0
   407
    <Parameter name="mProductId" optional="true" type="argument" after="AND 3=3"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
carlos@423
   408
    <Parameter name="cProjectId" optional="true" after="AND 3=3"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   409
    <Parameter name="filter" type="replace" optional="true" after="AND 3=3" text="AND  4=4"/>
carlos@0
   410
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY  ev.accountSign, TO_NUMBER(TO_CHAR(fa.dateacct,'MM'))" text=", 1"/>
carlos@0
   411
    <Parameter name="level" type="replace" optional="true" after="SELECT ID,ORDEN,PERIOD, to_char(" text="'24'"/>
carlos@0
   412
    <Parameter name="idDimension" type="replace" optional="true" after="FROM (SELECT to_char(" text="'15'"/>
carlos@0
   413
    <Parameter name="dateFrom"/>
carlos@0
   414
    <Parameter name="dateToAux"/>
carlos@0
   415
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN  (" text="'16'"/>
carlos@0
   416
    <Parameter name="accountId"/>
carlos@0
   417
    <Parameter name="dateFrom"/>
carlos@0
   418
    <Parameter name="dateTo"/>
carlos@0
   419
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 10=10 AND fa.ad_org_id IN (" text="'1'"/>
carlos@0
   420
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 5=5"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
carlos@0
   421
    <Parameter name="mProductId" optional="true" type="argument" after="AND 5=5"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
carlos@423
   422
    <Parameter name="cProjectId" optional="true" after="AND 5=5"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   423
    <Parameter name="filter" type="replace" optional="true" after="AND 5=5" text="AND  6=6"/>
carlos@0
   424
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY ev.accountSign, D.PERIOD" text=", 1"/>
carlos@0
   425
    <Parameter name="idDimension" type="replace" optional="true" after="UNION  SELECT to_char(" text="'18'"/>
carlos@0
   426
    <Parameter name="dateFrom"/>
carlos@0
   427
    <Parameter name="dateToAux"/>
carlos@0
   428
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN   (" text="'19'"/>
carlos@0
   429
     <Parameter name="accountId"/>
carlos@0
   430
    <Parameter name="dateFromRef"/>
carlos@0
   431
    <Parameter name="dateToRef"/>
carlos@0
   432
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 11=11 AND fa.ad_org_id IN (" text="'1'"/>
carlos@0
   433
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 7=7"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
carlos@0
   434
    <Parameter name="mProductId" optional="true" type="argument" after="AND 7=7"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
carlos@423
   435
    <Parameter name="cProjectId" optional="true" after="AND 7=7"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   436
    <Parameter name="filter" type="replace" optional="true" after="AND 7=7" text="AND  8=8"/>
carlos@0
   437
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY  ev.accountSign, D.PERIOD" text=", 1"/>
carlos@0
   438
    <Parameter name="tableDimension" type="replace" optional="true" after="GROUP BY ID, ORDEN, PERIOD, LEVEL_REPORT) AM, " text="DUAL"/>
carlos@0
   439
    <Parameter name="idtableDimension" type="replace" optional="true" after="WHERE AM.ID = " text="'21'"/>
carlos@0
   440
    <Parameter name="nivel" type="replace" optional="true" after="GROUP BY AM.ID, AM.PERIOD, CLASS_LEVEL, CLASS_LEVEL_REF, CLASS_IMAGE, " text="1"/>
carlos@0
   441
    <Parameter name="nivel" type="replace" optional="true" after="ORDER BY " text="1"/>
carlos@0
   442
  </SqlMethod>
carlos@0
   443
carlos@0
   444
  <SqlMethod name="selectPeriodComparativeQuarter" type="preparedStatement" return="multiple">
carlos@0
   445
    <SqlMethodComment></SqlMethodComment>
carlos@0
   446
    <Sql>
carlos@0
   447
    <![CDATA[
carlos@0
   448
      SELECT ID, LEVEL_ID, NIVEL,totalAmt, totalamt_ref, COLUMN_LEVEL, LEVEL_REPORT,TO_NUMBER(LEVEL_REPORT)*4 AS WIDTH_MARGIN, 100-TO_NUMBER(LEVEL_REPORT)*4 AS WIDTH_TEXT, NAME, CASE LEVEL_REPORT WHEN to_char(1) THEN  ID ELSE  LEVEL1 END AS LEVEL1,CASE LEVEL_REPORT WHEN to_char(2) THEN  ID ELSE  LEVEL2 END AS LEVEL2,CASE LEVEL_REPORT WHEN to_char(3) THEN  ID ELSE  LEVEL3 END AS LEVEL3,CASE LEVEL_REPORT WHEN to_char(4) THEN  ID ELSE  LEVEL4 END AS LEVEL4, CLASS_LEVEL, CLASS_LEVEL_REF, CLASS_IMAGE
carlos@0
   449
      FROM (
carlos@0
   450
      SELECT AM.ID, to_char('1')||AM.ID||to_char('99') AS LEVEL_ID, to_char('2') AS NIVEL, to_char('3') AS COLUMN_LEVEL, SUM(AM.totalAmt) AS TOTALAMT, SUM(AM.totalamt_ref) AS TOTALAMT_REF, to_char('4') AS LEVEL_REPORT, '' AS NAME, to_char('5') AS LEVEL1, to_char('6') AS LEVEL2, to_char('7') AS LEVEL3, to_char('8') AS LEVEL4 , CLASS_LEVEL, CLASS_LEVEL_REF, CLASS_IMAGE
carlos@0
   451
        FROM 
carlos@0
   452
        (SELECT ID, ORDEN, PERIOD, SUM(CASE ORDEN WHEN 1 THEN  TOTALAMT ELSE  0 END) AS TOTALAMT, SUM(CASE ORDEN WHEN 2 THEN  TOTALAMT ELSE  0 END) AS TOTALAMT_REF, CASE LEVEL_REPORT WHEN to_char(1) THEN 'TableHeaderLevel1' WHEN to_char(2) THEN 'TableHeaderLevel2' WHEN to_char(3) THEN 'TableHeaderLevel3' ELSE 'TableHeaderLevel4' END AS CLASS_LEVEL, to_char('HeaderExcelLevel1') AS CLASS_LEVEL_REF, CASE ORDEN WHEN 1 THEN  'datawarehouseclose' ELSE  '' END AS CLASS_IMAGE
carlos@0
   453
		FROM 
carlos@0
   454
		(SELECT ID, ORDEN, PERIOD , to_char('23') AS LEVEL_REPORT, SUM(TOTALAMT) AS TOTALAMT
carlos@0
   455
         FROM(SELECT to_char('9') AS ID, 1 AS ORDEN, TO_NUMBER(AD_MONTH.QUARTER) AS period, CASE ev.accountSign WHEN  'D' THEN  SUM(amtacctdr - amtacctcr) ELSE  SUM(amtacctcr - amtacctdr) END  AS totalAmt
carlos@0
   456
        FROM fact_acct fa, c_elementvalue ev, AD_MONTH
carlos@0
   457
        WHERE fa.account_id = ev.c_elementValue_id
carlos@0
   458
        AND fa.ad_client_id IN ('10')
carlos@423
   459
        AND fa.account_ID = TO_NUMBER(?)
carlos@423
   460
        AND fa.dateacct >= TO_DATE(?)
carlos@423
   461
        AND fa.dateacct < TO_DATE(?)
carlos@0
   462
        AND fa.factaccttype NOT IN ('O','C','R')
carlos@423
   463
        AND TO_NUMBER(TO_CHAR(TO_DATE(fa.dateacct), 'MM')) = TO_NUMBER(AD_MONTH.VALUE)
carlos@0
   464
        AND 0=0 AND fa.ad_org_id IN ('1')
carlos@0
   465
        AND 1=1
carlos@0
   466
        AND  2=2
carlos@0
   467
        GROUP BY ev.accountSign, TO_NUMBER(AD_MONTH.QUARTER), 1
carlos@0
   468
        UNION SELECT to_char('12') AS ID, 2 AS ORDEN, TO_NUMBER(AD_MONTH.QUARTER) AS period, CASE ev.accountSign WHEN  'D' THEN  SUM(amtacctdr - amtacctcr) ELSE  SUM(amtacctcr - amtacctdr) END  AS totalAmt
carlos@0
   469
        FROM fact_acct fa, c_elementvalue ev, AD_MONTH
carlos@0
   470
        WHERE fa.account_id = ev.c_elementValue_id
carlos@0
   471
        AND fa.ad_client_id IN('13')
carlos@423
   472
        AND fa.account_ID = TO_NUMBER(?)
carlos@423
   473
        AND fa.dateacct >= TO_DATE(?)
carlos@423
   474
        AND fa.dateacct < TO_DATE(?)
carlos@0
   475
        AND fa.factaccttype NOT IN ('O','C','R')
carlos@423
   476
        AND TO_NUMBER(TO_CHAR(TO_DATE(fa.dateacct), 'MM')) = TO_NUMBER(AD_MONTH.VALUE)
carlos@0
   477
        AND 9=9 AND fa.ad_org_id IN ('1')
carlos@0
   478
        AND 3=3
carlos@0
   479
        AND  4=4
carlos@0
   480
        GROUP BY  ev.accountSign, TO_NUMBER(AD_MONTH.QUARTER), 1) BBB
carlos@0
   481
        GROUP BY ID, ORDEN, PERIOD
carlos@0
   482
		UNION
carlos@0
   483
		SELECT ID,ORDEN,PERIOD, to_char('24') AS LEVEL_REPORT, SUM(TOTALAMT) AS TOTALAMT
carlos@0
   484
         FROM (SELECT to_char('15') AS ID, 1 AS ORDEN, D.PERIOD, 0  AS totalAmt
carlos@0
   485
        FROM fact_acct fa, c_elementvalue ev, (SELECT TO_NUMBER(QUARTER) AS PERIOD
carlos@0
   486
                                                          FROM AD_MONTH
carlos@0
   487
                                                          WHERE TO_NUMBER(VALUE) >= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))
carlos@0
   488
                                                          AND TO_NUMBER(VALUE) <= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))) D
carlos@0
   489
        WHERE fa.account_id = ev.c_elementValue_id
carlos@0
   490
        AND fa.ad_client_id IN  ('16')
carlos@423
   491
        AND fa.account_ID = TO_NUMBER(?)
carlos@423
   492
        AND fa.dateacct >= TO_DATE(?)
carlos@423
   493
        AND fa.dateacct < TO_DATE(?)
carlos@0
   494
        AND fa.factaccttype NOT IN ('O','C','R')
carlos@0
   495
        AND 10=10 AND fa.ad_org_id IN ('1')
carlos@0
   496
        AND 5=5
carlos@0
   497
        AND  6=6
carlos@0
   498
        GROUP BY ev.accountSign, D.PERIOD, 1
carlos@0
   499
        UNION  SELECT to_char('18') AS ID, 2 AS ORDEN, D.PERIOD, 0 AS totalAmt
carlos@0
   500
        FROM fact_acct fa, c_elementvalue ev, (SELECT TO_NUMBER(QUARTER) AS PERIOD
carlos@0
   501
                                                          FROM AD_MONTH
carlos@0
   502
                                                          WHERE TO_NUMBER(VALUE) >= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))
carlos@0
   503
                                                          AND TO_NUMBER(VALUE) <= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))) D
carlos@0
   504
        WHERE fa.account_id = ev.c_elementValue_id
carlos@0
   505
        AND fa.ad_client_id IN   ('19')
carlos@423
   506
        AND fa.account_ID = TO_NUMBER(?)
carlos@423
   507
        AND fa.dateacct >= TO_DATE(?)
carlos@423
   508
        AND fa.dateacct < TO_DATE(?)
carlos@0
   509
        AND fa.factaccttype NOT IN ('O','C','R')
carlos@0
   510
        AND 11=11 AND fa.ad_org_id IN ('1')
carlos@0
   511
        AND 7=7
carlos@0
   512
        AND  8=8
carlos@0
   513
        GROUP BY  ev.accountSign, D.PERIOD, 1) CC
carlos@0
   514
        GROUP BY ID, ORDEN, PERIOD) BB
carlos@0
   515
		GROUP BY ID, ORDEN, PERIOD, LEVEL_REPORT) AM, DUAL
carlos@0
   516
      WHERE AM.ID = '21'
carlos@0
   517
      GROUP BY AM.ID, AM.PERIOD, CLASS_LEVEL, CLASS_LEVEL_REF, CLASS_IMAGE, 1
carlos@0
   518
      ORDER BY 1, AM.PERIOD) AA
carlos@0
   519
     ]]></Sql>
carlos@0
   520
    <Parameter name="levelId" type="replace" optional="true" after="SELECT AM.ID, to_char(" text="'1'"/>
carlos@0
   521
    <Parameter name="parentLevel" type="replace" optional="true" after="||AM.ID||to_char(" text="'99'"/>
carlos@0
   522
    <Parameter name="nivel" type="replace" optional="true" after="AS LEVEL_ID, to_char(" text="'2'"/>
carlos@0
   523
    <Parameter name="levelId" type="replace" optional="true" after="AS NIVEL, to_char(" text="'3'"/>
carlos@0
   524
    <Parameter name="level" type="replace" optional="true" after=", SUM(AM.totalamt_ref) AS TOTALAMT_REF, to_char(" text="'4'"/>
carlos@0
   525
    <Parameter name="level1" type="replace" optional="true" after=" AS NAME, to_char(" text="'5'"/>
carlos@0
   526
    <Parameter name="level2" type="replace" optional="true" after="AS LEVEL1, to_char(" text="'6'"/>
carlos@0
   527
    <Parameter name="level3" type="replace" optional="true" after="AS LEVEL2, to_char(" text="'7'"/>
carlos@0
   528
    <Parameter name="level4" type="replace" optional="true" after="AS LEVEL3, to_char(" text="'8'"/>
carlos@0
   529
    <Parameter name="level" type="replace" optional="true" after="(SELECT ID, ORDEN, PERIOD , to_char(" text="'23'"/>
carlos@0
   530
    <Parameter name="idDimension" type="replace" optional="true" after="FROM(SELECT to_char(" text="'9'"/>
carlos@0
   531
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN (" text="'10'"/>
carlos@0
   532
    <Parameter name="accountId"/>
carlos@0
   533
    <Parameter name="dateFrom"/>
carlos@0
   534
    <Parameter name="dateTo"/>
carlos@0
   535
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 0=0 AND fa.ad_org_id IN (" text="'1'"/>
carlos@0
   536
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
carlos@0
   537
    <Parameter name="mProductId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
carlos@423
   538
    <Parameter name="cProjectId" optional="true" after="AND 1=1"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   539
    <Parameter name="filter" type="replace" optional="true" after="AND 1=1" text="AND  2=2"/>
carlos@0
   540
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY ev.accountSign, TO_NUMBER(AD_MONTH.QUARTER)" text=", VARIBLE11"/>
carlos@0
   541
    <Parameter name="idDimension" type="replace" optional="true" after="UNION SELECT to_char(" text="'12'"/>
carlos@0
   542
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN(" text="'13'"/>
carlos@0
   543
     <Parameter name="accountId"/>
carlos@0
   544
    <Parameter name="dateFromRef"/>
carlos@0
   545
    <Parameter name="dateToRef"/>
carlos@0
   546
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 9=9 AND fa.ad_org_id IN (" text="'1'"/>
carlos@0
   547
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 3=3"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
carlos@0
   548
    <Parameter name="mProductId" optional="true" type="argument" after="AND 3=3"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
carlos@423
   549
    <Parameter name="cProjectId" optional="true" after="AND 3=3"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   550
    <Parameter name="filter" type="replace" optional="true" after="AND 3=3" text="AND  4=4"/>
carlos@0
   551
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY  ev.accountSign, TO_NUMBER(AD_MONTH.QUARTER)" text=", 1"/>
carlos@0
   552
    <Parameter name="level" type="replace" optional="true" after="SELECT ID,ORDEN,PERIOD, to_char(" text="'24'"/>
carlos@0
   553
    <Parameter name="idDimension" type="replace" optional="true" after="FROM (SELECT to_char(" text="'15'"/>
carlos@0
   554
    <Parameter name="dateFrom"/>
carlos@0
   555
    <Parameter name="dateToAux"/>
carlos@0
   556
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN  (" text="'16'"/>
carlos@0
   557
    <Parameter name="accountId"/>
carlos@0
   558
    <Parameter name="dateFrom"/>
carlos@0
   559
    <Parameter name="dateTo"/>
carlos@0
   560
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 10=10 AND fa.ad_org_id IN (" text="'1'"/>
carlos@0
   561
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 5=5"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
carlos@0
   562
    <Parameter name="mProductId" optional="true" type="argument" after="AND 5=5"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
carlos@423
   563
    <Parameter name="cProjectId" optional="true" after="AND 5=5"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   564
    <Parameter name="filter" type="replace" optional="true" after="AND 5=5" text="AND  6=6"/>
carlos@0
   565
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY ev.accountSign, D.PERIOD" text=", 1"/>
carlos@0
   566
    <Parameter name="idDimension" type="replace" optional="true" after="UNION  SELECT to_char(" text="'18'"/>
carlos@0
   567
    <Parameter name="dateFrom"/>
carlos@0
   568
    <Parameter name="dateToAux"/>
carlos@0
   569
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN   (" text="'19'"/>
carlos@0
   570
     <Parameter name="accountId"/>
carlos@0
   571
    <Parameter name="dateFromRef"/>
carlos@0
   572
    <Parameter name="dateToRef"/>
carlos@0
   573
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 11=11 AND fa.ad_org_id IN (" text="'1'"/>
carlos@0
   574
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 7=7"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
carlos@0
   575
    <Parameter name="mProductId" optional="true" type="argument" after="AND 7=7"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
carlos@423
   576
    <Parameter name="cProjectId" optional="true" after="AND 7=7"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
carlos@0
   577
    <Parameter name="filter" type="replace" optional="true" after="AND 7=7" text="AND  8=8"/>
carlos@0
   578
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY  ev.accountSign, D.PERIOD" text=", 1"/>
carlos@0
   579
    <Parameter name="tableDimension" type="replace" optional="true" after="GROUP BY ID, ORDEN, PERIOD, LEVEL_REPORT) AM, " text="DUAL"/>
carlos@0
   580
    <Parameter name="idtableDimension" type="replace" optional="true" after="WHERE AM.ID = " text="'21'"/>
carlos@0
   581
    <Parameter name="nivel" type="replace" optional="true" after="GROUP BY AM.ID, AM.PERIOD, CLASS_LEVEL, CLASS_LEVEL_REF, CLASS_IMAGE, " text="1"/>
carlos@0
   582
    <Parameter name="nivel" type="replace" optional="true" after="ORDER BY " text="1"/>
carlos@0
   583
  </SqlMethod>
carlos@0
   584
carlos@0
   585
  <SqlMethod name="selectDate" type="preparedStatement" return="multiple" default="">
carlos@0
   586
    <SqlMethodComment></SqlMethodComment>
carlos@0
   587
    <Sql>
carlos@0
   588
    <![CDATA[
carlos@0
   589
      SELECT NAME AS ID
carlos@0
   590
      FROM AD_MONTH
carlos@0
   591
      WHERE TO_NUMBER(VALUE) >= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))
carlos@0
   592
      AND TO_NUMBER(VALUE) <= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))
carlos@0
   593
     ]]></Sql>
carlos@0
   594
     <Parameter name="dateFrom"/>
carlos@0
   595
     <Parameter name="dateTo"/>
carlos@0
   596
  </SqlMethod>
carlos@0
   597
carlos@0
   598
  <SqlMethod name="selectQuarter" type="preparedStatement" return="multiple" default="">
carlos@0
   599
    <SqlMethodComment></SqlMethodComment>
carlos@0
   600
    <Sql>
carlos@0
   601
    <![CDATA[
carlos@0
   602
      SELECT DISTINCT QUARTER AS ID
carlos@0
   603
      FROM AD_MONTH
carlos@0
   604
      WHERE TO_NUMBER(VALUE) >= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))
carlos@0
   605
      AND TO_NUMBER(VALUE) <= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))
carlos@0
   606
     ]]></Sql>
carlos@0
   607
     <Parameter name="dateFrom"/>
carlos@0
   608
     <Parameter name="dateTo"/>
carlos@0
   609
  </SqlMethod>
carlos@0
   610
carlos@0
   611
  <SqlMethod name="selectProject" type="preparedStatement" return="String" default="">
carlos@0
   612
    <SqlMethodComment></SqlMethodComment>
carlos@0
   613
    <Sql>
carlos@0
   614
    <![CDATA[
carlos@0
   615
      SELECT C_PROJECT.NAME
carlos@0
   616
      FROM C_PROJECT
carlos@423
   617
      WHERE C_PROJECT_ID = TO_NUMBER(?)
carlos@0
   618
     ]]></Sql>
carlos@0
   619
     <Parameter name="cProjectId"/>
carlos@0
   620
  </SqlMethod>
carlos@0
   621
carlos@0
   622
  <SqlMethod name="selectAccountingCount" type="preparedStatement" return="String" default="">
carlos@0
   623
    <SqlMethodComment></SqlMethodComment>
carlos@0
   624
    <Sql>
carlos@0
   625
    <![CDATA[
carlos@0
   626
      SELECT C_ELEMENTVALUE.NAME
carlos@0
   627
      FROM C_ELEMENTVALUE
carlos@423
   628
      WHERE C_ELEMENTVALUE.C_ELEMENTVALUE_ID = TO_NUMBER(?)
carlos@0
   629
      AND C_ELEMENTVALUE.ISACTIVE = 'Y'
carlos@0
   630
     ]]></Sql>
carlos@0
   631
     <Parameter name="accountingId"/>
carlos@0
   632
  </SqlMethod>
carlos@0
   633
carlos@0
   634
  <SqlMethod name="selectEntity" type="preparedStatement" return="String" default="">
carlos@0
   635
    <SqlMethodComment></SqlMethodComment>
carlos@0
   636
    <Sql>
carlos@0
   637
    <![CDATA[
carlos@0
   638
      SELECT AD_CLIENT.NAME
carlos@0
   639
      FROM AD_CLIENT
carlos@423
   640
      WHERE AD_CLIENT.AD_CLIENT_ID = TO_NUMBER(?)
carlos@0
   641
     ]]></Sql>
carlos@0
   642
    <Parameter name="adClientId"/>
carlos@0
   643
  </SqlMethod>
carlos@0
   644
carlos@0
   645
  <SqlMethod name="selectShown" type="preparedStatement" return="multiple">
carlos@0
   646
    <SqlMethodComment></SqlMethodComment>
carlos@0
   647
    <Sql>
carlos@0
   648
    <![CDATA[
carlos@0
   649
      SELECT ID, NAME
carlos@0
   650
      FROM (SELECT 'AD_ORG_ID' AS ID, 'Organizacion' AS NAME FROM DUAL
carlos@0
   651
      UNION
carlos@0
   652
      SELECT 'C_BPARTNER_ID' AS ID, 'Tercero' AS NAME FROM DUAL
carlos@0
   653
      UNION 
carlos@0
   654
      SELECT 'M_PRODUCT_ID' AS ID, 'Producto' AS NAME FROM DUAL
carlos@0
   655
      UNION
carlos@0
   656
      SELECT 'C_PROJECT_ID' AS ID, 'Proyecto' AS NAME FROM DUAL) A
carlos@0
   657
     ]]></Sql>
carlos@0
   658
  </SqlMethod>
carlos@0
   659
carlos@0
   660
  <SqlMethod name="selectDimension" type="preparedStatement" return="multiple">
carlos@0
   661
    <SqlMethodComment></SqlMethodComment>
carlos@0
   662
    <Sql>
carlos@0
   663
    <![CDATA[
carlos@0
   664
      SELECT AD_DIMENSION.COLUMNNAME, AD_DIMENSION.LINE, AD_DIMENSION.DESCRIPTION, AD_DIMENSION.JOIN_GROUP1, AD_DIMENSION.TABLENAME, AD_DIMENSION.JOIN_GROUP2
carlos@0
   665
      FROM AD_DIMENSION, AD_PROCESS
carlos@0
   666
      WHERE AD_DIMENSION.AD_PROCESS_ID = AD_PROCESS.AD_PROCESS_ID
carlos@0
   667
      AND AD_PROCESS.VALUE = ?
carlos@0
   668
      ORDER BY AD_DIMENSION.LINE
carlos@0
   669
     ]]></Sql>
carlos@0
   670
     <Parameter name="processName"/>
carlos@0
   671
  </SqlMethod>
carlos@0
   672
carlos@0
   673
  <SqlMethod name="selectDimensionName" type="preparedStatement" return="string" default="">
carlos@0
   674
    <SqlMethodComment></SqlMethodComment>
carlos@0
   675
    <Sql>
carlos@0
   676
    <![CDATA[
carlos@0
   677
    SELECT ((CASE AD_ELEMENT.ISACTIVE WHEN 'N' THEN '**' ELSE '' END) || (CASE WHEN AD_ELEMENT_TRL.NAME IS NULL THEN AD_ELEMENT.NAME ELSE AD_ELEMENT_TRL.NAME END)) AS NAME
carlos@0
   678
    FROM AD_ELEMENT left join AD_ELEMENT_TRL on AD_ELEMENT.AD_ELEMENT_ID = AD_ELEMENT_TRL.AD_ELEMENT_ID
carlos@0
   679
                                            and AD_ELEMENT_TRL.AD_LANGUAGE  = ?
carlos@0
   680
    WHERE UPPER(AD_ELEMENT.COLUMNNAME) = ?
carlos@0
   681
     ]]></Sql>
carlos@0
   682
     <Parameter name="language"/>
carlos@0
   683
    <Parameter name="columnname"/>    
carlos@0
   684
  </SqlMethod>
carlos@0
   685
</SqlClass>