Bank rec - 'historical anomalies'
Posted:
Mon Aug 05, 2019 8:31 am
by pricerc
So we upgraded a customer to 7.2 a week and a half ago.
Everything's going swell. mostly.
Being a new month, they're doing their bank recs for the first time in V7. While the problem they reported was something else, in the screen shot they sent us, there are a dozen 'legacy' transactions that come up on their bank rec, one dating back to 2014, half of them from 2015 and the latest being a little over a year go.
They're a mix of debit and credit, and are all GL Transactions. The total works out to $975 and some change (not that the amount is important to the answer).
At this stage, the 'current' balance is correct, and we've just upgraded, so there's no value in hunting down causes.
Is there a simple bank-end way to just blat them?
P.S. I have also just noticed that when I drill to the GL from the bank rec, I get an "errored sets exist" error, although it does then drill through.
Re: Bank rec - 'historical anomalies'
Posted:
Mon Aug 05, 2019 10:15 am
by Mike.Sheen
If the following query (once you replace @BankAccountLedgerID with the GL_Ledger.GLLedgerID of the bank account) returns your problem rows, then just set the AmountReconciledIncGST for those BR_OpeningBalanceTransactions to be AmountIncGST.
- Code: Select all
SELECT BR_Main.BankRecID, OpeningBalanceTransactionID,
BR_OpeningBalanceTransactions.CreatedByStaffID, BR_OpeningBalanceTransactions.CreatedDateTime, BR_OpeningBalanceTransactions.LastSavedByStaffID,
BR_OpeningBalanceTransactions.LastSavedDateTime, LineNumber, TransactionDateTime, BR_OpeningBalanceTransactions.Status,
AmountReconciledIncGST, BR_OpeningBalanceTransactions.Reference, DebitCredit, Remark, AmountIncGST
FROM BR_OpeningBalanceTransactions
JOIN BR_Main ON BR_OpeningBalanceTransactions.BankRecID = BR_Main.BankRecID
WHERE BR_OpeningBalanceTransactions.Status = 0
AND BR_OpeningBalanceTransactions.AmountReconciledIncGST = 0
AND BR_Main.BankRecID IN
(
SELECT TOP 1 BankRecID
FROM
BR_Main
WHERE
BankAccountLedgerID = @BankAccountLedgerID
ORDER BY
LastSavedDateTime DESC
)
If it doesn't, then I'll need to look further - This was the first likely thing I found which would be pulling through old transactions. Incidentally, if that is the case for you, then it indicates there are unactivated bank rec(s) for that account which contain those transactions.
Re: Bank rec - 'historical anomalies'
Posted:
Mon Aug 05, 2019 10:19 am
by Mike.Sheen
If the above query didn't yield your transactions, then this is the other query which pulls transactions into a new bank rec:
- Code: Select all
Sql = "SELECT GL_Transactions.GLTransactionID, MAX(GL_Transactions.TransPostDateTime) [TransPostDateTime], MAX(GL_Transactions.Ref) [Ref], MAX(CAST(GL_Transactions.DebitCredit AS TINYINT)) [DebitCredit], " &
"MAX(GL_Transactions.Remark) [Remark], MAX(GL_Transactions.Amount) [Amount], SUM (ISNULL(AmountReconciledIncGST, 0)) [AmountReconciledIncGST], MAX(COALESCE(BR_GLTransactions.FirstAppearanceBankRecID,'')) [FirstAppearanceBankRecID] " &
"FROM " &
"GL_Transactions " &
"INNER JOIN GL_Sets ON GL_Transactions.GLSetID = GL_Sets.GLSetID " &
"LEFT OUTER JOIN BR_GLTransactions ON GL_Transactions.GLTransactionID = BR_GLTransactions.GLTransactionID " &
"LEFT OUTER JOIN BR_Main ON BR_GLTransactions.BankRecID = BR_Main.BankRecID " &
"WHERE GL_Transactions.GLLedgerID = @GLLedgerID "
If AllowForwardDatedTransactions = False Then
Sql += "AND GL_Transactions.TransPostDateTime <= @BankStatementDate "
End If
'Omit gl_transactions that were generated by a previous bank rec direct gl entry, where the bank account ledger is the same as the current bank rec.
Sql += "AND GL_Transactions.GLTransactionID NOT IN (SELECT GLTransactionID FROM GL_Transactions JOIN GL_Sets ON GL_Transactions.GLSetID = GL_Sets.GLSetID JOIN BR_Main ON GL_Sets.SourceID = BR_Main.BankRecID WHERE GL_Transactions.GLLedgerID = BR_Main.BankAccountLedgerID) "
'Omit already reconciled gl transactions
Sql += "AND GL_Transactions.GLTransactionID NOT IN (SELECT GLTransactionID FROM BR_GLTransactions WHERE Status > 0) "
Sql += "AND GL_Transactions.Reconciled = 0 AND " &
"GL_Transactions.Cleared = 0 AND " &
"GL_Sets.SetType = 0 AND " &
"GL_Transactions.Amount <> 0 " &
"GROUP BY " &
"GL_Transactions.GLTransactionID " &
"ORDER BY " &
"MAX (GL_Transactions.TransPostDateTime) "
So, if that returns your rows, you can just set the GL_Transactions.Reconciled to be 1, or the GL_Transactions.Cleared to be 1.
Re: Bank rec - 'historical anomalies'
Posted:
Mon Aug 05, 2019 11:43 am
by pricerc
Thanks Mike.
In SQL Management Studio, this did it for me:
- Code: Select all
declare @GLLedgerID varchar(40) = (select GL.GLLedgerID from GL_Ledger GL where AccountNo = '1010');
declare @BankStatementDate datetime = datefromparts(2019,7,1);
with source as (
SELECT GL_Transactions.GLTransactionID, MAX(GL_Transactions.TransPostDateTime) [TransPostDateTime], MAX(GL_Transactions.Ref) [Ref], MAX(CAST(GL_Transactions.DebitCredit AS TINYINT)) [DebitCredit],
MAX(GL_Transactions.Remark) [Remark], MAX(GL_Transactions.Amount) [Amount], SUM (ISNULL(AmountReconciledIncGST, 0)) [AmountReconciledIncGST], MAX(COALESCE(BR_GLTransactions.FirstAppearanceBankRecID,'')) [FirstAppearanceBankRecID]
FROM
GL_Transactions
INNER JOIN GL_Sets ON GL_Transactions.GLSetID = GL_Sets.GLSetID
LEFT OUTER JOIN BR_GLTransactions ON GL_Transactions.GLTransactionID = BR_GLTransactions.GLTransactionID
LEFT OUTER JOIN BR_Main ON BR_GLTransactions.BankRecID = BR_Main.BankRecID
WHERE GL_Transactions.GLLedgerID = @GLLedgerID
AND GL_Transactions.TransPostDateTime <= @BankStatementDate
-- Omit gl_transactions that were generated by a previous bank rec direct gl entry, where the bank account ledger is the same as the current bank rec.
AND GL_Transactions.GLTransactionID NOT IN (SELECT GLTransactionID FROM GL_Transactions JOIN GL_Sets ON GL_Transactions.GLSetID = GL_Sets.GLSetID JOIN BR_Main ON GL_Sets.SourceID = BR_Main.BankRecID WHERE GL_Transactions.GLLedgerID = BR_Main.BankAccountLedgerID)
-- Omit already reconciled gl transactions
AND GL_Transactions.GLTransactionID NOT IN (SELECT GLTransactionID FROM BR_GLTransactions WHERE Status > 0)
AND GL_Transactions.Reconciled = 0 AND
GL_Transactions.Cleared = 0 AND
GL_Sets.SetType = 0 AND
GL_Transactions.Amount <> 0
GROUP BY
GL_Transactions.GLTransactionID
--ORDER BY MAX (GL_Transactions.TransPostDateTime)
)
update GL_Transactions set Reconciled = 1, Cleared = 1
output inserted.*
from GL_Transactions GL inner join source on GL.GLTransactionID = source.GLTransactionID