Sage 200 aged debtors

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.[osb]30 Days[csb], 
  dbo.CreditControlWithMemo.[osb]60 Days[csb],
  dbo.CreditControlWithMemo.[osb]90 Days[csb], 
  dbo.CreditControlWithMemo.[osb]120 Days[csb],
  dbo.CreditControlWithMemo.[osb]60 Days[csb] + dbo.CreditControlWithMemo.[osb]90 Days[csb] + 
    dbo.CreditControlWithMemo.[osb]120 Days[csb] AS [osb]Over 30 Day Total[csb],
  dbo.CreditControlWithMemo.[osb]Explanation/Excuse[csb], 
  dbo.CreditControlWithMemo.[osb]Date Of Last Contact[csb], 
  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.[osb]30 Days[csb], 
  dbo.CreditControlWithMemo.[osb]60 Days[csb], 
  dbo.CreditControlWithMemo.[osb]90 Days[csb],
  dbo.CreditControlWithMemo.[osb]120 Days[csb], 
  dbo.CreditControlWithMemo.[osb]60 Days[csb] + dbo.CreditControlWithMemo.[osb]90 Days[csb] + 
    dbo.CreditControlWithMemo.[osb]120 Days[csb],
  dbo.CreditControlWithMemo.[osb]Explanation/Excuse[csb], 
  dbo.CreditControlWithMemo.[osb]Date Of Last Contact[csb], 
  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.[osb]60 Days[csb] + dbo.CreditControlWithMemo.[osb]90 Days[csb] + 
    dbo.CreditControlWithMemo.[osb]120 Days[csb] 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 [osb]30 Days[csb],
  SUM(CASE 
    WHEN DATEDIFF(d, dbo.SLPostedCustomerTran.TransactionDate, GETDATE()) BETWEEN 61 AND 90 
    THEN dbo.SLPostedCustomerTran.GoodsValueInAccountCurrency – dbo.SLPostedCustomerTran.AllocatedValue 
    ELSE 0 END) AS [osb]60 Days[csb],
  SUM(CASE 
    WHEN DATEDIFF(d, dbo.SLPostedCustomerTran.TransactionDate, GETDATE()) BETWEEN 91 AND 120 
    THEN dbo.SLPostedCustomerTran.GoodsValueInAccountCurrency – dbo.SLPostedCustomerTran.AllocatedValue 
    ELSE 0 END) AS [osb]90 Days[csb],
  SUM(CASE 
    WHEN DATEDIFF(d, dbo.SLPostedCustomerTran.TransactionDate, GETDATE()) BETWEEN 121 AND 1000 
    THEN dbo.SLPostedCustomerTran.GoodsValueInAccountCurrency – dbo.SLPostedCustomerTran.AllocatedValue 
    ELSE 0 END) AS [osb]120 Days[csb],
  dbo.SYSCompany.CompanyName AS Source, 
  dbo.SalesMemosB.MemoText AS [osb]Explanation/Excuse[csb],
  dbo.SalesMemosB.TimeAndDateMemoCreated AS [osb]Date Of Last Contact[csb]
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)