details: https://code.openbravo.com/erp/devel/pi/rev/4af49610cdb2
changeset: 15956:4af49610cdb2
user: Eduardo Argal Guibert <eduardo.argal <at> openbravo.com>
date: Thu Mar 29 16:39:23 2012 +0200
summary: Fixes bug 19829: Fiscal Year End Close Generates Incorrect Accounting
diffstat:
src-db/database/sourcedata/AD_MESSAGE.xml | 11 +
src/org/openbravo/erpCommon/ad_actionButton/CreateRegFactAcct.java | 29
+++-
src/org/openbravo/erpCommon/ad_actionButton/CreateRegFactAcct_data.xsql | 81
++++++++++
3 files changed, 118 insertions(+), 3 deletions(-)
diffs (185 lines):
diff -r a40438ae1ee5 -r 4af49610cdb2 src-db/database/sourcedata/AD_MESSAGE.xml
--- a/src-db/database/sourcedata/AD_MESSAGE.xml Thu Mar 29 18:17:42 2012 +0200
+++ b/src-db/database/sourcedata/AD_MESSAGE.xml Thu Mar 29 16:39:23 2012 +0200
@@ -17202,6 +17202,17 @@
<!--7483DF96F3C34BB38C67B66BFAC79404-->
<AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
<!--7483DF96F3C34BB38C67B66BFAC79404--></AD_MESSAGE>
+<!--76044028A6A54026900ABC05910E2216--><AD_MESSAGE>
+<!--76044028A6A54026900ABC05910E2216-->
<AD_MESSAGE_ID><![CDATA[76044028A6A54026900ABC05910E2216]]></AD_MESSAGE_ID>
+<!--76044028A6A54026900ABC05910E2216-->
<AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--76044028A6A54026900ABC05910E2216--> <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--76044028A6A54026900ABC05910E2216--> <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--76044028A6A54026900ABC05910E2216-->
<VALUE><![CDATA[BalanceIsNotBalanced]]></VALUE>
+<!--76044028A6A54026900ABC05910E2216--> <MSGTEXT><![CDATA[Balance for
selected year is NOT Balanced. Please verify that your balance for selected
year in accounting schema @AcctSchema@ is balanced.]]></MSGTEXT>
+<!--76044028A6A54026900ABC05910E2216--> <MSGTYPE><![CDATA[E]]></MSGTYPE>
+<!--76044028A6A54026900ABC05910E2216-->
<AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--76044028A6A54026900ABC05910E2216--></AD_MESSAGE>
+
<!--7687410573FA477BBDDD8B54FDBB74CD--><AD_MESSAGE>
<!--7687410573FA477BBDDD8B54FDBB74CD-->
<AD_MESSAGE_ID><![CDATA[7687410573FA477BBDDD8B54FDBB74CD]]></AD_MESSAGE_ID>
<!--7687410573FA477BBDDD8B54FDBB74CD-->
<AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
diff -r a40438ae1ee5 -r 4af49610cdb2
src/org/openbravo/erpCommon/ad_actionButton/CreateRegFactAcct.java
--- a/src/org/openbravo/erpCommon/ad_actionButton/CreateRegFactAcct.java
Thu Mar 29 18:17:42 2012 +0200
+++ b/src/org/openbravo/erpCommon/ad_actionButton/CreateRegFactAcct.java
Thu Mar 29 16:39:23 2012 +0200
@@ -23,6 +23,8 @@
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.SQLException;
+import java.util.HashMap;
+import java.util.Map;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
@@ -31,6 +33,8 @@
import org.openbravo.base.secureApp.HttpSecureAppServlet;
import org.openbravo.base.secureApp.VariablesSecureApp;
+import org.openbravo.dal.core.OBContext;
+import org.openbravo.dal.security.OrganizationStructureProvider;
import org.openbravo.dal.service.OBDal;
import org.openbravo.erpCommon.utility.OBError;
import org.openbravo.erpCommon.utility.SequenceIdData;
@@ -91,7 +95,27 @@
vars.getClient(), strOrgId);
CreateRegFactAcctData[] acctSchema =
CreateRegFactAcctData.treeAcctSchema(this,
vars.getClient(), strOrgId);
+ String strPediodId = CreateRegFactAcctData.getLastPeriod(this, strKey);
for (int j = 0; j < acctSchema.length; j++) {
+ String balanceAmount = CreateRegFactAcctData.balanceAmount(this,
strKey, acctSchema[j].id,
+ Utility.getInStrSet(new
OrganizationStructureProvider().getChildTree(strOrgId, true)));
+ if (BigDecimal.ZERO.compareTo(new BigDecimal(balanceAmount)) != 0) {
+ releaseRollbackConnection(conn);
+ myError.setType("Error");
+ myError.setTitle("");
+ Map<String, String> parameters = new HashMap<String, String>();
+ try {
+ OBContext.setAdminMode();
+ AcctSchema schema = OBDal.getInstance().get(AcctSchema.class,
acctSchema[j].id);
+ parameters.put("AcctSchema", schema.getName());
+ } finally {
+ OBContext.restorePreviousMode();
+ }
+
+ myError.setMessage(Utility.parseTranslation(this, vars, parameters,
vars.getLanguage(),
+ Utility.messageBD(this, "BalanceIsNotBalanced",
vars.getLanguage())));
+ return myError;
+ }
String strRegId = SequenceIdData.getUUID();
String strCloseId = SequenceIdData.getUUID();
String strOpenId = SequenceIdData.getUUID();
@@ -108,7 +132,6 @@
for (int i = 0; i < dataOrgs.length; i++) {
if (log4j.isDebugEnabled())
log4j.debug("Output: Before buttonReg");
- String strPediodId = CreateRegFactAcctData.getLastPeriod(this,
strKey);
String regCount = CreateRegFactAcctData.getRegCount(this,
vars.getClient(),
dataOrgs[i].org, acctSchema[j].id, strPediodId);
if (new Integer(regCount).intValue() > 0) {
@@ -320,8 +343,8 @@
Fact_Acct_Group_ID, "10", "C", strClosingEntry, strKey, "'L','O'",
strAcctSchema);
String Fact_Acct_Group_ID2 = strOpenID;
- CreateRegFactAcctData.insertSelect(conn, this, vars.getClient(),
stradOrgId, vars.getUser(),
- CreateRegFactAcctData.getStartDate(this, newPeriod), newPeriod,
currency,
+ CreateRegFactAcctData.insertSelectOpening(conn, this, vars.getClient(),
stradOrgId,
+ vars.getUser(), CreateRegFactAcctData.getStartDate(this, newPeriod),
newPeriod, currency,
Fact_Acct_Group_ID2, "20", "O", strOpeningEntry, strKey,
"'A','L','O'", strAcctSchema);
return "Success";
diff -r a40438ae1ee5 -r 4af49610cdb2
src/org/openbravo/erpCommon/ad_actionButton/CreateRegFactAcct_data.xsql
--- a/src/org/openbravo/erpCommon/ad_actionButton/CreateRegFactAcct_data.xsql
Thu Mar 29 18:17:42 2012 +0200
+++ b/src/org/openbravo/erpCommon/ad_actionButton/CreateRegFactAcct_data.xsql
Thu Mar 29 16:39:23 2012 +0200
@@ -250,6 +250,70 @@
<Parameter name="ad_org_id"/>
<Parameter name="acctSchema"/>
</SqlMethod>
+ <SqlMethod name="insertSelectOpening" type="preparedStatement"
connection="true" return="rowCount">
+ <SqlMethodComment></SqlMethodComment>
+ <Sql>
+ <![CDATA[
+ INSERT INTO FACT_ACCT
+ (FACT_ACCT_ID, AD_CLIENT_ID, AD_ORG_ID,
+ ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY,
+ C_ACCTSCHEMA_ID,ACCOUNT_ID, DATETRX, DATEACCT,
+ C_PERIOD_ID, AD_TABLE_ID, RECORD_ID,
+ POSTINGTYPE,C_CURRENCY_ID,AMTSOURCECR,
+ AMTSOURCEDR, AMTACCTCR, AMTACCTDR,
+ FACT_ACCT_GROUP_ID,SEQNO, FACTACCTTYPE,
+ ACCTDESCRIPTION, ACCTVALUE, C_BPARTNER_ID,
+ RECORD_ID2, M_PRODUCT_ID, A_ASSET_ID,DESCRIPTION,
+ C_TAX_ID,C_PROJECT_ID,C_ACTIVITY_ID,
+ USER1_ID,USER2_ID,C_CAMPAIGN_ID,C_SALESREGION_ID)
+ SELECT get_UUID(), ?, ?,
+ 'Y', now(), ?, now(), ?,
+ C_ACCTSCHEMA_ID, ACCOUNT_ID, to_date(?), to_date(?),
+ ?, '224', ?,
+ 'A', ?, (CASE SIGN(SUM(AMTACCTCR)-SUM(AMTACCTDR)) WHEN -1 THEN 0 ELSE
(SUM(AMTACCTCR)-SUM(AMTACCTDR)) END),
+ (CASE SIGN(SUM(AMTACCTDR)-SUM(AMTACCTCR)) WHEN -1 THEN 0 ELSE
(SUM(AMTACCTDR)-SUM(AMTACCTCR)) END),
+ (CASE SIGN(SUM(AMTACCTCR)-SUM(AMTACCTDR)) WHEN -1 THEN 0 ELSE
(SUM(AMTACCTCR)-SUM(AMTACCTDR)) END),
+ (CASE SIGN(SUM(AMTACCTDR)-SUM(AMTACCTCR)) WHEN -1 THEN 0 ELSE
(SUM(AMTACCTDR)-SUM(AMTACCTCR)) END),
+ ?, to_number(?), ?,
+ ACCTDESCRIPTION, ACCTVALUE, C_BPARTNER_ID,
+ RECORD_ID2, M_PRODUCT_ID, A_ASSET_ID, ? ,
+ C_TAX_ID,C_PROJECT_ID,C_ACTIVITY_ID,
+ USER1_ID,USER2_ID,C_CAMPAIGN_ID,C_SALESREGION_ID
+ FROM FACT_ACCT
+ WHERE EXISTS (SELECT 1 FROM C_PERIOD P
+ WHERE P.C_YEAR_ID = ?
+ AND P.C_PERIOD_ID = FACT_ACCT.C_PERIOD_ID)
+ AND EXISTS (SELECT 1
+ FROM C_ELEMENTVALUE
+ WHERE C_ELEMENTVALUE.ACCOUNTTYPE IN ('1')
+ AND ACCOUNT_ID = C_ELEMENTVALUE_ID)
+ AND AD_ORG_ID = ?
+ AND C_ACCTSCHEMA_ID = ?
+ AND FACTACCTTYPE <>'C'
+ GROUP BY ACCOUNT_ID, ACCTDESCRIPTION, ACCTVALUE, RECORD_ID2,
C_BPARTNER_ID, M_PRODUCT_ID, A_ASSET_ID,
+
C_TAX_ID,C_PROJECT_ID,C_ACTIVITY_ID,USER1_ID,USER2_ID,C_CAMPAIGN_ID,C_SALESREGION_ID,
C_ACCTSCHEMA_ID
+ HAVING SUM(AMTACCTDR)-SUM(AMTACCTCR)<>0
+ order by ACCTVALUE
+ ]]>
+ </Sql>
+ <Parameter name="ad_client_id"/>
+ <Parameter name="ad_org_id"/>
+ <Parameter name="ad_user_id"/>
+ <Parameter name="ad_user_id"/>
+ <Parameter name="date"/>
+ <Parameter name="date"/>
+ <Parameter name="c_period_id"/>
+ <Parameter name="c_period_id"/>
+ <Parameter name="c_currency_id"/>
+ <Parameter name="fact_acct_group_id"/>
+ <Parameter name="seqno"/>
+ <Parameter name="factAcctType"/>
+ <Parameter name="description"/>
+ <Parameter name="year"/>
+ <Parameter name="accounttype" optional="true" type="replace" after="WHERE
C_ELEMENTVALUE.ACCOUNTTYPE IN (" text="'1'"/>
+ <Parameter name="ad_org_id"/>
+ <Parameter name="acctSchema"/>
+ </SqlMethod>
<SqlMethod name="getEndDate" type="preparedStatement" return="Date">
<SqlMethodComment></SqlMethodComment>
<Sql>
@@ -555,4 +619,21 @@
]]></Sql>
<Parameter name="clientId"/>
</SqlMethod>
+ <SqlMethod name="balanceAmount" type="preparedStatement" return="string">
+ <SqlMethodComment>When balance is balanced this amount should be
ZERO</SqlMethodComment>
+ <Sql><![CDATA[
+ select coalesce(sum(amtacctdr)-sum(amtacctcr),0)
+ from fact_acct, c_elementvalue
+ where fact_acct.account_id = c_elementvalue.c_elementvalue_id
+ and dateacct >= (select min(startdate) from c_period where c_year_id =
?)
+ and dateacct <= (select max(enddate) from c_period where c_year_id = ?)
+ and accounttype in ('E','R','A','L','O')
+ and fact_acct.c_acctschema_id = ?
+ and fact_acct.ad_org_id in ('1')
+ ]]></Sql>
+ <Parameter name="yearId"/>
+ <Parameter name="yearId"/>
+ <Parameter name="acctSchemaId"/>
+ <Parameter name="organizationList" optional="true" type="replace"
after="and fact_acct.ad_org_id in (" text="'1'"/>
+ </SqlMethod>
</SqlClass>
------------------------------------------------------------------------------
This SF email is sponsosred by:
Try Windows Azure free for 90 days Click Here
http://p.sf.net/sfu/sfd2d-msazure
_______________________________________________
Openbravo-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
