Bank rec - 'historical anomalies'

Find general Jiwa support here.

Bank rec - 'historical anomalies'

Postby pricerc » Mon Aug 05, 2019 8:31 am

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.
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Bank rec - 'historical anomalies'

Postby Mike.Sheen » Mon Aug 05, 2019 10:15 am

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.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Bank rec - 'historical anomalies'

Postby Mike.Sheen » Mon Aug 05, 2019 10:19 am

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.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Bank rec - 'historical anomalies'

Postby pricerc » Mon Aug 05, 2019 11:43 am

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
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20


Return to Core Product Support

Who is online

Users browsing this forum: No registered users and 1 guest