You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@ofbiz.apache.org by "Daniel Watford (Jira)" <ji...@apache.org> on 2023/02/07 18:27:00 UTC
[jira] [Created] (OFBIZ-12755) Ending balance of closed period summed with transaction entries from open period on balance sheet
Daniel Watford created OFBIZ-12755:
--------------------------------------
Summary: Ending balance of closed period summed with transaction entries from open period on balance sheet
Key: OFBIZ-12755
URL: https://issues.apache.org/jira/browse/OFBIZ-12755
Project: OFBiz
Issue Type: Bug
Components: accounting
Affects Versions: 22.01.01
Reporter: Daniel Watford
Assignee: Daniel Watford
When closing a Custom Time Period, a GlAccountHistory value is created for each of the organization's GL Accounts, containing the opening balance, total debits, total credits and closing balance for the account.
If a GL Account doesn't have any transaction entries for the time period being closed, a GlAccountHistory value will be created with equal OpeningBalance and ClosingBalance attributes, and with zero PostedDebits and PostedCredits attributes.
When calculating balances, BalanceSheet.groovy will use an account's most recent GlAccountHistory value, combining it with relevant account entries that have occurred after the closed time period. However, rather than using ClosingBalance from GlAccountHistory, the script calculates a new balance from PostedDebits and PostedCredits.
PostedDebits and PostedCredits in GlAccountHistory only reflect the debits or credits for the closed time period, not an accumulation of debits and credits for consecutive time periods. Relying on the PostedDebit and PostedCredit attributes to calculate balances gives an incorrect result.
To observe this bug using demo data, create the following Fiscal Month custom time periods (time values should be 00:00:00):
* 2009-01-01 to 2009-02-01
* 2009-02-01 to 2009-03-01
* 2009-03-01 to 2009-04-01
Close these time periods in chronological order.
Visit screen [https://localhost:8443/webtools/control/entity/find/GlAccountAndHistory] and search for values with Account Code 111100. You should see a table containing results similar to:
h3. Search Results
|[Account Name|https://localhost:8443/webtools/control/entity/find/GlAccountAndHistory%3FendingBalance_fld1_op%3DlessThanEqualTo%26amp%3BglAccountId_op%3Dlike%26amp%3BglResourceTypeId_op%3Dcontains%26amp%3BaccountName_op%3Dcontains%26amp%3BglAccountTypeId_ic%3DY%26amp%3BglAccountClassId_ic%3DY%26amp%3BglAccountId_ic%3DY%26amp%3BpostedCredits_fld0_op%3DgreaterThanEqualTo%26amp%3BglResourceTypeId_ic%3DY%26amp%3BcustomTimePeriodId_op%3Dlike%26amp%3BaccountName_ic%3DY%26amp%3BaccountCode_ic%3DY%26amp%3BglAccountClassId_op%3Dlike%26amp%3Bdescription_ic%3DY%26amp%3BopeningBalance_fld0_op%3DgreaterThanEqualTo%26amp%3BsortField%3DaccountName%26amp%3BpostedDebits_fld0_op%3DgreaterThanEqualTo%26amp%3BsearchOptions_collapsed%3Dtrue%26amp%3BaccountCode%3D111100%26amp%3BendingBalance_fld0_op%3DgreaterThanEqualTo%26amp%3BaccountCode_op%3Dcontains%26amp%3BnoConditionFind%3DY%26amp%3BpostedCredits_fld1_op%3DlessThanEqualTo%26amp%3BorganizationPartyId_op%3Dlike%26amp%3BglXbrlClassId_ic%3DY%26amp%3BproductId_op%3Dcontains%26amp%3Bdescription_op%3Dcontains%26amp%3BparentGlAccountId_op%3Dcontains%26amp%3BglXbrlClassId_op%3Dcontains%26amp%3BcustomTimePeriodId_ic%3DY%26amp%3BorganizationPartyId_ic%3DY%26amp%3BproductId_ic%3DY%26amp%3BexternalId_ic%3DY%26amp%3BopeningBalance_fld1_op%3DlessThanEqualTo%26amp%3B_method%3DGET%26amp%3BparentGlAccountId_ic%3DY%26amp%3BglAccountTypeId_op%3Dcontains%26amp%3BpostedDebits_fld1_op%3DlessThanEqualTo%26amp%3BexternalId_op%3Dcontains]|[Gl Account ID|https://localhost:8443/webtools/control/entity/find/GlAccountAndHistory%3FendingBalance_fld1_op%3DlessThanEqualTo%26amp%3BglAccountId_op%3Dlike%26amp%3BglResourceTypeId_op%3Dcontains%26amp%3BaccountName_op%3Dcontains%26amp%3BglAccountTypeId_ic%3DY%26amp%3BglAccountClassId_ic%3DY%26amp%3BglAccountId_ic%3DY%26amp%3BpostedCredits_fld0_op%3DgreaterThanEqualTo%26amp%3BglResourceTypeId_ic%3DY%26amp%3BcustomTimePeriodId_op%3Dlike%26amp%3BaccountName_ic%3DY%26amp%3BaccountCode_ic%3DY%26amp%3BglAccountClassId_op%3Dlike%26amp%3Bdescription_ic%3DY%26amp%3BopeningBalance_fld0_op%3DgreaterThanEqualTo%26amp%3BsortField%3DglAccountId%26amp%3BpostedDebits_fld0_op%3DgreaterThanEqualTo%26amp%3BsearchOptions_collapsed%3Dtrue%26amp%3BaccountCode%3D111100%26amp%3BendingBalance_fld0_op%3DgreaterThanEqualTo%26amp%3BaccountCode_op%3Dcontains%26amp%3BnoConditionFind%3DY%26amp%3BpostedCredits_fld1_op%3DlessThanEqualTo%26amp%3BorganizationPartyId_op%3Dlike%26amp%3BglXbrlClassId_ic%3DY%26amp%3BproductId_op%3Dcontains%26amp%3Bdescription_op%3Dcontains%26amp%3BparentGlAccountId_op%3Dcontains%26amp%3BglXbrlClassId_op%3Dcontains%26amp%3BcustomTimePeriodId_ic%3DY%26amp%3BorganizationPartyId_ic%3DY%26amp%3BproductId_ic%3DY%26amp%3BexternalId_ic%3DY%26amp%3BopeningBalance_fld1_op%3DlessThanEqualTo%26amp%3B_method%3DGET%26amp%3BparentGlAccountId_ic%3DY%26amp%3BglAccountTypeId_op%3Dcontains%26amp%3BpostedDebits_fld1_op%3DlessThanEqualTo%26amp%3BexternalId_op%3Dcontains]|[Posted Credits|https://localhost:8443/webtools/control/entity/find/GlAccountAndHistory%3FendingBalance_fld1_op%3DlessThanEqualTo%26amp%3BglAccountId_op%3Dlike%26amp%3BglResourceTypeId_op%3Dcontains%26amp%3BaccountName_op%3Dcontains%26amp%3BglAccountTypeId_ic%3DY%26amp%3BglAccountClassId_ic%3DY%26amp%3BglAccountId_ic%3DY%26amp%3BpostedCredits_fld0_op%3DgreaterThanEqualTo%26amp%3BglResourceTypeId_ic%3DY%26amp%3BcustomTimePeriodId_op%3Dlike%26amp%3BaccountName_ic%3DY%26amp%3BaccountCode_ic%3DY%26amp%3BglAccountClassId_op%3Dlike%26amp%3Bdescription_ic%3DY%26amp%3BopeningBalance_fld0_op%3DgreaterThanEqualTo%26amp%3BsortField%3DpostedCredits%26amp%3BpostedDebits_fld0_op%3DgreaterThanEqualTo%26amp%3BsearchOptions_collapsed%3Dtrue%26amp%3BaccountCode%3D111100%26amp%3BendingBalance_fld0_op%3DgreaterThanEqualTo%26amp%3BaccountCode_op%3Dcontains%26amp%3BnoConditionFind%3DY%26amp%3BpostedCredits_fld1_op%3DlessThanEqualTo%26amp%3BorganizationPartyId_op%3Dlike%26amp%3BglXbrlClassId_ic%3DY%26amp%3BproductId_op%3Dcontains%26amp%3Bdescription_op%3Dcontains%26amp%3BparentGlAccountId_op%3Dcontains%26amp%3BglXbrlClassId_op%3Dcontains%26amp%3BcustomTimePeriodId_ic%3DY%26amp%3BorganizationPartyId_ic%3DY%26amp%3BproductId_ic%3DY%26amp%3BexternalId_ic%3DY%26amp%3BopeningBalance_fld1_op%3DlessThanEqualTo%26amp%3B_method%3DGET%26amp%3BparentGlAccountId_ic%3DY%26amp%3BglAccountTypeId_op%3Dcontains%26amp%3BpostedDebits_fld1_op%3DlessThanEqualTo%26amp%3BexternalId_op%3Dcontains]|[Posted Debits|https://localhost:8443/webtools/control/entity/find/GlAccountAndHistory%3FendingBalance_fld1_op%3DlessThanEqualTo%26amp%3BglAccountId_op%3Dlike%26amp%3BglResourceTypeId_op%3Dcontains%26amp%3BaccountName_op%3Dcontains%26amp%3BglAccountTypeId_ic%3DY%26amp%3BglAccountClassId_ic%3DY%26amp%3BglAccountId_ic%3DY%26amp%3BpostedCredits_fld0_op%3DgreaterThanEqualTo%26amp%3BglResourceTypeId_ic%3DY%26amp%3BcustomTimePeriodId_op%3Dlike%26amp%3BaccountName_ic%3DY%26amp%3BaccountCode_ic%3DY%26amp%3BglAccountClassId_op%3Dlike%26amp%3Bdescription_ic%3DY%26amp%3BopeningBalance_fld0_op%3DgreaterThanEqualTo%26amp%3BsortField%3DpostedDebits%26amp%3BpostedDebits_fld0_op%3DgreaterThanEqualTo%26amp%3BsearchOptions_collapsed%3Dtrue%26amp%3BaccountCode%3D111100%26amp%3BendingBalance_fld0_op%3DgreaterThanEqualTo%26amp%3BaccountCode_op%3Dcontains%26amp%3BnoConditionFind%3DY%26amp%3BpostedCredits_fld1_op%3DlessThanEqualTo%26amp%3BorganizationPartyId_op%3Dlike%26amp%3BglXbrlClassId_ic%3DY%26amp%3BproductId_op%3Dcontains%26amp%3Bdescription_op%3Dcontains%26amp%3BparentGlAccountId_op%3Dcontains%26amp%3BglXbrlClassId_op%3Dcontains%26amp%3BcustomTimePeriodId_ic%3DY%26amp%3BorganizationPartyId_ic%3DY%26amp%3BproductId_ic%3DY%26amp%3BexternalId_ic%3DY%26amp%3BopeningBalance_fld1_op%3DlessThanEqualTo%26amp%3B_method%3DGET%26amp%3BparentGlAccountId_ic%3DY%26amp%3BglAccountTypeId_op%3Dcontains%26amp%3BpostedDebits_fld1_op%3DlessThanEqualTo%26amp%3BexternalId_op%3Dcontains]|[Ending Balance|https://localhost:8443/webtools/control/entity/find/GlAccountAndHistory%3FendingBalance_fld1_op%3DlessThanEqualTo%26amp%3BglAccountId_op%3Dlike%26amp%3BglResourceTypeId_op%3Dcontains%26amp%3BaccountName_op%3Dcontains%26amp%3BglAccountTypeId_ic%3DY%26amp%3BglAccountClassId_ic%3DY%26amp%3BglAccountId_ic%3DY%26amp%3BpostedCredits_fld0_op%3DgreaterThanEqualTo%26amp%3BglResourceTypeId_ic%3DY%26amp%3BcustomTimePeriodId_op%3Dlike%26amp%3BaccountName_ic%3DY%26amp%3BaccountCode_ic%3DY%26amp%3BglAccountClassId_op%3Dlike%26amp%3Bdescription_ic%3DY%26amp%3BopeningBalance_fld0_op%3DgreaterThanEqualTo%26amp%3BsortField%3DendingBalance%26amp%3BpostedDebits_fld0_op%3DgreaterThanEqualTo%26amp%3BsearchOptions_collapsed%3Dtrue%26amp%3BaccountCode%3D111100%26amp%3BendingBalance_fld0_op%3DgreaterThanEqualTo%26amp%3BaccountCode_op%3Dcontains%26amp%3BnoConditionFind%3DY%26amp%3BpostedCredits_fld1_op%3DlessThanEqualTo%26amp%3BorganizationPartyId_op%3Dlike%26amp%3BglXbrlClassId_ic%3DY%26amp%3BproductId_op%3Dcontains%26amp%3Bdescription_op%3Dcontains%26amp%3BparentGlAccountId_op%3Dcontains%26amp%3BglXbrlClassId_op%3Dcontains%26amp%3BcustomTimePeriodId_ic%3DY%26amp%3BorganizationPartyId_ic%3DY%26amp%3BproductId_ic%3DY%26amp%3BexternalId_ic%3DY%26amp%3BopeningBalance_fld1_op%3DlessThanEqualTo%26amp%3B_method%3DGET%26amp%3BparentGlAccountId_ic%3DY%26amp%3BglAccountTypeId_op%3Dcontains%26amp%3BpostedDebits_fld1_op%3DlessThanEqualTo%26amp%3BexternalId_op%3Dcontains]|[Custom Time Period ID|https://localhost:8443/webtools/control/entity/find/GlAccountAndHistory%3FendingBalance_fld1_op%3DlessThanEqualTo%26amp%3BglAccountId_op%3Dlike%26amp%3BglResourceTypeId_op%3Dcontains%26amp%3BaccountName_op%3Dcontains%26amp%3BglAccountTypeId_ic%3DY%26amp%3BglAccountClassId_ic%3DY%26amp%3BglAccountId_ic%3DY%26amp%3BpostedCredits_fld0_op%3DgreaterThanEqualTo%26amp%3BglResourceTypeId_ic%3DY%26amp%3BcustomTimePeriodId_op%3Dlike%26amp%3BaccountName_ic%3DY%26amp%3BaccountCode_ic%3DY%26amp%3BglAccountClassId_op%3Dlike%26amp%3Bdescription_ic%3DY%26amp%3BopeningBalance_fld0_op%3DgreaterThanEqualTo%26amp%3BsortField%3DcustomTimePeriodId%26amp%3BpostedDebits_fld0_op%3DgreaterThanEqualTo%26amp%3BsearchOptions_collapsed%3Dtrue%26amp%3BaccountCode%3D111100%26amp%3BendingBalance_fld0_op%3DgreaterThanEqualTo%26amp%3BaccountCode_op%3Dcontains%26amp%3BnoConditionFind%3DY%26amp%3BpostedCredits_fld1_op%3DlessThanEqualTo%26amp%3BorganizationPartyId_op%3Dlike%26amp%3BglXbrlClassId_ic%3DY%26amp%3BproductId_op%3Dcontains%26amp%3Bdescription_op%3Dcontains%26amp%3BparentGlAccountId_op%3Dcontains%26amp%3BglXbrlClassId_op%3Dcontains%26amp%3BcustomTimePeriodId_ic%3DY%26amp%3BorganizationPartyId_ic%3DY%26amp%3BproductId_ic%3DY%26amp%3BexternalId_ic%3DY%26amp%3BopeningBalance_fld1_op%3DlessThanEqualTo%26amp%3B_method%3DGET%26amp%3BparentGlAccountId_ic%3DY%26amp%3BglAccountTypeId_op%3Dcontains%26amp%3BpostedDebits_fld1_op%3DlessThanEqualTo%26amp%3BexternalId_op%3Dcontains]|[Opening Balance|https://localhost:8443/webtools/control/entity/find/GlAccountAndHistory%3FendingBalance_fld1_op%3DlessThanEqualTo%26amp%3BglAccountId_op%3Dlike%26amp%3BglResourceTypeId_op%3Dcontains%26amp%3BaccountName_op%3Dcontains%26amp%3BglAccountTypeId_ic%3DY%26amp%3BglAccountClassId_ic%3DY%26amp%3BglAccountId_ic%3DY%26amp%3BpostedCredits_fld0_op%3DgreaterThanEqualTo%26amp%3BglResourceTypeId_ic%3DY%26amp%3BcustomTimePeriodId_op%3Dlike%26amp%3BaccountName_ic%3DY%26amp%3BaccountCode_ic%3DY%26amp%3BglAccountClassId_op%3Dlike%26amp%3Bdescription_ic%3DY%26amp%3BopeningBalance_fld0_op%3DgreaterThanEqualTo%26amp%3BsortField%3DopeningBalance%26amp%3BpostedDebits_fld0_op%3DgreaterThanEqualTo%26amp%3BsearchOptions_collapsed%3Dtrue%26amp%3BaccountCode%3D111100%26amp%3BendingBalance_fld0_op%3DgreaterThanEqualTo%26amp%3BaccountCode_op%3Dcontains%26amp%3BnoConditionFind%3DY%26amp%3BpostedCredits_fld1_op%3DlessThanEqualTo%26amp%3BorganizationPartyId_op%3Dlike%26amp%3BglXbrlClassId_ic%3DY%26amp%3BproductId_op%3Dcontains%26amp%3Bdescription_op%3Dcontains%26amp%3BparentGlAccountId_op%3Dcontains%26amp%3BglXbrlClassId_op%3Dcontains%26amp%3BcustomTimePeriodId_ic%3DY%26amp%3BorganizationPartyId_ic%3DY%26amp%3BproductId_ic%3DY%26amp%3BexternalId_ic%3DY%26amp%3BopeningBalance_fld1_op%3DlessThanEqualTo%26amp%3B_method%3DGET%26amp%3BparentGlAccountId_ic%3DY%26amp%3BglAccountTypeId_op%3Dcontains%26amp%3BpostedDebits_fld1_op%3DlessThanEqualTo%26amp%3BexternalId_op%3Dcontains]|
|GENERAL CHECKING ACCOUNT|111100|148,619.93|200,000|51,380.07|10000|0|
|GENERAL CHECKING ACCOUNT|111100|0|0|51,380.07|10001|51,380.07|
|GENERAL CHECKING ACCOUNT|111100|0|0|51,380.07|10010|51,380.07|
(Note: the first 4 values created by demo data can be ignored and have been removed from the above table. Some columns have also been removed.)
Notice that the posted credits and debits attributes for the GlAccountHistory values for February and March are zero.
Access screen [https://localhost:8443/accounting/control/BalanceSheet] for the company and generate a balance sheet with Thru Date 2009-07-11 00:00:00.
Observe that the balance for the GENERAL CHECKING ACOUNT is $51,380.07.
Now generate a balance sheet with Thru Date 2009-07-12 00:00:00 (i.e. one day later).
Observe that the balance for the GENERAL CHECKING ACCOUNT is now -$116.85.
This value is wrong as it now only summarises the two transactions (IDs 8006 and 8009) dated 2009-07-11 that affected the GENERAL CHECKING ACCOUNT. The previous balance of $51,380.07 has not been included in the sum.
Fix the method used to calculate balances to ensure that the closing balance from GlAccountHistory values are included.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)