Fixes bug 20472: General Ledger does not calculate previous amount taking into account all the movement history.
authorEduardo Argal Guibert <eduardo.argal@openbravo.com>
Fri, 11 May 2012 18:47:35 +0200
changeset 16498 f8752c98c5d9
parent 16497 c7d28f77823d
child 16499 9ae759d03174
Fixes bug 20472: General Ledger does not calculate previous amount taking into account all the movement history.
src-db/database/sourcedata/AD_TEXTINTERFACES.xml
src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.html
src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.java
src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.xml
src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger_data.xsql
--- a/src-db/database/sourcedata/AD_TEXTINTERFACES.xml	Mon May 07 10:56:04 2012 +0200
+++ b/src-db/database/sourcedata/AD_TEXTINTERFACES.xml	Fri May 11 18:47:35 2012 +0200
@@ -7697,17 +7697,6 @@
 <!--807611-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
 <!--807611--></AD_TEXTINTERFACES>
 
-<!--807738--><AD_TEXTINTERFACES>
-<!--807738-->  <AD_TEXTINTERFACES_ID><![CDATA[807738]]></AD_TEXTINTERFACES_ID>
-<!--807738-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
-<!--807738-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
-<!--807738-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
-<!--807738-->  <TEXT><![CDATA[Hide matched Debt/Payments]]></TEXT>
-<!--807738-->  <FILENAME><![CDATA[/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.html]]></FILENAME>
-<!--807738-->  <ISUSED><![CDATA[Y]]></ISUSED>
-<!--807738-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
-<!--807738--></AD_TEXTINTERFACES>
-
 <!--807852--><AD_TEXTINTERFACES>
 <!--807852-->  <AD_TEXTINTERFACES_ID><![CDATA[807852]]></AD_TEXTINTERFACES_ID>
 <!--807852-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
--- a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.html	Mon May 07 10:56:04 2012 +0200
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.html	Fri May 11 18:47:35 2012 +0200
@@ -13,7 +13,7 @@
  * under the License.
  * The Original Code is Openbravo ERP.
  * The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2001-2011 Openbravo SLU
+ * All portions are Copyright (C) 2001-2012 Openbravo SLU
  * All Rights Reserved.
  * Contributor(s):  ______________________________________.
  ************************************************************************
@@ -130,22 +130,22 @@
   }
 
   function openGeneralLedgerJournal(recordid) {
-	  
-	  var myframe = getFrame('LayoutMDI') || top.opener;
-      if (myframe) {	  
-		  myframe.OB.RemoteCallManager.call('org.openbravo.client.application.ComputeTranslatedNameActionHandler', {}, {'processId': '800000'}, 
-		  function(response, data, request){
-		            myframe.OB.Layout.ViewManager.openView('OBClassicWindow', {
-		                command: 'DIRECT2',
-		                icon: '[SKINIMG]../../org.openbravo.client.application/images/application-menu/iconReport.png',
-		                id: '800000',
-		                obManualURL: '/ad_reports/ReportGeneralLedgerJournal.html?inpFactAcctGroupId=' + recordid,
-		                processId: '800000',
-		                tabTitle: data.processTitle,
-		                type: 'report',
-		                viewId: 'OBClassicWindow'
-		            });		        		   
-		  });
+      
+      var myframe = getFrame('LayoutMDI') || top.opener;
+      if (myframe) {      
+          myframe.OB.RemoteCallManager.call('org.openbravo.client.application.ComputeTranslatedNameActionHandler', {}, {'processId': '800000'}, 
+          function(response, data, request){
+                    myframe.OB.Layout.ViewManager.openView('OBClassicWindow', {
+                        command: 'DIRECT2',
+                        icon: '[SKINIMG]../../org.openbravo.client.application/images/application-menu/iconReport.png',
+                        id: '800000',
+                        obManualURL: '/ad_reports/ReportGeneralLedgerJournal.html?inpFactAcctGroupId=' + recordid,
+                        processId: '800000',
+                        tabTitle: data.processTitle,
+                        type: 'report',
+                        viewId: 'OBClassicWindow'
+                    });                        
+          });
       }
   }
   </script>
@@ -374,9 +374,6 @@
                   <div id="reportAD_ORGID"></div>
                 </select>
               </td>
-              <td class="TitleCell"><span class="LabelText">Hide matched Debt/Payments</span></td>
-              <td class="Radio_Check_ContentCell"><input TYPE="checkbox" id="paramHideMatched" name="inpHideMatched" value="Y"/></td>
-              <td class="ContentCell"></td>
             </tr>
             <tr>
               <td class="TitleCell"> <span class="LabelText">Accounting schema</span></td>
@@ -808,8 +805,7 @@
                 <div id="sectionAmount">
                   <table cellspacing="0" cellpadding="0" width="100%" class="DataGrid_Header_Table DataGrid_Body_Table" style="table-layout: auto;">
                     <tr class="DataGrid_Body_Row">
-                      <th colspan="2" class="DataGrid_Header_Cell">Account No.<span>:&nbsp;</span><span id="fieldValue">xx70000</span></th>
-                      <th colspan="3" class="DataGrid_Header_Cell">Name<span>:&nbsp;</span><span id="fieldName">xxChecking Unallocated Receipts</span></th>
+                      <th colspan="5" class="DataGrid_Header_Cell">Account<span>:&nbsp;</span><span id="fieldValue">xx70000</span><span>&nbsp;</span><span id="fieldName">xxChecking Unallocated Receipts</span></th>
                     </tr>
 
                     <tr class="DataGrid_Body_Row">
@@ -874,16 +870,12 @@
                 </div>
                 <div id="sectionGroupBy">
                   <table cellspacing="0" cellpadding="0" width="90%" class="DataGrid_Header_Table DataGrid_Body_Table" style="table-layout: auto;">
-                    <tr class="DataGrid_Body_Row">
-                      <th colspan="5" class="DataGrid_Header_Cell"><span id="fieldGroupBy">xxBusiness Partner</span><span>:&nbsp;</span>
-                        <span id="fieldGroupByName">xxGroupbyname</span></th>
-                    </tr>
-
                     <div id="sectionAmount2">
 
                       <tr class="DataGrid_Body_Row">
-                        <th colspan="2" class="DataGrid_Header_Cell">Account No.<span>:&nbsp;</span><span id="fieldValue2">xx70000</span></th>
-                        <th colspan="3" class="DataGrid_Header_Cell">Name<span>:&nbsp;</span><span id="fieldName2">xxChecking Unallocated Receipts</span></th>
+                        <th colspan="4" class="DataGrid_Header_Cell">Account<span>:&nbsp;</span><span id="fieldValue2">xx70000</span><span>&nbsp;</span><span id="fieldName2">xxChecking Unallocated Receipts</span></th>
+                        <th class="DataGrid_Header_Cell"><span id="fieldGroupBy">xxBusiness Partner</span><span>:&nbsp;</span>
+                        <span id="fieldGroupByName">xxGroupbyname</span></th>
                       </tr>
                       <tr class="DataGrid_Body_Row">
                         <th width="10%" class="DataGrid_Header_Cell">DATE</th>
--- a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.java	Mon May 07 10:56:04 2012 +0200
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.java	Fri May 11 18:47:35 2012 +0200
@@ -86,12 +86,10 @@
       String strcProjectId = vars.getInGlobalVariable("inpcProjectId_IN",
           "ReportGeneralLedger|cProjectId", "", IsIDFilter.instance);
       String strGroupBy = vars.getGlobalVariable("inpGroupBy", "ReportGeneralLedger|GroupBy", "");
-      String strHide = vars.getGlobalVariable("inpHideMatched", "ReportGeneralLedger|HideMatched",
-          "");
 
       printPageDataSheet(response, vars, strDateFrom, strDateTo, strPageNo, strAmtFrom, strAmtTo,
           strcelementvaluefrom, strcelementvalueto, strOrg, strcBpartnerId, strmProductId,
-          strcProjectId, strGroupBy, strHide, strcAcctSchemaId, strcelementvaluefromdes,
+          strcProjectId, strGroupBy, strcAcctSchemaId, strcelementvaluefromdes,
           strcelementvaluetodes);
     } else if (vars.commandIn("FIND")) {
       String strcAcctSchemaId = vars.getRequestGlobalVariable("inpcAcctSchemaId",
@@ -126,11 +124,6 @@
           "ReportGeneralLedger|cProjectId", IsIDFilter.instance);
       String strGroupBy = vars
           .getRequestGlobalVariable("inpGroupBy", "ReportGeneralLedger|GroupBy");
-      String strHide = vars.getStringParameter("inpHideMatched");
-      if (strHide.equals(""))
-        vars.removeSessionValue("ReportGeneralLedger|HideMatched");
-      else
-        strHide = vars.getGlobalVariable("inpHideMatched", "ReportGeneralLedger|HideMatched");
       if (log4j.isDebugEnabled())
         log4j.debug("##################### DoPost - Find - strcBpartnerId= " + strcBpartnerId);
       if (log4j.isDebugEnabled())
@@ -142,7 +135,7 @@
       vars.setSessionValue("ReportGeneralLedger.initRecordNumber", "0");
       printPageDataSheet(response, vars, strDateFrom, strDateTo, strPageNo, strAmtFrom, strAmtTo,
           strcelementvaluefrom, strcelementvalueto, strOrg, strcBpartnerId, strmProductId,
-          strcProjectId, strGroupBy, strHide, strcAcctSchemaId, strcelementvaluefromdes,
+          strcProjectId, strGroupBy, strcAcctSchemaId, strcelementvaluefromdes,
           strcelementvaluetodes);
     } else if (vars.commandIn("PREVIOUS_RELATION")) {
       String strInitRecord = vars.getSessionValue("ReportGeneralLedger.initRecordNumber");
@@ -191,16 +184,15 @@
           "ReportGeneralLedger|cProjectId", "", IsIDFilter.instance);
       String strGroupBy = vars
           .getRequestGlobalVariable("inpGroupBy", "ReportGeneralLedger|GroupBy");
