details: https://code.openbravo.com/erp/devel/pi/rev/2f8dba119a61
changeset: 32125:2f8dba119a61
user: Atul Gaware <atul.gaware <at> openbravo.com>
date: Mon May 15 00:24:10 2017 +0530
summary: Fixes issue 35896: Wrong Trial Balance at "Account" level
and without checking "Opening Entry Amount to Initial Balance"
select is modified to get all account tree elements and their respective dr and
cr in fact
acct other than fact acct type R (Income Statement), C (Closing) and
O (Opening) in between date from and date to filters. Select Initial Balance
Method
is modified to just get the initial balance of the account tree element upto
from date
filter or the opening type fact acct register on from date filter.
Using the Opening Entry Amount to Initial Balance flag and initial balance sign
it is
decided to add to either dr or cr column respectively. +ve value added to
debits and
-ve value added to credits.
diffstat:
src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.java | 20 +++--
src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance_data.xsql | 32
++-------
2 files changed, 18 insertions(+), 34 deletions(-)
diffs (130 lines):
diff -r f2ce1dbf54a3 -r 2f8dba119a61
src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.java
--- a/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.java Tue May
16 17:16:27 2017 +0200
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.java Mon May
15 00:24:10 2017 +0530
@@ -11,7 +11,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-2016 Openbravo SLU
+ * All portions are Copyright (C) 2001-2017 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************
@@ -757,9 +757,7 @@
DateTimeData.nDaysAfter(this, strDateTo, "1"), "", "");
ReportTrialBalanceData[] dataInitialBalance =
ReportTrialBalanceData.selectInitialBalance(this,
strDateFrom, strcAcctSchemaId, "", "", "", strOrgFamily,
- Utility.getContext(this, vars, "#User_Client", "ReportTrialBalance"),
- strNotInitialBalance.equals("Y") ? "initial" : "notinitial",
- strNotInitialBalance.equals("Y") ? "initial" : "notinitial");
+ Utility.getContext(this, vars, "#User_Client", "ReportTrialBalance"));
log4j.debug("Calculating tree...");
dataAux = calculateTree(dataAux, null, new Vector<Object>(),
dataInitialBalance,
@@ -829,6 +827,7 @@
vecParcial.addElement("0");
ReportTrialBalanceData[] dataChilds = calculateTree(data, data[i].id,
vecParcial, dataIB,
strNotInitialBalance);
+
BigDecimal parcialDR = new BigDecimal((String)
vecParcial.elementAt(0));
BigDecimal parcialCR = new BigDecimal((String)
vecParcial.elementAt(1));
BigDecimal parcialInicial = new BigDecimal((String)
vecParcial.elementAt(2));
@@ -846,17 +845,20 @@
if (strNotInitialBalance.equals("Y")) {
data[i].saldoInicial = (new
BigDecimal(dataIB[k].saldoInicial).add(parcialInicial))
.toPlainString();
+
} else {
- data[i].amtacctdr = (new
BigDecimal(dataIB[k].amtacctdr).add(parcialDR)
- .add(new BigDecimal(data[i].amtacctdr))).toPlainString();
- data[i].amtacctcr = (new
BigDecimal(dataIB[k].amtacctcr).add(parcialCR)
- .add(new BigDecimal(data[i].amtacctcr))).toPlainString();
+ if (new
BigDecimal(dataIB[k].saldoInicial).compareTo(BigDecimal.ZERO) > 0) {
+ data[i].amtacctdr = (new
BigDecimal(dataIB[k].saldoInicial).add(parcialDR)
+ .add(new BigDecimal(data[i].amtacctdr))).toPlainString();
+ } else {
+ data[i].amtacctcr = (new
BigDecimal(dataIB[k].saldoInicial).negate().add(parcialCR)
+ .add(new BigDecimal(data[i].amtacctcr))).toPlainString();
+ }
}
data[i].saldoFinal = (new
BigDecimal(dataIB[k].saldoInicial).add(parcialDR)
.subtract(parcialCR)).toPlainString();
}
}
-
totalDR = totalDR.add(new BigDecimal(data[i].amtacctdr));
totalCR = totalCR.add(new BigDecimal(data[i].amtacctcr));
totalInicial = totalInicial.add(new BigDecimal(data[i].saldoInicial));
diff -r f2ce1dbf54a3 -r 2f8dba119a61
src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance_data.xsql
--- a/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance_data.xsql
Tue May 16 17:16:27 2017 +0200
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance_data.xsql
Mon May 15 00:24:10 2017 +0530
@@ -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-2016 Openbravo SLU
+ * All portions are Copyright (C) 2001-2017 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************
@@ -51,6 +51,7 @@
AND 1=1
AND F.FACTACCTTYPE <> 'R'
AND F.FACTACCTTYPE <> 'C'
+ AND F.FACTACCTTYPE <> 'O'
AND F.ISACTIVE = 'Y'
GROUP BY ACCOUNT_ID,EV.NAME, EV.VALUE, EV.ELEMENTLEVEL) A
GROUP BY ID, NAME, ACCOUNT_ID, ELEMENTLEVEL
@@ -293,47 +294,28 @@
SELECT ACCOUNT_ID, COALESCE(SUM(AMTACCTDR),0) AS AMTACCTDR,
COALESCE(SUM(AMTACCTCR),0) AS AMTACCTCR,
COALESCE(SUM(AMTACCTDR-AMTACCTCR), 0) AS SALDO_INICIAL
FROM (
- (SELECT F.ACCOUNT_ID AS ACCOUNT_ID, F.AMTACCTDR AS AMTACCTDR,
F.AMTACCTCR AS AMTACCTCR,
+ SELECT F.ACCOUNT_ID AS ACCOUNT_ID, F.AMTACCTDR AS AMTACCTDR,
F.AMTACCTCR AS AMTACCTCR,
F.DATEACCT, F.FACTACCTTYPE
FROM FACT_ACCT F
WHERE 1=1
- AND F.DATEACCT < TO_DATE(?)
+ AND (F.DATEACCT < TO_DATE(?) OR (F.DATEACCT = TO_DATE(?) AND
F.FACTACCTTYPE = 'O'))
AND F.C_ACCTSCHEMA_ID = ?
AND F.AD_ORG_ID IN ('1')
AND F.AD_CLIENT_ID IN ('1')
- AND F.ISACTIVE = 'Y')
- UNION ALL
- (SELECT F.ACCOUNT_ID AS ACCOUNT_ID, F.AMTACCTDR AS AMTACCTDR,
F.AMTACCTCR AS AMTACCTCR,
- F.DATEACCT, F.FACTACCTTYPE
- FROM FACT_ACCT F
- WHERE 2=2
- AND F.DATEACCT = TO_DATE(?)
- AND F.C_ACCTSCHEMA_ID = ?
- AND F.AD_ORG_ID IN ('1')
- AND F.AD_CLIENT_ID IN ('1')
- AND F.ISACTIVE = 'Y'
- AND 3=3)) A
+ AND F.ISACTIVE = 'Y'
+ ) A
GROUP BY ACCOUNT_ID
HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0
ORDER BY ACCOUNT_ID
]]></Sql>
<Parameter name="dateFrom"/>
+ <Parameter name="dateFrom"/>
<Parameter name="acctschema"/>
<Parameter name="bpartner" optional="true" type="argument"
after="1=1"><![CDATA[ AND F.C_BPARTNER_ID IN]]></Parameter>
<Parameter name="product" optional="true" type="argument"
after="1=1"><![CDATA[ AND F.M_PRODUCT_ID IN]]></Parameter>
<Parameter name="project" optional="true" type="argument"
after="1=1"><![CDATA[ AND F.C_PROJECT_ID IN]]></Parameter>
<Parameter name="orgFamily" type="replace" optional="true" after="AND
F.AD_ORG_ID IN (" text="'1'"/>
<Parameter name="clientFamily" type="replace" optional="true" after="AND
F.AD_CLIENT_ID IN (" text="'1'"/>
- <!-- -->
- <Parameter name="dateFrom"/>
- <Parameter name="acctschema"/>
- <Parameter name="bpartner" optional="true" type="argument"
after="2=2"><![CDATA[ AND F.C_BPARTNER_ID IN]]></Parameter>
- <Parameter name="product" optional="true" type="argument"
after="2=2"><![CDATA[ AND F.M_PRODUCT_ID IN]]></Parameter>
- <Parameter name="project" optional="true" type="argument"
after="2=2"><![CDATA[ AND F.C_PROJECT_ID IN]]></Parameter>
- <Parameter name="orgFamily" type="replace" optional="true" after="AND
F.AD_ORG_ID IN (" text="'1'"/>
- <Parameter name="clientFamily" type="replace" optional="true" after="AND
F.AD_CLIENT_ID IN (" text="'1'"/>
- <Parameter name="initial" type="none" optional="true" after="AND
3=3"><![CDATA[ AND F.FACTACCTTYPE = 'O']]></Parameter>
- <Parameter name="notinitial" type="none" optional="true" after="AND
3=3"><![CDATA[ AND F.FACTACCTTYPE <> 'O']]></Parameter>
</SqlMethod>
<SqlMethod name="treeAccount" type="preparedStatement" return="string">
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Openbravo-commits mailing list
Openbravo-commits@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
