Re: [Emerald] Balance Forward On Upgrade

Josh Hillman ( (no email) )
Tue, 26 Jan 1999 14:48:55 -0500

From: Jason Powell <>
> I was wondering if anyone else experienced this problem. When we
>upgrade to the 2.5 version of emerald, a large amout of our users now have
>credit in the balance field. Is there a script you can run to reset the
>balance to 0.....without having to go to every user and manually resetting

Here's one:

/* This is a cursor that walks each customer and sets their balance
/* based on thier last non-voided renewal (remember thats the only one that
/* counted in 2.2 and lower). Also, the release scripts that update a
/* database flips the balance, which is all about 99% of the users need.

DECLARE @custid int, @invid int, @balance money, @amount money

FOR Select CustomerID, Balance
From MasterAccounts
Order By CustomerID

OPEN ub_cursor

FETCH NEXT FROM ub_cursor INTO @custid, @balance
WHILE (@@fetch_status <> -1)
Select @invid = 0

Select @invid = (Select Max(i.InvoiceID)
From Invoices i, InvoiceItems ii
Where CustomerID = @CustID
and i.InvoiceID = ii.InvoiceID
AND i.Type <> 'VOID'
AND i.type not like '%invoice%'
AND ii.PaymentID is NULL)
IF @invid > 0
select @amount=amount From invoices Where InvoiceID = @invid
select @amount=0

Select @custid, @balance, @invid, @amount

Update MasterAccounts
Set Balance = @amount
Where Current of ub_Cursor

FETCH NEXT FROM ub_cursor INTO @custid, @balance

CLOSE ub_cursor
DEALLOCATE ub_cursor

For more information about this list, including removal,
please see