Since a couple of people have had this question, I snagged to
query from profiler. Its dynamically generated based on a
what kind of batch you are trying to do (ie, renewals, cc,
BT, invoices, etc).
This is for Renewals. For CC, you need to change the PayMethod IN
like to "Credit Card"
SELECT ma.CustomerID
FROM MasterAccounts ma, SubAccounts sa, BillingCycles bc
WHERE ma.CustomerID = sa.CustomerID
AND ma.BillingCycleID = bc.BillingCycleID
AND ma.Active=1 AND sa.Active=1
AND ma.PayMethod IN ('Invoice', 'Purchase Order', 'Renewal')
AND (IBillDays >= DateDiff(Day, GetDate(), ma.BilledThru) OR BilledThru
IS NULL)
AND (maExpireDate>=ma.BilledThru OR AutoBill=1 OR maExpireDate IS NULL)
AND NOT EXISTS (SELECT InvoiceID FROM Invoices
WHERE Invoices.CustomerID=ma.CustomerID
AND Invoices.EndDate > ma.BilledThru
AND Invoices.EndDate <> Invoices.StartDate
AND Invoices.Type <> 'VOID'
AND Invoices.Type <> 'Invoice' )
--Dale E. Reed Jr. Emerald and RadiusNT__________________________________________IEA Software, Inc. www.iea-software.com
For more information about this list (including removal) go to:http://www.iea-software.com/support/maillists/liststart