Fiscal year closing

Fiscal year closing - Error

Past night was kind of nightmare as I was closing Financial Year for a client. Before I conclude the Reasons and solution to issue I would like to share my story so that you can have rich idea about depth of issue.

Every time I run process of Financial Year closing I came across following error message

Total of the transactions in the year is not 0.00.

Total is -265,144.37.

This is a serious error. Run \"Check\" on ledger transactions.

• First I thought it to be inconsistency of data, and supposed that it will be fixed by running consistency check.

  Surprisingly nothing found in in Consistency check, all was fine. No errors no warnings.

• I went to trail balance for specific year, and found Debit = Credit resulting 0/zero difference.

• Finally I decided to dive in code and reached to class responsible for Financial Year Posting. I reached to piece of code that was trying to transfer Financial Year balances entries but was failing to do so.

  Its right here at

   

  AOT\Classes\LedgerTransferOpening\insertOpeningTrans

   

  Below lines of code were throwing error

   if (Currency::amount(sumInTotal))
      {
          // <GEERU>
          if (!SysCountryRegionCode::isLegalEntityInCountryRegion([#isoRU]))
          {
          // </GEERU>
              throw error(strFmt("@SYS18947", 0.00) + '\n' +
                          strFmt("@SYS18948", sumInTotal) + '\n\n' +
                          "@SYS10015");
          // <GEERU>
          }
          // </GEERU>
      }

   

• After debugging I found that if sum of entries for a year is not equal to zero, it will not allow you to close the year. But matter of trouble for me, was that Trial Balance was showing Debit equal to Credit

• I noticed a loop with while select, in same class method which was taking balances and was doing sum for year that was the moment to find the culprit.

• Straightaway I created New Customized Table and inserted fields in this new table, same as fields of GeneralJournalAccountEntry & GeneralJournalEntry.

• Using existing loop which was summing up year balance I inserted records in my customized table, so I got list of all entries which system was taking into consideration, to close the year.

• One of the entries was having same amount, as was being shown in error message.

• I took this entry and went to trial balance. General ledger >> Common >> Trial Balance

• I found something surprising i.e. as I click that specific line of Trail Balance, to view details of its vouchers, the balance in MainAccount was more than sum of Voucher lines. Surprisingly only 1 voucher was appearing in details, whereas it was supposed to be there more vouchers, because of Line of Trail Balance was more than sum of voucher lines; this made me believe that some vouchers are missing.

• As my trail Balance was showing Debit = Credit, so that made me thought, where vouchers went if trail balance is equal.

• After hours of debugging I found the it’s because of DimensionAttributeValueCombination

• Due to any reasons a record was deleted from DimensionAttributeValueCombination and as a result it vouchers were not appearing in details of a trial balance line. And as result sum of Trail Balance line was more than Sum of its voucher lines.

• By code I update GeneralJournalAccountEntry. LedgerDimension for those records which were missing a corresponding record in DimensionAttributeValueCombination

• That fixed the issue immediately and we successfully closed the Financial year

   

  Summary:

   

  This problem occurs because you have unbalanced transactions for the year. Therefore, the debits and the credits for the year are not equal. The difference between the debits and the credits is listed in the error message.

   

  So, as a summary I would like to share that if you are getting this error message, this is because Sum of Year balances is not equal to zero. Few/main reasons for this issue can be following

   

1. Rounding issue, most probably if you increased number of decimal somewhere or without increasing number of decimals also, this can occur, In case of rounding issue, there is very much probability that difference/amount which is being showing in error message will be small amount.

   To fix this case you have to fix rounding of transactions manually.

2. Orphan Records: It can be a case where GeneralJournalAccountEntry. LedgerDimension, having orphan records.

   For this case, you have to adjust records manually inside table, depending upon which LedgerCombinations are missing. You can use Not-Exist join to analyze if there are any orphan records.

3. Wrong Account Type: It can also be due to any wrong account type; In case if you find that there are no orphan records and also there is no rounding issue, have a look at MainAccountBalances, find the account which is having same balance, as being shown on error message. This can be a culprit as well due to wrong Account Type.