| Description |
*This issue relates to i21 Help Desk ticket:* [ HDTN-263875 - [Bank Reconciliation] - Import bank rec --Reconcile the import file|https://helpdesk.irely.com/iRelyi21live/#/HD/Ticket/?ticket=HDTN-263875]
We need a grid that lets the user reconcile the import file of the bank rec. The business case is letting the user verify they have all the records from the import file matched or not matched to the bank transactions. It only reviews the current import file.
This grid will list all the records in the import file. Then compare that back to the bank register table. The scenarios that could happen in the result field:
# Reference No not found - A check number (Reference No) was not found in the bank transactions.
# Difference Found - The check number was found however, there is a difference between the check amount written and the amount that was cleared by the bank.
# OK - The check number was found and the amount of the check written matched the amount was cleared by the bank (diff = 0)
Below is what the dashboard looks like (for Central Ohio),
!pastedImage_d99287_0.png!
Working with Central Ohio, I have been was able to work out the SQL select as noted below:
SELECT tblCMBankStatementImport.dtmDate, tblCMBankStatementImport.strReferenceNo, tblCMBankStatementImport.dblWithdrawalAmount,
tblCMBankStatementImport.intImportStatus, BankTrans.dblAmount, tblCMBankStatementImport.dblWithdrawalAmount - BankTrans.dblAmount AS Diff,
CASE WHEN intImportStatus = 1 THEN 'OK' WHEN tblCMBankStatementImport.dblWithdrawalAmount - BankTrans.dblAmount <> 0 THEN 'Difference Found' ELSE 'Reference No not found'
END AS Result
FROM tblCMBankStatementImport INNER JOIN
(SELECT 'BSI-' + CONVERT(varchar(10), intNumber - 1) AS StmtID, strTransactionType
FROM tblSMStartingNumber
WHERE (strPrefix = N'BSI-')) AS CurrentBankStmt ON tblCMBankStatementImport.strBankStatementImportId = CurrentBankStmt.StmtID LEFT OUTER JOIN
(SELECT intBankAccountId, REPLACE(LTRIM(REPLACE(strReferenceNo, '0', ' ')), ' ', '0') AS ReferenceNo, dblAmount
FROM tblCMBankTransaction) AS BankTrans ON tblCMBankStatementImport.intBankAccountId = BankTrans.intBankAccountId AND
tblCMBankStatementImport.strReferenceNo = BankTrans.ReferenceNo
ORDER BY tblCMBankStatementImport.strReferenceNo
|