Because this is currently related to Emerald 2.1, the blances are
irrelevant--I'd guess that about 95% of all balances are incorrect, but
because they were never used for anything, I ignored them. I generate my
current accounting reports based on other valid information (invoices,
payments, etc.) rather than using ma.Balance. I'll end up rewriting these
reports for Emerald 2.5 though since that field is used heavily used in it
and works correctly (I think). A while back, I wrote a script to fix all of
the balances (for the upgrade to 2.5) and at the same time, you wrote one
also. Although the two scripts are vastly different from eachother they
produce identical results. Each time I transfer the 2.1 production database
to my testing machine (for Emerald 2.5), I run (your) balance-fixing script
(and also one or two other necessary fixes) before doing any accounting work
with E2.5.
On the occasion that I might use the script below with Emerald 2.5, I'd also
run the balance-fixing script (at least for those accounts).
Josh
>Josh Hillman wrote:
>> Would the following script work if I wanted to void all unpaid,
non-voided
>> invoices associated with MBRs that have been deactivated? I'm guessing
this
>> may change the status of an invoice a few times in a row if there are
>> multiple InvoiceItems associated with that InvoiceID.
>> This is relative to Emerald 2.1.11, not 2.5.
>>
>> update i
>> set i.Type = 'Void',
>> i.LastModifyDate=getdate(),
>> i.LastModifyUser=user_name()
>> from invoices i, invoiceitems ii, masteraccounts ma
>> where i.invoiceid = ii.invoiceid
>> and ma.customerid = i.customerid
>> and ii.paymentid is null
>> and i.type != 'void'
>> and ma.active = 0