SET NOCOUNT ON
SELECT 'Accounts Receivable as of'=convert(varchar(20), getdate())
/* TOTALS */
PRINT ''
SELECT 'Non Trade / Charity'=g.GroupName,
Customers=count(ma.CustomerID),
Balance=sum(ma.Balance)
FROM MasterAccounts ma, Groups g
WHERE ma.GroupID = g.GroupID
AND ma.Active = 1
AND ma.Balance <> 0
GROUP BY g.SortOrder, g.GroupName
ORDER BY g.SortOrder
COMPUTE sum(sum(ma.Balance))
/* Oddballs (unpaid non-renewal invoices of Type='Invoice') */
PRINT 'Invoices NOT included in above totals, but should be:'
SELECT DISTINCT 'Billing Group'=g.GroupName,
i.InvoiceID,
CreateDate=convert(char(10), i.Date, 101),
'Amount Due'=convert(char(12), i.Amount),
'Name (CustomerID)'=CASE
WHEN ma.Company=''
THEN convert(varchar, ma.FirstName + ' ' + ma.LastName)
ELSE convert(varchar, ma.Company)
END + ' (' + convert(varchar(5), ma.CustomerID) + ')'
/* ,'Service Period'=convert(char(10), i.StartDate, 101) + ' - ' + convert(char(10), i.EndDate, 101) */
FROM MasterAccounts ma, Invoices i, Groups g, InvoiceItems ii
WHERE ma.CustomerID = i.CustomerID
AND ii.InvoiceID = i.InvoiceID
AND ma.GroupID = g.GroupID
AND ma.Active = 1
AND i.Date < getdate()
AND i.Type = 'Invoice'
AND (EXISTS (SELECT ItemID
FROM InvoiceItems ii
WHERE i.InvoiceID = ii.InvoiceID
AND ii.PaymentID IS NULL)
OR
EXISTS (SELECT PaymentID
FROM Payments p
WHERE p.PaymentID = ii.PaymentID
AND i.InvoiceID = ii.InvoiceID
AND p.Date >= getdate()))
ORDER BY g.GroupName, i.InvoiceID
/* BEGIN: Adopted from IEA Software's "Aging" stored procedure */
DECLARE @custid as int, @bal as money, @a0 as money, @a1 as money, @a2 as money, @a3 as money
DECLARE @msg as varchar(64), @idays as int, @amount as money, @done as int, @obal as money
SELECT CustomerID=0,
Age0=convert(money, 0),
Age30=convert(money, 0),
Age60=convert(money, 0),
Age90=convert(money, 0)
INTO #temp_aging
CREATE INDEX itmp_customerid ON #temp_aging(CustomerID)
DECLARE uc_cursor CURSOR FOR
SELECT CustomerID,
Balance
FROM MasterAccounts
WHERE Balance <> 0
OPEN uc_cursor
FETCH NEXT FROM uc_cursor INTO @custid, @bal
WHILE (@@fetch_status <> -1)
BEGIN
SELECT @a0=0, @a1=0, @a2=0, @a3=0, @done=0, @obal = @bal
DECLARE ud_cursor CURSOR FOR
SELECT DateDiff(Day, i.Date, GetDate()),
Amount=(ii.Amount * ii.Quantity)
FROM Invoices i, InvoiceItems ii
WHERE i.InvoiceID = ii.InvoiceID
AND i.CustomerID = @custid
AND i.Type <> "VOID"
ORDER BY i.Date DESC
OPEN ud_cursor
FETCH NEXT FROM ud_cursor INTO @idays, @amount
WHILE (@@fetch_status <> -1 AND @done = 0 )
BEGIN
IF @amount > @bal
BEGIN
SELECT @amount = @bal,
@done = 1
END
IF @idays <= 29 (SELECT @a0=@a0+@amount)
ELSE IF @idays <= 59 (SELECT @a1=@a1+@amount)
ELSE IF @idays <= 89 (SELECT @a2=@a2+@amount)
ELSE (SELECT @a3=@a3+@amount)
SELECT @bal = @bal - @amount
FETCH NEXT FROM ud_cursor INTO @idays, @amount
END
CLOSE ud_cursor
DEALLOCATE ud_cursor
/* print convert(varchar(6), @custid) + " " + convert(varchar(10), @obal) + ' 0:' + convert(varchar(10), @a0) + ' 30:' +
convert(varchar(10), @a1) + ' 60:' + convert(varchar(10), @a2) + ' 90:' + convert(varchar(10), @a3) */
INSERT INTO #temp_aging(CustomerID, Age0, Age30, Age60, Age90)
VALUES(@custid, @a0, @a1, @a2, @a3)
FETCH NEXT FROM uc_cursor INTO @custid, @bal
END
CLOSE uc_cursor
DEALLOCATE uc_cursor
/* END: Adopted from IEA Software's "Aging" stored procedure */
/* DETAILS */
PRINT 'Billing Record Details:'
DECLARE @GroupId int
DECLARE BillingGroup_cursor CURSOR FOR
SELECT GroupID
FROM Groups
ORDER BY SortOrder
OPEN BillingGroup_cursor
FETCH NEXT FROM BillingGroup_cursor INTO @GroupID
WHILE (@@fetch_status <> -1)
BEGIN /* beginning of main loop */
/* Display column headers, etc. ONLY if there's data to be displayed */
IF EXISTS (SELECT ma.GroupID
FROM MasterAccounts ma
WHERE Balance <> 0
AND ma.GroupID = @GroupID)
BEGIN /* Display detailed listings */
/* Display the billing group associated with this listing */
SELECT ''=GroupName
FROM Groups
WHERE GroupID = @GroupID
/* All of the details for the billing group */
SELECT ta.CustomerID,
' Balance'=convert(char(10), Balance),
' 0 Days'=convert(char(10), Age0),
' 30 Days'=convert(char(10), Age30),
' 60 Days'=convert(char(10), Age60),
' 90 Days'=convert(char(10), Age90),
Name=convert(char(25), ma.FirstName + ' ' + ma.LastName),
Company
FROM #temp_aging ta, MasterAccounts ma
WHERE ta.CustomerID = ma.CustomerID
AND ma.GroupID = @GroupID
AND ma.Active = 1
AND ma.Balance <> 0
ORDER BY ta.CustomerID
/* Totals for the billing group */
SELECT 'Count'=count(ma.CustomerID),
' Balance'=convert(char(10), sum(Balance)),
' 0 Days'=convert(char(10), sum(Age0)),
' 30 Days'=convert(char(10), sum(Age30)),
' 60 Days'=convert(char(10), sum(Age60)),
' 90 Days'=convert(char(10), sum(Age90))
FROM #temp_aging ta, MasterAccounts ma
WHERE ta.CustomerID = ma.CustomerID
AND ma.GroupID = @GroupID
AND ma.Active = 1
AND ma.Balance <> 0
END /* end of detailed listings */
FETCH NEXT FROM BillingGroup_cursor INTO @GroupID
END /* end of main loop */
CLOSE BillingGroup_cursor
DEALLOCATE BillingGroup_cursor
DROP TABLE #temp_aging
GO
For more information about this list (including removal) go to:
http://www.iea-software.com/support/maillists/liststart