Re: [Emerald] Re: Emerald.exe 2.5.322

Dale E. Reed Jr. ( (no email) )
Sat, 26 Feb 2000 15:39:44 -0800

PowerNet wrote:
>
> I just spoke with my billing person in detail.
> She is very confused. The reason she is confused is because we actually are using a manual list we check every day against the Emerald batch to make sure they match. It is correct with the 300 invoiced services.
> We don't know where to start.
> We just last week inventoried every customer file.
>
> I have an idea to avoid creating all the invoices that may not be correct.
>
> Is there an SQL query I can run to get me the detail of what the 327 (196 invoices) batch was doing so I can print it out and look at it before we create all these invoices?

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