Subject RE: [firebird-support] Generators, revisited
Author Alan McDonald
> Personally I use a 2-stage approach for invoices. I run a batch
> of invoices,
> that all get a PK from a generator (meaningless PK, just a unique
> number to
> force RI). The user can view and edit those invoices that did not get an
> invoicenumber yet (so they are not invoices yet). In a second
> run, the user
> can add invoicenumbers to this batch. After that invoices can not
> be edited
> anymore. For the invoicenumber I use a generator and a prefix the user can
> assign. This works for me on multiple clients that create thousands of
> invoices a year.
> Basically use generators what they are made for (let the engine increment
> them).
> Just my 2 cents
> Benno

This is, IMO, the only way sequential numbers should be attacked. I'm
pleased to see someone else using this method.
For full auditability, invoice numbers are therefore never cancelled and
re-used (as in some methods recommended). Nor do they ever have gaps.
Instead, a credit note is generated which reverses the invoice if ever the
invoice is found to be incorrect after posting. These credit notes are also
sequentially numbered in batches and relate to the invoice number to which
they credit. Credit note numbers also do not have gaps - only the invoice
numbers to which they relate have gaps.
The only issue with this method is to carefully and atomically house the
batch in it's own transaction and ensure that no matter what, there is no
exception in posting the batch contents. This is easiest when the operation
is only an insert operation and client side carefully examines the field
contents to be inserted before executing.
I'm quite sure non-accounting based tasks can also use this same method of
ensuring "no-gap sequentially-numbered-documents".