Bug in the 15.x Rebuild Counter proc

UPDATE 07/08/10: ASI has given this issue a priority of HIGH. It was reported as SMR 216423.

I came across a bug in the Rebuild Counter process that you might want to be aware of that affects iMIS 15 and only if you are using online events. (Am I remembering correctly that the Prepaid invoice created by iMIS would have the invoice reference number in it already? If not, then online Orders module would also be open to this bug.)

The proc in question is called asi_RebuildCounters. It is what is executed if you use the DBRepair tool to run the Rebuild Counters. That proc is missing a little bit of logic that will throw off the Invoice_Ref counter if you do a Rebuild Counters while you have unposted Internet batches (or if you are using Separate Posting Cycle).

The proc only checks the Invoice table to find the maximum value used, but that's wrong to stop there. If you have an unposted event registration, then iMIS has not yet created the Invoice record, but it has allocated an Invoice_Ref counter and used it in both Trans and Orders.

The asi_RebuildCounters has is this statement only:

UPDATE Counter SET LAST_VALUE = (SELECT COALESCE(MAX(REFERENCE_NUM), 1) FROM Invoice), LAST_UPDATED = getdate(), UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Invoice_Ref'

I did a trace on iMIS 10.6 and it does have this issue covered. It has the statement above but then it is followed by this statement:

update Counter set LAST_VALUE= isnull((select max(Trans.INVOICE_REFERENCE_NUM) from Trans),1),LAST_UPDATED=GETDATE(),UPDATED_BY='MANAGER' where COUNTER_NAME='Invoice_Ref'  and  isnull((select max(Trans.INVOICE_REFERENCE_NUM) from Trans),1) > LAST_VALUE  

An alternative way to do this would be to do it in a single statement like this:

UPDATE Counter
SET LAST_VALUE = (select max(invoice_ref) from
                    (SELECT COALESCE(MAX(REFERENCE_NUM), 1) as    

                                            invoice_ref FROM Invoice
                        UNION
                    SELECT COALESCE(MAX(INVOICE_REFERENCE_NUM), 1)                                         as invoice_ref FROM Trans) i),
            LAST_UPDATED = getdate(),
            UPDATED_BY = 'MANAGER'
WHERE COUNTER_NAME = 'Invoice_Ref'

What brought this to light was that a user entered an event registration and then applied a payment to it in Service Central. However, the payment did not update the Order balance that displays in the Event registration window. That was because the payment was applied to the other Order with the same invoice reference num. Turned out there were 24 orders sharing 12 unique invoice reference numbers.

Comments

[Bruce Wilson]

Good catch, but I wonder if this probably may have existed even in iMIS 10? The Build Counters button in Table Analysis probably didn't have this sophisticated logic either.

[Cathy]

Bruce,
I did mention in my post that I did run a SQL trace on 10.6 and found it had the logic, so it does look to me like this was something that got lost in the transition to having it based in a stored proc in version 15.