How should journal balance differences be treated in cash reconciliation?

Due to an error in RetailVista, it is possible or has been possible for a journal that is generated to be out of balance. Journals have a debit and a credit side, and their totals should always be equal. If this is not the case, an inconsistency has occurred in RetailVista.

The background of this issue is a difficult-to-find error that occurs very rarely and is not reproducible. Some safeguards have been implemented to prevent this problem, and eventually the error itself will be found. Part of the safeguard is the check when exporting a journal to the financial administration. At that moment, a check is performed to ensure the journal is in balance. If it is not, the journal will not be submitted to the financial administration.

The underlying cause of this problem is incorrect links in the CashDeclarationAttributes table. These links will need to be manually checked by a developer. Each attribute record is linked to the previous cash declaration of the same user or POS terminal through the ParentCashDeclarationAttributeId field (depending on the settings for cash declaration).

In RetailVista itself, it is quickly visible from which cash declaration the links are incorrect. The ending cash of one cash declaration does not match the displayed starting cash of the next day. By selecting both columns in the cash declaration search screen, this is clearly visible.

By sorting the CashDeclarationAttribute records by UserId or PosTerminalId, it is quickly visible in SQL Management Studio which links are incorrect.

The query to be used is as follows:

select * From CashDeclarations where DeclarationNumber in (475,476)

select PosTerminalId, * From CashDeclarationAttributes where CashDeclarationId in(501,502)

order by 1,cashdeclarationid

In the above query, first two cash declarations are searched between which a problem is detected in the ending cash of the oldest cash declaration and the starting cash of the new cash declaration. Then, the records of those cash declarations are retrieved and sorted by PosTerminalId (or PosUserId in case of user-specific cash declaration). Now, check the ParentCashDeclarationAttributeValue of cash declaration Id 502 with the ItemId of the previous row. They should always be equal. If not, update that row. Example:

Update CashDeclarationAttributes set ParentCashDeclarationAttributeId=3949 where ItemId=3939

Once all the defective links are fixed, all the attributes of the oldest cash declaration should be opened and saved once. Then, all the underlying cash declarations should be recursively calculated and all the balances should match again. From that moment, a new journal can be generated.