0012654: Changing accounting year (CreateRegFactAcct) doesn't make the needed postings in certain cases

Description

This occurs when the order of the id's of the periods and the order of the actual dates are different. This is because some comparisons are erroneously based on the c_period_id instead of the starting date. The attached fix explains the problem.

Steps To Reproduce

1. Create calendar periods with "Create Periods" button.
2. (e.g.) Change the last period manually to the first.
3. Try to close the accounting year with "Create Reg Fact Acct"

No accounting records are created. All the data in the UI is right, but the problem is that the order of the period id's is different than the actual order.

Tags

No tags attached.

Attached Files

fix.diff [^] (2,288 bytes) 2010-03-11 16:55 [Show Content] [Hide Content]

Index: src/org/openbravo/erpCommon/ad_actionButton/CreateRegFactAcct_data.xsql
===================================================================
--- src/org/openbravo/erpCommon/ad_actionButton/CreateRegFactAcct_data.xsql	(revision 15140)
+++ src/org/openbravo/erpCommon/ad_actionButton/CreateRegFactAcct_data.xsql	(working copy)
@@ -50,10 +50,11 @@
         (CASE SIGN(SUM(AMTACCTCR)-SUM(AMTACCTDR)) WHEN -1 THEN 0 ELSE (SUM(AMTACCTCR)-SUM(AMTACCTDR)) END) AS TOTALAMTDR, ACCOUNT_ID, ACCTDESCRIPTION, ACCTVALUE,
         RECORD_ID2, C_BPARTNER_ID, M_PRODUCT_ID, A_ASSET_ID
         FROM FACT_ACCT
-        WHERE C_PERIOD_ID BETWEEN (SELECT C_PERIOD_ID FROM  C_PERIOD WHERE C_YEAR_ID = TO_NUMBER(?) AND PERIODNO = (SELECT MIN(PERIODNO)
+        WHERE C_PERIOD_ID IN (SELECT C_PERIOD_ID FROM C_PERIOD WHERE C_YEAR_ID = TO_NUMBER(?)
+        AND PERIODNO BETWEEN ((SELECT MIN(PERIODNO)
                 FROM C_PERIOD
                 WHERE C_YEAR_ID = TO_NUMBER(?)
-                AND REG_FACT_ACCT_GROUP_ID IS NULL)) AND TO_NUMBER(?)
+                AND CLOSE_FACT_ACCT_GROUP_ID IS NULL)) AND (SELECT PERIODNO FROM C_PERIOD WHERE C_PERIOD_ID = TO_NUMBER(?)))
         AND ACCOUNT_ID IN (SELECT C_ELEMENTVALUE_ID
                 FROM C_ELEMENTVALUE
                 WHERE C_ELEMENTVALUE.ACCOUNTTYPE = ?)
@@ -77,10 +78,11 @@
         (CASE sign(SUM(AMTACCTCR)-sum(amtacctdr)) WHEN -1 THEN 0 ELSE (SUM(AMTACCTCR)-sum(amtacctdr)) END) AS TOTALAMTDR, ACCOUNT_ID,
         ACCTDESCRIPTION, ACCTVALUE, RECORD_ID2, C_BPARTNER_ID, M_PRODUCT_ID, A_ASSET_ID
         FROM FACT_ACCT
-        WHERE C_PERIOD_ID BETWEEN (SELECT C_PERIOD_ID FROM  C_PERIOD WHERE C_YEAR_ID = TO_NUMBER(?) AND PERIODNO = (SELECT MIN(PERIODNO)
+        WHERE C_PERIOD_ID IN (SELECT C_PERIOD_ID FROM C_PERIOD WHERE C_YEAR_ID = TO_NUMBER(?)
+        AND PERIODNO BETWEEN ((SELECT MIN(PERIODNO)
                 FROM C_PERIOD
                 WHERE C_YEAR_ID = TO_NUMBER(?)
-                AND CLOSE_FACT_ACCT_GROUP_ID IS NULL)) AND TO_NUMBER(?)
+                AND CLOSE_FACT_ACCT_GROUP_ID IS NULL)) AND (SELECT PERIODNO FROM C_PERIOD WHERE C_PERIOD_ID = TO_NUMBER(?)))
         AND ACCOUNT_ID IN (SELECT C_ELEMENTVALUE_ID
                 FROM C_ELEMENTVALUE
                 WHERE C_ELEMENTVALUE.ACCOUNTTYPE IN ('1'))

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
(0038609) dalsasua (reporter) 2011-06-29 19:19	This has been already fixed. Thanks for your contribution, OlliJT.

Issue History
Date Modified	Username	Field	Change
2010-03-11 16:55	OlliJT	New Issue
2010-03-11 16:55	OlliJT	Assigned To	=> dalsasua
2010-03-11 16:55	OlliJT	File Added: fix.diff
2011-06-29 19:19	dalsasua	Note Added: 0038609
2011-06-29 19:19	dalsasua	Status	new => closed
2011-06-29 19:19	dalsasua	Resolution	open => no change required