Aging Report compare with Ledger report not tally due to wrong matching info

Created by Cheng Kah Poh, Modified on Thu, 27 Jul, 2023 at 3:06 PM by Cheng Kah Poh

Scenario :



Note : As we can see both Pay Bills, and Bills are selected different Supplier, but this both transaction have matched info in backend APMatched Table.


Solution :


Supplier side :


Select G.GLAccountCode,P.CreditorName,P.Paymentcode, C.CompanyCode,C.CompanyName,B.BillCode from APMATCHED APM

JOIN payments P

ON APM.APTranId = P.Id

Join Bills B

ON APM.PayForId = B.Id

Join Creditors C

ON C.id = B.CreditorId

Join GLAccounts G

ON P.CreditorId =  G.id

Where APM.APType = 'PV'

and B.CreditorId != P.CreditorId

Debtor side :

Select G.GLAccountCode,R.DebtorName,R.ReceiptCode, D.CompanyCode,D.CompanyName,I.InvoiceCode from ARMATCHED ARM

JOIN Receipts R

ON ARM.ARTranId = R.Id

Join Invoices I

ON ARM.PayForId = I.Id

Join Debtors D

ON D.id = I.DebtorId

Join GLAccounts G

ON R.DebtorId =  G.id

Where ARM.ARType = 'RV'

and R.DebtorId != I.DebtorId


Run Above Script to find all same scenario Matched Info, which appear in APMATCHED Table.



Note : All the Same Scenario Wrong Result will appear.


To remove it.


Supplier Side :

Delete APM from APMATCHED APM

JOIN payments P

ON APM.APTranId = P.Id

Join Bills B

ON APM.PayForId = B.Id

Where APM.APType = 'PV'

and B.CreditorId != P.CreditorId

Debtor Side :


Delete ARM from ARMatched ARM

JOIN Receipts R

ON ARM.ARTranId = R.Id

Join Invoices I

ON ARM.PayForId = I.Id

Where ARM.ARType = 'PV'

and R.DebtorId != I.DebtorId


** Learn more about Azure Cloud

** Learn more about QNE Hybrid Cloud Software



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