Re: [Emerald] Daily Payment Report

Josh Hillman ( (no email) )
Mon, 19 Jul 1999 17:15:48 -0400

From: Charles Tomala <banghi@olywa.net>
> So does anyone have a report worked up for daily payments that shows the
> 'description' field? I have Crystal Reports, but thought somebody may
have
> already made one, saving me the time and trouble of actually doing it. It
> would be helpful for our records to see check numbers and the other data
> that is entered with the payments on this report.

I don't have a CR report for this, but I have a fairly detailed SQL script
that I run in ISQL/w that has this info. We have two billing groups
("Trade" and "Charity") that we need to separate from the regular
payments/sales/etc., so that's why they're listed separately below.

Josh Hillman
hillman@talstar.com

/* Daily Payments (Emerald 2.5) */
/* Lists payment and credit details with totals for all billing groups */

SET NOCOUNT ON

DECLARE @GroupId int, @PaymentDate datetime

SELECT @PaymentDate = '7/14/1999'

SELECT "Payment Date"=CONVERT(char(10), @PaymentDate, 101),
"Date Printed"=GetDate()
PRINT ''

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 */

/* Don't display empty listings for the current billing group */
IF EXISTS (SELECT ma.GroupID
FROM MasterAccounts ma, Payments p
WHERE p.CustomerID = ma.CustomerID
AND ma.GroupID = @GroupID
AND DATEDIFF(Day, p.Date, @PaymentDate) = 0)
BEGIN /* Display detailed payment listings */

SET NOCOUNT ON

SELECT ''=GroupName
FROM Groups
WHERE GroupID = @GroupID

SET NOCOUNT OFF

SELECT DISTINCT "Payment Type"=p.Type,
ii.InvoiceID,
"Amount Paid"=p.Amount,
p.PayInfo,
Name=(ma.FirstName+' '+ma.LastName),
ma.Company
FROM MasterAccounts ma, Payments p, InvoiceItems ii
WHERE DATEDIFF(Day, p.Date, @PaymentDate) = 0
AND ma.GroupID = @GroupID
AND ma.CustomerID = p.CustomerID
AND p.PaymentID = ii.PaymentID

/* Include payments that are missing associations with InvoiceIDs */
UNION
SELECT DISTINCT "Payment Type"=p.Type,
"InvoiceID "=null,
"Amount Paid"=p.Amount,
p.PayInfo,
Name=(ma.FirstName+' '+ma.LastName),
ma.Company
FROM MasterAccounts ma, Payments p
WHERE DATEDIFF(Day, p.Date, @PaymentDate) = 0
AND ma.CustomerID = p.CustomerID
AND ma.GroupID = @GroupID
AND NOT EXISTS (SELECT ii.InvoiceID
FROM InvoiceItems ii
WHERE p.PaymentID = ii.PaymentID)

ORDER BY p.Type, ii.InvoiceID

SET NOCOUNT ON

END /* end of detailed payment listings */

FETCH NEXT FROM BillingGroup_cursor INTO @GroupID

END /* end of main loop */

CLOSE BillingGroup_cursor
DEALLOCATE BillingGroup_cursor

/* TOTALS */

/* Payments */

SELECT "Payments"=g.Groupname,
p.Type,
Total=sum(p.Amount)
FROM Payments p, MasterAccounts ma, Groups g
WHERE DATEDIFF(Day, p.Date, @PaymentDate) = 0
AND ma.CustomerID = p.CustomerID
AND g.GroupID = ma.GroupID
AND (p.Type NOT LIKE '%Trade%' AND p.Type NOT LIKE '%Charity%')
AND p.Amount > 0
GROUP BY g.GroupName, p.Type
ORDER BY g.GroupName, p.Type
Compute Sum(sum(p.Amount))

/* Trade / Charity */

/* Don't display blank totals if there aren't any */
IF EXISTS (SELECT PaymentID
FROM Payments
WHERE DATEDIFF(Day, Date, @PaymentDate) = 0
AND Amount > 0
AND (Type LIKE '%Trade%' OR Type LIKE '%Charity%'))
BEGIN
SELECT "Trade/Charity"=Type,
Total=sum(Amount)
FROM Payments p
WHERE DATEDIFF(Day, Date, @PaymentDate) = 0
AND (Type LIKE '%Trade%' OR Type LIKE '%Charity%')
AND Amount > 0
GROUP BY Type
ORDER BY Type
Compute Sum(sum(Amount))
END
ELSE PRINT 'Trade / Charity: 0.00'

/* Credits */

/* Don't display blank totals if there aren't any */
IF EXISTS (SELECT p.PaymentID
FROM Payments p, MasterAccounts ma, Groups g
WHERE DATEDIFF(Day, p.Date, @PaymentDate) = 0
AND p.CustomerID = ma.CustomerID
AND ma.GroupID = g.GroupID
AND p.Amount < 0)
BEGIN
SELECT Credits=g.GroupName,
p.Type,
Total=sum(p.Amount)
FROM Payments p, MasterAccounts ma, Groups g
WHERE DATEDIFF(Day, p.Date, @PaymentDate) = 0
AND ma.CustomerID = p.CustomerID
AND g.GroupID = ma.GroupID
AND p.Amount < 0
GROUP BY g.GroupName, p.Type
ORDER BY g.GroupName, p.Type
Compute Sum(sum(p.Amount))
END
ELSE PRINT 'Credits: 0.00'

For more information about this list (including removal) go to:
http://www.iea-software.com/support/maillists/liststart