From: "Dale E. Reed Jr." <firstname.lastname@example.org> Subject: RE: [Emerald] 2.5.342 / Unpaid Invoices Date: Fri, 20 Sep 2002 13:41:04 -0700 Message-ID: <LAELKJHLOBGMNAFCOPIEEENDCBAA.email@example.com>
> I need a SQL query that returns the -EXACT- information that is
> found in the "Invoice" screen. I.E. a query that will duplicate the data
> that is displayed when a user selects "Show Unpaid Only" and hits the
> "Seach" button in the Invoices screen.
> I can get this information, obviously, live from the screen, but
> our accountant wants to snapshot the status of open invoices before doing
> payments. (Why? I don't know). If anyone could help me out, I would
> appreciate it.
For Emerald 2.5 only:
From Invoices i, MasterAccounts ma
Where i.CustomerID = ma.CustomerID
AND EXISTS (Select ItemID From InvoiceItems ii Where i.InvoiceID =
ii.InvoiceID and PaymentID IS NULL)
AND i.Type <> 'VOID'
AND ma.Balance > 0
Order By ma.CustomerID, i.InvoiceID DESC