Re: Can't Invoice Next Term

David Khoury ( dave@flex.com.au )
Wed, 18 Feb 1998 10:25:06 +1100

>John Lange wrote:
>>
>> Emerald 2.2.38
>>
>> This problem just reared it's ugly little head.
>>
>> We have about 10 out of 1000 customers that we cannot do an "Invoice Next
>> Term". Emerald also did "NOT" create an invoice for these customers at the
>> begining of the month.
>>
>> Now "OF COURSE" these customers are calling & complaining that they didn't
>> get a bill - "Why are we cut off".
>>
>> We can still NOT do an invoice next term, it says "NO Invoices To Create".
>> Paid thru, Expire, etc are all set to 2/15/98. We should be able to do
this.
>
>Typically when this happens, its because there is an existing invoice
>that has an end date after the customers expire date. Emerald will NOT
>created over-lapping invoices. Moving the expiredate back is one thing
>that can cause this. The same routine does the batch and single
>invoice, so that same results will come from it.
>
>--
>Dale E. Reed Jr. (daler@iea-software.com)

I run this sql script after every batch renewal. It will highlight any
users that have Invoices with an end date greater than the expiry date.
Try it out and see if it shows those 10 customers that cannot be renewed.

------------------------------
SELECT ma.LastName, ma.FirstName
FROM MasterAccounts ma
WHERE ma.Active=1 AND
EXISTS (SELECT InvoiceID
FROM Invoices
WHERE Invoices.CustomerID=ma.CustomerID AND
Invoices.EndDate > ma.maExpireDate AND
Invoices.Type <> 'Void' AND
Invoices.Type <> 'Renewal' AND
NOT EXISTS (SELECT InvoiceID
FROM Invoices
WHERE Invoices.CustomerID=ma.CustomerID AND
Invoices.EndDate > ma.maExpireDate AND
Invoices.Type = 'Renewal' ))
-------------------------------

If you DO find a few customers that have this problem, the way I fix it
is to find out the ID of the invoice that is causing the problem and then
set the end date on that invoice to the same as the Expiry date. The
following script should do the trick.

-------------------------------
DECLARE @InvID integer
SELECT @InvID = {Type in number of the Invoice you want to change}

UPDATE invoices
SET enddate = (SELECT maexpiredate FROM masteraccounts, invoices
WHERE masteraccounts.customerid = invoices.customerid AND
invoices.invoiceid = @InvID)
WHERE invoiceid = @InvID
-------------------------------

Standard disclaimer applies to the above script: if it bungles your
system, don't blame me. I am not responsible, and I have never been
responsible ... you just have to ask my family for proof of that *grin*.

--------------------------------------#------------------------------------
David Khoury | "Where's the KABOOM ??
Technical Manager & Comms Engineer | There was supposed to be an
dave@flex.com.au | earth shattering KABOOM!!"
http://www.flex.com.au/~dave | Marvin the Martian
--------------------------------------#------------------------------------