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
<?xml version="1.0" encoding="UTF-8" ?>
<!--
 *************************************************************************
 * The contents of this file are subject to the Openbravo  Public  License
 * Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
 * Version 1.1  with a permitted attribution clause; you may not  use this
 * file except in compliance with the License. You  may  obtain  a copy of
 * the License at http://www.openbravo.com/legal/license.html 
 * Software distributed under the License  is  distributed  on  an "AS IS"
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
 * License for the specific  language  governing  rights  and  limitations
 * under the License. 
 * The Original Code is Openbravo ERP. 
 * The Initial Developer of the Original Code is Openbravo SL 
 * All portions are Copyright (C) 2001-2006 Openbravo SL 
 * All Rights Reserved. 
 * Contributor(s):  ______________________________________.
 ************************************************************************
-->

<SqlClass name="ReportAccountingCountDimensionalAnalysesData" package="org.openbravo.erpCommon.ad_reports">
  <SqlClassComment></SqlClassComment>
  <SqlMethod name="select" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      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
      FROM (
      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
        (SELECT to_char('2') AS ID, (CASE ev.accountSign WHEN 'D' THEN SUM(amtacctdr - amtacctcr) ELSE SUM(amtacctcr - amtacctdr) END)  AS totalAmt
        FROM fact_acct fa, c_elementvalue ev
        WHERE fa.account_id = ev.c_elementValue_id
        AND fa.ad_client_id IN ('3')
        AND fa.account_id = TO_number(?)
        AND fa.dateacct >= TO_DATE(?)
        AND fa.dateacct < TO_DATE(?)
        AND fa.factaccttype NOT IN ('O','C','R')
        AND fa.ad_org_id IN ('1')
        AND 1=1
        AND  2=2
        GROUP BY ev.accountSign, 1) A, DUAL
      WHERE A.ID = '1'
      ORDER BY 1) AA
     ]]></Sql> 
    <Parameter name="levelId" type="replace" optional="true" after="SELECT A.ID, to_char(" text="'12'"/>
    <Parameter name="parentLevel" type="replace" optional="true" after="||A.ID||to_char(" text="'99'"/>
    <Parameter name="nivel" type="replace" optional="true" after="AS LEVEL_ID, to_char(" text="'1'"/>
    <Parameter name="levelId" type="replace" optional="true" after="AS NIVEL, to_char(" text="'13'"/>
    <Parameter name="level" type="replace" optional="true" after="A.totalAmt, to_char(" text="'7'"/>
    <Parameter name="level1" type="replace" optional="true" after="AS NAME, to_char(" text="'8'"/>
    <Parameter name="level2" type="replace" optional="true" after="AS LEVEL1, to_char(" text="'9'"/>
    <Parameter name="level3" type="replace" optional="true" after="AS LEVEL2, to_char(" text="'10'"/>
    <Parameter name="level4" type="replace" optional="true" after="AS LEVEL3, to_char(" text="'11'"/>
    <Parameter name="idDimension" type="replace" optional="true" after="(SELECT to_char(" text="'2'"/>
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN (" text="'3'"/>
    <Parameter name="accountId"/>
    <Parameter name="dateFrom"/>
    <Parameter name="dateTo"/>
    <Parameter name="adOrgId" optional="true" type="replace" after="AND fa.ad_org_id IN (" text="'1'"/>
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
    <Parameter name="mProductId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
    <Parameter name="cProjectId" optional="true" after="AND 1=1"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="filter" type="replace" optional="true" after="AND 1=1" text="AND  2=2"/>
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY ev.accountSign" text=", 1"/>
    <Parameter name="tableDimension" type="replace" optional="true" after=") A, " text="DUAL"/>
    <Parameter name="idtableDimension" type="replace" optional="true" after="WHERE A.ID = " text="'1'"/>
    <Parameter name="nivel" type="replace" optional="true" after="ORDER BY " text="1"/>
  </SqlMethod>

  <SqlMethod name="set" type="constant" return="multiple">
      <SqlMethodComment></SqlMethodComment>
      <Sql></Sql>
  </SqlMethod>

  <SqlMethod name="selectComparative" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      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
      FROM (
      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
        FROM 
        (SELECT ID, ORDEN, SUM(TOTALAMT) AS TOTALAMT
         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
        FROM fact_acct fa, c_elementvalue ev
        WHERE fa.account_id = ev.c_elementValue_id
        AND fa.ad_client_id IN ('3')
        AND fa.account_ID = TO_NUMBER(?)
        AND fa.dateacct >= TO_DATE(?)
        AND fa.dateacct < TO_DATE(?)
        AND fa.factaccttype NOT IN ('O','C','R')
        AND 0=0 AND fa.ad_org_id IN ('1')
        AND 1=1
        AND  2=2
        GROUP BY ev.accountSign, 1 
        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
        FROM fact_acct fa, c_elementvalue ev
        WHERE fa.account_id = ev.c_elementValue_id
        AND fa.ad_client_id IN('13')
        AND fa.account_ID = TO_NUMBER(?)
        AND fa.dateacct >= TO_DATE(?)
        AND fa.dateacct < TO_DATE(?)
        AND fa.factaccttype NOT IN ('O','C','R')
        AND 5=5 AND fa.ad_org_id IN ('1')
        AND 3=3
        AND  4=4
        GROUP BY  ev.accountSign, 1) BB
        GROUP BY ID, ORDEN) AM, DUAL
      WHERE AM.ID = '5'
      GROUP BY AM.ID, 1 
      ORDER BY 1) AA
     ]]></Sql>
    <Parameter name="levelId" type="replace" optional="true" after="SELECT AM.ID, to_char(" text="'15'"/>
    <Parameter name="parentLevel" type="replace" optional="true" after="||AM.ID||to_char(" text="'99'"/>
    <Parameter name="nivel" type="replace" optional="true" after="AS LEVEL_ID, to_char(" text="'1'"/>
    <Parameter name="levelId" type="replace" optional="true" after="AS NIVEL, to_char(" text="'15'"/>
    <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'"/>
    <Parameter name="level1" type="replace" optional="true" after="AS NAME, to_char(" text="'8'"/>
    <Parameter name="level2" type="replace" optional="true" after="AS LEVEL1, to_char(" text="'9'"/>
    <Parameter name="level3" type="replace" optional="true" after="AS LEVEL2, to_char(" text="'10'"/>
    <Parameter name="level4" type="replace" optional="true" after="AS LEVEL3, to_char(" text="'11'"/>
    <Parameter name="idDimension" type="replace" optional="true" after="FROM(SELECT to_char(" text="'2'"/>
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN (" text="'3'"/>
    <Parameter name="accountId"/>
    <Parameter name="dateFrom"/>
    <Parameter name="dateTo"/>
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 0=0 AND fa.ad_org_id IN (" text="'1'"/>
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
    <Parameter name="mProductId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
    <Parameter name="cProjectId" optional="true" after="AND 1=1"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="filter" type="replace" optional="true" after="AND 1=1" text="AND  2=2"/>
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY ev.accountSign" text=", 1"/>
    <Parameter name="idDimension" type="replace" optional="true" after="UNION SELECT to_char(" text="'12'"/>
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN(" text="'13'"/>
    <Parameter name="accountId"/>
    <Parameter name="dateFromRef"/>
    <Parameter name="dateToRef"/>
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 5=5 AND fa.ad_org_id IN (" text="'1'"/>
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 3=3"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
    <Parameter name="mProductId" optional="true" type="argument" after="AND 3=3"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
    <Parameter name="cProjectId" optional="true" after="AND 3=3"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="filter" type="replace" optional="true" after="AND 3=3" text="AND  4=4"/>
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY  ev.accountSign" text=", 1"/>
    <Parameter name="tableDimension" type="replace" optional="true" after="GROUP BY ID, ORDEN) AM, " text="DUAL"/>
    <Parameter name="idtableDimension" type="replace" optional="true" after="WHERE AM.ID = " text="'5'"/>
    <Parameter name="nivel" type="replace" optional="true" after="GROUP BY AM.ID, " text="1"/>
    <Parameter name="nivel" type="replace" optional="true" after="ORDER BY " text="1"/>
  </SqlMethod>

  <SqlMethod name="selectPeriod" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      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
      FROM(
      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
         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
            FROM fact_acct fa, c_elementvalue ev
            WHERE fa.account_id = ev.c_elementValue_id
            AND fa.ad_client_id IN ('3')
            AND fa.account_ID = TO_NUMBER(?)
            AND fa.dateacct >= TO_DATE(?)
            AND fa.dateacct < TO_DATE(?)
            AND fa.factaccttype NOT IN ('O','C','R')
            AND 0=0 AND fa.ad_org_id IN ('1')
            AND 1=1
            AND  2=2
            GROUP BY ev.accountSign, TO_NUMBER(TO_CHAR(fa.dateacct,'MM')), 1
            UNION SELECT to_char('5') AS ID, D.PERIOD AS period, 0  AS totalAmt
            FROM fact_acct fa, c_elementvalue ev, (SELECT TO_NUMBER(VALUE) AS PERIOD
                                                          FROM AD_MONTH
                                                          WHERE TO_NUMBER(VALUE) >= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))
                                                          AND TO_NUMBER(VALUE) <= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))) D
            WHERE fa.account_id = ev.c_elementValue_id
            AND fa.ad_client_id IN('6')
            AND fa.account_ID = TO_NUMBER(?)
            AND fa.dateacct >= TO_DATE(?)
            AND fa.dateacct < TO_DATE(?)
            AND fa.factaccttype NOT IN ('O','C','R')
            AND 5=5 AND fa.ad_org_id IN ('1')
            AND 3=3
            AND  4=4
            GROUP BY D.PERIOD, 1 ) A, DUAL
            WHERE A.ID = '8'
            GROUP BY A.ID, A.PERIOD, 1 
            ORDER BY 1, A.PERIOD) AA
     ]]></Sql>
    <Parameter name="levelId" type="replace" optional="true" after="SELECT A.ID, to_char(" text="'10'"/>
    <Parameter name="parentLevel" type="replace" optional="true" after="||A.ID||to_char(" text="'99'"/>
    <Parameter name="nivel" type="replace" optional="true" after="LEVEL_ID, A.PERIOD, to_char(" text="'11'"/>
    <Parameter name="levelId" type="replace" optional="true" after="AS NIVEL, to_char(" text="'12'"/>
    <Parameter name="level" type="replace" optional="true" after="AS TOTALAMT, to_char(" text="'13'"/>
    <Parameter name="level1" type="replace" optional="true" after="AS NAME, to_char(" text="'14'"/>
    <Parameter name="level2" type="replace" optional="true" after="AS LEVEL1, to_char(" text="'15'"/>
    <Parameter name="level3" type="replace" optional="true" after="AS LEVEL2, to_char(" text="'16'"/>
    <Parameter name="level4" type="replace" optional="true" after="AS LEVEL3, to_char(" text="'17'"/>
    <Parameter name="idDimension" type="replace" optional="true" after="FROM (SELECT to_char(" text="'2'"/>
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN (" text="'3'"/>
    <Parameter name="accountId"/>
    <Parameter name="dateFrom"/>
    <Parameter name="dateTo"/>
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 0=0 AND fa.ad_org_id IN (" text="'1'"/>
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
    <Parameter name="mProductId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
    <Parameter name="cProjectId" optional="true" after="AND 1=1"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="filter" type="replace" optional="true" after="AND 1=1" text="AND  2=2"/>
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY ev.accountSign, TO_NUMBER(TO_CHAR(fa.dateacct,'MM'))" text=", 1"/>
    <Parameter name="idDimension" type="replace" optional="true" after="UNION SELECT to_char(" text="'5'"/>
    <Parameter name="dateFrom"/>
    <Parameter name="dateToAux"/>
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN(" text="'6'"/>
     <Parameter name="accountId"/>
    <Parameter name="dateFrom"/>
    <Parameter name="dateTo"/>
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 5=5 AND fa.ad_org_id IN (" text="'1'"/>
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 3=3"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
    <Parameter name="mProductId" optional="true" type="argument" after="AND 3=3"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
    <Parameter name="cProjectId" optional="true" after="AND 3=3"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="filter" type="replace" optional="true" after="AND 3=3" text="AND  4=4"/>
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY D.PERIOD" text=", 1"/>
    <Parameter name="tableDimension" type="replace" optional="true" after=") A, " text="DUAL"/>
    <Parameter name="idtableDimension" type="replace" optional="true" after="WHERE A.ID = " text="'8'"/>
    <Parameter name="nivel" type="replace" optional="true" after="GROUP BY A.ID, A.PERIOD, " text="1"/>
    <Parameter name="nivel" type="replace" optional="true" after="ORDER BY " text="1"/>
  </SqlMethod>

  <SqlMethod name="selectPeriodQuarter" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      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
      FROM(
      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
         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
            FROM fact_acct fa, c_elementvalue ev, AD_MONTH
            WHERE fa.account_id = ev.c_elementValue_id
            AND fa.ad_client_id IN ('3')
            AND fa.account_ID = TO_NUMBER(?)
            AND fa.dateacct >= TO_DATE(?)
            AND fa.dateacct < TO_DATE(?)
            AND fa.factaccttype NOT IN ('O','C','R')
            AND TO_NUMBER(TO_CHAR(TO_DATE(fa.dateacct), 'MM')) = TO_NUMBER(AD_MONTH.VALUE)
            AND 0=0 AND fa.ad_org_id IN ('1')
            AND 1=1
            AND  2=2
            GROUP BY ev.accountSign, TO_NUMBER(AD_MONTH.QUARTER), 1
            UNION SELECT TO_CHAR('5') AS ID, D.PERIOD AS period, 0  AS totalAmt
            FROM fact_acct fa, c_elementvalue ev, (SELECT TO_NUMBER(QUARTER) AS PERIOD
                                                          FROM AD_MONTH
                                                          WHERE TO_NUMBER(VALUE) >= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))
                                                          AND TO_NUMBER(VALUE) <= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))) D
            WHERE fa.account_id = ev.c_elementValue_id
            AND fa.ad_client_id IN('6')
            AND fa.account_ID = TO_NUMBER(?)
            AND fa.dateacct >= TO_DATE(?)
            AND fa.dateacct < TO_DATE(?)
            AND fa.factaccttype NOT IN ('O','C','R')
            AND 5=5 AND fa.ad_org_id IN ('1')
            AND 3=3
            AND  4=4
            GROUP BY D.PERIOD, 1) A, DUAL
            WHERE A.ID = '8'
            GROUP BY A.ID, A.PERIOD, 1
            ORDER BY 1, A.PERIOD) AA
     ]]></Sql>
    <Parameter name="levelId" type="replace" optional="true" after="SELECT A.ID, to_char(" text="'10'"/>
    <Parameter name="parentLevel" type="replace" optional="true" after="||A.ID||to_char(" text="'99'"/>
    <Parameter name="nivel" type="replace" optional="true" after="LEVEL_ID, A.PERIOD, to_char(" text="'11'"/>
    <Parameter name="levelId" type="replace" optional="true" after="AS NIVEL, to_char(" text="'12'"/>
    <Parameter name="level" type="replace" optional="true" after="AS TOTALAMT, to_char(" text="'13'"/>
    <Parameter name="level1" type="replace" optional="true" after="AS NAME, to_char(" text="'14'"/>
    <Parameter name="level2" type="replace" optional="true" after="AS LEVEL1, to_char(" text="'15'"/>
    <Parameter name="level3" type="replace" optional="true" after="AS LEVEL2, to_char(" text="'16'"/>
    <Parameter name="level4" type="replace" optional="true" after="AS LEVEL3, to_char(" text="'17'"/>
    <Parameter name="idDimension" type="replace" optional="true" after="FROM (SELECT to_char(" text="'2'"/>
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN (" text="'3'"/>
    <Parameter name="accountId"/>
    <Parameter name="dateFrom"/>
    <Parameter name="dateTo"/>
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 0=0 AND fa.ad_org_id IN (" text="'1'"/>
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
    <Parameter name="mProductId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
    <Parameter name="cProjectId" optional="true" after="AND 1=1"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="filter" type="replace" optional="true" after="AND 1=1" text="AND  2=2"/>
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY ev.accountSign, TO_NUMBER(AD_MONTH.QUARTER)" text=", 1"/>
    <Parameter name="idDimension" type="replace" optional="true" after="UNION SELECT TO_CHAR(" text="'5'"/>
    <Parameter name="dateFrom"/>
    <Parameter name="dateToAux"/>
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN(" text="'6'"/>
     <Parameter name="accountId"/>
    <Parameter name="dateFrom"/>
    <Parameter name="dateTo"/>
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 5=5 AND fa.ad_org_id IN (" text="'1'"/>
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 3=3"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
    <Parameter name="mProductId" optional="true" type="argument" after="AND 3=3"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
    <Parameter name="cProjectId" optional="true" after="AND 3=3"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="filter" type="replace" optional="true" after="AND 3=3" text="AND  4=4"/>
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY D.PERIOD" text=", 1"/>
    <Parameter name="tableDimension" type="replace" optional="true" after=") A, " text="DUAL"/>
    <Parameter name="idtableDimension" type="replace" optional="true" after="WHERE A.ID = " text="'8'"/>
    <Parameter name="nivel" type="replace" optional="true" after="GROUP BY A.ID, A.PERIOD, " text="1"/>
    <Parameter name="nivel" type="replace" optional="true" after="ORDER BY " text="1"/>
  </SqlMethod>

  <SqlMethod name="selectPeriodComparative" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      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
      FROM (
      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
        FROM 
        (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
		FROM 
		(SELECT ID, ORDEN, PERIOD , to_char('23') AS LEVEL_REPORT, SUM(TOTALAMT) AS TOTALAMT
         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
        FROM fact_acct fa, c_elementvalue ev
        WHERE fa.account_id = ev.c_elementValue_id
        AND fa.ad_client_id IN ('10')
        AND fa.account_ID = TO_NUMBER(?)
        AND fa.dateacct >= TO_DATE(?)
        AND fa.dateacct < TO_DATE(?)
        AND fa.factaccttype NOT IN ('O','C','R')
        AND 0=0 AND fa.ad_org_id IN ('1')
        AND 1=1
        AND  2=2
        GROUP BY ev.accountSign, TO_NUMBER(TO_CHAR(fa.dateacct,'MM')), 1
        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
        FROM fact_acct fa, c_elementvalue ev
        WHERE fa.account_id = ev.c_elementValue_id
        AND fa.ad_client_id IN('13')
        AND fa.account_ID = TO_NUMBER(?)
        AND fa.dateacct >= TO_DATE(?)
        AND fa.dateacct < TO_DATE(?)
        AND fa.factaccttype NOT IN ('O','C','R')
        AND 9=9 AND fa.ad_org_id IN ('1')
        AND 3=3
        AND  4=4
        GROUP BY  ev.accountSign, TO_NUMBER(TO_CHAR(fa.dateacct,'MM')), 1) BB
        GROUP BY ID, ORDEN, PERIOD
		UNION
		SELECT ID,ORDEN,PERIOD, to_char('24') AS LEVEL_REPORT, SUM(TOTALAMT) AS TOTALAMT
         FROM (SELECT to_char('15') AS ID, 1 AS ORDEN, D.PERIOD, 0  AS totalAmt
        FROM fact_acct fa, c_elementvalue ev, (SELECT TO_NUMBER(VALUE) AS PERIOD
                                                          FROM AD_MONTH
                                                          WHERE TO_NUMBER(VALUE) >= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))
                                                          AND TO_NUMBER(VALUE) <= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))) D
        WHERE fa.account_id = ev.c_elementValue_id
        AND fa.ad_client_id IN  ('16')
        AND fa.account_ID = TO_NUMBER(?)
        AND fa.dateacct >= TO_DATE(?)
        AND fa.dateacct < TO_DATE(?)
        AND fa.factaccttype NOT IN ('O','C','R')
        AND 10=10 AND fa.ad_org_id IN ('1')
        AND 5=5
        AND  6=6
        GROUP BY ev.accountSign, D.PERIOD, 1
        UNION  SELECT to_char('18') AS ID, 2 AS ORDEN, D.PERIOD, 0 AS totalAmt
        FROM fact_acct fa, c_elementvalue ev, (SELECT TO_NUMBER(VALUE) AS PERIOD
                                                          FROM AD_MONTH
                                                          WHERE TO_NUMBER(VALUE) >= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))
                                                          AND TO_NUMBER(VALUE) <= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))) D
        WHERE fa.account_id = ev.c_elementValue_id
        AND fa.ad_client_id IN   ('19')
        AND fa.account_ID = TO_NUMBER(?)
        AND fa.dateacct >= TO_DATE(?)
        AND fa.dateacct < TO_DATE(?)
        AND fa.factaccttype NOT IN ('O','C','R')
        AND 11=11 AND fa.ad_org_id IN ('1')
        AND 7=7
        AND  8=8
        GROUP BY  ev.accountSign, D.PERIOD, 1) BBB
        GROUP BY ID, ORDEN, PERIOD) AAA
		GROUP BY ID, ORDEN, PERIOD, LEVEL_REPORT) AM, DUAL
      WHERE AM.ID = '21'
      GROUP BY AM.ID, AM.PERIOD, CLASS_LEVEL, CLASS_LEVEL_REF, CLASS_IMAGE, 1
      ORDER BY 1, AM.PERIOD) AA
     ]]></Sql>
    <Parameter name="levelId" type="replace" optional="true" after="SELECT AM.ID, to_char(" text="'1'"/>
    <Parameter name="parentLevel" type="replace" optional="true" after="||AM.ID||to_char(" text="'99'"/>
    <Parameter name="nivel" type="replace" optional="true" after="AS LEVEL_ID, to_char(" text="'2'"/>
    <Parameter name="levelId" type="replace" optional="true" after="AS NIVEL, to_char(" text="'3'"/>
    <Parameter name="level" type="replace" optional="true" after=", SUM(AM.totalamt_ref) AS TOTALAMT_REF, to_char(" text="'4'"/>
    <Parameter name="level1" type="replace" optional="true" after=" AS NAME, to_char(" text="'5'"/>
    <Parameter name="level2" type="replace" optional="true" after="AS LEVEL1, to_char(" text="'6'"/>
    <Parameter name="level3" type="replace" optional="true" after="AS LEVEL2, to_char(" text="'7'"/>
    <Parameter name="level4" type="replace" optional="true" after="AS LEVEL3, to_char(" text="'8'"/>
    <Parameter name="level" type="replace" optional="true" after="(SELECT ID, ORDEN, PERIOD , to_char(" text="'23'"/>
    <Parameter name="idDimension" type="replace" optional="true" after="FROM(SELECT to_char(" text="'9'"/>
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN (" text="'10'"/>
    <Parameter name="accountId"/>
    <Parameter name="dateFrom"/>
    <Parameter name="dateTo"/>
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 0=0 AND fa.ad_org_id IN (" text="'1'"/>
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
    <Parameter name="mProductId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
    <Parameter name="cProjectId" optional="true" after="AND 1=1"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="filter" type="replace" optional="true" after="AND 1=1" text="AND  2=2"/>
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY ev.accountSign, TO_NUMBER(TO_CHAR(fa.dateacct,'MM'))" text=", 1"/>
    <Parameter name="idDimension" type="replace" optional="true" after="UNION SELECT to_char(" text="'12'"/>
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN(" text="'13'"/>
     <Parameter name="accountId"/>
    <Parameter name="dateFromRef"/>
    <Parameter name="dateToRef"/>
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 9=9 AND fa.ad_org_id IN (" text="'1'"/>
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 3=3"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
    <Parameter name="mProductId" optional="true" type="argument" after="AND 3=3"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
    <Parameter name="cProjectId" optional="true" after="AND 3=3"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="filter" type="replace" optional="true" after="AND 3=3" text="AND  4=4"/>
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY  ev.accountSign, TO_NUMBER(TO_CHAR(fa.dateacct,'MM'))" text=", 1"/>
    <Parameter name="level" type="replace" optional="true" after="SELECT ID,ORDEN,PERIOD, to_char(" text="'24'"/>
    <Parameter name="idDimension" type="replace" optional="true" after="FROM (SELECT to_char(" text="'15'"/>
    <Parameter name="dateFrom"/>
    <Parameter name="dateToAux"/>
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN  (" text="'16'"/>
    <Parameter name="accountId"/>
    <Parameter name="dateFrom"/>
    <Parameter name="dateTo"/>
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 10=10 AND fa.ad_org_id IN (" text="'1'"/>
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 5=5"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
    <Parameter name="mProductId" optional="true" type="argument" after="AND 5=5"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
    <Parameter name="cProjectId" optional="true" after="AND 5=5"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="filter" type="replace" optional="true" after="AND 5=5" text="AND  6=6"/>
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY ev.accountSign, D.PERIOD" text=", 1"/>
    <Parameter name="idDimension" type="replace" optional="true" after="UNION  SELECT to_char(" text="'18'"/>
    <Parameter name="dateFrom"/>
    <Parameter name="dateToAux"/>
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN   (" text="'19'"/>
     <Parameter name="accountId"/>
    <Parameter name="dateFromRef"/>
    <Parameter name="dateToRef"/>
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 11=11 AND fa.ad_org_id IN (" text="'1'"/>
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 7=7"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
    <Parameter name="mProductId" optional="true" type="argument" after="AND 7=7"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
    <Parameter name="cProjectId" optional="true" after="AND 7=7"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="filter" type="replace" optional="true" after="AND 7=7" text="AND  8=8"/>
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY  ev.accountSign, D.PERIOD" text=", 1"/>
    <Parameter name="tableDimension" type="replace" optional="true" after="GROUP BY ID, ORDEN, PERIOD, LEVEL_REPORT) AM, " text="DUAL"/>
    <Parameter name="idtableDimension" type="replace" optional="true" after="WHERE AM.ID = " text="'21'"/>
    <Parameter name="nivel" type="replace" optional="true" after="GROUP BY AM.ID, AM.PERIOD, CLASS_LEVEL, CLASS_LEVEL_REF, CLASS_IMAGE, " text="1"/>
    <Parameter name="nivel" type="replace" optional="true" after="ORDER BY " text="1"/>
  </SqlMethod>

  <SqlMethod name="selectPeriodComparativeQuarter" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      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
      FROM (
      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
        FROM 
        (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
		FROM 
		(SELECT ID, ORDEN, PERIOD , to_char('23') AS LEVEL_REPORT, SUM(TOTALAMT) AS TOTALAMT
         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
        FROM fact_acct fa, c_elementvalue ev, AD_MONTH
        WHERE fa.account_id = ev.c_elementValue_id
        AND fa.ad_client_id IN ('10')
        AND fa.account_ID = TO_NUMBER(?)
        AND fa.dateacct >= TO_DATE(?)
        AND fa.dateacct < TO_DATE(?)
        AND fa.factaccttype NOT IN ('O','C','R')
        AND TO_NUMBER(TO_CHAR(TO_DATE(fa.dateacct), 'MM')) = TO_NUMBER(AD_MONTH.VALUE)
        AND 0=0 AND fa.ad_org_id IN ('1')
        AND 1=1
        AND  2=2
        GROUP BY ev.accountSign, TO_NUMBER(AD_MONTH.QUARTER), 1
        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
        FROM fact_acct fa, c_elementvalue ev, AD_MONTH
        WHERE fa.account_id = ev.c_elementValue_id
        AND fa.ad_client_id IN('13')
        AND fa.account_ID = TO_NUMBER(?)
        AND fa.dateacct >= TO_DATE(?)
        AND fa.dateacct < TO_DATE(?)
        AND fa.factaccttype NOT IN ('O','C','R')
        AND TO_NUMBER(TO_CHAR(TO_DATE(fa.dateacct), 'MM')) = TO_NUMBER(AD_MONTH.VALUE)
        AND 9=9 AND fa.ad_org_id IN ('1')
        AND 3=3
        AND  4=4
        GROUP BY  ev.accountSign, TO_NUMBER(AD_MONTH.QUARTER), 1) BBB
        GROUP BY ID, ORDEN, PERIOD
		UNION
		SELECT ID,ORDEN,PERIOD, to_char('24') AS LEVEL_REPORT, SUM(TOTALAMT) AS TOTALAMT
         FROM (SELECT to_char('15') AS ID, 1 AS ORDEN, D.PERIOD, 0  AS totalAmt
        FROM fact_acct fa, c_elementvalue ev, (SELECT TO_NUMBER(QUARTER) AS PERIOD
                                                          FROM AD_MONTH
                                                          WHERE TO_NUMBER(VALUE) >= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))
                                                          AND TO_NUMBER(VALUE) <= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))) D
        WHERE fa.account_id = ev.c_elementValue_id
        AND fa.ad_client_id IN  ('16')
        AND fa.account_ID = TO_NUMBER(?)
        AND fa.dateacct >= TO_DATE(?)
        AND fa.dateacct < TO_DATE(?)
        AND fa.factaccttype NOT IN ('O','C','R')
        AND 10=10 AND fa.ad_org_id IN ('1')
        AND 5=5
        AND  6=6
        GROUP BY ev.accountSign, D.PERIOD, 1
        UNION  SELECT to_char('18') AS ID, 2 AS ORDEN, D.PERIOD, 0 AS totalAmt
        FROM fact_acct fa, c_elementvalue ev, (SELECT TO_NUMBER(QUARTER) AS PERIOD
                                                          FROM AD_MONTH
                                                          WHERE TO_NUMBER(VALUE) >= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))
                                                          AND TO_NUMBER(VALUE) <= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))) D
        WHERE fa.account_id = ev.c_elementValue_id
        AND fa.ad_client_id IN   ('19')
        AND fa.account_ID = TO_NUMBER(?)
        AND fa.dateacct >= TO_DATE(?)
        AND fa.dateacct < TO_DATE(?)
        AND fa.factaccttype NOT IN ('O','C','R')
        AND 11=11 AND fa.ad_org_id IN ('1')
        AND 7=7
        AND  8=8
        GROUP BY  ev.accountSign, D.PERIOD, 1) CC
        GROUP BY ID, ORDEN, PERIOD) BB
		GROUP BY ID, ORDEN, PERIOD, LEVEL_REPORT) AM, DUAL
      WHERE AM.ID = '21'
      GROUP BY AM.ID, AM.PERIOD, CLASS_LEVEL, CLASS_LEVEL_REF, CLASS_IMAGE, 1
      ORDER BY 1, AM.PERIOD) AA
     ]]></Sql>
    <Parameter name="levelId" type="replace" optional="true" after="SELECT AM.ID, to_char(" text="'1'"/>
    <Parameter name="parentLevel" type="replace" optional="true" after="||AM.ID||to_char(" text="'99'"/>
    <Parameter name="nivel" type="replace" optional="true" after="AS LEVEL_ID, to_char(" text="'2'"/>
    <Parameter name="levelId" type="replace" optional="true" after="AS NIVEL, to_char(" text="'3'"/>
    <Parameter name="level" type="replace" optional="true" after=", SUM(AM.totalamt_ref) AS TOTALAMT_REF, to_char(" text="'4'"/>
    <Parameter name="level1" type="replace" optional="true" after=" AS NAME, to_char(" text="'5'"/>
    <Parameter name="level2" type="replace" optional="true" after="AS LEVEL1, to_char(" text="'6'"/>
    <Parameter name="level3" type="replace" optional="true" after="AS LEVEL2, to_char(" text="'7'"/>
    <Parameter name="level4" type="replace" optional="true" after="AS LEVEL3, to_char(" text="'8'"/>
    <Parameter name="level" type="replace" optional="true" after="(SELECT ID, ORDEN, PERIOD , to_char(" text="'23'"/>
    <Parameter name="idDimension" type="replace" optional="true" after="FROM(SELECT to_char(" text="'9'"/>
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN (" text="'10'"/>
    <Parameter name="accountId"/>
    <Parameter name="dateFrom"/>
    <Parameter name="dateTo"/>
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 0=0 AND fa.ad_org_id IN (" text="'1'"/>
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
    <Parameter name="mProductId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
    <Parameter name="cProjectId" optional="true" after="AND 1=1"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="filter" type="replace" optional="true" after="AND 1=1" text="AND  2=2"/>
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY ev.accountSign, TO_NUMBER(AD_MONTH.QUARTER)" text=", VARIBLE11"/>
    <Parameter name="idDimension" type="replace" optional="true" after="UNION SELECT to_char(" text="'12'"/>
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN(" text="'13'"/>
     <Parameter name="accountId"/>
    <Parameter name="dateFromRef"/>
    <Parameter name="dateToRef"/>
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 9=9 AND fa.ad_org_id IN (" text="'1'"/>
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 3=3"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
    <Parameter name="mProductId" optional="true" type="argument" after="AND 3=3"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
    <Parameter name="cProjectId" optional="true" after="AND 3=3"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="filter" type="replace" optional="true" after="AND 3=3" text="AND  4=4"/>
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY  ev.accountSign, TO_NUMBER(AD_MONTH.QUARTER)" text=", 1"/>
    <Parameter name="level" type="replace" optional="true" after="SELECT ID,ORDEN,PERIOD, to_char(" text="'24'"/>
    <Parameter name="idDimension" type="replace" optional="true" after="FROM (SELECT to_char(" text="'15'"/>
    <Parameter name="dateFrom"/>
    <Parameter name="dateToAux"/>
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN  (" text="'16'"/>
    <Parameter name="accountId"/>
    <Parameter name="dateFrom"/>
    <Parameter name="dateTo"/>
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 10=10 AND fa.ad_org_id IN (" text="'1'"/>
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 5=5"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
    <Parameter name="mProductId" optional="true" type="argument" after="AND 5=5"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
    <Parameter name="cProjectId" optional="true" after="AND 5=5"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="filter" type="replace" optional="true" after="AND 5=5" text="AND  6=6"/>
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY ev.accountSign, D.PERIOD" text=", 1"/>
    <Parameter name="idDimension" type="replace" optional="true" after="UNION  SELECT to_char(" text="'18'"/>
    <Parameter name="dateFrom"/>
    <Parameter name="dateToAux"/>
    <Parameter name="adUserClient" type="replace" optional="true" after="AND fa.ad_client_id IN   (" text="'19'"/>
     <Parameter name="accountId"/>
    <Parameter name="dateFromRef"/>
    <Parameter name="dateToRef"/>
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 11=11 AND fa.ad_org_id IN (" text="'1'"/>
    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 7=7"><![CDATA[ AND C_BPARTNER_ID IN]]></Parameter>
    <Parameter name="mProductId" optional="true" type="argument" after="AND 7=7"><![CDATA[ AND M_PRODUCT_ID IN]]></Parameter>
    <Parameter name="cProjectId" optional="true" after="AND 7=7"><![CDATA[AND C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="filter" type="replace" optional="true" after="AND 7=7" text="AND  8=8"/>
    <Parameter name="groupBy" type="replace" optional="true" after="GROUP BY  ev.accountSign, D.PERIOD" text=", 1"/>
    <Parameter name="tableDimension" type="replace" optional="true" after="GROUP BY ID, ORDEN, PERIOD, LEVEL_REPORT) AM, " text="DUAL"/>
    <Parameter name="idtableDimension" type="replace" optional="true" after="WHERE AM.ID = " text="'21'"/>
    <Parameter name="nivel" type="replace" optional="true" after="GROUP BY AM.ID, AM.PERIOD, CLASS_LEVEL, CLASS_LEVEL_REF, CLASS_IMAGE, " text="1"/>
    <Parameter name="nivel" type="replace" optional="true" after="ORDER BY " text="1"/>
  </SqlMethod>

  <SqlMethod name="selectDate" type="preparedStatement" return="multiple" default="">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT NAME AS ID
      FROM AD_MONTH
      WHERE TO_NUMBER(VALUE) >= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))
      AND TO_NUMBER(VALUE) <= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))
     ]]></Sql>
     <Parameter name="dateFrom"/>
     <Parameter name="dateTo"/>
  </SqlMethod>

  <SqlMethod name="selectQuarter" type="preparedStatement" return="multiple" default="">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT DISTINCT QUARTER AS ID
      FROM AD_MONTH
      WHERE TO_NUMBER(VALUE) >= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))
      AND TO_NUMBER(VALUE) <= TO_NUMBER(TO_CHAR(TO_DATE(to_char(?)), 'MM'))
     ]]></Sql>
     <Parameter name="dateFrom"/>
     <Parameter name="dateTo"/>
  </SqlMethod>

  <SqlMethod name="selectProject" type="preparedStatement" return="String" default="">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT C_PROJECT.NAME
      FROM C_PROJECT
      WHERE C_PROJECT_ID = TO_NUMBER(?)
     ]]></Sql>
     <Parameter name="cProjectId"/>
  </SqlMethod>

  <SqlMethod name="selectAccountingCount" type="preparedStatement" return="String" default="">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT C_ELEMENTVALUE.NAME
      FROM C_ELEMENTVALUE
      WHERE C_ELEMENTVALUE.C_ELEMENTVALUE_ID = TO_NUMBER(?)
      AND C_ELEMENTVALUE.ISACTIVE = 'Y'
     ]]></Sql>
     <Parameter name="accountingId"/>
  </SqlMethod>

  <SqlMethod name="selectEntity" type="preparedStatement" return="String" default="">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT AD_CLIENT.NAME
      FROM AD_CLIENT
      WHERE AD_CLIENT.AD_CLIENT_ID = TO_NUMBER(?)
     ]]></Sql>
    <Parameter name="adClientId"/>
  </SqlMethod>

  <SqlMethod name="selectShown" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT ID, NAME
      FROM (SELECT 'AD_ORG_ID' AS ID, 'Organizacion' AS NAME FROM DUAL
      UNION
      SELECT 'C_BPARTNER_ID' AS ID, 'Tercero' AS NAME FROM DUAL
      UNION 
      SELECT 'M_PRODUCT_ID' AS ID, 'Producto' AS NAME FROM DUAL
      UNION
      SELECT 'C_PROJECT_ID' AS ID, 'Proyecto' AS NAME FROM DUAL) A
     ]]></Sql>
  </SqlMethod>

  <SqlMethod name="selectDimension" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT AD_DIMENSION.COLUMNNAME, AD_DIMENSION.LINE, AD_DIMENSION.DESCRIPTION, AD_DIMENSION.JOIN_GROUP1, AD_DIMENSION.TABLENAME, AD_DIMENSION.JOIN_GROUP2
      FROM AD_DIMENSION, AD_PROCESS
      WHERE AD_DIMENSION.AD_PROCESS_ID = AD_PROCESS.AD_PROCESS_ID
      AND AD_PROCESS.VALUE = ?
      ORDER BY AD_DIMENSION.LINE
     ]]></Sql>
     <Parameter name="processName"/>
  </SqlMethod>

  <SqlMethod name="selectDimensionName" type="preparedStatement" return="string" default="">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
    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
    FROM AD_ELEMENT left join AD_ELEMENT_TRL on AD_ELEMENT.AD_ELEMENT_ID = AD_ELEMENT_TRL.AD_ELEMENT_ID
                                            and AD_ELEMENT_TRL.AD_LANGUAGE  = ?
    WHERE UPPER(AD_ELEMENT.COLUMNNAME) = ?
     ]]></Sql>
     <Parameter name="language"/>
    <Parameter name="columnname"/>    
  </SqlMethod>
</SqlClass>