bug in automated invoicing

[geokill]

Again we are having an intermittent case of an account invoicing in error due to an error with automated invoicing.
I have made backups occasionally during the past few months just in case we ever had the problem of "crazy invoicing" again (it happend 2 times near the end of 2003).

Here are the facts I have put together:
------------------
1) The automated task to bill credit cards happens every 20 minutes.
2) The automated task for credit card billing was disabled 4-30-04 at the end of the day. This should not have an affect.
3) The automated task to invoice is every 30 minutes.
4) Automated tasks are set to never create invoices from 4pm to 6am (when the billing office isn't here).
5) Our current version of Optigold is 3.3
6) This customer has been invoiced monthly with no problems previously.
7) The time credit on his account has been properly given with each paid invoice.
8) It was invoiced by automated tasks on 4-29-04.
9) The customer was invoiced 20 times for one month of time credit each.
10) The customer was emailed only the last of the invoices (per the audit history).
11) The credit card automated task ran when it was re-enabled on 5-3-04...it billed all 20 invoices at one time (as it should).
12) Upon opening all the invoices in a backup of optigold on 4-15-04, prior to this error, the customer's expiration date was 10-1-01 (correct).
13) Upon opening all the invoices in the backup of optigold on 6-1-04, after the error, the customer's expiration date was 3-1-00.
--- The difference in the expiration date is exactly 20 months....
15) The establish date never moved.
16) The expiration date was not modified by any employee... they don't have permissions (other than 2 of us). Those with permissions would be logged to the notes and there are no notes.
17) The account type the customer is set to bills 3 days before the exp date.
18) The account type the customer is set to bills the credit card on the date due.


Hypothesis:
During the process of creating an invoice something went wrong and opti changed the expiration date (or tried to correct an error it was having).
Somehow it managed to keep creating a total of 20 invoices before the routine finished by sending out an email to the customer (he only received an email on the last invoice).


Misc notes:
====================
The following things were done by automated tasks:
On 4-29-04 optigold invoiced 20 times to the same customer for 107.08 each, all for 1 month of credit.

Nearly all were in sequential order:
206191808*
206191845
206191846
206191847
206191848
206191849
206191850
206191851
206191852
206191853
206191854
206191855
206191856
206191857
206191858
206191859
206191860*
206191862
206191863
206191864


On 4-29-04 optigold emailed only 1 of these invoices #206191864
On 5-3-04 optigold auto charged for all open invoices totaling 2141.60

[digitalpoint]

If you go to Maintenance & Preferences -> Clean Up -> Invalid Customer IDs, does anything come up by chance?

Are you running the web interface for Optigold?

[geokill]

We aren't running the web interface.

I also ran the clean-up and there are no invalid id's (as of 6-3-4). We do regularly run the clean-up routines so if there were any (in may) i can't tell now.

[digitalpoint]

Were all 20 invoices identical (and individually correct with just their billing cycle items)?

[geokill]

yes. They all showed the same thing exept for the invoice number.

[digitalpoint]

Is it something you are able to replicate with that customer (each time you run a generate invoices)?

[geokill]

I wish it were that easy to troubleshoot. It's totally random and occurs months apart (normally). This customer is invoicing normally after we fixed the invoices and expiration date.

Several of us have come to the conclusion that the invoices are not actually giving credit during the "create invoice" task, but once they are on the account they have the time credit box showing a value. Effectively, they aren't really modifying the expiration, but if you open or delete them it will make the account expire incorrectly.

It's as if the client (automated tasks machine) is dropping packets to the opti server and it continues to attempt to generate invoices until it receives a response (because none are actually on the account yet). Once it receives a response it stops creating it and places all it's queued onto the account.

This problem has ONLY happened using automated tasks to run invoicing. I've never been able to manually create it either. Even using a backup and "creating invoices" manually doesn't do it. So that is a main reason we don't think it's a problem in the database structure, but instead a connection related issue.

...just my 2¢.
Now if only i can make this program send me email notifications as it should (*wonders if our spam filter is grabbing it...)

[digitalpoint]

The automated task machine isn't local to the DB server? (On the same subnet)

[geokill]

They are each local. They are on the same switch and about 10' apart. I even verified the auto tasks machine didn't reboot or have other problems. It's stayed contstantly logged in except for doing windows updates. One of our engineers correctly pointed out if it was having connections issues opti loves to give a connection lost message (we get that on our wireless laptops sometimes if we close the lid).

Could editing the properties for "account type" while invoicing is running cause it? Im just fishing now....

[digitalpoint]

No, it should not affect it at all. The way the invoicing works is it puts a group of customers into a queue to be invoiced, so even if info on that customer record is changed from the tie they were queued to the time they were actually invoiced, it's still just one customer, so it should yield just one invoice.

Does the automated task machine have the Web Companion or data access companion plug-ins turned on for it's version of FileMaker by chance?

[dhardison]

We are running Optigold 3.34.
Same problems as described above.
Random occurences.
Any fix since this thread was started?

dhardison