While Sage 200 includes aged debtor reports as standard, we prefer to work with the aged debtor reports in Excel though, and we prefer to grab the data directly, so that we can manipulate it at will
This poss includes a series of ‘SQL Server views’ that together give an aged debt, grab the most recent memo (which we use for recording credit control conversations) from the customer account, and show basic contact information.
Provided you or a colleague have basis SQL Server skills, it is straight-forward to create the necessary views in your Sage 200 SQL Server database. You can choose your own name for the first view, and this is the one that you should link to from Excel, but the other views will need to use the names indicated.
This version doesn’t support foreign currency accounts, and it also can’t be used to produce retrospective aged debtors. There are other ways of achieving much the same end, including the use of pivot tables which has the advantage of letting you drill down to transactions.
The views don’t format particularly well as created via our blog software I’m afraid, but you should be able to cut and paste directly. Also the views were put together by someone relatively inexperienced, so we know they’re not as tidy as they might be!
If you need help implementing this or similar Sage 200 aged debt reports please contact our Sage 200 support service.
SELECT TOP 100 PERCENT dbo.CreditControlWithMemo.CustomerAccountNumber, dbo.CreditControlWithMemo.CustomerAccountName,
dbo.CreditControlWithMemo.AccountBalance, dbo.CreditControlWithMemo.Now, dbo.CreditControlWithMemo.[30 Days], dbo.CreditControlWithMemo.[60 Days],
dbo.CreditControlWithMemo.[90 Days], dbo.CreditControlWithMemo.[120 Days],
dbo.CreditControlWithMemo.[60 Days] + dbo.CreditControlWithMemo.[90 Days] + dbo.CreditControlWithMemo.[120 Days] AS [Over 30 Day Total],
dbo.CreditControlWithMemo.[Explanation/Excuse], dbo.CreditControlWithMemo.[Date Of Last Contact], dbo.CreditControlContacts.Contact AS CreditControlName,
dbo.CreditControlContacts.DefaultTelephone AS CreditControlPhone, dbo.CreditControlContacts.DefaultEmail AS CreditControlEmail,
dbo.CreditControlEscContacts.Contact AS EscalateToName, dbo.CreditControlEscContacts.DefaultTelephone AS EscalateToPhone,
dbo.CreditControlEscContacts.DefaultEmail AS EscalateToEmail, dbo.SYSCreditPosition.Name AS Creditability
FROM dbo.SLCustomerAccount LEFT OUTER JOIN
dbo.SYSCreditPosition ON dbo.SLCustomerAccount.SYSCreditPositionID = dbo.SYSCreditPosition.SYSCreditPositionID LEFT OUTER JOIN
dbo.CreditControlEscContacts ON dbo.SLCustomerAccount.SLCustomerAccountID = dbo.CreditControlEscContacts.SLCustomerAccountID LEFT OUTER JOIN
dbo.CreditControlContacts ON dbo.SLCustomerAccount.SLCustomerAccountID = dbo.CreditControlContacts.SLCustomerAccountID RIGHT OUTER JOIN
dbo.CreditControlWithMemo ON dbo.SLCustomerAccount.CustomerAccountNumber = dbo.CreditControlWithMemo.CustomerAccountNumber
GROUP BY dbo.CreditControlWithMemo.CustomerAccountNumber, dbo.CreditControlWithMemo.CustomerAccountName, dbo.CreditControlWithMemo.AccountBalance,
dbo.CreditControlWithMemo.Now, dbo.CreditControlWithMemo.[30 Days], dbo.CreditControlWithMemo.[60 Days], dbo.CreditControlWithMemo.[90 Days],
dbo.CreditControlWithMemo.[120 Days], dbo.CreditControlWithMemo.[60 Days] + dbo.CreditControlWithMemo.[90 Days] + dbo.CreditControlWithMemo.[120 Days],
dbo.CreditControlWithMemo.[Explanation/Excuse], dbo.CreditControlWithMemo.[Date Of Last Contact], dbo.CreditControlContacts.Contact,
dbo.CreditControlContacts.DefaultTelephone, dbo.CreditControlContacts.DefaultEmail, dbo.CreditControlEscContacts.Contact,
dbo.CreditControlEscContacts.DefaultTelephone, dbo.CreditControlEscContacts.DefaultEmail, dbo.SYSCreditPosition.Name
ORDER BY dbo.CreditControlWithMemo.[60 Days] + dbo.CreditControlWithMemo.[90 Days] + dbo.CreditControlWithMemo.[120 Days] DESC
CreditControlWithMemo
SELECT TOP 100 PERCENT dbo.SLCustomerAccount.CustomerAccountNumber, dbo.SLCustomerAccount.CustomerAccountName, dbo.SLCustomerAccount.AccountBalance,
SUM(CASE WHEN DATEDIFF(d, dbo.SLPostedCustomerTran.TransactionDate, GETDATE()) BETWEEN 1 AND
30 THEN dbo.SLPostedCustomerTran.GoodsValueInAccountCurrency – dbo.SLPostedCustomerTran.AllocatedValue ELSE 0 END) AS Now,
SUM(CASE WHEN DATEDIFF(d, dbo.SLPostedCustomerTran.TransactionDate, GETDATE()) BETWEEN 31 AND
60 THEN dbo.SLPostedCustomerTran.GoodsValueInAccountCurrency – dbo.SLPostedCustomerTran.AllocatedValue ELSE 0 END) AS [30 Days],
SUM(CASE WHEN DATEDIFF(d, dbo.SLPostedCustomerTran.TransactionDate, GETDATE()) BETWEEN 61 AND
90 THEN dbo.SLPostedCustomerTran.GoodsValueInAccountCurrency – dbo.SLPostedCustomerTran.AllocatedValue ELSE 0 END) AS [60 Days],
SUM(CASE WHEN DATEDIFF(d, dbo.SLPostedCustomerTran.TransactionDate, GETDATE()) BETWEEN 91 AND
120 THEN dbo.SLPostedCustomerTran.GoodsValueInAccountCurrency – dbo.SLPostedCustomerTran.AllocatedValue ELSE 0 END) AS [90 Days],
SUM(CASE WHEN DATEDIFF(d, dbo.SLPostedCustomerTran.TransactionDate, GETDATE()) BETWEEN 121 AND
1000 THEN dbo.SLPostedCustomerTran.GoodsValueInAccountCurrency – dbo.SLPostedCustomerTran.AllocatedValue ELSE 0 END) AS [120 Days],
dbo.SYSCompany.CompanyName AS Source, dbo.SalesMemosB.MemoText AS [Explanation/Excuse],
dbo.SalesMemosB.TimeAndDateMemoCreated AS [Date Of Last Contact]
FROM dbo.SLCustomerAccount INNER JOIN
dbo.SLPostedCustomerTran ON dbo.SLCustomerAccount.SLCustomerAccountID = dbo.SLPostedCustomerTran.SLCustomerAccountID LEFT OUTER JOIN
dbo.SalesMemosB ON dbo.SLCustomerAccount.SLCustomerAccountID = dbo.SalesMemosB.SLCustomerAccountID CROSS JOIN
dbo.SYSCompany
GROUP BY dbo.SLCustomerAccount.CustomerAccountNumber, dbo.SLCustomerAccount.CustomerAccountName, dbo.SLCustomerAccount.AccountBalance,
dbo.SYSCompany.CompanyName, dbo.SalesMemosB.MemoText, dbo.SalesMemosB.TimeAndDateMemoCreated
HAVING (dbo.SLCustomerAccount.AccountBalance <> 0)
ORDER BY dbo.SLCustomerAccount.CustomerAccountNumber
CreditControlEscContacts
SELECT SLCustomerAccountID, SLCustomerContactID, Contact, IsDefaultRole, DefaultTelephone, DefaultEmail, DefaultFax, DefaultWebsite, DefaultMobile,
IsPreferredContactForRole, ContactRoleName
FROM dbo.SLCustomerContactDefaultsVw
WHERE (ContactRoleName LIKE ‘esc%’) AND (IsPreferredContactForRole <> 0)
CreditControlContacts
SELECT SLCustomerAccountID, SLCustomerContactID, Contact, IsDefaultRole, DefaultTelephone, DefaultEmail, DefaultFax, DefaultWebsite, DefaultMobile,
IsPreferredContactForRole, ContactRoleName
FROM dbo.SLCustomerContactDefaultsVw
WHERE (ContactRoleName = ‘credit control’) AND (IsPreferredContactForRole <> 0)
Tags: aged debtor, Excel, Sage 200