-      String strHide = vars.getStringParameter("inpHideMatched");
       String strPageNo = vars.getGlobalVariable("inpPageNo", "ReportGeneralLedger|PageNo", "1");
       if (vars.commandIn("PDF"))
         printPageDataPDF(request, response, vars, strDateFrom, strDateTo, strAmtFrom, strAmtTo,
             strcelementvaluefrom, strcelementvalueto, strOrg, strcBpartnerId, strmProductId,
-            strcProjectId, strGroupBy, strHide, strcAcctSchemaId, strPageNo);
+            strcProjectId, strGroupBy, strcAcctSchemaId, strPageNo);
       else
         printPageDataXLS(request, response, vars, strDateFrom, strDateTo, strAmtFrom, strAmtTo,
             strcelementvaluefrom, strcelementvalueto, strOrg, strcBpartnerId, strmProductId,
-            strcProjectId, strGroupBy, strHide, strcAcctSchemaId);
+            strcProjectId, strGroupBy, strcAcctSchemaId);
     } else
       pageError(response);
   }
@@ -208,9 +200,9 @@
   private void printPageDataSheet(HttpServletResponse response, VariablesSecureApp vars,
       String strDateFrom, String strDateTo, String strPageNo, String strAmtFrom, String strAmtTo,
       String strcelementvaluefrom, String strcelementvalueto, String strOrg, String strcBpartnerId,
-      String strmProductId, String strcProjectId, String strGroupBy, String strHide,
-      String strcAcctSchemaId, String strcelementvaluefromdes, String strcelementvaluetodes)
-      throws IOException, ServletException {
+      String strmProductId, String strcProjectId, String strGroupBy, String strcAcctSchemaId,
+      String strcelementvaluefromdes, String strcelementvaluetodes) throws IOException,
+      ServletException {
     String strRecordRange = Utility.getContext(this, vars, "#RecordRange", "ReportGeneralLedger");
     int intRecordRange = (strRecordRange.equals("") ? 0 : Integer.parseInt(strRecordRange));
     String strInitRecord = vars.getSessionValue("ReportGeneralLedger.initRecordNumber");
@@ -241,16 +233,7 @@
     XmlDocument xmlDocument = null;
     ReportGeneralLedgerData[] data = null;
     String strTreeOrg = TreeData.getTreeOrg(this, vars.getClient());
-    // String strTreeAccount = ReportTrialBalanceData.treeAccount(this, vars.getClient());
     String strOrgFamily = getFamily(strTreeOrg, strOrg);
-    String strFinancialOrgFamily = getFinancialFamily(strTreeOrg, strOrg, vars.getClient());
-    String strExistsInitialDate = ReportGeneralLedgerData.yearInitialDate(this,
-        vars.getSessionValue("#AD_SqlDateFormat"), strDateFrom,
-        Utility.getContext(this, vars, "#User_Client", "ReportGeneralLedger"),
-        strFinancialOrgFamily);
-    String strYearInitialDate = strDateFrom;
-    if (strExistsInitialDate.equals(""))
-      strYearInitialDate = strExistsInitialDate;
     String toDatePlusOne = DateTimeData.nDaysAfter(this, strDateTo, "1");
 
     String strGroupByText = (strGroupBy.equals("BPartner") ? Utility.messageBD(this, "BusPartner",
@@ -279,8 +262,6 @@
         discard[0] = "sectionAmount";
       BigDecimal previousDebit = BigDecimal.ZERO;
       BigDecimal previousCredit = BigDecimal.ZERO;
-      if (strHide.equals(""))
-        strHide = "N";
       String strAllaccounts = "Y";
       if (strcelementvaluefrom != null && !strcelementvaluefrom.equals("")) {
         if (strcelementvalueto.equals("")) {
@@ -300,43 +281,39 @@
         strcelementvaluetodes = "";
         vars.setSessionValue("inpElementValueIdTo_DES", strcelementvaluetodes);
       }
-      data = ReportGeneralLedgerData.select(this, rowNum, strGroupByText, strGroupBy,
-          vars.getLanguage(), strDateFrom, toDatePlusOne, strAllaccounts, strcelementvaluefrom,
-          strcelementvalueto,
+      Long initMainSelect = System.currentTimeMillis();
+      data = ReportGeneralLedgerData.select2(this, "0", strGroupByText, strGroupBy, strAllaccounts,
+          strcelementvaluefrom, strcelementvalueto,
           Utility.getContext(this, vars, "#AccessibleOrgTree", "ReportGeneralLedger"),
-          Utility.getContext(this, vars, "#User_Client", "ReportGeneralLedger"), strHide,
-          strcAcctSchemaId, strDateFrom, toDatePlusOne, strOrgFamily, strcBpartnerId,
-          strmProductId, strcProjectId, strAmtFrom, strAmtTo, null, null, null, pgLimit, oraLimit1,
-          oraLimit2);
+          Utility.getContext(this, vars, "#User_Client", "ReportGeneralLedger"), strcAcctSchemaId,
+          strDateFrom, toDatePlusOne, strOrgFamily, strcBpartnerId, strmProductId, strcProjectId,
+          strAmtFrom, strAmtTo, null, null, pgLimit, oraLimit1, oraLimit2, null);
+      log4j.debug("Select2. Time in mils: " + (System.currentTimeMillis() - initMainSelect));
       if (log4j.isDebugEnabled())
         log4j.debug("RecordNo: " + initRecordNumber);
-      // In case this is not the first screen to show, initial balance may need to include amounts
-      // of previous screen, so same sql -but from the beginning of the fiscal year- is executed
 
       ReportGeneralLedgerData[] dataTotal = null;
-      if (data != null && data.length >= 1) {
-        dataTotal = ReportGeneralLedgerData.select(this, rowNum, strGroupByText, strGroupBy,
-            vars.getLanguage(), strDateFrom, toDatePlusOne, strAllaccounts, strcelementvaluefrom,
-            strcelementvalueto,
-            Utility.getContext(this, vars, "#AccessibleOrgTree", "ReportGeneralLedger"),
-            Utility.getContext(this, vars, "#User_Client", "ReportGeneralLedger"), strHide,
-            strcAcctSchemaId, strYearInitialDate,
-            DateTimeData.nDaysAfter(this, data[0].dateacct, "1"), strOrgFamily, strcBpartnerId,
-            strmProductId, strcProjectId, strAmtFrom, strAmtTo, data[0].id, data[0].dateacctnumber
-                + data[0].factAcctGroupId + data[0].description + data[0].isdebit,
-            data[0].groupbyid, null, null, null);
-      }
-      // Now dataTotal is covered adding debit and credit amounts
-      for (int i = 0; dataTotal != null && i < dataTotal.length; i++) {
-        previousDebit = previousDebit.add(new BigDecimal(dataTotal[i].amtacctdr));
-        previousCredit = previousCredit.add(new BigDecimal(dataTotal[i].amtacctcr));
-      }
       String strOld = "";
       ReportGeneralLedgerData[] subreportElement = new ReportGeneralLedgerData[1];
       for (int i = 0; data != null && i < data.length; i++) {
         if (!strOld.equals(data[i].groupbyid + data[i].id)) {
           subreportElement = new ReportGeneralLedgerData[1];
           if (i == 0 && initRecordNumber > 0) {
+            Long init = System.currentTimeMillis();
+            dataTotal = ReportGeneralLedgerData.select2Total(this, rowNum, strGroupByText,
+                strGroupBy, strAllaccounts, strcelementvaluefrom, strcelementvalueto,
+                Utility.getContext(this, vars, "#AccessibleOrgTree", "ReportGeneralLedger"),
+                Utility.getContext(this, vars, "#User_Client", "ReportGeneralLedger"),
+                strcAcctSchemaId, "", DateTimeData.nDaysAfter(this, data[0].dateacct, "1"),
+                strOrgFamily, strcBpartnerId, strmProductId, strcProjectId, strAmtFrom, strAmtTo,
+                data[0].id, data[0].groupbyid, null, null, null, data[0].dateacctnumber
+                    + data[0].factaccttype + data[0].factAcctGroupId + data[0].factAcctId);
+            log4j.debug("Select2Total. Time in mils: " + (System.currentTimeMillis() - init));
+            // Now dataTotal is covered adding debit and credit amounts
+            for (int j = 0; dataTotal != null && j < dataTotal.length; j++) {
+              previousDebit = previousDebit.add(new BigDecimal(dataTotal[j].amtacctdr));
+              previousCredit = previousCredit.add(new BigDecimal(dataTotal[j].amtacctcr));
+            }
             subreportElement = new ReportGeneralLedgerData[1];
             subreportElement[0] = new ReportGeneralLedgerData();
             subreportElement[0].totalacctdr = previousDebit.toPlainString();
@@ -346,19 +323,22 @@
             if ("".equals(data[i].groupbyid)) {
               // The argument " " is used to simulate one value and put the optional parameter-->
               // AND FACT_ACCT.C_PROJECT_ID IS NULL for example
-              subreportElement = ReportGeneralLedgerData.selectTotal(this, strDateFrom,
-                  toDatePlusOne, null, (strGroupBy.equals("BPartner") ? " " : null), null,
-                  (strGroupBy.equals("Product") ? " " : null), null,
-                  (strGroupBy.equals("Project") ? " " : null), strcAcctSchemaId, data[i].id,
-                  strYearInitialDate, strDateFrom, strOrgFamily, strHide);
+              Long init = System.currentTimeMillis();
+              subreportElement = ReportGeneralLedgerData.selectTotal2(this, strcBpartnerId,
+                  (strGroupBy.equals("BPartner") ? " " : null), strmProductId,
+                  (strGroupBy.equals("Product") ? " " : null), strcProjectId,
+                  (strGroupBy.equals("Project") ? " " : null), strcAcctSchemaId, data[i].id, "",
+                  strDateFrom, strOrgFamily);
+              log4j.debug("SelectTotalNew. Time in mils: " + (System.currentTimeMillis() - init));
             } else {
-              subreportElement = ReportGeneralLedgerData.selectTotal(this, strDateFrom,
-                  toDatePlusOne, (strGroupBy.equals("BPartner") ? "('" + data[i].groupbyid + "')"
-                      : strcBpartnerId), null, (strGroupBy.equals("Product") ? "('"
+              Long init = System.currentTimeMillis();
+              subreportElement = ReportGeneralLedgerData
+                  .selectTotal2(this, (strGroupBy.equals("BPartner") ? "('" + data[i].groupbyid
+                      + "')" : strcBpartnerId), null, (strGroupBy.equals("Product") ? "('"
                       + data[i].groupbyid + "')" : strmProductId), null, (strGroupBy
                       .equals("Project") ? "('" + data[i].groupbyid + "')" : strcProjectId), null,
-                  strcAcctSchemaId, data[i].id, strYearInitialDate, strDateFrom, strOrgFamily,
-                  strHide);
+                      strcAcctSchemaId, data[i].id, "", strDateFrom, strOrgFamily);
+              log4j.debug("SelectTotalNew. Time in mils: " + (System.currentTimeMillis() - init));
             }
           }
           data[i].totalacctdr = subreportElement[0].totalacctdr;
@@ -371,8 +351,8 @@
         data[i].previoustotal = subreportElement[0].total;
         strOld = data[i].groupbyid + data[i].id;
       }
+      // TODO: What is strTotal?? is this the proper variable name?
       String strTotal = "";
-      int g = 0;
       subreportElement = new ReportGeneralLedgerData[1];
       for (int i = 0; data != null && i < data.length; i++) {
         if (!strTotal.equals(data[i].groupbyid + data[i].id)) {
@@ -380,21 +360,23 @@
           if ("".equals(data[i].groupbyid)) {
             // The argument " " is used to simulate one value and put the optional parameter--> AND
             // FACT_ACCT.C_PROJECT_ID IS NULL for example
-            subreportElement = ReportGeneralLedgerData.selectTotal(this, strDateFrom,
-                toDatePlusOne, null, (strGroupBy.equals("BPartner") ? " " : null), null,
-                (strGroupBy.equals("Product") ? " " : null), null,
-                (strGroupBy.equals("Project") ? " " : null), strcAcctSchemaId, data[i].id,
-                strYearInitialDate, toDatePlusOne, strOrgFamily, strHide);
+            Long init = System.currentTimeMillis();
+            subreportElement = ReportGeneralLedgerData.selectTotal2(this, strcBpartnerId,
+                (strGroupBy.equals("BPartner") ? " " : null), strmProductId,
+                (strGroupBy.equals("Product") ? " " : null), strcProjectId,
+                (strGroupBy.equals("Project") ? " " : null), strcAcctSchemaId, data[i].id, "",
+                toDatePlusOne, strOrgFamily);
+            log4j.debug("SelectTotal2. Time in mils: " + (System.currentTimeMillis() - init));
           } else {
-            subreportElement = ReportGeneralLedgerData.selectTotal(this, strDateFrom,
-                toDatePlusOne, (strGroupBy.equals("BPartner") ? "('" + data[i].groupbyid + "')"
-                    : strcBpartnerId), null, (strGroupBy.equals("Product") ? "('"
-                    + data[i].groupbyid + "')" : strmProductId), null, (strGroupBy
-                    .equals("Project") ? "('" + data[i].groupbyid + "')" : strcProjectId), null,
-                strcAcctSchemaId, data[i].id, strYearInitialDate, toDatePlusOne, strOrgFamily,
-                strHide);
+            Long init = System.currentTimeMillis();
+            subreportElement = ReportGeneralLedgerData.selectTotal2(this, (strGroupBy
+                .equals("BPartner") ? "('" + data[i].groupbyid + "')" : strcBpartnerId), null,
+                (strGroupBy.equals("Product") ? "('" + data[i].groupbyid + "')" : strmProductId),
+                null, (strGroupBy.equals("Project") ? "('" + data[i].groupbyid + "')"
+                    : strcProjectId), null, strcAcctSchemaId, data[i].id, "", toDatePlusOne,
+                strOrgFamily);
+            log4j.debug("SelectTotal2. Time in mils: " + (System.currentTimeMillis() - init));
           }
-          g++;
         }
         data[i].finaldebit = subreportElement[0].totalacctdr;
         data[i].finalcredit = subreportElement[0].totalacctcr;
@@ -467,7 +449,6 @@
     xmlDocument.setParameter("paramElementvalueIdFrom", strcelementvaluefrom);
     xmlDocument.setParameter("inpElementValueIdTo_DES", strcelementvaluetodes);
     xmlDocument.setParameter("inpElementValueIdFrom_DES", strcelementvaluefromdes);
-    xmlDocument.setParameter("paramHide0", !strHide.equals("Y") ? "0" : "1");
     xmlDocument.setParameter("groupbyselected", strGroupBy);
     xmlDocument.setData(
         "reportCBPartnerId_IN",
@@ -498,13 +479,6 @@
       log4j.debug("data.length: " + data.length);
 
     if (data != null && data.length > 0) {
-      if (strExistsInitialDate.equals("") && vars.commandIn("FIND")) {
-        xmlDocument.setParameter("messageType", "WARNING");
-        xmlDocument.setParameter("messageTitle",
-            Utility.messageBD(this, "ProcessStatus-W", vars.getLanguage()));
-        xmlDocument.setParameter("messageMessage",
-            Utility.messageBD(this, "InitialDateNotFoundCalendar", vars.getLanguage()));
-      }
       if (strGroupBy.equals(""))
         xmlDocument.setData("structure1", data);
       else
@@ -520,14 +494,6 @@
       }
     }
 
-    /*
-     * if (strcBpartnerId.equals("") && strAll.equals("")) xmlDocument.setDataArray("reportTotals",
-     * "structure", subreport); else xmlDocument.setDataArray("reportTotals2", "structure",
-     * subreport); if (strcBpartnerId.equals("") && strAll.equals(""))
-     * xmlDocument.setDataArray("reportAll", "structure", subreport2); else
-     * xmlDocument.setDataArray("reportAll2", "structure", subreport2);
-     */
-
     out.println(xmlDocument.print());
     out.close();
   }
@@ -536,8 +502,7 @@
       VariablesSecureApp vars, String strDateFrom, String strDateTo, String strAmtFrom,
       String strAmtTo, String strcelementvaluefrom, String strcelementvalueto, String strOrg,
       String strcBpartnerId, String strmProductId, String strcProjectId, String strGroupBy,
-      String strHide, String strcAcctSchemaId, String strPageNo) throws IOException,
-      ServletException {
+      String strcAcctSchemaId, String strPageNo) throws IOException, ServletException {
     if (log4j.isDebugEnabled())
       log4j.debug("Output: PDF");
     response.setContentType("text/html; charset=UTF-8");
@@ -546,13 +511,6 @@
     String strTreeOrg = TreeData.getTreeOrg(this, vars.getClient());
     String strOrgFamily = "";
     strOrgFamily = getFamily(strTreeOrg, strOrg);
-    String strFinancialOrgFamily = getFinancialFamily(strTreeOrg, strOrg, vars.getClient());
-    String strYearInitialDate = ReportGeneralLedgerData.yearInitialDate(this,
-        vars.getSessionValue("#AD_SqlDateFormat"), strDateFrom,
-        Utility.getContext(this, vars, "#User_Client", "ReportGeneralLedger"),
-        strFinancialOrgFamily);
-    if (strYearInitialDate.equals(""))
-      strYearInitialDate = strDateFrom;
     String toDatePlusOne = DateTimeData.nDaysAfter(this, strDateTo, "1");
 
     String strGroupByText = (strGroupBy.equals("BPartner") ? Utility.messageBD(this, "BusPartner",
@@ -563,20 +521,18 @@
 
     if (!strDateFrom.equals("") && !strDateTo.equals("")) {
       strOrgFamily = getFamily(strTreeOrg, strOrg);
-      if (!strHide.equals("Y"))
-        strHide = "N";
       if (strcelementvaluefrom != null && !strcelementvaluefrom.equals("")) {
         if (strcelementvalueto.equals(""))
           strcelementvalueto = strcelementvaluefrom;
         strAllaccounts = "N";
       }
-      data = ReportGeneralLedgerData.select(this, "0", strGroupByText, strGroupBy,
-          vars.getLanguage(), strDateFrom, toDatePlusOne, strAllaccounts, strcelementvaluefrom,
-          strcelementvalueto,
+
+      data = ReportGeneralLedgerData.select2(this, "0", strGroupByText, strGroupBy, strAllaccounts,
+          strcelementvaluefrom, strcelementvalueto,
           Utility.getContext(this, vars, "#AccessibleOrgTree", "ReportGeneralLedger"),
-          Utility.getContext(this, vars, "#User_Client", "ReportGeneralLedger"), strHide,
-          strcAcctSchemaId, strDateFrom, toDatePlusOne, strOrgFamily, strcBpartnerId,
-          strmProductId, strcProjectId, strAmtFrom, strAmtTo, null, null, null, null, null, null);
+          Utility.getContext(this, vars, "#User_Client", "ReportGeneralLedger"), strcAcctSchemaId,
+          strDateFrom, toDatePlusOne, strOrgFamily, strcBpartnerId, strmProductId, strcProjectId,
+          strAmtFrom, strAmtTo, null, null, null, null, null, null);
     }
     if (data == null || data.length == 0) {
       advisePopUp(request, response, "WARNING",
@@ -594,19 +550,18 @@
           if ("".equals(data[i].groupbyid)) {
             // The argument " " is used to simulate one value and put the optional parameter--> AND
             // FACT_ACCT.C_PROJECT_ID IS NULL for example
-            subreport = ReportGeneralLedgerData.selectTotal(this, strDateFrom, DateTimeData
-                .nDaysAfter(this, strDateTo, "1"), null, (strGroupBy.equals("BPartner") ? " "
-                : null), null, (strGroupBy.equals("Product") ? " " : null), null, (strGroupBy
-                .equals("Project") ? " " : null), strcAcctSchemaId, data[i].id, strYearInitialDate,
-                strDateFrom, strOrgFamily, strHide);
+            subreport = ReportGeneralLedgerData.selectTotal2(this, strcBpartnerId,
+                (strGroupBy.equals("BPartner") ? " " : null), strmProductId,
+                (strGroupBy.equals("Product") ? " " : null), strcProjectId,
+                (strGroupBy.equals("Project") ? " " : null), strcAcctSchemaId, data[i].id, "",
+                strDateFrom, strOrgFamily);
           } else {
-            subreport = ReportGeneralLedgerData.selectTotal(this, strDateFrom, DateTimeData
-                .nDaysAfter(this, strDateTo, "1"), (strGroupBy.equals("BPartner") ? "('"
-                + data[i].groupbyid + "')" : strcBpartnerId), null,
-                (strGroupBy.equals("Product") ? "('" + data[i].groupbyid + "')" : strmProductId),
-                null, (strGroupBy.equals("Project") ? "('" + data[i].groupbyid + "')"
-                    : strcProjectId), null, strcAcctSchemaId, data[i].id, strYearInitialDate,
-                strDateFrom, strOrgFamily, strHide);
+            subreport = ReportGeneralLedgerData.selectTotal2(this,
+                (strGroupBy.equals("BPartner") ? "('" + data[i].groupbyid + "')" : strcBpartnerId),
+                null, (strGroupBy.equals("Product") ? "('" + data[i].groupbyid + "')"
+                    : strmProductId), null, (strGroupBy.equals("Project") ? "('"
+                    + data[i].groupbyid + "')" : strcProjectId), null, strcAcctSchemaId,
+                data[i].id, "", strDateFrom, strOrgFamily);
           }
           totalDebit = BigDecimal.ZERO;
           totalCredit = BigDecimal.ZERO;
@@ -653,7 +608,7 @@
       VariablesSecureApp vars, String strDateFrom, String strDateTo, String strAmtFrom,
       String strAmtTo, String strcelementvaluefrom, String strcelementvalueto, String strOrg,
       String strcBpartnerId, String strmProductId, String strcProjectId, String strGroupBy,
-      String strHide, String strcAcctSchemaId) throws IOException, ServletException {
+      String strcAcctSchemaId) throws IOException, ServletException {
     if (log4j.isDebugEnabled())
       log4j.debug("Output: XLS");
     response.setContentType("text/html; charset=UTF-8");
@@ -661,31 +616,22 @@
     String strTreeOrg = TreeData.getTreeOrg(this, vars.getClient());
     String strOrgFamily = "";
     strOrgFamily = getFamily(strTreeOrg, strOrg);
-    String strFinancialOrgFamily = getFinancialFamily(strTreeOrg, strOrg, vars.getClient());
-    String strYearInitialDate = ReportGeneralLedgerData.yearInitialDate(this,
-        vars.getSessionValue("#AD_SqlDateFormat"), strDateFrom,
-        Utility.getContext(this, vars, "#User_Client", "ReportGeneralLedger"),
-        strFinancialOrgFamily);
-    if (strYearInitialDate.equals(""))
-      strYearInitialDate = strDateFrom;
     String toDatePlusOne = DateTimeData.nDaysAfter(this, strDateTo, "1");
 
     String strAllaccounts = "Y";
 
     if (!strDateFrom.equals("") && !strDateTo.equals("")) {
-      if (!strHide.equals("Y"))
-        strHide = "N";
       if (strcelementvaluefrom != null && !strcelementvaluefrom.equals("")) {
         if (strcelementvalueto.equals(""))
           strcelementvalueto = strcelementvaluefrom;
         strAllaccounts = "N";
       }
-      data = ReportGeneralLedgerData.selectXLS(this, vars.getLanguage(), strDateFrom,
-          toDatePlusOne, strAllaccounts, strcelementvaluefrom, strcelementvalueto,
+      data = ReportGeneralLedgerData.selectXLS2(this, strAllaccounts, strcelementvaluefrom,
+          strcelementvalueto,
           Utility.getContext(this, vars, "#AccessibleOrgTree", "ReportGeneralLedger"),
-          Utility.getContext(this, vars, "#User_Client", "ReportGeneralLedger"), strHide,
-          strcAcctSchemaId, strDateFrom, toDatePlusOne, strOrgFamily, strcBpartnerId,
-          strmProductId, strcProjectId, strAmtFrom, strAmtTo);
+          Utility.getContext(this, vars, "#User_Client", "ReportGeneralLedger"), strcAcctSchemaId,
+          strDateFrom, toDatePlusOne, strOrgFamily, strcBpartnerId, strmProductId, strcProjectId,
+          strAmtFrom, strAmtTo);
     }
     if (data == null || data.length == 0) {
       advisePopUp(request, response, "WARNING",
@@ -717,26 +663,6 @@
     return Tree.getMembers(this, strTree, strChild);
   }
 
-  private String getFinancialFamily(String strTree, String strChild, String strClientId)
-      throws IOException, ServletException {
-    log4j.debug("Tree.getFinancialMembers");
-    ReportGeneralLedgerData[] data = ReportGeneralLedgerData.getFinancialOrgs(this, strTree,
-        strChild, strClientId);
-
-    boolean bolFirstLine = true;
-    String strText = "";
-    for (int i = 0; i < data.length; i++) {
-      data[i].id = "'" + data[i].id + "'";
-      if (bolFirstLine) {
-        bolFirstLine = false;
-        strText = data[i].id;
-      } else {
-        strText = data[i].id + "," + strText;
-      }
-    }
-    return strText;
-  }
-
   @Override
   public String getServletInfo() {
     return "Servlet ReportGeneralLedger. This Servlet was made by Pablo Sarobe";
--- a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.xml	Mon May 07 10:56:04 2012 +0200
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.xml	Fri May 11 18:47:35 2012 +0200
@@ -12,7 +12,7 @@
 * under the License. 
 * The Original Code is Openbravo ERP. 
 * The Initial Developer of the Original Code is Openbravo SLU 
-* All portions are Copyright (C) 2001-2009 Openbravo SLU 
+* All portions are Copyright (C) 2001-2012 Openbravo SLU 
 * All Rights Reserved. 
 * Contributor(s):  ______________________________________.
 ************************************************************************
@@ -39,8 +39,6 @@
   <PARAMETER id="inpElementValueIdFrom_DES" name="inpElementValueIdFrom_DES" attribute="value"/>
   <PARAMETER id="paramPageNo" name="PageNo" attribute="value"/>
 
-  <PARAMETER id="parameterHide" name="all" default="1"/>
-  <PARAMETER id="paramHideMatched" name="paramHide0" boolean="checked" withId="parameterHide" default="0"/>
   <PARAMETER id="paramCSS" name="theme" attribute="href" replace="Default"/>
   <PARAMETER id="tdleftTabs" name="leftTabs" default=""/>
   <PARAMETER id="paramParentTabContainer" name="parentTabContainer" default=" "/>
--- a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger_data.xsql	Mon May 07 10:56:04 2012 +0200
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger_data.xsql	Fri May 11 18:47:35 2012 +0200
@@ -12,7 +12,7 @@
  * under the License.
  * The Original Code is Openbravo ERP.
  * The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2001-2010 Openbravo SLU
+ * All portions are Copyright (C) 2001-2012 Openbravo SLU
  * All Rights Reserved.
  * Contributor(s):  ______________________________________.
  ************************************************************************
@@ -35,7 +35,8 @@
               0 AS PREVIOUSDEBIT, 0 AS PREVIOUSCREDIT, 0 AS PREVIOUSTOTAL,
               0 AS FINALDEBIT, 0 AS FINALCREDIT, 0 AS FINALTOTAL,
               TO_CHAR(DATEACCT,'J') AS DATEACCTNUMBER,
-              ? AS groupby, '' as bpid, '' as bpname, '' as pdid, '' as pdname, '' as pjid, '' as pjname, factaccttype
+              ? AS groupby, '' as bpid, '' as bpname, '' as pdid, '' as pdname, '' as pjid, '' as pjname, 
+              factaccttype, '' as fact_acct_id
           FROM
             (SELECT FACT_ACCT.ACCTVALUE AS VALUE, FACT_ACCT.ACCTDESCRIPTION AS NAME,
                 DATEACCT, AMTACCTDR, AMTACCTCR,
@@ -122,6 +123,177 @@
     <Parameter name="oraLimit2" type="argument" optional="true" after="WHERE 1=1"><![CDATA[ AND RN1 BETWEEN ]]></Parameter>
   </SqlMethod>
 
+  <SqlMethod name="select2" type="preparedStatement" return="multiple">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql>
+      <![CDATA[
+        SELECT *
+        FROM ( SELECT '0' AS RN1, C.* FROM (
+          SELECT VALUE, NAME, DATEACCT,
+              COALESCE(SUM(AMTACCTDR),0) AS AMTACCTDR, COALESCE(SUM(AMTACCTCR),0) AS AMTACCTCR, COALESCE(SUM(AMTACCTDR-AMTACCTCR),0) AS TOTAL,
+              MAX(FACT_ACCT_ID) AS FACT_ACCT_ID,
+              FACT_ACCT_GROUP_ID, ID, groupbyid, groupbyname,
+              DESCRIPTION, ISDEBIT,
+              0 AS TOTALACCTDR, 0 AS TOTALACCTCR, 0 AS TOTALACCTSUB,
+              0 AS PREVIOUSDEBIT, 0 AS PREVIOUSCREDIT, 0 AS PREVIOUSTOTAL,
+              0 AS FINALDEBIT, 0 AS FINALCREDIT, 0 AS FINALTOTAL,
+              TO_CHAR(DATEACCT,'J') AS DATEACCTNUMBER,
+              ? AS groupby, '' as bpid, '' as bpname, '' as pdid, '' as pdname, '' as pjid, '' as pjname, factaccttype
+          FROM
+            (SELECT FACT_ACCT.ACCTVALUE AS VALUE, FACT_ACCT.ACCTDESCRIPTION AS NAME,
+                DATEACCT, AMTACCTDR, AMTACCTCR, FACT_ACCT_ID,
+                FACT_ACCT_GROUP_ID, FACT_ACCT.ACCOUNT_ID AS ID, FACT_ACCT.DESCRIPTION,
+                CASE ?
+                  WHEN 'BPartner' THEN c_bpartner.c_bpartner_id
+                  WHEN 'Product' THEN m_product.m_product_id
+                  WHEN 'Project' THEN c_project.c_project_id
+                  ELSE '' END AS groupbyid,
+                CASE ?
+                  WHEN 'BPartner' THEN (select name from c_bpartner b where b.c_bpartner_id = c_bpartner.c_bpartner_id)
+                  WHEN 'Product' THEN (select value || ' - ' || name from m_product p where p.m_product_id = m_product.m_product_id)
+                  WHEN 'Project' THEN (select name from c_project pj where pj.c_project_id = c_project.c_project_id)
+                  ELSE '' END AS groupbyname,
+                CASE WHEN AMTACCTDR <> 0 THEN 'Y' ELSE 'N' END AS ISDEBIT, 
+        CASE WHEN FACT_ACCT.FACTACCTTYPE = 'O' THEN 1 ELSE (CASE WHEN FACT_ACCT.FACTACCTTYPE = 'N' THEN 2 ELSE (CASE WHEN FACT_ACCT.FACTACCTTYPE = 'R' THEN 3 ELSE (CASE WHEN FACT_ACCT.FACTACCTTYPE = 'C' THEN 4 END) END) END) END AS FACTACCTTYPE
+            FROM FACT_ACCT
+              LEFT JOIN C_BPARTNER ON FACT_ACCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
+              LEFT JOIN M_PRODUCT ON FACT_ACCT.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
+              LEFT JOIN C_PROJECT ON FACT_ACCT.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID
+            WHERE (?='Y' OR FACT_ACCT.ACCOUNT_ID IN (
+                      SELECT c_elementvalue_id as name
+                      FROM C_ELEMENTVALUE
+                      WHERE value >= (  select value from c_elementvalue where c_elementvalue_id = ?)
+                        and value <= (  select value from c_elementvalue where c_elementvalue_id = ?)
+                        and c_elementvalue.ELEMENTLEVEL = 'S'
+                    ))
+              AND FACT_ACCT.AD_ORG_ID IN ('1')
+              AND FACT_ACCT.AD_CLIENT_ID IN ('1')
+              AND 1=1
+              AND  FACT_ACCT.AD_ORG_ID IN ('2')
+              AND 2=2
+              AND 3=3) D
+            WHERE 6=6
+            GROUP BY groupbyname, groupbyid, VALUE, NAME, ID,
+            DATEACCT, FACTACCTTYPE, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT
+            HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0
+            ORDER  BY groupbyname, groupbyid, VALUE, NAME, ID,
+            DATEACCT, FACTACCTTYPE, FACT_ACCT_GROUP_ID,  FACT_ACCT_ID, DESCRIPTION, ISDEBIT
+          ) C
+        ) B WHERE 1=1
+      ]]></Sql>
+    <Field name="rownum" value="count"/>
+    <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
+    <Parameter name="groupbytext"/>
+    <Parameter name="groupby"/>
+    <Parameter name="groupby"/>
+    <Parameter name="allaccounts"/>
+    <Parameter name="accountFrom"/>
+    <Parameter name="accountTo"/>
+    <Parameter name="adUserOrg" type="replace" optional="true" after="AND FACT_ACCT.AD_ORG_ID IN (" text="'1'"/>
+    <Parameter name="adUserClient" type="replace" optional="true" after="AND FACT_ACCT.AD_CLIENT_ID IN (" text="'1'"/>
+    <Parameter name="acctschema" optional="true" after="AND 1=1"><![CDATA[AND FACT_ACCT.C_ACCTSCHEMA_ID = ? ]]></Parameter>
+    <Parameter name="parDateFrom2" optional="true" after="AND 1=1"><![CDATA[AND FACT_ACCT.DATEACCT >= TO_DATE(?) ]]></Parameter>
+    <Parameter name="parDateTo2" optional="true" after="AND 1=1"><![CDATA[AND FACT_ACCT.DATEACCT < TO_DATE(?) ]]></Parameter>
+    <Parameter name="org" type="replace" optional="true" after="AND  FACT_ACCT.AD_ORG_ID IN (" text="'2'"/>
+    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND FACT_ACCT.C_BPARTNER_ID IN]]></Parameter>
+    <Parameter name="mProductId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND FACT_ACCT.M_PRODUCT_ID IN]]></Parameter>
+    <Parameter name="cProjectId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND FACT_ACCT.C_PROJECT_ID IN]]></Parameter>
+    <Parameter name="paramAmtFrom" optional="true" after="3=3"><![CDATA[ AND (FACT_ACCT.AMTACCTDR + FACT_ACCT.AMTACCTCR)>=TO_NUMBER(?) ]]></Parameter>
+    <Parameter name="paramAmtTo" optional="true" after="3=3"><![CDATA[ AND (FACT_ACCT.AMTACCTDR + FACT_ACCT.AMTACCTCR)<=TO_NUMBER(?) ]]></Parameter>
+    <Parameter name="accountId" optional="true" after="WHERE 6=6"><![CDATA[AND ID = ? ]]></Parameter>
+    <Parameter name="cBPartner" optional="true" after="WHERE 6=6"><![CDATA[AND groupbyid = ? ]]></Parameter>
+    <Parameter name="pgLimit" type="argument" optional="true" after=",  FACT_ACCT_ID, DESCRIPTION, ISDEBIT"><![CDATA[ LIMIT ]]></Parameter>
+    <Parameter name="oraLimit1" type="argument" optional="true" after=") C"><![CDATA[ WHERE ROWNUM <= ]]></Parameter>
+    <Parameter name="oraLimit2" type="argument" optional="true" after="WHERE 1=1"><![CDATA[ AND RN1 BETWEEN ]]></Parameter>
+    <Parameter name="factAcctId" optional="true" after="WHERE 1=1"><![CDATA[ and to_char(DATEACCT,'J')||FACTACCTTYPE||FACT_ACCT_GROUP_ID||FACT_ACCT_ID < ? ]]></Parameter>
+  </SqlMethod>
+
+  <SqlMethod name="select2Total" type="preparedStatement" return="multiple">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql>
+      <![CDATA[
+        SELECT COALESCE(SUM(AMTACCTDR),0) AS AMTACCTDR, COALESCE(SUM(AMTACCTCR)) AS AMTACCTCR
+        FROM ( SELECT '0' AS RN1, C.* FROM (
+          SELECT VALUE, NAME, DATEACCT,
+          COALESCE(SUM(AMTACCTDR),0) AS AMTACCTDR, COALESCE(SUM(AMTACCTCR),0) AS AMTACCTCR,
+              COALESCE(SUM(AMTACCTDR-AMTACCTCR),0) AS TOTAL,
+              MAX(FACT_ACCT_ID) AS FACT_ACCT_ID,
+              FACT_ACCT_GROUP_ID, ID, groupbyid, groupbyname,
+              DESCRIPTION, ISDEBIT,
+              0 AS TOTALACCTDR, 0 AS TOTALACCTCR, 0 AS TOTALACCTSUB,
+              0 AS PREVIOUSDEBIT, 0 AS PREVIOUSCREDIT, 0 AS PREVIOUSTOTAL,
+              0 AS FINALDEBIT, 0 AS FINALCREDIT, 0 AS FINALTOTAL,
+              TO_CHAR(DATEACCT,'J') AS DATEACCTNUMBER,
+              ? AS groupby, '' as bpid, '' as bpname, '' as pdid, '' as pdname, '' as pjid, '' as pjname, factaccttype
+          FROM
+            (SELECT FACT_ACCT.ACCTVALUE AS VALUE, FACT_ACCT.ACCTDESCRIPTION AS NAME,
+                DATEACCT, AMTACCTDR, AMTACCTCR, FACT_ACCT_ID,
+                FACT_ACCT_GROUP_ID, FACT_ACCT.ACCOUNT_ID AS ID, FACT_ACCT.DESCRIPTION,
+                CASE ?
+                  WHEN 'BPartner' THEN c_bpartner.c_bpartner_id
+                  WHEN 'Product' THEN m_product.m_product_id
+                  WHEN 'Project' THEN c_project.c_project_id
+                  ELSE '' END AS groupbyid,
+                CASE ?
+                  WHEN 'BPartner' THEN (select name from c_bpartner b where b.c_bpartner_id = c_bpartner.c_bpartner_id)
+                  WHEN 'Product' THEN (select value || ' - ' || name from m_product p where p.m_product_id = m_product.m_product_id)
+                  WHEN 'Project' THEN (select name from c_project pj where pj.c_project_id = c_project.c_project_id)
+                  ELSE '' END AS groupbyname,
+                CASE WHEN AMTACCTDR <> 0 THEN 'Y' ELSE 'N' END AS ISDEBIT, 
+        CASE WHEN FACT_ACCT.FACTACCTTYPE = 'O' THEN 1 ELSE (CASE WHEN FACT_ACCT.FACTACCTTYPE = 'N' THEN 2 ELSE (CASE WHEN FACT_ACCT.FACTACCTTYPE = 'R' THEN 3 ELSE (CASE WHEN FACT_ACCT.FACTACCTTYPE = 'C' THEN 4 END) END) END) END AS FACTACCTTYPE
+            FROM FACT_ACCT
+              LEFT JOIN C_BPARTNER ON FACT_ACCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
+              LEFT JOIN M_PRODUCT ON FACT_ACCT.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
+              LEFT JOIN C_PROJECT ON FACT_ACCT.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID
+            WHERE (?='Y' OR FACT_ACCT.ACCOUNT_ID IN (
+                      SELECT c_elementvalue_id as name
+                      FROM C_ELEMENTVALUE
+                      WHERE value >= (  select value from c_elementvalue where c_elementvalue_id = ?)
+                        and value <= (  select value from c_elementvalue where c_elementvalue_id = ?)
+                        and c_elementvalue.ELEMENTLEVEL = 'S'
+                    ))
+              AND FACT_ACCT.AD_ORG_ID IN ('1')
+              AND FACT_ACCT.AD_CLIENT_ID IN ('1')
+              AND 1=1
+              AND  FACT_ACCT.AD_ORG_ID IN ('2')
+              AND 2=2
+              AND 3=3) D
+            WHERE 6=6
+            GROUP BY groupbyname, groupbyid, VALUE, NAME, ID,
+            DATEACCT, FACTACCTTYPE, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT
+            HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0
+            ORDER  BY groupbyname, groupbyid, VALUE, NAME, ID,
+            DATEACCT, FACTACCTTYPE, FACT_ACCT_GROUP_ID,  FACT_ACCT_ID, DESCRIPTION, ISDEBIT
+          ) C
+        ) B WHERE 1=1
+      ]]></Sql>
+    <Field name="rownum" value="count"/>
+    <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
+    <Parameter name="groupbytext"/>
+    <Parameter name="groupby"/>
+    <Parameter name="groupby"/>
+    <Parameter name="allaccounts"/>
+    <Parameter name="accountFrom"/>
+    <Parameter name="accountTo"/>
+    <Parameter name="adUserOrg" type="replace" optional="true" after="AND FACT_ACCT.AD_ORG_ID IN (" text="'1'"/>
+    <Parameter name="adUserClient" type="replace" optional="true" after="AND FACT_ACCT.AD_CLIENT_ID IN (" text="'1'"/>
+    <Parameter name="acctschema" optional="true" after="AND 1=1"><![CDATA[AND FACT_ACCT.C_ACCTSCHEMA_ID = ? ]]></Parameter>
+    <Parameter name="parDateFrom2" optional="true" after="AND 1=1"><![CDATA[AND FACT_ACCT.DATEACCT >= TO_DATE(?) ]]></Parameter>
+    <Parameter name="parDateTo2" optional="true" after="AND 1=1"><![CDATA[AND FACT_ACCT.DATEACCT < TO_DATE(?) ]]></Parameter>
+    <Parameter name="org" type="replace" optional="true" after="AND  FACT_ACCT.AD_ORG_ID IN (" text="'2'"/>
+    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND FACT_ACCT.C_BPARTNER_ID IN]]></Parameter>
+    <Parameter name="mProductId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND FACT_ACCT.M_PRODUCT_ID IN]]></Parameter>
+    <Parameter name="cProjectId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND FACT_ACCT.C_PROJECT_ID IN]]></Parameter>
+    <Parameter name="paramAmtFrom" optional="true" after="3=3"><![CDATA[ AND (FACT_ACCT.AMTACCTDR + FACT_ACCT.AMTACCTCR)>=TO_NUMBER(?) ]]></Parameter>
+    <Parameter name="paramAmtTo" optional="true" after="3=3"><![CDATA[ AND (FACT_ACCT.AMTACCTDR + FACT_ACCT.AMTACCTCR)<=TO_NUMBER(?) ]]></Parameter>
+    <Parameter name="accountId" optional="true" after="WHERE 6=6"><![CDATA[AND ID = ? ]]></Parameter>
+    <Parameter name="cBPartner" optional="true" after="WHERE 6=6"><![CDATA[AND groupbyid = ? ]]></Parameter>
+    <Parameter name="pgLimit" type="argument" optional="true" after=",  FACT_ACCT_ID, DESCRIPTION, ISDEBIT"><![CDATA[ LIMIT ]]></Parameter>
+    <Parameter name="oraLimit1" type="argument" optional="true" after=") C"><![CDATA[ WHERE ROWNUM <= ]]></Parameter>
+    <Parameter name="oraLimit2" type="argument" optional="true" after="WHERE 1=1"><![CDATA[ AND RN1 BETWEEN ]]></Parameter>
+    <Parameter name="factAcctId" optional="true" after="WHERE 1=1"><![CDATA[ and to_char(DATEACCT,'J')||FACTACCTTYPE||FACT_ACCT_GROUP_ID||FACT_ACCT_ID < ? ]]></Parameter>
+  </SqlMethod>
+
   <SqlMethod name="selectXLS" type="preparedStatement" return="multiple">
     <SqlMethodComment></SqlMethodComment>
     <Sql>
@@ -193,6 +365,63 @@
     <Parameter name="paramAmtTo" optional="true" after="3=3"><![CDATA[ AND (FACT_ACCT.AMTACCTDR + FACT_ACCT.AMTACCTCR)<=TO_NUMBER(?) ]]></Parameter>
   </SqlMethod>
 
+  <SqlMethod name="selectXLS2" type="preparedStatement" return="multiple">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql>
+      <![CDATA[
+      SELECT VALUE, NAME, DATEACCT,
+          COALESCE(SUM(AMTACCTDR),0) AS AMTACCTDR, COALESCE(SUM(AMTACCTCR),0) AS AMTACCTCR,
+          FACT_ACCT_GROUP_ID, ID, bpid, bpname, pdid, pdname, pjid, pjname,
+          DESCRIPTION, FACTACCTTYPE
+      FROM
+        (SELECT FACT_ACCT.ACCTVALUE AS VALUE, FACT_ACCT.ACCTDESCRIPTION AS NAME,
+            DATEACCT, AMTACCTDR, AMTACCTCR,
+            FACT_ACCT_GROUP_ID, FACT_ACCT.ACCOUNT_ID AS ID, FACT_ACCT.DESCRIPTION,
+            c_bpartner.c_bpartner_id as bpid, m_product.m_product_id as pdid, c_project.c_project_id as pjid,
+            (select name from c_bpartner b where b.c_bpartner_id = c_bpartner.c_bpartner_id) as bpname, 
+            (select value || ' - ' || name from m_product p where p.m_product_id = m_product.m_product_id) as pdname, 
+            (select name from c_project pj where pj.c_project_id = c_project.c_project_id)  as pjname,
+            CASE WHEN AMTACCTDR <> 0 THEN 'Y' ELSE 'N' END AS ISDEBIT,
+            CASE WHEN FACT_ACCT.FACTACCTTYPE = 'O' THEN 1 ELSE (CASE WHEN FACT_ACCT.FACTACCTTYPE = 'N' THEN 2 ELSE (CASE WHEN FACT_ACCT.FACTACCTTYPE = 'R' THEN 3 ELSE (CASE WHEN FACT_ACCT.FACTACCTTYPE = 'C' THEN 4 END) END) END) END AS FACTACCTTYPE
+        FROM FACT_ACCT
+          LEFT JOIN C_BPARTNER ON FACT_ACCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
+          LEFT JOIN M_PRODUCT ON FACT_ACCT.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
+          LEFT JOIN C_PROJECT ON FACT_ACCT.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID
+        WHERE (?='Y' OR FACT_ACCT.ACCOUNT_ID IN (
+                  SELECT c_elementvalue_id as name
+                  FROM C_ELEMENTVALUE
+                  WHERE value >= (  select value from c_elementvalue where c_elementvalue_id = ?)
+                    and value <= (  select value from c_elementvalue where c_elementvalue_id = ?)
+                    and c_elementvalue.ELEMENTLEVEL = 'S'
+                ))
+          AND FACT_ACCT.AD_ORG_ID IN ('1')
+          AND FACT_ACCT.AD_CLIENT_ID IN ('1')
+          AND 1=1
+          AND  FACT_ACCT.AD_ORG_ID IN ('2')
+          AND 2=2
+          AND 3=3) D
+        WHERE 6=6
+        GROUP BY VALUE, NAME, ID, FACTACCTTYPE, DATEACCT, FACT_ACCT_GROUP_ID, description, bpname, bpid, pdname, pdid, pjname, pjid, ISDEBIT
+        HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0
+        ORDER BY VALUE, NAME, ID, DATEACCT, FACTACCTTYPE, FACT_ACCT_GROUP_ID, description,  bpname, bpid, pdname, pdid, pjname, pjid, ISDEBIT
+      ]]></Sql>
+    <Field name="rownum" value="count"/>
+    <Parameter name="allaccounts"/>
+    <Parameter name="accountFrom"/>
+    <Parameter name="accountTo"/>
+    <Parameter name="adUserOrg" type="replace" optional="true" after="AND FACT_ACCT.AD_ORG_ID IN (" text="'1'"/>
+    <Parameter name="adUserClient" type="replace" optional="true" after="AND FACT_ACCT.AD_CLIENT_ID IN (" text="'1'"/>
+    <Parameter name="acctschema" optional="true" after="AND 1=1"><![CDATA[AND FACT_ACCT.C_ACCTSCHEMA_ID = ? ]]></Parameter>
+    <Parameter name="parDateFrom2" optional="true" after="AND 1=1"><![CDATA[AND FACT_ACCT.DATEACCT >= TO_DATE(?) ]]></Parameter>
+    <Parameter name="parDateTo2" optional="true" after="AND 1=1"><![CDATA[AND FACT_ACCT.DATEACCT < TO_DATE(?) ]]></Parameter>
+    <Parameter name="org" type="replace" optional="true" after="AND  FACT_ACCT.AD_ORG_ID IN (" text="'2'"/>
+    <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND FACT_ACCT.C_BPARTNER_ID IN]]></Parameter>
+    <Parameter name="mProductId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND FACT_ACCT.M_PRODUCT_ID IN]]></Parameter>
+    <Parameter name="cProjectId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND FACT_ACCT.C_PROJECT_ID IN]]></Parameter>
+    <Parameter name="paramAmtFrom" optional="true" after="3=3"><![CDATA[ AND (FACT_ACCT.AMTACCTDR + FACT_ACCT.AMTACCTCR)>=TO_NUMBER(?) ]]></Parameter>
+    <Parameter name="paramAmtTo" optional="true" after="3=3"><![CDATA[ AND (FACT_ACCT.AMTACCTDR + FACT_ACCT.AMTACCTCR)<=TO_NUMBER(?) ]]></Parameter>
+  </SqlMethod>
+
   <SqlMethod name="set" type="constant" return="multiple">
       <SqlMethodComment></SqlMethodComment>
       <Sql></Sql>
@@ -256,6 +485,30 @@
       <Parameter name="dateFrom"/>
    </SqlMethod>
 
+  <SqlMethod name="selectTotal2" type="preparedStatement" return="multiple">
+      <SqlMethodComment></SqlMethodComment>
+      <Sql><![CDATA[
+      SELECT COALESCE(SUM(AMTACCTDR),0) AS TOTALACCTDR, COALESCE(SUM(AMTACCTCR),0) AS TOTALACCTCR,
+      COALESCE(SUM(AMTACCTDR-AMTACCTCR),0) AS TOTAL
+      FROM FACT_ACCT
+      WHERE 1=1
+      AND FACT_ACCT.ACCOUNT_ID = ?
+      AND DATEACCT < TO_DATE(?)
+      AND FACT_ACCT.AD_ORG_ID IN ('1')
+      ]]></Sql>
+      <Parameter name="bpartner" optional="true" type="argument" after="1=1"><![CDATA[ AND FACT_ACCT.C_BPARTNER_ID IN]]></Parameter>
+      <Parameter name="bpartnerNULL" optional="true" type="argument" after="1=1"><![CDATA[ AND FACT_ACCT.C_BPARTNER_ID IS NULL]]></Parameter>
+      <Parameter name="product" optional="true" type="argument" after="1=1"><![CDATA[ AND FACT_ACCT.M_PRODUCT_ID IN]]></Parameter>
+      <Parameter name="productNULL" optional="true" type="argument" after="1=1"><![CDATA[ AND FACT_ACCT.M_PRODUCT_ID IS NULL]]></Parameter>
+      <Parameter name="project" optional="true" type="argument" after="1=1"><![CDATA[ AND FACT_ACCT.C_PROJECT_ID IN]]></Parameter>
+      <Parameter name="projectNULL" optional="true" type="argument" after="1=1"><![CDATA[ AND FACT_ACCT.C_PROJECT_ID IS NULL]]></Parameter>
+      <Parameter name="acctschema" optional="true" after="1=1"><![CDATA[ AND FACT_ACCT.C_ACCTSCHEMA_ID = ? ]]></Parameter>
+      <Parameter name="account"/>
+      <Parameter name="dateFrom" optional="true" after="ACCOUNT_ID = ?"><![CDATA[ AND DATEACCT >= TO_DATE(?) ]]></Parameter>
+      <Parameter name="dateTo2"/>
+      <Parameter name="orgs" type="replace" optional="true" after="AND FACT_ACCT.AD_ORG_ID IN (" text="'1'"/>
+   </SqlMethod>
+   
   <SqlMethod name="yearInitialDate" type="preparedStatement" return="string">
       <SqlMethodComment></SqlMethodComment>
       <Sql><![CDATA[