(Optimum) Filter Non-Tally Debtor Aging versus Debtor Ledger

Created by Cheng Kah Poh, Modified on Fri, 25 Jun, 2021 at 3:40 PM by QNE Software Sdn bhd

  1. Connect to Database use Microsoft SQL Server Management Studio, Please refer How to Connect to Database use Microsoft SQL Server Management Studio and How to install Microsoft SQL Management Studio
  2. Download .sql script from below link:

https://drive.google.com/open?id=1Dku2XHkBKl8bo5eauhzopqwEmFCa-8y8

  1. Double click on downloaded .sql script file
  2. Change the closing date if required

  1. Click Execute
  2. Result will show which Debtor Aging vs Ledger not tall

BEGIN
DECLARE @DATE DATE = '2016-12-31' ----- Change the Closing Date if required
DECLARE @LEDGER TABLE(CompanyCode VARCHAR(20), LedgerBalance NUMERIC(28,2))
DECLARE @AGING TABLE(CompanyCode VARCHAR(20), AgingBalance NUMERIC(28,2))
DECLARE @RESULT TABLE(CompanyCode VARCHAR(20), LedgerBalance NUMERIC(28,2), AgingBalance NUMERIC(28,2))

-----Ledger-----
INSERT INTO @LEDGER (CompanyCode, LedgerBalance)
SELECT gla.GLAccountCode, SUM(glt.Debit-glt.Credit) 
FROM dbo.GLTransactions glt
JOIN dbo.GLAccounts gla ON gla.Id = glt.GLAccountId
WHERE gla.SpecialAccountId = (SELECT ID FROM SpecialAccounts WHERE SpecialAccountCode = 'AR') 
AND glt.PostingDate <= @DATE AND ISNULL(glt.IsCancelled,0) = 0 AND ISNULL(glt.IsPostDatedCheque,0) = 0
GROUP BY gla.GLAccountCode

-----AGING-----
INSERT INTO @AGING (CompanyCode, AgingBalance)
SELECT CompanyCode, SUM(OutstandingLocal) 
FROM FN_GetAROutstanding(@DATE,'False','False')
GROUP BY CompanyCode

-----RESULT-----
INSERT INTO @RESULT ( CompanyCode, LedgerBalance, AgingBalance)
SELECT L.CompanyCode,L.LedgerBalance, A.AgingBalance 
FROM @Ledger L LEFT JOIN @AGING A
ON L.CompanyCode = A.CompanyCode
WHERE L.LedgerBalance <> A.AgingBalance
ORDER BY L.CompanyCode

SELECT CompanyCode, LedgerBalance, AgingBalance, LedgerBalance - AgingBalance AS Variance 
FROM @RESULT
WHERE LedgerBalance - AgingBalance <> 0

RETURN 
END

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article